Description:
Comparing an indexed DATETIME(N) column for equality against a constant that
carries more fractional-second digits than N returns a row that does not satisfy
the predicate. The constant is narrowed onto the index key and the original
predicate is never re-checked against the row the index lookup produced.
The server will report, in the same result set, that the predicate is false for
the row it just returned:
SELECT d, (d = '2020-01-01 00:00:00.4') AS predicate
FROM t WHERE d = '2020-01-01 00:00:00.4';
+---------------------+-----------+
| d | predicate |
+---------------------+-----------+
| 2020-01-01 00:00:00 | 0 |
+---------------------+-----------+
Suppressing the index gives the right answer, which is what makes this an
optimizer defect rather than a question about temporal comparison semantics:
SELECT count(*) FROM t IGNORE INDEX(d)
WHERE d = '2020-01-01 00:00:00.4'; -- 0, correct
EXPLAIN on trunk shows the constant at full precision in the lookup that
produced a row not equal to it:
-> Covering index lookup on t using d
(d = TIMESTAMP'2020-01-01 00:00:00.4') (cost=0.35 rows=1)
SHOW WARNINGS is empty.
--------------------------------------------------------------------------------
The boundary is exactly the declared precision
--------------------------------------------------------------------------------
For each DATETIME(N), a constant with N fractional digits is handled correctly
and one with N+1 is not. Measured on 26.10.0 for every legal precision:
column constant with index correct
DATETIME '2020-01-01 00:00:00' 1 1
DATETIME '2020-01-01 00:00:00.4' 1 0 WRONG
DATETIME(1) '2020-01-01 00:00:00.4' 0 0
DATETIME(1) '2020-01-01 00:00:00.04' 1 0 WRONG
DATETIME(2) '2020-01-01 00:00:00.04' 0 0
DATETIME(2) '2020-01-01 00:00:00.004' 1 0 WRONG
DATETIME(3) '2020-01-01 00:00:00.004' 0 0
DATETIME(3) '2020-01-01 00:00:00.0004' 1 0 WRONG
DATETIME(4) '2020-01-01 00:00:00.0004' 0 0
DATETIME(4) '2020-01-01 00:00:00.00004' 1 0 WRONG
DATETIME(5) '2020-01-01 00:00:00.00004' 0 0
DATETIME(5) '2020-01-01 00:00:00.000004' 1 0 WRONG
DATETIME(6) '2020-01-01 00:00:00.000004' 0 0
DATETIME(6) '2020-01-01 00:00:00.0000004' 1 1 ok
The last line is not an exception -- seven fractional digits exceed what the
server accepts at all, so the constant is legitimately reduced to six before
anything else happens, and both paths then agree.
--------------------------------------------------------------------------------
Which row comes back follows the rounding rule
--------------------------------------------------------------------------------
The narrowed key is the constant rounded to the column's precision, so the row
returned changes at the half-way point. With rows '...00:00:00' and
'...00:00:01':
SELECT id FROM t2 WHERE d = '2020-01-01 00:00:00.4'; -- 1
SELECT id FROM t2 WHERE d = '2020-01-01 00:00:00.6'; -- 2
Neither row satisfies its predicate. Under TIME_TRUNCATE_FRACTIONAL the
direction changes and '...00.6' returns row 1 instead -- still wrong, now wrong
the other way.
--------------------------------------------------------------------------------
Scope, as measured on 26.10.0
--------------------------------------------------------------------------------
Types DATETIME and TIMESTAMP affected. TIME and DATE correct.
Predicates d = K, d IN (K) and d BETWEEN K AND K all affected -- the last
two are equality written differently. Genuine ranges (<, <=,
>, >=, <>) are correct, because a bound rounded to the column's
precision still selects the same row set.
Index shapes KEY(d), the leading column of KEY(d, o), and KEY(d DESC) are
affected. KEY(o, d) is not, since d cannot be searched alone.
A table with no index is correct.
Composite Also reproduces on the second column once the first is pinned:
WHERE a = 1 AND b = '2020-01-01 00:00:00.4'.
Constant A literal, a client-side bound parameter and a server-side
delivery PREPARE/EXECUTE all give the same wrong answer. Using bound
parameters is not a workaround.
Persistence UPDATE ... WHERE p and DELETE ... WHERE p affect 0 rows, so the
table being read is not corrupted. But
CREATE TABLE cp AS SELECT * FROM t WHERE d = '...00.4'
COPIES the spurious row into the new table, where it persists.
How to repeat:
Needs no data files. Against a stock server, default sql_mode:
CREATE DATABASE IF NOT EXISTS t077; USE t077;
CREATE TABLE t(d DATETIME, KEY(d)) ENGINE=InnoDB; -- DATETIME = DATETIME(0)
INSERT INTO t VALUES ('2020-01-01 00:00:00'),
('2020-01-01 00:00:01'),
('2020-01-01 00:00:02');
SELECT count(*) FROM t WHERE d = '2020-01-01 00:00:00.4';
-- got 1, expected 0 -- no row holds 2020-01-01 00:00:00.4
The control that makes it an optimizer defect:
SELECT count(*) FROM t IGNORE INDEX(d)
WHERE d = '2020-01-01 00:00:00.4'; -- 0, correct
The self-contradiction, in one result set:
SELECT d, (d = '2020-01-01 00:00:00.4') AS predicate
FROM t WHERE d = '2020-01-01 00:00:00.4';
-- 2020-01-01 00:00:00 | 0
The row is returned by the WHERE clause, and the same WHERE expression evaluated
as a value on that row is 0. SHOW WARNINGS is empty.
Which row comes back follows the rounding:
CREATE TABLE t2(id INT PRIMARY KEY, d DATETIME, KEY(d)) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,'2020-01-01 00:00:00'), (2,'2020-01-01 00:00:01');
SELECT id FROM t2 WHERE d = '2020-01-01 00:00:00.4'; -- 1
SELECT id FROM t2 WHERE d = '2020-01-01 00:00:00.6'; -- 2
The spurious row persists if it is copied:
CREATE TABLE cp AS SELECT * FROM t WHERE d = '2020-01-01 00:00:00.4';
SELECT * FROM cp; -- holds 2020-01-01 00:00:00
Every legal precision, showing the boundary is exactly N:
CREATE TABLE p1(d DATETIME(1), KEY(d)); INSERT INTO p1 VALUES ('2020-01-01 00:00:00');
SELECT count(*) FROM p1 WHERE d = '2020-01-01 00:00:00.4'; -- 0, correct
SELECT count(*) FROM p1 WHERE d = '2020-01-01 00:00:00.04'; -- 1, WRONG
-- and so on through DATETIME(5); see the table in the Description.
Reproduction script: repro.py in the attached folder, run as
python3 repro.py --port <port>
It exits non-zero if any row is returned whose own predicate evaluates to false,
and prints the per-precision table.
Source build used:
git clone https://github.com/mysql/mysql-server.git # trunk, fcf22d3
cmake -S mysql-trunk -B build -G Ninja \
-DCMAKE_BUILD_TYPE=RelWithDebInfo -DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=../boost -DWITH_UNIT_TESTS=OFF \
-DWITH_ROUTER=OFF -DWITH_MYSQLX=OFF
Suggested fix:
Either re-check the original predicate against the row the index lookup
produced, or refuse the index lookup when narrowing the constant to the key's
precision is not exact -- the same treatment Bug #16249 applied to invalid
datetime constants in range analysis, and the same property the numeric path now
appears to have after #108765.
Declining the optimization costs nothing on the overwhelmingly common case where
the constant fits the column's precision, since that case narrows exactly.
A regression test needs an indexed DATETIME(N) column, a constant with N+1
fractional digits, and an assertion that the row set matches IGNORE INDEX. The
cheapest single assertion is that
SELECT d, (d = K) FROM t WHERE d = K
never returns a row whose second column is 0. The existing coverage compares
against constants that fit the declared precision, which take the exact path and
are correct, which is why this survived.
© 2026, Oracle Corporation and/or its affiliates
Description: Comparing an indexed DATETIME(N) column for equality against a constant that carries more fractional-second digits than N returns a row that does not satisfy the predicate. The constant is narrowed onto the index key and the original predicate is never re-checked against the row the index lookup produced. The server will report, in the same result set, that the predicate is false for the row it just returned: SELECT d, (d = '2020-01-01 00:00:00.4') AS predicate FROM t WHERE d = '2020-01-01 00:00:00.4'; +---------------------+-----------+ | d | predicate | +---------------------+-----------+ | 2020-01-01 00:00:00 | 0 | +---------------------+-----------+ Suppressing the index gives the right answer, which is what makes this an optimizer defect rather than a question about temporal comparison semantics: SELECT count(*) FROM t IGNORE INDEX(d) WHERE d = '2020-01-01 00:00:00.4'; -- 0, correct EXPLAIN on trunk shows the constant at full precision in the lookup that produced a row not equal to it: -> Covering index lookup on t using d (d = TIMESTAMP'2020-01-01 00:00:00.4') (cost=0.35 rows=1) SHOW WARNINGS is empty. -------------------------------------------------------------------------------- The boundary is exactly the declared precision -------------------------------------------------------------------------------- For each DATETIME(N), a constant with N fractional digits is handled correctly and one with N+1 is not. Measured on 26.10.0 for every legal precision: column constant with index correct DATETIME '2020-01-01 00:00:00' 1 1 DATETIME '2020-01-01 00:00:00.4' 1 0 WRONG DATETIME(1) '2020-01-01 00:00:00.4' 0 0 DATETIME(1) '2020-01-01 00:00:00.04' 1 0 WRONG DATETIME(2) '2020-01-01 00:00:00.04' 0 0 DATETIME(2) '2020-01-01 00:00:00.004' 1 0 WRONG DATETIME(3) '2020-01-01 00:00:00.004' 0 0 DATETIME(3) '2020-01-01 00:00:00.0004' 1 0 WRONG DATETIME(4) '2020-01-01 00:00:00.0004' 0 0 DATETIME(4) '2020-01-01 00:00:00.00004' 1 0 WRONG DATETIME(5) '2020-01-01 00:00:00.00004' 0 0 DATETIME(5) '2020-01-01 00:00:00.000004' 1 0 WRONG DATETIME(6) '2020-01-01 00:00:00.000004' 0 0 DATETIME(6) '2020-01-01 00:00:00.0000004' 1 1 ok The last line is not an exception -- seven fractional digits exceed what the server accepts at all, so the constant is legitimately reduced to six before anything else happens, and both paths then agree. -------------------------------------------------------------------------------- Which row comes back follows the rounding rule -------------------------------------------------------------------------------- The narrowed key is the constant rounded to the column's precision, so the row returned changes at the half-way point. With rows '...00:00:00' and '...00:00:01': SELECT id FROM t2 WHERE d = '2020-01-01 00:00:00.4'; -- 1 SELECT id FROM t2 WHERE d = '2020-01-01 00:00:00.6'; -- 2 Neither row satisfies its predicate. Under TIME_TRUNCATE_FRACTIONAL the direction changes and '...00.6' returns row 1 instead -- still wrong, now wrong the other way. -------------------------------------------------------------------------------- Scope, as measured on 26.10.0 -------------------------------------------------------------------------------- Types DATETIME and TIMESTAMP affected. TIME and DATE correct. Predicates d = K, d IN (K) and d BETWEEN K AND K all affected -- the last two are equality written differently. Genuine ranges (<, <=, >, >=, <>) are correct, because a bound rounded to the column's precision still selects the same row set. Index shapes KEY(d), the leading column of KEY(d, o), and KEY(d DESC) are affected. KEY(o, d) is not, since d cannot be searched alone. A table with no index is correct. Composite Also reproduces on the second column once the first is pinned: WHERE a = 1 AND b = '2020-01-01 00:00:00.4'. Constant A literal, a client-side bound parameter and a server-side delivery PREPARE/EXECUTE all give the same wrong answer. Using bound parameters is not a workaround. Persistence UPDATE ... WHERE p and DELETE ... WHERE p affect 0 rows, so the table being read is not corrupted. But CREATE TABLE cp AS SELECT * FROM t WHERE d = '...00.4' COPIES the spurious row into the new table, where it persists. How to repeat: Needs no data files. Against a stock server, default sql_mode: CREATE DATABASE IF NOT EXISTS t077; USE t077; CREATE TABLE t(d DATETIME, KEY(d)) ENGINE=InnoDB; -- DATETIME = DATETIME(0) INSERT INTO t VALUES ('2020-01-01 00:00:00'), ('2020-01-01 00:00:01'), ('2020-01-01 00:00:02'); SELECT count(*) FROM t WHERE d = '2020-01-01 00:00:00.4'; -- got 1, expected 0 -- no row holds 2020-01-01 00:00:00.4 The control that makes it an optimizer defect: SELECT count(*) FROM t IGNORE INDEX(d) WHERE d = '2020-01-01 00:00:00.4'; -- 0, correct The self-contradiction, in one result set: SELECT d, (d = '2020-01-01 00:00:00.4') AS predicate FROM t WHERE d = '2020-01-01 00:00:00.4'; -- 2020-01-01 00:00:00 | 0 The row is returned by the WHERE clause, and the same WHERE expression evaluated as a value on that row is 0. SHOW WARNINGS is empty. Which row comes back follows the rounding: CREATE TABLE t2(id INT PRIMARY KEY, d DATETIME, KEY(d)) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,'2020-01-01 00:00:00'), (2,'2020-01-01 00:00:01'); SELECT id FROM t2 WHERE d = '2020-01-01 00:00:00.4'; -- 1 SELECT id FROM t2 WHERE d = '2020-01-01 00:00:00.6'; -- 2 The spurious row persists if it is copied: CREATE TABLE cp AS SELECT * FROM t WHERE d = '2020-01-01 00:00:00.4'; SELECT * FROM cp; -- holds 2020-01-01 00:00:00 Every legal precision, showing the boundary is exactly N: CREATE TABLE p1(d DATETIME(1), KEY(d)); INSERT INTO p1 VALUES ('2020-01-01 00:00:00'); SELECT count(*) FROM p1 WHERE d = '2020-01-01 00:00:00.4'; -- 0, correct SELECT count(*) FROM p1 WHERE d = '2020-01-01 00:00:00.04'; -- 1, WRONG -- and so on through DATETIME(5); see the table in the Description. Reproduction script: repro.py in the attached folder, run as python3 repro.py --port <port> It exits non-zero if any row is returned whose own predicate evaluates to false, and prints the per-precision table. Source build used: git clone https://github.com/mysql/mysql-server.git # trunk, fcf22d3 cmake -S mysql-trunk -B build -G Ninja \ -DCMAKE_BUILD_TYPE=RelWithDebInfo -DDOWNLOAD_BOOST=1 \ -DWITH_BOOST=../boost -DWITH_UNIT_TESTS=OFF \ -DWITH_ROUTER=OFF -DWITH_MYSQLX=OFF Suggested fix: Either re-check the original predicate against the row the index lookup produced, or refuse the index lookup when narrowing the constant to the key's precision is not exact -- the same treatment Bug #16249 applied to invalid datetime constants in range analysis, and the same property the numeric path now appears to have after #108765. Declining the optimization costs nothing on the overwhelmingly common case where the constant fits the column's precision, since that case narrows exactly. A regression test needs an indexed DATETIME(N) column, a constant with N+1 fractional digits, and an assertion that the row set matches IGNORE INDEX. The cheapest single assertion is that SELECT d, (d = K) FROM t WHERE d = K never returns a row whose second column is 0. The existing coverage compares against constants that fit the declared precision, which take the exact path and are correct, which is why this survived.