Description:
Hello, MySQL Developers,
Considering the following query：
q1: SELECT * FROM t0 HAVING t0.Time_zone_id <1000;
q2: SELECT * FROM t0 HAVING t0.Time_zone_id >=1000;
Since q1 and q2 have opposite HAVING clause (e.g., < and >=), q1 INTERSECT q2 should always produce empty set and without physical read. However, MySQL wasting much time to executing such queries.
Moreover, the execution time is proportional to the amount of table data.
Best regard,
Jinhui Lai
How to repeat:
mysql> use mysql;
mysql> CREATE TABLE t0 AS SELECT * FROM time_zone_transition CROSS JOIN user;
Query OK, 590075 rows affected (54.419 sec)
Records: 590075 Duplicates: 0 Warnings: 0
mysql> FLUSH STATUS;
mysql> SELECT * FROM t0 HAVING t0.Time_zone_id <1000 INTERSECT SELECT * FROM t0 HAVING t0.Time_zone_id >=1000;
Empty set (19.290 sec) -- expect: Empty set (0.xxx sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Handler_read_first | 3 |
| Handler_read_key | 3 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 1526318 |
+-----------------------+---------+
7 rows in set (0.106 sec)
