MySQL Bugs: #118195: The opposite WHERE clause intersects and is always an empty set.

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

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)

Thank you for the report and test case.

Bug #118196 marked as duplicate of this one

Bug #118197 is marked as duplicate of this one

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.