Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
11.7.2
-
None
-
None
-
docker pull mariadb:latest
Description
Dear MariaDB Developer:
I find a performance bug as follow:
In the case of q1 INTERSECT q2, when q2 takes the form of t0 CROSS JOIN t1 ON FALSE, this operation should not require physical reads. Since t0 CROSS JOIN t1 ON FALSE by definition always returns an empty set, the intersection q1 INTERSECT q2 must logically yield an empty result without needing to execute the underlying query.
MariaDB [fuzz]> use information_schema;
|
MariaDB [information_schema]> FLUSH STATUS;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [information_schema]> SELECT * FROM TABLES CROSS JOIN VIEWS ON FALSE;
|
Empty set (0.002 sec)
|
|
|
MariaDB [information_schema]> SHOW SESSION STATUS LIKE 'Handler_read%';
|
+--------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------+-------+
|
| Handler_read_first | 0 |
|
| Handler_read_key | 0 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 0 |
|
| Handler_read_prev | 0 |
|
| Handler_read_retry | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 0 |
|
+--------------------------+-------+
|
9 rows in set (0.002 sec)
|
|
|
MariaDB [information_schema]> FLUSH STATUS;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [information_schema]> SELECT * FROM TABLES CROSS JOIN VIEWS ON TRUE INTERSECT SELECT * FROM TABLES CROSS JOIN VIEWS ON FALSE;
|
Empty set (4.175 sec)
|
|
|
MariaDB [information_schema]> SHOW SESSION STATUS LIKE 'Handler_read%';
|
+--------------------------+--------+
|
| Variable_name | Value |
|
+--------------------------+--------+
|
| Handler_read_first | 0 |
|
| Handler_read_key | 152 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 0 |
|
| Handler_read_prev | 0 |
|
| Handler_read_retry | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 146836 |
|
+--------------------------+--------+
|
9 rows in set (0.001 sec)
|
Thank you for your time and attention to this issue. I greatly appreciate your assistance and look forward to hearing your thoughts.
Best regards,
Attachments
Issue Links
- relates to
-
-
- Confirmed
-