query optimizer does not detect "provable empty sets"

[morgo]

Bug Report

This is issue (7) on https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY auto_increment,
 t2_id INT NOT NULL,
 pad1 varchar(100)
);

CREATE TABLE t2 (
 id INT NOT NULL PRIMARY KEY auto_increment,
 nn_col INT NOT NULL,
 pad1 varchar(100)
);
 
INSERT INTO t2 SELECT NULL, 1, 'aaa' FROM dual;
INSERT INTO t2 SELECT NULL, 1, 'aaa' FROM t2 a JOIN t2 b JOIN t2 c;
INSERT INTO t1 SELECT NULL, id, 'aaa' FROM t2;

EXPLAIN SELECT t1.* FROM t1 JOIN (SELECT * FROM t2 WHERE nn_col IS NULL) t2 WHERE t1.t2_id = t2.id;

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

Here is what MySQL says:

mysql [localhost:8024] {root} (test) > EXPLAIN SELECT t1.* FROM t1 JOIN (SELECT * FROM t2 WHERE nn_col IS NULL) t2 WHERE t1.t2_id = t2.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead (Required)

mysql> EXPLAIN SELECT t1.* FROM t1 JOIN (SELECT * FROM t2 WHERE nn_col IS NULL) t2 WHERE t1.t2_id = t2.id;
+-----------------------------+----------+-----------+---------------+---------------------------------------------------+
| id                          | estRows  | task      | access object | operator info                                     |
+-----------------------------+----------+-----------+---------------+---------------------------------------------------+
| HashJoin_14                 | 0.00     | root      |               | inner join, equal:[eq(test.t2.id, test.t1.t2_id)] |
| ├─TableDual_18(Build)       | 0.00     | root      |               | rows:0                                            |
| └─TableReader_17(Probe)     | 10000.00 | root      |               | data:TableFullScan_16                             |
|   └─TableFullScan_16        | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                    |
+-----------------------------+----------+-----------+---------------+---------------------------------------------------+
4 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.2.0-alpha-81-g62b70d310-dirty
Edition: Community
Git Commit Hash: 62b70d3101c6dbb06f1f7f4005eba28344932ee4
Git Branch: alter-user-for-self
UTC Build Time: 2021-06-16 14:44:37
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[morgo]

Another example:

mysql> EXPLAIN SELECT * FROM t1 WHERE t2_id IN (SELECT id FROM t2 WHERE nn_col IS NULL);
+-----------------------------+---------+-----------+---------------+-------------------------------------------------------------+
| id                          | estRows | task      | access object | operator info                                               |
+-----------------------------+---------+-----------+---------------+-------------------------------------------------------------+
| HashJoin_15                 | 1.25    | root      |               | inner join, equal:[eq(test.t2.id, test.t1.t2_id)]           |
| ├─HashAgg_16(Build)         | 1.00    | root      |               | group by:test.t2.id, funcs:firstrow(test.t2.id)->test.t2.id |
| │ └─TableDual_17            | 0.00    | root      |               | rows:0                                                      |
| └─TableReader_19(Probe)     | 2.00    | root      |               | data:TableFullScan_18                                       |
|   └─TableFullScan_18        | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                              |
+-----------------------------+---------+-----------+---------------+-------------------------------------------------------------+
5 rows in set (0.00 sec)

[winoros]

Yes, this is a duplicate issue of #25234.
We would improve it later. But I don't want to treat this like a bug.