[
https://issues.apache.org/jira/browse/CALCITE-7642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
zzwqqq updated CALCITE-7642:
----------------------------
Description:
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 whose field
aliases are `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 aliases of `t1`.
The internal 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}
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 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}
For a case-insensitive dialect, the derived table `t1` has output columns `id`
and `ID`, which collide.
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}
I tested the sqls there: https://onecompiler.com/mysql/44udbnuqp
I think RelToSqlConverter should uniquify generated output names using the
target dialect's case-sensitivity.
> 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 whose
> field aliases are `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 aliases of
> `t1`.
> The internal 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)
