| Bug #109145 | Using index for skip scan cause incorrect result | ||
|---|---|---|---|
| Submitted: | 19 Nov 2022 8:28 | Modified: | 29 Aug 2023 9:53 |
| Reporter: | Fan Wang | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 8.0.31 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution, incorrect result, Optimizer bug, Using index for skip scan | ||
[19 Nov 2022 8:28]
Fan Wang
[19 Nov 2022 12:08]
Fan Wang
afan_test.sql
Attachment: afan_test.sql (application/sql, text), 7.43 KiB.
[21 Nov 2022 6:50]
MySQL Verification Team
Hello Fan Wang Thank you for the bug report and test case. I quickly tried to reproduce the issue at my end but not seeing any issues at least in 10/10 times. Am I missing anything here? Please let me know and also if not on default conf then would you please share the configuration to try at my end? Thank you. - rm -rf 109145/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/109145 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version=debug --basedir=$PWD --datadir=$PWD/109145 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/109145/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.31 MySQL Community Server - GPL Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test Database changed mysql> source afan_test.sql; Query OK, 0 rows affected, 1 warning (0.00 sec) . Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | test.afan_test | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.02 sec) mysql> explain select max(the_date) from afan_test where prj_id = 'PRJID07'; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------+ | 1 | SIMPLE | afan_test | NULL | range | index_1 | index_1 | 516 | NULL | 113365 | 100.00 | Using where; Using index for skip scan | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select max(the_date) from afan_test where prj_id = 'PRJID07'; +---------------+ | max(the_date) | +---------------+ | 20220229 | +---------------+ 1 row in set (0.00 sec) mysql> delete from afan_test where the_date like '2022011%'; Query OK, 170400 rows affected (2.14 sec) mysql> select max(the_date) from afan_test where prj_id = 'PRJID07'; +---------------+ | max(the_date) | +---------------+ | 20220229 | +---------------+ 1 row in set (0.00 sec) regards, Umesh
[21 Nov 2022 6:55]
MySQL Verification Team
Please ignore, I'm able to see the issue now. mysql> source afan_test.sql; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.08 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 11 rows affected (0.00 sec) Records: 11 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 Query OK, 32 rows affected (0.00 sec) Records: 32 Duplicates: 0 Warnings: 0 Query OK, 64 rows affected (0.01 sec) Records: 64 Duplicates: 0 Warnings: 0 Query OK, 128 rows affected (0.00 sec) Records: 128 Duplicates: 0 Warnings: 0 Query OK, 256 rows affected (0.01 sec) Records: 256 Duplicates: 0 Warnings: 0 Query OK, 512 rows affected (0.02 sec) Records: 512 Duplicates: 0 Warnings: 0 Query OK, 1065 rows affected (0.01 sec) Records: 1065 Duplicates: 0 Warnings: 0 Query OK, 2130 rows affected (0.04 sec) Records: 2130 Duplicates: 0 Warnings: 0 Query OK, 4260 rows affected (0.07 sec) Records: 4260 Duplicates: 0 Warnings: 0 Query OK, 8520 rows affected (0.15 sec) Records: 8520 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.02 sec) Query OK, 17040 rows affected (0.28 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.16 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.17 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.16 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.16 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.17 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.16 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.16 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.12 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.17 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.13 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.16 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.16 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.15 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 17040 rows affected (0.14 sec) Records: 17040 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | test.afan_test | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.02 sec) mysql> select max(the_date) from afan_test where prj_id = 'PRJID07'; +---------------+ | max(the_date) | +---------------+ | 20220229 | +---------------+ 1 row in set (0.00 sec) mysql> delete from afan_test where the_date like '2022011%'; Query OK, 170400 rows affected (2.08 sec) mysql> select max(the_date) from afan_test where prj_id = 'PRJID07'; +---------------+ | max(the_date) | +---------------+ | 20220109 | +---------------+ 1 row in set (0.00 sec)
[21 Nov 2022 7:16]
MySQL Verification Team
5.7.40 - issue not seen +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | test.afan_test | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.01 sec) +---------------+ | max(the_date) | +---------------+ | 20220229 | +---------------+ 1 row in set (0.25 sec) Query OK, 170400 rows affected (1.09 sec) +---------------+ | max(the_date) | +---------------+ | 20220229 | +---------------+ 1 row in set (0.22 sec)
[22 Nov 2022 6:44]
MySQL Verification Team
Bug #109124 marked as duplicate of this one
[22 Nov 2022 6:45]
MySQL Verification Team
Bug #109127, Bug #109136, Bug #109139 and Bug #109142 are marked as duplicate of this one
[24 Nov 2022 9:06]
MySQL Verification Team
Bug #109190 marked as duplicate of this one
[24 Nov 2022 9:12]
Brian Yue
duplicate with Bug #109190, fixed basing on version MySQL8.0.25 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: contribution_bug#109145_and_bug#109190.txt (text/plain), 727 bytes.
[24 Nov 2022 9:23]
MySQL Verification Team
Thank you for the contribution, Brian Yue. Sincerely, Umesh
[29 Aug 2023 9:53]
MySQL Verification Team
Confirmed internally from module dev's that this is fixed.
Documented fix as follows in the MySQL 8.0.34 and 8.1.0 changelogs:
For index skip scans, the first range read set an end-of-range
value to indicate the end of the first range, but the next range
read did not clear the stale end-of-range value and applies this
stale value to the current range. Since the end-of-range value
had already been crossed in the previous range read, this caused
the reads to stop, causing multiple rows to be missed in the
result.
We fix this by making sure in such cases that the old
end-of-range value is cleared.
Closed.
[13 Feb 8:11]
Frederic Descamps
Thank you for your contribution, our development team already fixed under another bug related to dirty reads with repeatable read isolation when "Using index for skip scan".
