[Bug] PostgreSQL: UPDATE ... FROM (VALUES ...) AS t(col_list) fails with "Unknown column 't.X' in 'unknown clause'"

[adsk-duf]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.5.3 (Proxy, frontend = PostgreSQL, backend = PostgreSQL 15+)

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

The PostgreSQL-valid statement below should pass SQL binding and execute on the backend.

  UPDATE t1 f
  SET col_a = CASE WHEN t.col_a_type = 1 THEN t.col_a ELSE f.col_a END
  FROM (VALUES ('k1', 'v1', 1), ('k2', 'v2', 0)) AS t(pk, col_a, col_a_type)
  WHERE t.pk = f.pk;

Actual behavior

Binding fails with:
  Unknown column 't.col_a_type' in 'unknown clause'.
The same SQL runs fine when sent directly to PostgreSQL.

Reason analyze (If you can)

Root cause is in the PostgreSQL parser + binder interaction:

  1. parser/sql/engine/dialect/postgresql/.../PostgreSQLStatementVisitor.java#visitAliasClause flattens the alias column list into the alias name string rather than extracting it as individual column aliases:

  public ASTNode visitAliasClause(final AliasClauseContext ctx) {
      StringBuilder aliasName = new StringBuilder(ctx.colId().getText());
      if (null != ctx.nameList()) {
          aliasName.append(ctx.LP_().getText());
          aliasName.append(ctx.nameList().getText());
          aliasName.append(ctx.RP_().getText());
      }
      return new AliasSegment(..., new IdentifierValue(aliasName.toString()));
  }

  So AS t(pk, col_a, col_a_type) is parsed as a single alias name literally "t(pk,col_a,col_a_type)".

  2. SubqueryTableSegmentBinder.bind registers the derived-table context keyed by that full string (CaseInsensitiveString.of("t(pk,col_a,col_a_type)")). Subsequent references to owner t in the SET/WHERE clauses cannot find a match.
  3. Column resolution falls through all binder contexts and throws ColumnNotFoundException. Because the SET clause's parentSegmentType == SET_ASSIGNMENT is not in ColumnSegmentBinder.SEGMENT_TYPE_MESSAGES, the message label defaults to
  "unknown clause" — producing the confusing error text.

  Secondary (related) gap: visitSimpleSelect only fills projections from targetList(); the valuesClause alternative of simpleSelect is ignored, so a parenthesized (VALUES ...) subquery yields an empty ProjectionsSegment regardless.

  Reason analyze (summary)
  - PG parser does not surface the column-list of derived-table aliases as separate column aliases.
  - VALUES clause inside a subquery table yields no projections.
  - Combined, (VALUES ...) AS alias(col_list) references like alias.col are unresolvable during binding.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. Stand up SS Proxy with PostgreSQL frontend/backend.
2. Create a sharded table t1(pk, col_a) with sharding on pk.
3. Run the SQL above.

Example codes for reproduce this issue (such as a github link).

None required — minimal SQL above reproduces.

[daguimu]

Thanks for the very detailed root-cause analysis. Before I start on a PR, I would like to run the approach by a committer since the fix would touch the shared AliasSegment type.

Proposed approach:

1. Extend AliasSegment with an additive List<IdentifierValue> columnAliases field (empty by default so every existing caller and every non-PostgreSQL dialect stays behavior-identical).
2. Rework PostgreSQLStatementVisitor.visitAliasClause so that AS t(c1, c2, c3) fills columnAliases with [c1, c2, c3] instead of flattening into a single alias name like t(c1,c2,c3).
3. Extend PostgreSQLStatementVisitor.visitSimpleSelect to surface projections from the valuesClause alternative when targetList is absent, so (VALUES ...) as a subquery table no longer yields empty projections.
4. In SubqueryTableSegmentBinder, when the derived table's alias carries a non-empty columnAliases, use that list to override the projection column names registered in the binder context. Guarded by a non-empty check so other dialects and existing PostgreSQL alias usage without a column list keep the current binding behavior.

Tests would cover the reproduction UPDATE ... FROM (VALUES ...) AS t(col_list) flow, plus regression cases for existing alias usage (no column list) and for SELECT ... FROM (VALUES ...) AS t(col_list) to ensure the VALUES-projection path binds correctly.

Does this direction look acceptable, or would you prefer a different approach (for example keeping the composite alias string and splitting at bind time, or surfacing the column list through a dedicated segment instead of a field on AliasSegment)? Happy to adjust before writing the PR.

[adsk-duf]

Thanks for your attention on this. Your proposal sounds good to me.