[ https://issues.apache.org/jira/browse/CALCITE-3850?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17057770#comment-17057770 ]
fa zheng edited comment on CALCITE-3850 at 3/14/20, 7:14 AM: ------------------------------------------------------------- [~danny0405] Hi danny, I find the problem, it can execute successfully with sql: select * from (select * from emps)e join (select * from depts)d on (case when e.deptno=1 then e.deptno else e.deptno end) = d.deptno join (select * from sdepts)f on (case when e.deptno=1 then e.deptno else e.deptno end) =f.deptno; and select * from (select * from emps)e join (select deptno from depts)d on (case when e.deptno=1 then e.deptno else e.deptno end) = d.deptno join (select deptno from sdepts)f on (case when e.deptno=1 then e.deptno else e.deptno end) =f.deptno; but fail with sql: select * from (select empno,name,deptno from emps)e join (select * from depts)d on (case when e.deptno=1 then e.deptno else e.deptno end) = d.deptno join (select * from sdepts)f on (case when e.deptno=1 then e.deptno else e.deptno end) =f.deptno; Otherwise, if I use function if in flink sql, it only works with sql: select * from (select * from emps)e join (select * from depts)d on if (e.deptno=1 ,e.deptno ,e.deptno) = d.deptno join (select * from sdepts)f on if(e.deptno=1, e.deptno, e.deptno) =f.deptno; I run them in sqlline 1.8.0 with calcite 1.21.0 was (Author: faaronzheng): Hi danny, I find the problem, it can execute successfully with sql: select * from (select * from emps)e join (select * from depts)d on (case when e.deptno=1 then e.deptno else e.deptno end) = d.deptno join (select * from sdepts)f on (case when e.deptno=1 then e.deptno else e.deptno end) =f.deptno; and select * from (select * from emps)e join (select deptno from depts)d on (case when e.deptno=1 then e.deptno else e.deptno end) = d.deptno join (select deptno from sdepts)f on (case when e.deptno=1 then e.deptno else e.deptno end) =f.deptno; but fail with sql: select * from (select empno,name,deptno from emps)e join (select * from depts)d on (case when e.deptno=1 then e.deptno else e.deptno end) = d.deptno join (select * from sdepts)f on (case when e.deptno=1 then e.deptno else e.deptno end) =f.deptno; Otherwise, if I use function if in flink sql, it only works with sql: select * from (select * from emps)e join (select * from depts)d on if (e.deptno=1 ,e.deptno ,e.deptno) = d.deptno join (select * from sdepts)f on if(e.deptno=1, e.deptno, e.deptno) =f.deptno; I run them in sqlline 1.8.0 with calcite 1.21.0 > AssertionError when converts a SQL parse tree into a relational algebra > expression > ---------------------------------------------------------------------------------- > > Key: CALCITE-3850 > URL: https://issues.apache.org/jira/browse/CALCITE-3850 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.21.0 > Reporter: fa zheng > Priority: Major > > I execute following sql with calcite 1.21.0 in flink 1.10. When the two > conditions of left outer join are like"ON t1.a=t2.a" and "ON t1.a=t3.a ", it > execute successfully. However, when the two conditions of left outer join are > like "ON IF(t1.a='...',CAST(RAND(123) AS STRING),t1.a)=t2.a" and "ON > IF(t1.a='...',CAST(RAND(123) AS STRING),t1.a)=t3.a", it gets a assert error > in method getRootField. > It also can execute successfully with "ON t1.a=t2.a" and "ON > IF(t1.a='...',CAST(RAND(123) AS STRING),t1.a)=t3.a". > But failed with "ON IF(t1.a='...',CAST(RAND(123) AS STRING),t1.a)=t2.a" and > "ON t1.a=t3.a" > SELECT > ... > FROM > ( > SELECT > ... > ) t1 > LEFT OUTER JOIN > ( > SELECT > ... > ) t2 > ON ... > LEFT OUTER JOIN > ( > SELECT > ... > ) t3 > ON ... -- This message was sent by Atlassian Jira (v8.3.4#803005)