暖心向阳  发表于 2020-7-14 10:21 显示全部楼层 楼主
查询语句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) 结果错误。
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)

逆境成才  发表于 2020-7-14 10:39 显示全部楼层 沙发
问题分析:
left join 为相关子查询,并且相关子查询join条件有右表单表条件,集群优化器将右表条件下推,但查询计划未正确上拉,导致结果集错误。
问题解决:
无法通过参数规避,只能改写语句,把相关子查询中的left join右表,及其on条件写成from子查询。
原语句:
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)

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