Bug #118197 The opposite ON clause intersects and is always an empty set.

• Submitted: 14 May 13:50
• Modified: 15 May 20:16
• Reporter: jinhui lai
• Status: Analyzing
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.3.0
• OS: Ubuntu (22.04)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[14 May 13:50] jinhui lai

Description:
Hello, MySQL Developers:

Please considering the following query：

q1: SELECT * FROM t0 CROSS JOIN user ON t0.Time_zone_id <1000; 
q2: SELECT * FROM t0 CROSS JOIN user ON t0.Time_zone_id >=1000;

Since q1 and q2 have opposite ON clause (e.g., < and >=), q1 INTERSECT q2 should always produce empty set and without physical read. However, MySQL wastes much time to execute such queries.

Moreover, the execution time is proportional to the amount of table data.

Best regard,
Jinhui Lai

How to repeat:
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 CROSS JOIN user ON t0.Time_zone_id <1000 INTERSECT SELECT * FROM t0 CROSS JOIN user ON t0.Time_zone_id >=1000;
Empty set (2 min 28.186 sec) -- expect: Empty set (0.xxx sec)

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 5       |
| Handler_read_key      | 2949328 |
| Handler_read_last     | 0       |
| Handler_read_next     | 0       |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 2865116 |
+-----------------------+---------+
7 rows in set (0.002 sec)