| Bug #119325 | SELECT does not create a snapshot when the WHERE condition evaluates to false | ||
|---|---|---|---|
| Submitted: | 7 Nov 7:33 | ||
| Reporter: | Dai Dinary | Email Updates: | |
| Status: | Open | Impact on me: | |
| 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 | ||
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.037 sec. using MySQL 8.4.5-u5-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.
© 2025, Oracle Corporation and/or its affiliates
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.