[
https://issues.apache.org/jira/browse/CALCITE-7642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
zzwqqq updated CALCITE-7642:
----------------------------
Description:
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.
was:
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 PostgresqlSqlDialect(
PostgresqlSqlDialect.DEFAULT_CONTEXT.withCaseSensitive(false));
final String expected = "SELECT \"t\".\"id\", \"t0\".\"ID\" AS \"ID0\"\n"
+ "FROM (VALUES (1)) AS \"t\" (\"id\"),\n"
+ "(VALUES (1)) AS \"t0\" (\"ID\")";
relFn(b -> {
b.values(new String[]{"id"}, 1);
b.values(new String[]{"ID"}, 1);
return b.join(JoinRelType.INNER)
.project(b.fields(), ImmutableList.of(), true)
.build();
}).dialect(dialect).ok(expected);
}
{code}
The projection does not specify duplicate aliases. The output names are
inferred from the projection input fields.
Currently, RelToSqlConverter emits:
{code:sql}
SELECT "t"."id", "t0"."ID"
FROM (VALUES (1)) AS "t" ("id"),
(VALUES (1)) AS "t0" ("ID")
{code}
I think RelToSqlConverter should uniquify generated output names using the
target dialect's case-sensitivity.
> 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)
