You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "hyqf2021 (via GitHub)" <gi...@apache.org> on 2026/07/16 11:26:44 UTC

[I] Column alias is unavailable in result-set metadata when using sqlx with ShardingSphere-Proxy 5.5.3 [shardingsphere]

hyqf2021 opened a new issue, #39159:
URL: https://github.com/apache/shardingsphere/issues/39159

   ## Bug Report
   
   For English only, other languages will not be accepted.
   
   Before reporting this bug, I have:
   
   - [x] Searched open and closed GitHub issues.
   - [x] Read the ShardingSphere documentation.
   
   ### Which version of ShardingSphere did you use?
   
   Apache ShardingSphere-Proxy 5.5.3.
   
   ### Which project did you use?
   
   ShardingSphere-Proxy.
   
   Frontend protocol: `MySQL >`
   
   Backend database and version: `MySQL 8.0.32`
   
   ### Expected behavior
   
   When executing the following query through ShardingSphere-Proxy:
   
   ```sql
   SELECT MAX(event_id) AS id FROM events;
   ```
   
   the result-set metadata should expose the column alias as `id`.
   
   A Rust SQLx client should be able to retrieve the value by column name:
   
   ```rust
   let max_id: Option<i64> = row.try_get("id")?;
   ```
   
   ### Actual behavior
   
   The query executes successfully and returns the correct value, but SQLx cannot find a column named `id`.
   
   The error is similar to:
   
   ```text
   ColumnNotFound("id")
   ```
   
   The value can still be retrieved by column index:
   
   ```rust
   let max_id: Option<i64> = row.try_get(0usize)?;
   ```
   
   The same SQL behaves differently depending on the connection path:
   
   | Connection path | Result |
   | --- | --- |
   | Command-line client connected to ShardingSphere-Proxy | The column name is displayed as `id` |
   | SQLx connected directly to the backend database | `try_get("id")` works |
   | SQLx connected to ShardingSphere-Proxy | `try_get("id")` fails |
   | SQLx connected to ShardingSphere-Proxy using column index `0` | Works |
   
   ### Steps to reproduce
   
   A minimal query is:
   
   ```sql
   SELECT MAX(event_id) AS id FROM events;
   ```
   
   Rust reproduction code:
   
   ```rust
   use sqlx::{Column, Row};
   
   let row = sqlx::query("SELECT MAX(event_id) AS id FROM events")
       .fetch_one(&pool)
       .await?;
   
   for (index, column) in row.columns().iter().enumerate() {
       println!("column[{index}] = {:?}", column.name());
   }
   
   let max_id_by_index: Option<i64> = row.try_get(0usize)?;
   println!("value by index: {:?}", max_id_by_index);
   
   let max_id_by_name: Option<i64> = row.try_get("id")?;
   println!("value by name: {:?}", max_id_by_name);
   ```
   
   The last `try_get("id")` call fails when connected through
   ShardingSphere-Proxy.
   
   Actual output from `row.columns()`:
   
   ```text
   <insert the actual output here>
   ```
   
   ### Additional context
   
   SQLx `query()` uses prepared statements, while the command-line client
   normally uses the text query protocol.
   
   Therefore, this may be a difference between the result-set metadata returned
   for a normal query and the metadata returned for a prepared statement.
   
   The alias is returned correctly by the text query path, but it appears to be
   missing or different in the prepared-statement metadata.
   
   Retrieving the column by ordinal is only a workaround. The expected behavior
   is for ShardingSphere-Proxy to return the SQL alias consistently, as the
   backend database does.
   
   ### Environment
   
   - ShardingSphere-Proxy: `5.5.3`
   - SQLx: `0.7.2>`
   - Rust: `1.91.1>`
   - Frontend protocol: `MySQL`
   - Backend database: `8.0.32`
   - Operating system: `ubuntu24.04`


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Column alias is unavailable in result-set metadata when using sqlx with ShardingSphere-Proxy 5.5.3 [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #39159: Column alias is unavailable in result-set metadata when using sqlx with ShardingSphere-Proxy 5.5.3
URL: https://github.com/apache/shardingsphere/issues/39159


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Column alias is unavailable in result-set metadata when using sqlx with ShardingSphere-Proxy 5.5.3 [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #39159:
URL: https://github.com/apache/shardingsphere/issues/39159#issuecomment-5003630577

   Hi @hyqf2021, thanks for reporting this. This is a duplicate of [#38507](https://github.com/apache/shardingsphere/pull/38507), which already covers the same root cause on `master`.
   
   `MAX(event_id) AS id` is a non-`ColumnProjection` projection. In ShardingSphere 5.5.3, the MySQL `COM_STMT_PREPARE` path creates its `ColumnDefinition41` metadata with an empty column name. This explains why the text-protocol client displays `id`, while SQLx cannot resolve `try_get("id")`.
   
   #38507 added backend prepared-metadata probing for non-`ColumnProjection` projections and writes the resolved `columnLabel` into the protocol column name. The change was merged as commit [`a2cc4e4741e574f74ab599e4c966cae62e0445e1`](https://github.com/apache/shardingsphere/commit/a2cc4e4741e574f74ab599e4c966cae62e0445e1) after 5.5.3 and is assigned to milestone 5.5.4.
   
   Please verify with a current `master` build or with 5.5.4 once available. If it still reproduces on a version containing #38507, please provide the real `row.columns()` output—the current issue contains a placeholder—and the related Proxy logs so we can determine whether the metadata probe fell back or a different path is involved.
   
   I suggest labeling this as `type: duplicate`, `in: proxy`, `in: protocol`, and `db: MySQL`, then closing it as a duplicate of #38507 unless it can be reproduced with the fix included.
   
   The reply above is based on the analysis below; the detailed reasoning is kept here for reference and follow-up contributors.
   
   ### Problem Understanding
   
   - **Issue:** [#39159](https://github.com/apache/shardingsphere/issues/39159) reports that SQLx cannot find alias `id` for `SELECT MAX(event_id) AS id FROM events`, although retrieval by index succeeds. `OBS-2`
   - **Topology:** ShardingSphere-Proxy 5.5.3, MySQL frontend protocol, MySQL 8.0.32 backend, SQLx 0.7.2. Governance mode and registry/config center are not provided and do not affect this frontend metadata classification. `OBS-2`
   - **Observation (`OBS-1`):** The [5.5.3 ShardingSphere-Proxy documentation](https://shardingsphere.apache.org/document/5.5.3/en/quick-start/shardingsphere-proxy-quick-start/) positions Proxy as a transparent database proxy supporting MySQL-protocol clients. This is broad compatibility context rather than an explicit per-field metadata specification.
   - **Observation (`OBS-2`):** The issue isolates the difference to SQLx's prepared-statement path: the text-query path and direct backend path expose `id`, while Proxy's prepared path raises `ColumnNotFound("id")`.
   - **Observed Evidence:** `OBS-1` through `OBS-7`
   
   ### Root Cause
   
   - **Observation (`OBS-3`):** In 5.5.3, [`MySQLComStmtPrepareExecutor.java:173`](https://github.com/apache/shardingsphere/blob/5.5.3/proxy/frontend/dialect/mysql/src/main/java/org/apache/shardingsphere/proxy/frontend/mysql/command/query/binary/prepare/MySQLComStmtPrepareExecutor.java#L173-L206) handles a non-`ColumnProjection` by creating a default `MySQLColumnDefinition41Packet`; its helper passes empty strings for the protocol metadata fields, including `name` and `orgName`.
   - **Observation (`OBS-4`):** [`ProjectionEngine.java:131`](https://github.com/apache/shardingsphere/blob/5.5.3/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/engine/ProjectionEngine.java#L131-L139) creates an `AggregationProjection` for aggregation expressions. [`AggregationProjection.java:78`](https://github.com/apache/shardingsphere/blob/5.5.3/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationProjection.java#L78-L88) resolves its column label from the explicit alias, so this query's logical label is `id`, but it is not a `ColumnProjection`.
   - **Observation (`OBS-5`):** [`MySQLColumnDefinition41Packet.java:43`](https://github.com/apache/shardingsphere/blob/5.5.3/database/protocol/dialect/mysql/src/main/java/org/apache/shardingsphere/database/protocol/mysql/packet/command/query/MySQLColumnDefinition41Packet.java#L43-L51) defines `name` and `orgName`, and [`MySQLColumnDefinition41Packet.java:89`](https://github.com/apache/shardingsphere/blob/5.5.3/database/protocol/dialect/mysql/src/main/java/org/apache/shardingsphere/database/protocol/mysql/packet/command/query/MySQLColumnDefinition41Packet.java#L89-L96) serializes them into the response. Empty constructor values therefore become empty client-visible metadata names.
   - **Observation (`OBS-6`):** [#38507](https://github.com/apache/shardingsphere/pull/38507) identifies incorrect `ColumnDefinition41Packets` for non-`ColumnProjection` prepared statements and adds backend metadata probing. Current [`MySQLProjectionMetadataResolver.java:68`](https://github.com/apache/shardingsphere/blob/master/proxy/frontend/dialect/mysql/src/main/java/org/apache/shardingsphere/proxy/frontend/mysql/command/query/binary/prepare/MySQLProjectionMetadataResolver.java#L68-L101) writes `queryHeader.getColumnLabel()` into the packet's `name` field.
   - **Observation (`OBS-7`):** #38507 was merged into `master` on March 19, 2026 as commit `a2cc4e4741e574f74ab599e4c966cae62e0445e1`, targets milestone 5.5.4, and appears in the [5.5.4 release notes](https://github.com/apache/shardingsphere/blob/master/RELEASE-NOTES.md#L77). ShardingSphere 5.5.3 predates that merge.
   - **Inference (`INF-1`):** Because the reported `MAX(...) AS id` is a non-`ColumnProjection`, the 5.5.3 empty-name behavior and the scope of #38507 form the same trigger, code path, and root-cause chain. Therefore, #39159 is a duplicate of #38507. `OBS-2`, `OBS-3`, `OBS-4`, `OBS-6`, `OBS-7`
   - **Confidence:** High
   
   ### Problem Analysis
   
   - **Issue Type:** Duplicate
   - **Fast Triage:** The issue provides version, mode, database, SQL, client version, expected behavior, and actual error. Although the actual `row.columns()` output remains a placeholder, the deterministic 5.5.3 code path is sufficient to classify the root cause. `OBS-2`, `OBS-3`, `OBS-5`
   - **Duplicate Evidence:** #38507 covers every non-`ColumnProjection` in the MySQL prepared-statement metadata path, not only its example expression. `MAX(...)` is an `AggregationProjection`, so the issue falls within that scope. `OBS-4`, `OBS-6`, `INF-1`
   - **Negative Check:** [#38517](https://github.com/apache/shardingsphere/pull/38517) is not the duplicate target. It fixes loss of the `PRIMARY_KEY` flag for aliased physical columns after the probe was introduced; it does not address an empty protocol column name. Likewise, reproduction on `master` or 5.5.4 could indicate probe fallback or another root cause and should be investigated separately.
   - **Label Recommendation:** `type: duplicate`, `in: proxy`, `in: protocol`, `db: MySQL`
   
   ### Problem Conclusion
   
   - **Evidence Confidence:** High. `OBS-2` through `OBS-7`, `INF-1`
   - **Impact Scope:** MySQL ShardingSphere-Proxy prepared-statement metadata for non-`ColumnProjection` SELECT items, including aggregation aliases. `OBS-2`, `OBS-3`, `OBS-4`, `OBS-6`
   - **Topology:** Proxy + Standalone/Cluster unknown; governance topology is not material to this frontend protocol path. `OBS-2`, `OBS-3`
   - **Issue Type:** Duplicate. `INF-1`
   - **Duplicate Of:** #38507. `OBS-6`, `INF-1`
   - **Fix PR:** #38507. `OBS-6`
   - **Merged In:** `master` commit `a2cc4e4741e574f74ab599e4c966cae62e0445e1`; milestone 5.5.4. `OBS-6`, `OBS-7`
   - **Recommended Labels:** `type: duplicate`, `in: proxy`, `in: protocol`, `db: MySQL`. `OBS-6`, `INF-1`
   - **Next Action:** Verify with current `master` or 5.5.4, then close as duplicate unless the behavior remains reproducible with #38507 included. If it remains, request the actual `row.columns()` output and Proxy logs to distinguish metadata-probe fallback from a new root cause. `OBS-2`, `OBS-6`, `OBS-7`, `INF-1`


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org