[
https://issues.apache.org/jira/browse/CALCITE-2753?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16727830#comment-16727830
]
zhengzi commented on CALCITE-2753:
----------------------------------
the sql
[ select * from "aaa" where ID in ( select ID from "aaa
" where NAME=2) limit 100 ]
after calcite transformed will be
[SELECT `t1`.`ID0` AS `ID`, `t1`.`NAME`, `t1`.`HEIGHT` FROM (SELECT * FROM
(SELECT `ID` FROM `aaa` WHERE `NAME` = '2' GROUP BY `ID`) AS `t0` INNER JOIN
`aaa` AS `aaa0` ON `t0`.`ID` = `AAA0`.`ID` LIMIT 100) AS `t1`] .
the transformed sql push down to mysql database, mysql show error message
!error.png!
I think the star (*) does not expand , so mysql cannot recognize ID belong
to which table.
If the transformed sql is
[ SELECT `t1`.`ID0` AS `ID`, `t1`.`NAME`, `t1`.`HEIGHT` FROM (SELECT t0.ID,
aaa0.ID as ID0, aaa0.NAME, aaa0.HEIGHT FROM (SELECT `ID` FROM `aaa` WHERE
`NAME` = '2' GROUP BY `ID`) AS `t0` INNER JOIN `aaa` AS `aaa0` ON `t0`.`ID` =
`AAA0`.`ID` LIMIT 100) AS `t1` ] , mysql will execute it correctly.
> ambiguous column error when push down a query that has a sub-query after IN
> and a LIMIT keyword outmost to jdbc datasource
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-2753
> URL: https://issues.apache.org/jira/browse/CALCITE-2753
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.17.0
> Reporter: zhengzi
> Assignee: Julian Hyde
> Priority: Major
> Attachments: error.png
>
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
