drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1(a int, b int, c int) distributed by ('a');
create table t2(a int, b int, c int) distributed by ('a');
create table t3(a int, b int, c int) distributed by ('a');
insert into t1 values(1,2,3),(3,5,6),(7,8,9);
insert into t2 values(1,2,3),(2,2,3),(3,3,3),(3,5,6);
insert into t3 values(1,1,1),(1,2,3),(2,2,2),(3,3,3),(4,5,6),(7,8,9),(null,null,null);
--结果错误
gc：
gbase> select a,b,c from t3 where  t3.a in (select t2.a from t1 left join t2 on t1.b=t2.a and t2.b is not  null where t3.c=t2.c or t2.b is null) ;
Empty set (Elapsed: 00:33:49.17)
gnode:
gbase> select a,b,c from t3 where  t3.a in (select t2.a from t1 left join t2 on t1.b=t2.a and t2.b is not  null where t3.c=t2.c or t2.b is null) ;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    2 |    2 |
+------+------+------+
1 row in set (Elapsed: 00:00:00.07)

left join 为相关子查询，并且相关子查询join条件有右表单表条件，集群优化器将右表条件下推，但查询计划未正确上拉，导致结果集错误。

select a,b,c from t3 where  t3.a in (select t2.a from t1 left join t2 on t1.b=t2.a and t2.b is not  null where t3.c=t2.c or t2.b is null) ;

select a,b,c from t3 where  t3.a in
(select tt.a from t1 left join (select a, b, c from t2 where t2.b is not null) tt on t1.b=tt.a   where t3.c=tt.c or tt.b is null)

 您需要登录后才可以回帖 登录 | 立即注册