planner: wrong results of null and 0

[fzhedu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

this error exsits regardless of mpp execution.
mysql -h 172.16.5.85 -P 52324 -uroot -D agg_sub200

2. What did you expect to see? (Required)

mysql :

 SELECT  table1 . `col_int` >= ( SELECT   SUM(  SUBQUERY1_t1 . `col_int_not_null_key` ) AS SUBQUERY1_field1 FROM `L` AS SUBQUERY1_t1 WHERE SUBQUERY1_t1 . `col_int_key` <> 4 ) AS field1 ,  table1 . `pk`  IN ( SELECT 3 UNION  SELECT 5 ) AS field2 ,  ( table1 . `col_varchar_binary_key` , table1 . `col_varchar_binary_key` )  IN ( SELECT 'k' , 'n' UNION ALL SELECT 'q' , 'e' ) AS field3 ,  ( table1 . `col_int_not_null` , table1 . `pk` )  IN ( SELECT 5 , 9 UNION  SELECT 5, 0 ) AS field4 ,  table1 . `col_int_key` != ANY ( SELECT   SUBQUERY5_t1 . `col_int_key` AS SUBQUERY5_field1 FROM ( `D` AS SUBQUERY5_t1 STRAIGHT_JOIN ( `C` AS SUBQUERY5_t2 INNER JOIN `I` AS SUBQUERY5_t3 ON (SUBQUERY5_t3 . `col_int_not_null_key` = SUBQUERY5_t2 . `pk`  ) ) ON (SUBQUERY5_t3 . `col_varchar_binary_not_null_key` = SUBQUERY5_t2 . `col_varchar_binary_key`  ) ) WHERE SUBQUERY5_t2 . `col_varchar_binary_key` > SUBQUERY5_t1 . `col_varchar_binary_key`   ) AS field5 FROM ( `E` AS table1 STRAIGHT_JOIN `C` AS table2 ON (table2 . `pk` = table1 . `col_int_not_null_key`  ) ) WHERE (  table1 . `col_varchar_binary_key` != ALL ( SELECT 'q' UNION  SELECT 'a' ) ) AND table1 . `col_varchar_binary_key`  IN ('k', 'x')   ;
+--------+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 | field5 |
+--------+--------+--------+--------+--------+
|      0 |      0 |      0 |      0 |   NULL |
|   NULL |      0 |      0 |      0 |      1 |
|   NULL |      0 |      0 |      0 |   NULL |
+--------+--------+--------+--------+--------+
3 rows in set (0.04 sec)

 SELECT distinct  table1 . `col_int` > ( SELECT 3 FROM DUAL ) AS field1 ,  ( 'q', 'e' )  IN ( SELECT   SUBQUERY2_t1 . `col_varchar_binary_not_null` AS SUBQUERY2_field1 , SUBQUERY2_t2 . `col_varchar_binary_not_null_key` AS SUBQUERY2_field2 FROM ( `J` AS SUBQUERY2_t1 RIGHT  JOIN `H` AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_varchar_binary_not_null` = SUBQUERY2_t1 . `col_varchar_binary_key`  ) )    ) AS field2 ,  table1 . `col_varchar_binary`  IN ( SELECT   SUBQUERY3_t1 . `col_varchar_binary_key` AS SUBQUERY3_field1 FROM ( `B` AS SUBQUERY3_t1 STRAIGHT_JOIN `A` AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_int_not_null_key` = SUBQUERY3_t1 . `pk`  ) ) WHERE SUBQUERY3_t1 . `col_varchar_binary` = 't'  ) AS field3 ,  table1 . `col_varchar_binary_not_null`  IN ( SELECT 'j' UNION  SELECT 't' ) AS field4 ,  table1 . `col_int_key` != ALL ( SELECT 5 UNION  SELECT 9 ) AS field5 ,  ( table1 . `col_int_key` , table1 . `pk` ) NOT IN ( SELECT   SUBQUERY6_t1 . `col_int` AS SUBQUERY6_field1 , SUBQUERY6_t1 . `col_int_not_null_key` AS SUBQUERY6_field2 FROM `L` AS SUBQUERY6_t1    ) AS field6 ,  ( table1 . `col_varchar_binary` , table1 . `col_varchar_binary_not_null_key` ) NOT IN ( SELECT 'q' , 'z' UNION  SELECT 'w' , 'e' ) AS field7 ,  table1 . `pk` > ( SELECT   MAX(  SUBQUERY8_t1 . `col_int_key` ) AS SUBQUERY8_field1 FROM ( `G` AS SUBQUERY8_t1 INNER JOIN `H` AS SUBQUERY8_t2 ON (SUBQUERY8_t2 . `pk` = SUBQUERY8_t1 . `col_int_key`  ) )  ) AS field8 ,  table1 . `col_int_not_null_key` < ALL ( SELECT 1 UNION  SELECT 6 ) AS field9 ,  ( 0, 2 ) NOT IN ( SELECT 3 , 5 UNION  SELECT 0, 1 ) AS field10 ,  table1 . `col_int_key` = ANY ( SELECT 8 UNION  SELECT 3 ) AS field11 ,  table1 . `col_int` <> ( SELECT   SUM(  SUBQUERY12_t1 . `col_int` ) AS SUBQUERY12_field1 FROM ( `H` AS SUBQUERY12_t1 INNER JOIN ( `H` AS SUBQUERY12_t2 LEFT OUTER JOIN `B` AS SUBQUERY12_t3 ON (SUBQUERY12_t3 . `col_varchar_binary_key` = SUBQUERY12_t2 . `col_varchar_binary`  ) ) ON (SUBQUERY12_t3 . `col_int_key` = SUBQUERY12_t2 . `col_int`  ) ) WHERE SUBQUERY12_t1 . `pk` <= SUBQUERY12_t3 . `pk` ) AS field12 ,  table1 . `col_varchar_binary` <= ALL ( SELECT 'k' UNION  SELECT 'p' ) AS field13 FROM ( `B` AS table1 INNER JOIN (  SELECT   SUBQUERY14_t1 . * FROM `B` AS SUBQUERY14_t1  ) AS table2 ON (table2 . `col_int_key` = table1 . `col_int_key`  ) ) WHERE (  table1 . `col_varchar_binary` NOT IN ( SELECT   SUBQUERY15_t1 . `col_varchar_binary_not_null_key` AS SUBQUERY15_field1 FROM `E` AS SUBQUERY15_t1   ) ) AND ( table1 . `pk` > 160 AND table1 . `pk` < ( 160 + 163 ) OR table1 . `col_varchar_binary_key` > 'v' )  order by 1,2,3,4,5,6,7,8,9,10,11,12,13;

