[
https://issues.apache.org/jira/browse/CALCITE-7642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18093905#comment-18093905
]
zzwqqq commented on CALCITE-7642:
---------------------------------
The pg example made the issue confusing. It is hard to construct a
case-insensitive PostgreSQL runtime example.
To make it clearer, I moved from pg and updated the reproducer to use a MySQL
dialect with `caseSensitive(false)` instead.
Could you please take another look and let me know whether the description is
clearer now?
> RelToSqlConverter may generate duplicate output column names for
> 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 can emit output column names that are distinct in a row
> type but not distinct for the target dialect.
> Reproducer using a RelNode input:
> {code:java}
> @Test void testCaseInsensitiveAliasesProjectJoin() {
> final SqlDialect dialect =
> new MysqlSqlDialect(
> MysqlSqlDialect.DEFAULT_CONTEXT.withCaseSensitive(false));
> final String expected = "SELECT `t1`.`id`\n"
> + "FROM (SELECT `t`.`id`, `t0`.`ID` AS `ID0`\n"
> + "FROM (SELECT 1 AS `id`) AS `t`,\n"
> + "(SELECT 2 AS `ID`) AS `t0`) AS `t1`,\n"
> + "(SELECT 3 AS `x`) AS `t2`";
> 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)), ImmutableList.of(), true)
> .build();
> }).dialect(dialect).ok(expected);
> }
> } {code}
> Currently, RelToSqlConverter emits:
> {code:sql}
> SELECT `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, the derived table `t1` has output columns
> `id` and `ID`, which collide. Expected:
> {code:sql}
> SELECT `t1`.`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}
> I tested the sql here: [https://onecompiler.com/mysql/44udbnuqp]
> I think RelToSqlConverter should uniquify generated output names using the
> target dialect's case-sensitivity.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
