You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "zhengzi (JIRA)" <ji...@apache.org> on 2018/12/23 08:18:00 UTC
[jira] [Commented] (CALCITE-2753) ambiguous column error when push
down a query that has a sub-query after IN and a LIMIT keyword outmost to
jdbc datasource
[ 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)