mysql

+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+
| field1 | field2 | field3 | field4 | field5 | field6 | field7 | field8 | field9 | field10 | field11 | field12 | field13 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+
|   NULL |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |    NULL |       0 |
|   NULL |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |    NULL |       1 |
|   NULL |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       1 |    NULL |       0 |
|   NULL |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      1 |       1 |       0 |    NULL |       1 |
|   NULL |      1 |      0 |      1 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |    NULL |       0 |
|      0 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|      0 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       1 |       1 |       0 |
|      0 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      1 |       1 |       0 |       1 |       0 |
|      0 |      1 |      0 |      1 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|      1 |      1 |      0 |      0 |      0 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|      1 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|      1 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      1 |       1 |       0 |       1 |       1 |
|      1 |      1 |      0 |      1 |      1 |      1 |      1 |      1 |      1 |       1 |       0 |       1 |       0 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+

3. What did you see instead (Required)

 SELECT  table1 . `col_int` >= ( SELECT   SUM(  SUBQUERY1_t1 . `col_int_not_null_key` ) AS SUBQUERY1_field1 FROM `L` AS SUBQUERY1_t1 WHERE SUBQUERY1_t1 . `col_int_key` <> 4 ) AS field1 ,  table1 . `pk`  IN ( SELECT 3 UNION  SELECT 5 ) AS field2 ,  ( table1 . `col_varchar_binary_key` , table1 . `col_varchar_binary_key` )  IN ( SELECT 'k' , 'n' UNION ALL SELECT 'q' , 'e' ) AS field3 ,  ( table1 . `col_int_not_null` , table1 . `pk` )  IN ( SELECT 5 , 9 UNION  SELECT 5, 0 ) AS field4 ,  table1 . `col_int_key` != ANY ( SELECT   SUBQUERY5_t1 . `col_int_key` AS SUBQUERY5_field1 FROM ( `D` AS SUBQUERY5_t1 STRAIGHT_JOIN ( `C` AS SUBQUERY5_t2 INNER JOIN `I` AS SUBQUERY5_t3 ON (SUBQUERY5_t3 . `col_int_not_null_key` = SUBQUERY5_t2 . `pk`  ) ) ON (SUBQUERY5_t3 . `col_varchar_binary_not_null_key` = SUBQUERY5_t2 . `col_varchar_binary_key`  ) ) WHERE SUBQUERY5_t2 . `col_varchar_binary_key` > SUBQUERY5_t1 . `col_varchar_binary_key`   ) AS field5 FROM ( `E` AS table1 STRAIGHT_JOIN `C` AS table2 ON (table2 . `pk` = table1 . `col_int_not_null_key`  ) ) WHERE (  table1 . `col_varchar_binary_key` != ALL ( SELECT 'q' UNION  SELECT 'a' ) ) AND table1 . `col_varchar_binary_key`  IN ('k', 'x')   ;
