Bug #118018 | A query expose a logic bug and performance bug | ||
---|---|---|---|
Submitted: | 20 Apr 13:49 | Modified: | 19 May 14:53 |
Reporter: | jinhui lai | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
Version: | 9.2.0, 9.3.0 | OS: | Ubuntu (22.04) |
Assigned to: | CPU Architecture: | Any |
[20 Apr 13:49]
jinhui lai
[22 Apr 7:10]
jinhui lai
I execute the query in mysql-9.2.0 again. Then it costs more time and returns 500 row. mysql> use information_schema; mysql> SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name EXCEPT (SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name); ... 500 rows in set (20 min 28.91 sec)
[22 Apr 7:15]
jinhui lai
This is a supplementary information. mysql> use information_schema; mysql> FLUSH STATUS; Query OK, 0 rows affected (0.12 sec) mysql> 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_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.01 sec) mysql> SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name EXCEPT (SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name); ... 500 rows in set (20 min 28.91 sec) SHOW SESSION STATUS LIKE 'Handler_read%'; +-----------------------+---------+ | Variable_name | Value | +-----------------------+---------+ | Handler_read_first | 7 | | Handler_read_key | 2411924 | | Handler_read_last | 0 | | Handler_read_next | 2314702 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 1171862 | +-----------------------+---------+ 7 rows in set (0.00 sec)
[19 May 14:53]
MySQL Verification Team
Hello jinhui lai, Thank you for the report and feedback. Verifying for now but I could not see the magnitude of slowness you had observed in your tests (sorry couldn't check on docker) in as much as 15+ attempts at my end. Development team may have follow up questions/request for details and if needed will request you to share. Thank you once again. regards, Umesh