[
https://issues.apache.org/jira/browse/CALCITE-7642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18094970#comment-18094970
]
zzwqqq commented on CALCITE-7642:
---------------------------------
I narrowed this issue to aliases generated by RelToSqlConverter for internal
derived tables.
I also updated the patch to match that scope. It does not uniquify user-visible
result names. It only uniquifies internal derived-table field aliases using
dialect.isCaseSensitive(), and when an internal alias changes, the outer SELECT
restores the original row type field name, for example `ID0 AS ID`.
The join case now demonstrates the problem: RelToSqlConverter wraps the first
join as an internal derived table `t1`; with caseSensitive(false), `t1` would
expose `id` and `ID`, which collide for later references. The patch makes the
internal aliases `id` and `ID0` while preserving the final output name `ID`.
Could you please take another look?
> RelToSqlConverter may generate duplicate aliases for internal derived tables
> in case-insensitive dialects
> ---------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7642
> URL: https://issues.apache.org/jira/browse/CALCITE-7642
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: zzwqqq
> Assignee: zzwqqq
> Priority: Major
> Labels: pull-request-available
>
> RelToSqlConverter may generate duplicate field aliases for internal derived
> tables when the target dialect is case-insensitive.
> A row type can contain field names that differ only by case, such as `id` and
> `ID`. These names should be preserved as the final output names. However,
> when RelToSqlConverter wraps such a result as an internal derived table,
> using those names directly as derived-table field aliases can make later
> references ambiguous for a case-insensitive dialect.
> Reproducer using a RelNode input:
> {code:java}
> @Test void testCaseInsensitiveJoinAliases() {
> final SqlDialect dialect =
> new MysqlSqlDialect(
> MysqlSqlDialect.DEFAULT_CONTEXT.withCaseSensitive(false));
> relFn(b -> {
> b.values(new String[]{"id"}, 1);
> b.values(new String[]{"ID"}, 2);
> final RelNode left = b.join(JoinRelType.INNER)
> .project(b.fields(), ImmutableList.of(), true)
> .build();
> return b.push(left)
> .values(new String[]{"x"}, 3)
> .join(JoinRelType.INNER)
> .project(ImmutableList.of(b.field(0), b.field(1)),
> ImmutableList.of(), true)
> .build();
> }).dialect(dialect).ok(expected);
> }
> {code}
> Currently, RelToSqlConverter may generate an internal derived table that
> exposes output columns `id` and `ID`:
> {code:sql}
> SELECT `t1`.`id`, `t1`.`ID`
> FROM (SELECT *
> FROM (SELECT 1 AS `id`) AS `t`,
> (SELECT 2 AS `ID`) AS `t0`) AS `t1`,
> (SELECT 3 AS `x`) AS `t2`
> {code}
> For a case-insensitive dialect, `id` and `ID` collide as field names exposed
> by the internal derived table `t1`.
> The generated internal derived-table aliases should be uniquified using
> `dialect.isCaseSensitive()`, while preserving the final output names:
> {code:sql}
> SELECT `t1`.`id`, `t1`.`ID0` AS `ID`
> FROM (SELECT `t`.`id`, `t0`.`ID` AS `ID0`
> FROM (SELECT 1 AS `id`) AS `t`,
> (SELECT 2 AS `ID`) AS `t0`) AS `t1`,
> (SELECT 3 AS `x`) AS `t2`
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
