Bug #111261 Skip scan results in wrong query results

• Submitted: 2 Jun 2023 17:38
• Modified: 5 Jun 2023 6:08
• Reporter: Baolin Huang (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.32, 8.0.33
• OS: Any
• CPU Architecture: Any

[2 Jun 2023 17:38] Baolin Huang

Description:
The problem is caused by the end_key, it is query similar with the Bug #100253.

In function IndexSkipScanIterator::Read()

1. index_next_different is to find next different key 
2. ha_read_range_first is to use the new key to fetch rows

The ha_read_range_first may set end range, which saves in prebuilt->m_mysql_handler->end_range. The end_range can effect index_next_different function.

There are several trigger conditions
1. use skip scan 
2. have delete-marked rows and not been purged yet.
3. the delete-marked rows size is larger than a page size.

The location of the query error problem is at

```
    /** Compare the last record of the page with end range
    passed to InnoDB when there is no ICP and number of
    loops in row_search_mvcc for rows found but not
    reporting due to search views etc. */
    if (prev_rec != nullptr && !prebuilt->innodb_api &&
        prebuilt->m_mysql_handler->end_range != nullptr &&
        prebuilt->idx_cond == false && end_loop >= 100) {
      dict_index_t *key_index = prebuilt->index;
```

How to repeat:
Here gives the test case, it must execute on debug binary.
```
SET global innodb_purge_stop_now=ON;

CREATE TABLE t1 (id BIGINT AUTO_INCREMENT PRIMARY KEY, s_id BIGINT, t_id VARCHAR(100), pad VARCHAR(512), INDEX sid_name(s_id, t_id));

DELIMITER //;

CREATE PROCEDURE insert_numbers()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000 DO
    INSERT INTO t1 (s_id, t_id, pad) VALUES (i/200, i, repeat('a', 512));
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;//

CALL insert_numbers();
INSERT INTO t1 (s_id,t_id, pad) SELECT s_id,t_id, pad from t1;

DELETE from t1 where s_id=3;

select id from t1 where t_id='800';

select id from t1 force index(primary) where t_id='800';

SET global innodb_purge_stop_now=OFF;

```

[2 Jun 2023 17:54] Baolin Huang

The problem is reproduced on the latest version 8.0.32.

Here gives the result.

```
SET global innodb_purge_stop_now=ON;
CREATE TABLE t1 (id BIGINT AUTO_INCREMENT PRIMARY KEY, s_id BIGINT, t_id VARCHAR(100), pad VARCHAR(512), INDEX sid_name(s_id, t_id));
CREATE PROCEDURE insert_numbers()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000 DO
INSERT INTO t1 (s_id, t_id, pad) VALUES (i/200, i, repeat('a', 512));
SET i = i + 1;
END WHILE;
END //
CALL insert_numbers();
INSERT INTO t1 (s_id,t_id, pad) SELECT s_id,t_id, pad from t1;
DELETE from t1 where s_id=3;
select id from t1 where t_id='800';
id
select id from t1 force index(primary) where t_id='800';
id
800
1800
SET global innodb_purge_stop_now=OFF;

DROP TABLE t1;
DROP PROCEDURE insert_numbers;

```

[5 Jun 2023 6:08] MySQL Verification Team

Hello Huang,

Thank you for the report and test case.
Verified as described.

regards,
Umesh