Bug #119152 ​​Anomalous transaction snapshot version behavior under the RR isolation level.​

• Submitted: 14 Oct 2025 12:01
• Reporter: Hongyu Yang
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 8.4, 8.0.22
• OS: Any
• CPU Architecture: Any

[14 Oct 2025 12:01] Hongyu Yang

Description:
The second SELECT in session2 should not return any rows.

How to repeat:
-- init
create table table1 (pkId integer, col0 integer);
create table table2 (pkId integer, col1 integer, col2 integer);

alter table `table2` add index idx1(col1, col2);

-- execute txns

/* session 1 */
begin;
SET SESSION transaction_isolation = 'REPEATABLE-READ';
insert into `table1`(`pkId`, `col0`) values (1, 1);

/* session 2 */
begin;
SET SESSION transaction_isolation = 'REPEATABLE-READ';
select * from table2 where col1 = null and col2 = 23;

/* session 1 */
commit;

/* session 2 */
select * from table1; -- (1,1) 
commit ;

Under the RR isolation level, the second SELECT statement in session2 should not retrieve the newly inserted data from session1.​​

This is because session1 committed after session2's first SELECT statement, and session2's snapshot should not reflect session1's changes.

Additionally, we observed that:​​
​​1. Without an index on table2​​, or
​​2. If the query in session2 is modified to use IS NULL​​ (e.g., SELECT * FROM table2 WHERE col1 IS NULL AND col2 = 23),
​​then the second SELECT in session2 returns no rows, which aligns with expectations.​

​Could this be a bug related to the query execution plan or the optimizer?​