Bug #118195 The opposite WHERE clause intersects and is always an empty set.
Submitted: 14 May 13:34 Modified: 20 May 8:23
Reporter: jinhui lai Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:9.3.0 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:Any

[14 May 13:34] jinhui lai
Description:
Hello, MySQL Developers,

Considering the following query:

q1: SELECT * FROM t0 WHERE t0.Time_zone_id <1000; 
q2: SELECT * FROM t0 WHERE t0.Time_zone_id >=1000;

Since q1 and q2 have opposite WHERE 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.

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 WHERE t0.Time_zone_id <1000 INTERSECT SELECT * FROM t0 WHERE t0.Time_zone_id >=1000;
Empty set (19.318 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)
[19 May 21:15] MySQL Verification Team
Thank you for the report and test case.
[19 May 21:16] MySQL Verification Team
Bug #118196 marked as duplicate of this one
[19 May 21:17] MySQL Verification Team
Bug #118197 is marked as duplicate of this one
[20 May 8:23] Roy Lyseng
Posted by developer:
 
Thank you for the bug report.
However, we do not do any kind of optimization by combining the inner parts of two (or more)
query blocks of a set operation, hence we do not consider this a bug.
You are however welcome to file a feature request for the same,
but I think it should be accompanied by a real-word use case.