[I] PostgreSQL reports wrong column label case for unaliased function projections [shardingsphere]

["viminizer (via GitHub)" <gi...@apache.org>]

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

   ## Bug Report
   
   ### Which version of ShardingSphere did you use?
   
   master @ d26c87dc, and released 5.5.3 (reproduced on both)
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   
   Both (kernel-level binder). Reproduced end to end on ShardingSphere-Proxy.
   
   ### Expected behavior
   
   PostgreSQL folds unquoted identifiers to lower case, so the column label of an unaliased function projection is the catalog function name in lower case. A quoted function name keeps its case and is reported without the quote characters.
   
   ```
   SELECT ABS(user_id) FROM t_order;      -- label: abs
   SELECT "MyFunc"(user_id) FROM t_order; -- label: MyFunc
   ```
   
   ShardingSphere should report the same labels.
   
   ### Actual behavior
   
   The proxy echoes back whatever case the user typed, and leaks quote characters for quoted names.
   
   Backend is PostgreSQL 16, `t_order` sharded across `t_order_0` and `t_order_1`.
   
   | Query | PostgreSQL | ShardingSphere-Proxy 5.5.3 |
   |---|---|---|
   | `SELECT ABS(user_id) FROM t_order` | `abs` | `ABS` |
   | `SELECT AbS(user_id) FROM t_order` | `abs` | `AbS` |
   | `SELECT MYABS(user_id) FROM t_order` | `myabs` | `MYABS` |
   | `SELECT "MyFunc"(user_id) FROM t_order` | `MyFunc` | `"MyFunc"` |
   | `SELECT abs(user_id) FROM t_order` | `abs` | `abs` |
   | `SELECT SUM(user_id) FROM t_order` | `sum` | `sum` |
   
   Clients that look up a column by name, for example `rs.getObject("abs")`, do not find it unless they happen to have typed the function in lower case.
   
   ### Reason analyze (If you can)
   
   `PostgreSQLProjectionIdentifierExtractor#getColumnNameFromExpression` (infra/binder/dialect/postgresql/.../PostgreSQLProjectionIdentifierExtractor.java lines 44-57) has two branches that disagree:
   
   ```java
   if (innerExpressionSegment instanceof FunctionSegment) {
       return ((FunctionSegment) innerExpressionSegment).getFunctionName();          // raw parsed text
   }
   if (innerExpressionSegment instanceof AggregationProjectionSegment) {
       ...
       return getColumnNameFromFunction(aggregationProjectionSegment.getType().name(), ...);   // lower cased
   }
   ```
   
   `getColumnNameFromFunction` lower cases. The aggregation branch uses it; the function branch does not.
   
   That asymmetry explains why `SUM` is correct while `ABS` is not. `AggregationProjection#getColumnLabel` (line 88) passes `AggregationType#name()`, which is always upper case, so that path had to fold to work at all. The function branch receives the raw source text produced by the parser through ANTLR `getText()`, so it is correct only when the user happens to type lower case.
   
   The label reaches the client through `QueryHeaderBuilderEngine#build` (proxy/backend/core/.../QueryHeaderBuilderEngine.java lines 74 and 93), which prefers `projection.getColumnLabel()` over the backend result set label, so the wrong value is user visible rather than internal only.
   
   The project already has the correct rule for this, one level up in `ProjectionIdentifierExtractEngine#getIdentifierValue` (lines 42-48):
   
   ```java
   if (QuoteCharacter.NONE != identifierValue.getQuoteCharacter()) {
       return identifierValue.getValue();
   }
   return ... dialect extractor ...
   ```
   
   Quoted identifiers are protected from case folding; unquoted ones are folded by the dialect. That guard is why quoted aliases such as `SELECT user_id AS "MyAlias"` already report correctly. Function names never received the same treatment.
   
   ### Steps to reproduce the behavior
   
   1. Start PostgreSQL 16 and create two shards.
   
   ```sql
   CREATE TABLE t_order_0 (order_id int PRIMARY KEY, user_id int);
   CREATE TABLE t_order_1 (order_id int PRIMARY KEY, user_id int);
   INSERT INTO t_order_0 VALUES (2,20),(4,40);
   INSERT INTO t_order_1 VALUES (1,10),(3,30);
   CREATE FUNCTION myabs(int) RETURNS int AS $$ SELECT abs($1) $$ LANGUAGE sql;
   CREATE FUNCTION "MyFunc"(int) RETURNS int AS $$ SELECT $1 $$ LANGUAGE sql;
   ```
   
   2. Start ShardingSphere-Proxy with `t_order` sharded by `order_id % 2` over those tables.
   
   3. Connect with psql and compare the column headers against the same queries run directly against PostgreSQL.
   
   The existing unit test also documents the current behavior. `PostgreSQLProjectionIdentifierExtractorTest` asserts three values for the same concept, and one disagrees with the other two:
   
   | line | case | asserts |
   |---|---|---|
   | 59 | `getColumnNameFromFunction("Function", ...)` | `"function"` |
   | 84 | window aggregation `MAX` | `"max"` |
   | 74 | `getColumnNameFromExpression(... FunctionSegment "SUM" ...)` | `"SUM"` |
   
   ### Additional notes
   
   `OpenGaussProjectionIdentifierExtractor` delegates every method to `PostgreSQLProjectionIdentifierExtractor`, so openGauss shows the same behavior and its test carries the same `"SUM"` expectation. openGauss folds identifiers to lower case as PostgreSQL does, so the correction applies there too.
   
   The `getColumnNameFromSubquery` TODO in the same class is a separate gap and is not addressed here.
   


-- 
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] PostgreSQL reports wrong column label case for unaliased function projections [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu closed issue #39392: PostgreSQL reports wrong column label case for unaliased function projections
URL: https://github.com/apache/shardingsphere/issues/39392


-- 
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