[
https://issues.apache.org/jira/browse/CALCITE-2246?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16432150#comment-16432150
]
Julian Hyde commented on CALCITE-2246:
--------------------------------------
Calcite is more lenient than MySQL. It allows duplicate column aliases. (You’ll
get an error if you don’t try to reference the ambiguous column.)
The fix would be for the SQL generator to rename columns to ensure aliases are
unique.
By the way there is at least one redundant “(select * from (...))” in that
query. We should flatten that out.
> column alias in view will cause duplicate column exception
> -----------------------------------------------------------
>
> Key: CALCITE-2246
> URL: https://issues.apache.org/jira/browse/CALCITE-2246
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.16.0
> Environment: using mysql 5.7 database
> Reporter: Yongqian Wang
> Assignee: Julian Hyde
> Priority: Minor
>
> I have a view like this , testdb is a mysql datasource , i have a view with
> a join statements
> {code:java}
> "name": "test",
> "type": "view",
> "sql": [
> "select t0.\"id\" as \"id\", t0.\"name\" as name from TESTDB.\"a\" t0
> left join TESTDB.\"b\" t1 on t0.\"id\" = t1.\"id\""
> {code}
> when i try to execute query like "select * from view.test limit 1", i got
> this exception
> {code:java}
> java.sql.SQLException: exception while executing query: while executing SQL
> [SELECT `t1`.`id`, `t1`.`name` AS `NAME`
> FROM (SELECT *
> FROM (SELECT *
> FROM `a`) AS `t`
> LEFT JOIN (SELECT `id`
> FROM `b`) AS `t0` ON `t`.`id` = `t0`.`id`) AS `t1`]
> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
> Duplicate column name 'id'
> {code}
>
> which t1 return two "id" column
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
