MySQL Bugs: #112180: "Skip Scan Range Access Method" may lead to incorrect query results

Bug #112180	"Skip Scan Range Access Method" may lead to incorrect query results
Submitted:	25 Aug 2023 8:50	Modified:	29 Aug 2023 9:51
Reporter:	Zhang JiYang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.0.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This issue is the same as the following one:

https://bugs.mysql.com/bug.php?id=104406

How to repeat:
CREATE TABLE `foo_t` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `schema_id` bigint unsigned NOT NULL,
  `name` varchar(64),
  `type` enum('BASE TABLE','VIEW','SYSTEM VIEW'),
  `engine` varchar(64),
  PRIMARY KEY (`id`),
  UNIQUE KEY `schema_id` (`schema_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 STATS_PERSISTENT=0;

--disable_query_log
connect (foo,localhost,root,,);
connection foo;
begin;
select count(*) from mysql.user;

connection default;

let $j = 5;
while ($j)
{
let $i = 1000;
while ($i)
{
  dec $i;
  eval insert into foo_t (schema_id, name, type, engine)
  values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));
}
delete from foo_t where id < 1000000;
dec $j;
}

--enable_query_log

let $i = 670;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

let $i = 675;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

connect (foo1,localhost,root,,);
connection foo1;

# ERROR. The result cannot be read. But, in fact, it should be readable.
let $cnt1 = `select count(1) from foo_t force index(schema_id) where name like 'name_670'`;
# If the query uses a primary key index, the data is read correctly.
let $cnt2 = `select count(1) from foo_t force index(primary) where name like 'name_670'`;

--let $assert_text= Assert value should be same
--let $assert_cond = ($cnt1 = $cnt2)
--source include/assert.inc

drop table foo_t;

Hello zanye zjy,

Thank you for the report and test case.
I quickly checked against 8.0.32/8.0.33 and 8.0.34 but observed that 8.0.34 is not affected.   I've used release build(binary tarball). Could you please share the cmake options used for the build? Thank you.

- 8.0.34 not affected (both queries return row)

[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.0.34/mysql-test]$ ./mtr bug112180 --nocheck-testcases
Logging: ./mtr  bug112180 --nocheck-testcases
MySQL Version 8.0.34
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.34/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE `foo_t` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`schema_id` bigint unsigned NOT NULL,
`name` varchar(64),
`type` enum('BASE TABLE','VIEW','SYSTEM VIEW'),
`engine` varchar(64),
PRIMARY KEY (`id`),
UNIQUE KEY `schema_id` (`schema_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 STATS_PERSISTENT=0;
count(*)
4
insert into foo_t (schema_id, name, type, engine)
values (670 % 6, CONCAT("name_", 670), 'SYSTEM VIEW', repeat('a', 60));
insert into foo_t (schema_id, name, type, engine)
values (675 % 6, CONCAT("name_", 675), 'SYSTEM VIEW', repeat('a', 60));
select count(1) from foo_t force index(schema_id) where name like 'name_670';
count(1)
1
select count(1) from foo_t force index(primary) where name like 'name_670';
count(1)
1
include/assert.inc [Assert value should be same]
drop table foo_t;
[ 50%] main.bug112180                            [ pass ]  14588
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 14.588 of 22 seconds executing testcases

- 8.0.32/33 - affected (first query returns no rows where the one which is using primary returns 1)

./mtr bug112180 --nocheck-testcases
Logging: ./mtr  bug112180 --nocheck-testcases
MySQL Version 8.0.33
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
 - WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.33/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE `foo_t` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`schema_id` bigint unsigned NOT NULL,
`name` varchar(64),
`type` enum('BASE TABLE','VIEW','SYSTEM VIEW'),
`engine` varchar(64),
PRIMARY KEY (`id`),
UNIQUE KEY `schema_id` (`schema_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 STATS_PERSISTENT=0;
count(*)
4
insert into foo_t (schema_id, name, type, engine)
values (670 % 6, CONCAT("name_", 670), 'SYSTEM VIEW', repeat('a', 60));
insert into foo_t (schema_id, name, type, engine)
values (675 % 6, CONCAT("name_", 675), 'SYSTEM VIEW', repeat('a', 60));
select count(1) from foo_t force index(schema_id) where name like 'name_670';
count(1)
0
select count(1) from foo_t force index(primary) where name like 'name_670';
count(1)
1
include/assert.inc [Assert value should be same]
######## Test assertion failed: Assert value should be same ########
Dumping debug info:
Assertion text: 'Assert value should be same'
Assertion condition: '(0 = 1)'
Assertion condition, interpolated: '(0 = 1)'
Assertion result: '0'
[ 50%] main.bug112180                            [ fail ]
        Test ended at 2023-08-25 12:10:57

CURRENT_TEST: main.bug112180
mysqltest: At line 96: Test assertion failed in assert.inc
In included file ./include/assert.inc: 97

regards,
Umesh

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.