[
https://issues.apache.org/jira/browse/CALCITE-5583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17755556#comment-17755556
]
libopeng commented on CALCITE-5583:
-----------------------------------
[~julianhyde] I'm sorry to bring up this issue again after such a long time, I
saw this [CALCITE-528].
{quote}Basically, yes. Rules should just use ordinals.
The row-type is a struct type, and therefore we have to give the fields names,
and those names have to be unique. I suppose that we could make sure that the
field names are always $0, $1 etc. But it's traditional, and useful, to try to
preserve meaningful field names for the benefit of a human debugging a plan.
(To do this, we sometimes have to assign field names like $f0 (where we can't
derive a field name from source), and we sometimes need to add suffixes to
ensure that field names are unique.)
I like that tradition, and don't feel a strong need to change it. But
developers need to remember that the field names cannot be relied upon.
{quote}
I agree. Can we rely on the field name to determine 'star'?
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> -------------------------------------------------------------------------------------------
>
> Key: CALCITE-5583
> URL: https://issues.apache.org/jira/browse/CALCITE-5583
> Project: Calcite
> Issue Type: Bug
> Reporter: libopeng
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> {code:java}
> LogicalProject(c0=[$0], c00=[$1])
> LogicalJoin(condition=[=($0, $1)], joinType=[inner])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]]){code}
> to sql
> {code:java}
> SELECT *
> FROM (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t"
> INNER JOIN (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0" {code}
> When this sql is a subquery, it can be wrong
> {code:java}
> select *
> from "EMPNO" t1
> left join (
> SELECT * FROM (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t" INNER JOIN
> (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0"
> ) t2
> on t1.EMPNO=t2."c0"{code}
> {color:#ff0000}fault: 'column reference "c0" is ambiguous' error when
> executing in postgresql{color}
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
