| Bug #100253 | Skip Scan retrieves incorrect Result | ||
|---|---|---|---|
| Submitted: | 18 Jul 2020 1:47 | Modified: | 13 Oct 2021 6:30 |
| Reporter: | Juan Arruti | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.21, 8.0.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Jul 2020 13:18]
MySQL Verification Team
Hello Juan Arruti, Thank you for the report and test case. Verified as described. regards, Umesh
[20 Jul 2020 13:22]
MySQL Verification Team
MySQL Server 8.0.21/8.0.13 test results
Attachment: 100253_8.x.results.txt (text/plain), 7.24 KiB.
[21 Jul 2020 2:57]
Juan Arruti
Hi Umesh,
Thanks for reviewing this case. I'm seeing this issue is triggered faster if GTID is enabled, as follows:
mysql [localhost:8021] {msandbox} ((none)) > select @@gtid_mode ;
+-------------+
| @@gtid_mode |
+-------------+
| ON |
+-------------+
1 row in set (0.00 sec)
mysql [localhost:8021] {msandbox} ((none)) > CALL test.skip_scan() ; SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY;
+----------+
| @id_list |
+----------+
| |
+----------+
1 row in set (23.74 sec)
+----------+
| rows_del |
+----------+
| 57510 |
+----------+
1 row in set (23.74 sec)
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (23.74 sec)
Query OK, 0 rows affected (23.74 sec)
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql [localhost:8021] {msandbox} ((none)) > SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY;
+----------+
| COUNT(*) |
+----------+
| 1100153 |
+----------+
1 row in set (1.92 sec)
Regards,
[28 Jul 2021 1:38]
Trey Raymond
been a year since the last update on this, it's a good feature and a shame it can't be used since it's bugged, any updates from developers?
[15 Sep 2021 11:13]
zkong kong
Hi Juan Arruti: Would you mind if I can get your test procedure and data?
[13 Oct 2021 6:30]
Srinivasarao Ravuri
This bug is closed as a duplicate to oracle bug report Bug#33384537
[18 Oct 2021 22:37]
Trey Raymond
if it's marked as a duplicate of a private internal bug...how do we get updates?
[5 Nov 2021 8:47]
Erlend Dahl
Fixed in the upcoming 8.0.28 under the heading of: Bug#33384537 prebuilt->m_end_range should be reset at fetch_cache initializing at row_search_mvcc()
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. How to repeat: mysql [localhost:8021] {msandbox} ((none)) > select @@version ; +-----------+ | @@version | +-----------+ | 8.0.21 | +-----------+ 1 row in set (0.00 sec) mysql [localhost:8021] {msandbox} ((none)) > select @@version_comment ; +------------------------------+ | @@version_comment | +------------------------------+ | MySQL Community Server - GPL | +------------------------------+ 1 row in set (0.00 sec) mysql [localhost:8021] {msandbox} ((none)) > SOURCE case_ddl.sql Query OK, 0 rows affected (0.55 sec) Query OK, 0 rows affected, 1 warning (0.02 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) mysql [localhost:8021] {msandbox} ((none)) > LOAD DATA INFILE 'case.csv' INTO TABLE test.review_request FIELDS TERMINATED BY ',' ENCLOSED BY '"'; Query OK, 1403719 rows affected (49.76 sec) Records: 1403719 Deleted: 0 Skipped: 0 Warnings: 0 mysql [localhost:8021] {msandbox} ((none)) > ANALYZE TABLE test.review_request ; +---------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------+---------+----------+----------+ | test.review_request | analyze | status | OK | +---------------------+---------+----------+----------+ 1 row in set (0.02 sec) # We check the explain returns an access using skip scan mysql [localhost:8021] {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: 470851 filtered: 100.00 Extra: Using where; Using index for skip scan 1 row in set, 1 warning (0.01 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:8021] {msandbox} ((none)) > CALL test.skip_scan() ; SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY; ... +----------+ | @id_list | +----------+ | | +----------+ 1 row in set (11.65 sec) +----------+ | rows_del | +----------+ | 30400 | +----------+ 1 row in set (11.65 sec) +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (11.65 sec) Query OK, 0 rows affected (11.65 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:8021] {msandbox} ((none)) > SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY; +----------+ | COUNT(*) | +----------+ | 407391 | +----------+ 1 row in set (0.90 sec) You can find below the list of commands executed: SOURCE case_ddl.sql LOAD DATA INFILE 'case.csv' INTO TABLE test.review_request FIELDS TERMINATED BY ',' ENCLOSED BY '"'; ANALYZE TABLE test.review_request ; EXPLAIN SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY\G CALL test.skip_scan() ; SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY; --- SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY;