Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-7193

Skip Scan retrieves incorrect Result

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 8.0.19-10
    • 8.0.29-21 (Q2 2022)
    • 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)
      

       

      Attachments

        1. case_ddl.sql
          1 kB
        2. case.csv.gz
          5.60 MB

        Activity

          People

            puneet.kaushik Puneet Kaushik
            juan.arruti Juan Arruti
            Votes:
            2 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 4 hours
                4h

                Smart Checklist