[Bug] Return Unknown column when left join a view within multiple inner join query statement.

[lide-reed]

Search before asking

• [X] I had searched in the issues and found no similar issues.

Version

doris 1.1.3-rc02

What's Wrong?

MySQL [db]> select -> f1.k1 -> from ( -> select -> t2.c as k1 -> from t5 -> inner join t2 on t2.x=t5.x -> inner join t4 on t2.a=t4.a and t2.x in ('ZCR', 'ZDR') -> ) f1 -> left join v1 on f1.k1=v1.k1; ERROR 1054 (42S22): errCode = 2, detailMessage = Unknown column 'x' in 't5'

What You Expected?

No error and do the query.

How to Reproduce?

DROP table if exists t1; DROP table if exists t2; DROP table if exists t4; DROP table if exists t5; DROP view if exists v1;

CREATE TABLE t2 ( a varchar(1) NULL COMMENT "", x varchar(1) NULL COMMENT "", c varchar(1) NULL COMMENT "" ) ENGINE=OLAP UNIQUE KEY(a) DISTRIBUTED BY HASH(a) BUCKETS 8;

CREATE TABLE t5 ( x varchar(5) NULL COMMENT "", d varchar(3) NULL COMMENT "" ) ENGINE=OLAP UNIQUE KEY(x) DISTRIBUTED BY HASH(x) BUCKETS 8;

CREATE TABLE t4 ( a varchar(1) NULL COMMENT "", e varchar(1) NULL COMMENT "" ) ENGINE=OLAP UNIQUE KEY(a) DISTRIBUTED BY HASH(a) BUCKETS 8;

CREATE TABLE t1 ( k1 varchar(20) NULL COMMENT "", dt date NULL COMMENT "" ) ENGINE=OLAP UNIQUE KEY(k1) DISTRIBUTED BY HASH(k1) BUCKETS 6;

CREATE VIEW v1 AS SELECT k1 FROM t1 WHERE dt = ( SELECT max(dt) FROM t1 );

select f1.k1 from ( select t2.c as k1 from t5 inner join t2 on t2.x=t5.x inner join t4 on t2.a=t4.a and t2.x in ('ZCR', 'ZDR') ) f1 left join v1 on f1.k1=v1.k1;

Anything Else?

No response

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[hf200012]

mysql> select
    -> f1.k1
    -> from (
    -> 
    -> select
    -> bbb.c as k1
    -> from t5 aaa
    -> left join t2 bbb on bbb.x=aaa.x
    -> left join t4  ccc on bbb.a=ccc.a and bbb.x in ('ZCR', 'ZDR')
    -> ) f1
    -> left join v1 on f1.k1=v1.k1;
Empty set (0.01 sec)

The inner join bug Left join is normal

[hf200012]

It can be avoided by rewriting sql first

select
f1.k1
from (
select
t2.c as k1
from t5
inner join t2 on t2.x=t5.x
inner join t4 on t2.a=t4.a WHERE t2.x in ('ZCR', 'ZDR')
) f1
left join v1 on f1.k1=v1.k1;