[jira] [Comment Edited] (CALCITE-3850) AssertionError when converts a SQL parse tree into a relational algebra expression

["fa zheng (Jira)" <ji...@apache.org>]

    [ 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/12/20, 10:20 AM:
--------------------------------------------------------------

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;

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 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; 
but fail 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;

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)