Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
8.0.19-10
-
None
Description
After running DELETE statements in a loop for a while subsequent SELECT gets an empty result set if skip scan is used in the execution plan.
In case we execute the same select once again we can see the correct result.
I can reproduce it consistently in PS 8.0.19 if GTID is enabled. It's present on MySQL Community 8.0.21 as well.
How to Repeat:
mysql [localhost:4557] \{msandbox} ((none)) > select @@version ; +-----------+ | @@version | +-----------+ | 8.0.19-10 | +-----------+ 1 row in set (0.00 sec) mysql [localhost:4557] \{msandbox} ((none)) > select @@version_comment ; +----------------------------------------------------+ | @@version_comment | +----------------------------------------------------+ | Percona Server (GPL), Release 10, Revision f446c04 | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost:4557] \{msandbox} ((none)) > select @@gtid_mode ; +-------------+ | @@gtid_mode | +-------------+ | ON | +-------------+ 1 row in set (0.00 sec) mysql [localhost:4557] \{msandbox} ((none)) > SOURCE case_ddl.sql Query OK, 0 rows affected (0.25 sec) Query OK, 0 rows affected, 1 warning (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql [localhost:4557] \{msandbox} ((none)) > LOAD DATA INFILE 'case.csv' INTO TABLE test.review_request FIELDS TERMINATED BY ',' ENCLOSED BY '"'; Query OK, 1403719 rows affected (37.71 sec) Records: 1403719 Deleted: 0 Skipped: 0 Warnings: 0 mysql [localhost:4557] \{msandbox} ((none)) > ANALYZE TABLE test.review_request ; +---------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------+---------+----------+----------+ | test.review_request | analyze | status | OK | +---------------------+---------+----------+----------+ 1 row in set (0.03 sec) # We check the explain returns an access using skip scan mysql [localhost:4557] \{msandbox} ((none)) > EXPLAIN SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: review_request partitions: NULL type: range possible_keys: queue_id key: queue_id key_len: 7 ref: NULL rows: 574386 filtered: 100.00 Extra: Using where; Using index for skip scan 1 row in set, 1 warning (0.00 sec) # We execute together the procedure call which it will stop once the list is empty and a SELECT count using same condition to access using skip scan. # Also there is the same SELECT count inside the procedure call. mysql [localhost:4557] \{msandbox} ((none)) > CALL test.skip_scan() ; SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY; ... +----------+ | @id_list | +----------+ | | +----------+ 1 row in set (14.57 sec) +----------+ | rows_del | +----------+ | 57510 | +----------+ 1 row in set (14.57 sec) +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (14.57 sec) Query OK, 0 rows affected (14.57 sec) +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) # We can see id_list was retrieved as empty, same as last two select count. # In case we execute again the same SELECT count we can see it returns the correct resultset. mysql [localhost:4557] \{msandbox} ((none)) > SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY; +----------+ | COUNT(*) | +----------+ | 1100153 | +----------+ 1 row in set (1.08 sec)