planner: don't push down conditions from in subquery with aggregators even though the columns in conditions is not null

[fzhedu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

create table s( a int not null, b int);  
select ( 2, 0 ) in (select s.b, min(s.b) from s )as f;
select ( 2, 0 ) in (select s.a, min(s.b) from s )as f;

2. What did you expect to see?
   results from MySQL:

mysql> select ( 2, 0 ) in (select s.b, min(s.b) from s )as f;
+------+
| f    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select ( 2, 0 ) in (select s.a, min(s.b) from s )as f;
+------+
| f    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

3. What did you see instead?
   Results from TiDB:

mysql> select ( 2, 0 ) in (select s.b, min(s.b) from s )as f;
+------+
| f    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select ( 2, 0 ) in (select s.a, min(s.b) from s )as f;
+------+
| f    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

The explain results:

mysql> explain select ( 2, 0 ) in (select s.b, min(s.b) from s )as f;
+--------------------------+----------+-----------+-------------------------------------------------------------------------------------------------+
| id                       | count    | task      | operator info                                                                                   |
+--------------------------+----------+-----------+-------------------------------------------------------------------------------------------------+
| HashLeftJoin_8           | 1.00     | root      | CARTESIAN left outer semi join, inner:StreamAgg_21, other cond:eq(0, Column#4), eq(2, Column#2) |
| ├─TableDual_9            | 1.00     | root      | rows:1                                                                                          |
| └─StreamAgg_21           | 1.00     | root      | funcs:min(Column#11), firstrow(Column#12)                                                       |
|   └─TableReader_22       | 1.00     | root      | data:StreamAgg_13                                                                               |
|     └─StreamAgg_13       | 1.00     | cop[tikv] | funcs:min(Column#2), firstrow(Column#2)                                                         |
|       └─TableScan_20     | 10000.00 | cop[tikv] | table:s, range:[-inf,+inf], keep order:false, stats:pseudo                                      |
+--------------------------+----------+-----------+-------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> explain select ( 2, 0 ) in (select s.a, min(s.b) from s )as f;
+----------------------------+----------+-----------+--------------------------------------------------------------------------------+
| id                         | count    | task      | operator info                                                                  |
+----------------------------+----------+-----------+--------------------------------------------------------------------------------+
| HashLeftJoin_9             | 1.00     | root      | CARTESIAN left outer semi join, inner:Selection_11, other cond:eq(0, Column#4) |
| ├─TableDual_10             | 1.00     | root      | rows:1                                                                         |
| └─Selection_11             | 0.80     | root      | eq(2, Column#1)                                                                |
|   └─StreamAgg_23           | 1.00     | root      | funcs:min(Column#11), firstrow(Column#12)                                      |
|     └─TableReader_24       | 1.00     | root      | data:StreamAgg_15                                                              |
|       └─StreamAgg_15       | 1.00     | cop[tikv] | funcs:min(Column#2), firstrow(Column#1)                                        |
|         └─TableScan_22     | 10000.00 | cop[tikv] | table:s, range:[-inf,+inf], keep order:false, stats:pseudo                     |
+----------------------------+----------+-----------+--------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

The essential problem is :

mysql> select s.a, min(s.b) from s;
+---+----------+
| a | min(s.b) |
+---+----------+
| NULL |     NULL |
+---+----------+
1 row in set (0.00 sec)

mysql> select s.a, s.b from s;
Empty set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   After the master merged with planner:dont push down right condition for anti semi join #12075, there are still errors.

[jingyugao]

Thanks.

// f is empty table.
select 2 in (select null from s) as f; // mysql: null, tidb:0
select 2 in (select s.a from s) as f;// both 0
select 2 in (select s.b from s) as f; // both 0
select 2 in (select 1 from s) as f;   // both 0

I try four sql, only the first is wrong.
I think this is because tidb do cast on null, while ExtractOnCondition only handler the situation that binop.GetArgs() both expression.Column.

[fzhedu]

Thanks.

// f is empty table.
select 2 in (select null from s) as f; // mysql: null, tidb:0
select 2 in (select s.a from s) as f;// both 0
select 2 in (select s.b from s) as f; // both 0
select 2 in (select 1 from s) as f;   // both 0

I try four sql, only the first is wrong.
I think this is because tidb do cast on null, while ExtractOnCondition only handler the situation that binop.GetArgs() both expression.Column.

1. I found the first query returns 0 in both mysql and TiDB(your PR code).

mysql> create table s( a int not null, b int);  
Query OK, 0 rows affected (0.18 sec)
mysql> select 2 in (select null from s) as f;
+------+
| f    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.00 sec)

@jingyugao
2.
Please see the explain results explain select ( 2, 0 ) in (select s.a, min(s.b) from s )as f; above. We note eq(2, Column#1) is pushed down because Column#1 (s.a) is not null. The expression is in ExtractOnCondition . I mean such expression cannot be pushed down.

[jingyugao]

1. In my test, f is empty table. so the result is different.
   2.It seems that we need to store if the condition is single condition or splited from (...) op (...). If it is splited contidion,we can't push down it easily.

[fzhedu]

1. In my test, f is empty table. so the result is different.
   2.It seems that we need to store if the condition is single condition or splited from (...) op (...). If it is splited contidion,we can't push down it easily.

@jingyugao
In 2, I think the conditions cannot be pushed down if the subquery contains aggregators. Because such subquery will return a tuple with NULLs if the table is empty, this is different from the query without aggregators. For example:

mysql> select s.a, min(s.b) from s;
+---+----------+
| a | min(s.b) |
+---+----------+
| NULL |     NULL |
+---+----------+
1 row in set (0.00 sec)

mysql> select s.a, s.b from s;
Empty set (0.00 sec)

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions