一个很诡异的mysql left join 问题

* 问题描述

下午写业务sql,本来想通过B表过滤掉一部分A表的数据,A和B的关联条件是日期和渠道,但是当时不知道怎么想的在B表里用常量1 as enabled补出了一列,在A和B left join 后,无论怎么判断enabled都不是空,按理说,A表里有的,B表里没有的,在关联后B表的enabld字段是null,但是在where B.enabled is null过滤时,结果怎么也拿不到enabled 为null的数据

* 问题演示

t1是A表,t是B表,t造了一列常量1变成了t2,

select t1.* ,t2.enabled
from
(select "2020-01-01" as dt,"gdt" as channel,1 as cnt
union 
select "2020-01-01" as dt,"mix" as channel,2 as cnt
)t1
left join
(select t.*,1 as enabled
from
(select "2020-01-01" as dt,"gdt" as channel
union 
select "2020-01-01" as dt,"mix1" as channel)t
)t2 on t1.dt=t2.dt and t1.channel=t2.channel

关联后的执行结果如图1,这个时候还没看出什么异常


图1

接下来我希望拿到A表里有,B表里没有的数据,那我过滤enabled is null应该就可以了!!!,在上面的语句后面补上where enabled is null

加过滤条件竟然变成空集了

我想这不对呀,是不是因为没有包一层变成子查询的原因?


包了一层查询还是不行啊

想了一会儿也还是没明白为啥,后来我想,试试过滤为1的情况

完全出乎意料,为NULL的那条你出来是干啥的???

完全出乎意料,为NULL的那条你出来是干啥的???突然感觉好像enabled不再像普通的变量那样了,那我索性把关联的数据变成完全不匹配的情况,看看还能不能拿出enabled为1的情况

让A表和B表完全不匹配,但是在过滤B表中enabled=1时,还是能拿出来数据

分析

到这似乎已经看出来问题了,因为eanbled是从t表查询时补出来的一列常量形成了t2表,在t1t2左连接时,无论t2的关联结果如果,在where 判断时enabled都被当成了常量1,但是查询结果的显示是与关联结果一致的,如上图1所示,enabled的查询有1也有NULL的情况,并且这个问题包一层查询都解决补了,不知道这个是不是mysql的bug,还是说我这么用是不对的,还请大神指点!!!

结论

想用B表过滤A表的话直接用关联的字段,别自己瞎补列,谁知道能遇到什么诡异问题!

新的尝试

又跑去hive试了一下,发现hive过滤和显示结果是完全一致的


hive很稳啊

推荐阅读更多精彩内容