| Bug #109142 | optimizer skip scan cause incorrect result | ||
|---|---|---|---|
| Submitted: | 18 Nov 2022 18:59 | Modified: | 22 Nov 2022 6:43 |
| Reporter: | Fan Wang | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 8.0.31 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | incorrect result, Optimizer, skip scan | ||
[18 Nov 2022 19:00]
Fan Wang
skip_scan.sql
Attachment: skip_scan.sql (application/sql, text), 6.80 KiB.
[18 Nov 2022 19:34]
MySQL Verification Team
Hi Mr. Wang, Thank you for your bug report. Also, thank you for your new test case. However ...... We are still unable to repeat the behaviour that you are reporting, with our official 8.0.31 binary. Here are our results: mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec) mysql> delete from skip_scan where dt like '2022011%'; Query OK, 170400 rows affected (1.38 sec) mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220109 | +----------+ 1 row in set (0.00 sec) # The correct result of max(dt) is 20220229, but we got a incorrect result 20220109 # The result without using skip scan is correct mysql> select /*+ NO_SKIP_SCAN(skip_scan) */ max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ |20220109 | +----------+ 1 row in set (0.01 sec) You might have some setting that causes different behaviour. Try to identify which, because we have to be able to repeat your results in order to proceed with further verification .... Can't repeat.
[19 Nov 2022 1:59]
Fan Wang
Your MySQL Client do not support Optimizer Hints. How to repeat: mysql> source skip_scan.sql mysql> explain select max(dt) from skip_scan where prj_id = 'TEST007'; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+ | 1 | SIMPLE | skip_scan | NULL | range | index_1 | index_1 | 516 | NULL | 85056 | 100.00 | Using where; Using index for skip scan | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+ 1 row in set, 1 warning (0.01 sec) mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec) mysql> delete from skip_scan where dt like '2022011%'; Query OK, 170400 rows affected (1.38 sec) # delete dt like '2022011%' is same as delete 20220110 to 20220119, the max(dt) in the table skip_scan is still 20220229 mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220109 | +----------+ 1 row in set (0.00 sec) # The correct result of max(dt) is 20220229, but we got a incorrect result 20220109 mysql> select max(dt) from skip_scan where prj_id = 'TEST007' and dt = '20220229'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec)
[19 Nov 2022 2:18]
Fan Wang
# max(dt) means the largest value of dt in the table. In table skip_scan the max(dt) is 20220229. mysql> delete from skip_scan where dt like '2022011%'; Query OK, 170400 rows affected (1.38 sec) mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220109 | +----------+ 1 row in set (0.00 sec) # 20220109 is an incorrect result
[22 Nov 2022 6:43]
MySQL Verification Team
Since Bug #109145 is already in verified state hence marking this as a duplicate of Bug #109145(Otherwise, in general newer one are marked as duplicate of older one.).
Description: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.00 sec) # The optimizer_switch skip_scan is on mysql> show variables like 'optimizer_switch'\G *************************** 1. row *************************** Variable_name: optimizer_switch Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on 1 row in set (0.00 sec) # The max(dt) in the table skip_scan is 20220229 mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec) mysql> delete from skip_scan where dt like '2022011%'; Query OK, 170400 rows affected (1.38 sec) # delete dt like '2022011%' is same as delete 20220110 to 20220119, the max(dt) in the table skip_scan is still 20220229 mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220109 | +----------+ 1 row in set (0.00 sec) # The correct result of max(dt) is 20220229, but we got a incorrect result 20220109 # The result without using skip scan is correct mysql> select /*+ NO_SKIP_SCAN(skip_scan) */ max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec) How to repeat: mysql> source skip_scan.sql mysql> explain select max(dt) from skip_scan where prj_id = 'TEST007'; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+ | 1 | SIMPLE | skip_scan | NULL | range | index_1 | index_1 | 516 | NULL | 85056 | 100.00 | Using where; Using index for skip scan | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+ 1 row in set, 1 warning (0.01 sec) mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec) mysql> delete from skip_scan where dt like '2022011%'; Query OK, 170400 rows affected (1.38 sec) mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220109 | +----------+ 1 row in set (0.00 sec) mysql> select /*+ NO_SKIP_SCAN(skip_scan) */ max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec)