Bug #119325 SELECT does not create a snapshot when the WHERE condition evaluates to false

Submitted:	7 Nov 2025 7:33
Reporter:	Dai Dinary	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	9.5.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ISOLATION, repeatable read, transaction

Description:
Isolation Level: Repeatable Read.
The SELECT statement does not create a snapshot when the WHERE condition evaluates to false. 

How to repeat:
/* init */ DROP TABLE IF EXISTS t;
/* init */ CREATE TABLE t (c1 CHAR(1));
/* init */ INSERT INTO t(c1) VALUES ('A');
 
/* t1 */ BEGIN;
/* t2 */ BEGIN;
/* t2 */ SELECT * FROM t WHERE TIMESTAMPDIFF(MONTH,'2025-11-6','2025-11-11');
/* t1 */ DELETE FROM t WHERE TRUE;
/* t2 */ INSERT INTO t(c1) VALUES ('B'); -- blocked
/* t1 */ COMMIT; -- t2 unblocked
/* t2 */ SELECT * FROM t ; -- [('B')]
/* t2 */ COMMIT;

The expectation is that transaction t2 will establish a snapshot at the first SELECT statement, so that the second SELECT statement can retrieve [('A'), ('B')]. However, in actual execution, the second SELECT statement in transaction t2 only retrieved [('B')] when the WHERE condition of the first SELECT statement was evaluated as false.

Page generated in 0.037 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.