+--------+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 | field5 |
+--------+--------+--------+--------+--------+
|      0 |      0 |      0 |      0 |   NULL |
|      0 |      0 |      0 |      0 |      1 |
|      0 |      0 |      0 |      0 |   NULL |
+--------+--------+--------+--------+--------+
3 rows in set (0.15 sec)

 SELECT distinct  table1 . `col_int` > ( SELECT 3 FROM DUAL ) AS field1 ,  ( 'q', 'e' )  IN ( SELECT   SUBQUERY2_t1 . `col_varchar_binary_not_null` AS SUBQUERY2_field1 , SUBQUERY2_t2 . `col_varchar_binary_not_null_key` AS SUBQUERY2_field2 FROM ( `J` AS SUBQUERY2_t1 RIGHT  JOIN `H` AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_varchar_binary_not_null` = SUBQUERY2_t1 . `col_varchar_binary_key`  ) )    ) AS field2 ,  table1 . `col_varchar_binary`  IN ( SELECT   SUBQUERY3_t1 . `col_varchar_binary_key` AS SUBQUERY3_field1 FROM ( `B` AS SUBQUERY3_t1 STRAIGHT_JOIN `A` AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_int_not_null_key` = SUBQUERY3_t1 . `pk`  ) ) WHERE SUBQUERY3_t1 . `col_varchar_binary` = 't'  ) AS field3 ,  table1 . `col_varchar_binary_not_null`  IN ( SELECT 'j' UNION  SELECT 't' ) AS field4 ,  table1 . `col_int_key` != ALL ( SELECT 5 UNION  SELECT 9 ) AS field5 ,  ( table1 . `col_int_key` , table1 . `pk` ) NOT IN ( SELECT   SUBQUERY6_t1 . `col_int` AS SUBQUERY6_field1 , SUBQUERY6_t1 . `col_int_not_null_key` AS SUBQUERY6_field2 FROM `L` AS SUBQUERY6_t1    ) AS field6 ,  ( table1 . `col_varchar_binary` , table1 . `col_varchar_binary_not_null_key` ) NOT IN ( SELECT 'q' , 'z' UNION  SELECT 'w' , 'e' ) AS field7 ,  table1 . `pk` > ( SELECT   MAX(  SUBQUERY8_t1 . `col_int_key` ) AS SUBQUERY8_field1 FROM ( `G` AS SUBQUERY8_t1 INNER JOIN `H` AS SUBQUERY8_t2 ON (SUBQUERY8_t2 . `pk` = SUBQUERY8_t1 . `col_int_key`  ) )  ) AS field8 ,  table1 . `col_int_not_null_key` < ALL ( SELECT 1 UNION  SELECT 6 ) AS field9 ,  ( 0, 2 ) NOT IN ( SELECT 3 , 5 UNION  SELECT 0, 1 ) AS field10 ,  table1 . `col_int_key` = ANY ( SELECT 8 UNION  SELECT 3 ) AS field11 ,  table1 . `col_int` <> ( SELECT   SUM(  SUBQUERY12_t1 . `col_int` ) AS SUBQUERY12_field1 FROM ( `H` AS SUBQUERY12_t1 INNER JOIN ( `H` AS SUBQUERY12_t2 LEFT OUTER JOIN `B` AS SUBQUERY12_t3 ON (SUBQUERY12_t3 . `col_varchar_binary_key` = SUBQUERY12_t2 . `col_varchar_binary`  ) ) ON (SUBQUERY12_t3 . `col_int_key` = SUBQUERY12_t2 . `col_int`  ) ) WHERE SUBQUERY12_t1 . `pk` <= SUBQUERY12_t3 . `pk` ) AS field12 ,  table1 . `col_varchar_binary` <= ALL ( SELECT 'k' UNION  SELECT 'p' ) AS field13 FROM ( `B` AS table1 INNER JOIN (  SELECT   SUBQUERY14_t1 . * FROM `B` AS SUBQUERY14_t1  ) AS table2 ON (table2 . `col_int_key` = table1 . `col_int_key`  ) ) WHERE (  table1 . `col_varchar_binary` NOT IN ( SELECT   SUBQUERY15_t1 . `col_varchar_binary_not_null_key` AS SUBQUERY15_field1 FROM `E` AS SUBQUERY15_t1   ) ) AND ( table1 . `pk` > 160 AND table1 . `pk` < ( 160 + 163 ) OR table1 . `col_varchar_binary_key` > 'v' )  order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+
| field1 | field2 | field3 | field4 | field5 | field6 | field7 | field8 | field9 | field10 | field11 | field12 | field13 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+
|   NULL |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|   NULL |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       1 |
|   NULL |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       1 |       1 |       0 |
|   NULL |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      1 |       1 |       0 |       1 |       1 |
|   NULL |      1 |      0 |      1 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|      0 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|      0 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       1 |       1 |       0 |
|      0 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      1 |       1 |       0 |       1 |       0 |
|      0 |      1 |      0 |      1 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|      1 |      1 |      0 |      0 |      0 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|      1 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      0 |       1 |       0 |       1 |       0 |
|      1 |      1 |      0 |      0 |      1 |      1 |      1 |      1 |      1 |       1 |       0 |       1 |       1 |
|      1 |      1 |      0 |      1 |      1 |      1 |      1 |      1 |      1 |       1 |       0 |       1 |       0 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+
13 rows in set (0.12 sec)

4. What is your TiDB version? (Required)

master

[fzhedu]

Uploading 1615898457_agg_sub200.txt…