Description:
With an index on a DATETIME(N) or TIMESTAMP(N) column and a range predicate
whose constant has N+1 fractional digits, the MIN/MAX index optimization
(EXPLAIN: "Rows fetched before execution" / "Zero input rows (No matching
min/max row)") rounds the constant onto the index key and uses it as the range
endpoint WITHOUT ADJUSTING THE COMPARISON FOR THE DIRECTION THE ROUNDING MOVED
IT.
Rounding is half away from zero, so the endpoint moves up, and:
- MAX(d) ... WHERE d <= K admits a row greater than K and returns it;
- MIN(d) ... WHERE d > K excludes the matching row and returns NULL, and the
count(*) computed beside it becomes 0 although the same predicate alone
counts 1.
MAX(d) ... WHERE d < K and MIN(d) ... WHERE d >= K are correct, because for
those two the upward move happens to be harmless. Every precision N = 0...5
behaves identically, for both DATETIME and TIMESTAMP. A constant that fits the
declared precision is always correct.
No warning is raised.
--------------------------------------------------------------------------------
The value returned is one the server itself rejects
--------------------------------------------------------------------------------
SELECT '2020-01-01 00:00:02' <= '2020-01-01 00:00:01.5'; -- 0
SELECT MAX(d) FROM t WHERE d <= '2020-01-01 00:00:01.5';
-- 2020-01-01 00:00:02
--------------------------------------------------------------------------------
Three other paths in the same server give the correct answer
--------------------------------------------------------------------------------
SELECT MAX(d) FROM t IGNORE INDEX(d)
WHERE d <= '2020-01-01 00:00:01.5'; -- 00:00:01
SELECT d FROM t WHERE d <= '2020-01-01 00:00:01.5'
ORDER BY d DESC LIMIT 1; -- 00:00:01
SELECT DISTINCT MAX(d) OVER () FROM t
WHERE d <= '2020-01-01 00:00:01.5'; -- 00:00:01
So does the plain range scan, whose plan shows the correct pattern for handling
a narrowed constant:
-> Filter: (t.d > TIMESTAMP'2020-01-01 00:00:01.5')
-> Covering index range scan on t using d
over ('2020-01-01 00:00:02' <= d)
It narrows the same constant, WIDENS the operator so the range is a superset,
and re-checks each row exactly. The MIN/MAX shortcut narrows without widening
and cannot re-check, since it reads an index endpoint rather than rows.
--------------------------------------------------------------------------------
Root cause
--------------------------------------------------------------------------------
sql/opt_sum.cc, matching_cond(), lines 942-962 on trunk. The endpoint is stored
with
value->save_in_field_no_warnings(part->field, true)
under a comment that says "A perfect save is necessary. Truncated / incorrect
value can result in an incorrect index lookup", and the result is rejected
unless it is TYPE_OK.
But a temporal store that rounds the fractional part away is a documented,
successful store: store_internal_adjust_frac() (sql/field.cc:5134) ->
my_datetime_adjust_frac() (mysys/my_time.cc:2451, "/* Add half away from zero
*/") returns TYPE_OK.
The guard asks whether the field ACCEPTED the value; it needs to ask whether the
COMPARISON IS UNCHANGED.
--------------------------------------------------------------------------------
Prior art
--------------------------------------------------------------------------------
The nearest ticket is MariaDB's MDEV-39192 -- the same guard site reached by a
different route, for a VARCHAR column with a numeric constant, confirmed six
months ago. That it is the same code path for a different value class makes
this instance easier to accept and harder to dismiss as documented rounding.
Searches on bugs.mysql.com (all statuses) for combinations of MIN, MAX, index,
fractional seconds, DATETIME, "No matching min/max row" and "Select tables
optimized away" returned nothing describing this defect. See also #36300
(MIN/MAX with NOT BETWEEN, Verified, open) and #16249 (range analysis with
invalid datetime constants, fixed 5.0.25) -- neither is this.
How to repeat:
Needs no data files. Against a stock server, default sql_mode:
CREATE DATABASE IF NOT EXISTS t083; USE t083;
CREATE TABLE t(d DATETIME, KEY(d)) ENGINE=InnoDB;
INSERT INTO t VALUES ('2020-01-01 00:00:00'),
('2020-01-01 00:00:01'),
('2020-01-01 00:00:02');
-- 1. the aggregate is not among the rows counted beside it
SELECT MAX(d), count(*) FROM t WHERE d <= '2020-01-01 00:00:01.5';
-- got MAX(d) = 2020-01-01 00:00:02, count(*) = 2
-- expected MAX(d) = 2020-01-01 00:00:01, count(*) = 2
SELECT d FROM t WHERE d <= '2020-01-01 00:00:01.5';
-- 2020-01-01 00:00:00, 2020-01-01 00:00:01 -- 00:00:02 is not among
-- them
SELECT '2020-01-01 00:00:02' <= '2020-01-01 00:00:01.5'; -- 0
-- 2. adding MIN(d) to the select list changes count(*)
SELECT count(*) FROM t WHERE d > '2020-01-01 00:00:01.5'; -- 1
SELECT count(*), MIN(d) FROM t WHERE d > '2020-01-01 00:00:01.5'; -- 0, NULL
EXPLAIN SELECT MIN(d) FROM t WHERE d > '2020-01-01 00:00:01.5';
-- Zero input rows (No matching min/max row), aggregated into one output row
-- (trunk defaults explain_format=TREE; with SET explain_format=TRADITIONAL
-- the Extra column of the MAX query reads "Select tables optimized away")
-- 3. the same question, three correct answers on the same server
SELECT MAX(d) FROM t IGNORE INDEX(d)
WHERE d <= '2020-01-01 00:00:01.5'; -- 00:00:01
SELECT d FROM t WHERE d <= '2020-01-01 00:00:01.5'
ORDER BY d DESC LIMIT 1; -- 00:00:01
SELECT DISTINCT MAX(d) OVER () FROM t
WHERE d <= '2020-01-01 00:00:01.5'; -- 00:00:01
-- 4. control: a constant that fits the column's precision is correct
SELECT MAX(d), count(*) FROM t WHERE d <= '2020-01-01 00:00:01';
-- 2020-01-01 00:00:01, 2 correct
-- 5. the two operators that are correct, for contrast
SELECT MAX(d) FROM t WHERE d < '2020-01-01 00:00:01.5'; -- 00:00:01 ok
SELECT MIN(d) FROM t WHERE d >= '2020-01-01 00:00:01.5'; -- 00:00:02 ok
TIMESTAMP behaves the same, and so does every precision DATETIME(1) ...
DATETIME(5) with K one digit finer -- e.g. for DATETIME(3), rows '...01.000' and
'...01.001' with K = '...01.0005':
CREATE TABLE t3(d DATETIME(3), KEY(d)) ENGINE=InnoDB;
INSERT INTO t3 VALUES ('2020-01-01 00:00:01.000'),
('2020-01-01 00:00:01.001');
SELECT MAX(d), count(*) FROM t3 WHERE d <= '2020-01-01 00:00:01.0005';
-- got 2020-01-01 00:00:01.001, 1 ; expected 2020-01-01 00:00:01.000, 1
verify-main.py in the attached folder sweeps all 96 combinations (DATETIME and
TIMESTAMP, precisions 0-5, four operators, indexed vs IGNORE INDEX) and prints
the transcript in main-verification-2026-09-20.log.
Source build used:
git clone https://github.com/mysql/mysql-server.git # trunk, fcf22d3
cmake ... -DCMAKE_BUILD_TYPE=RelWithDebInfo && make -j56
Suggested fix:
In sql/opt_sum.cc matching_cond(), treat a narrowing store as a failed save for
this purpose. Either:
- compare the stored key back against the original constant and return false
(declining the optimization) when they are not equal -- the safe, minimal
change, which costs nothing on the overwhelmingly common case where the
constant fits; or
- do what the range optimizer already does: move the endpoint in the SAFE
direction for the operator (round up for an upper bound written with <=,
down for a lower bound written with >=) and, where that is not possible
without a re-check, decline.
The first is one comparison and covers every value class that can narrow, not
only temporal ones. The same guard site is what MariaDB's MDEV-39192 (VARCHAR
column, numeric constant) reaches by a different route.
A regression test needs an indexed DATETIME(N) column, two rows one unit apart,
and a constant with N+1 digits placed between them, asserting all four operators
against IGNORE INDEX. Asserting that
SELECT MAX(d), count(*) FROM t WHERE d <= K
never returns a MAX(d) that fails d <= K is the cheapest single check.
© 2026, Oracle Corporation and/or its affiliates
Description: With an index on a DATETIME(N) or TIMESTAMP(N) column and a range predicate whose constant has N+1 fractional digits, the MIN/MAX index optimization (EXPLAIN: "Rows fetched before execution" / "Zero input rows (No matching min/max row)") rounds the constant onto the index key and uses it as the range endpoint WITHOUT ADJUSTING THE COMPARISON FOR THE DIRECTION THE ROUNDING MOVED IT. Rounding is half away from zero, so the endpoint moves up, and: - MAX(d) ... WHERE d <= K admits a row greater than K and returns it; - MIN(d) ... WHERE d > K excludes the matching row and returns NULL, and the count(*) computed beside it becomes 0 although the same predicate alone counts 1. MAX(d) ... WHERE d < K and MIN(d) ... WHERE d >= K are correct, because for those two the upward move happens to be harmless. Every precision N = 0...5 behaves identically, for both DATETIME and TIMESTAMP. A constant that fits the declared precision is always correct. No warning is raised. -------------------------------------------------------------------------------- The value returned is one the server itself rejects -------------------------------------------------------------------------------- SELECT '2020-01-01 00:00:02' <= '2020-01-01 00:00:01.5'; -- 0 SELECT MAX(d) FROM t WHERE d <= '2020-01-01 00:00:01.5'; -- 2020-01-01 00:00:02 -------------------------------------------------------------------------------- Three other paths in the same server give the correct answer -------------------------------------------------------------------------------- SELECT MAX(d) FROM t IGNORE INDEX(d) WHERE d <= '2020-01-01 00:00:01.5'; -- 00:00:01 SELECT d FROM t WHERE d <= '2020-01-01 00:00:01.5' ORDER BY d DESC LIMIT 1; -- 00:00:01 SELECT DISTINCT MAX(d) OVER () FROM t WHERE d <= '2020-01-01 00:00:01.5'; -- 00:00:01 So does the plain range scan, whose plan shows the correct pattern for handling a narrowed constant: -> Filter: (t.d > TIMESTAMP'2020-01-01 00:00:01.5') -> Covering index range scan on t using d over ('2020-01-01 00:00:02' <= d) It narrows the same constant, WIDENS the operator so the range is a superset, and re-checks each row exactly. The MIN/MAX shortcut narrows without widening and cannot re-check, since it reads an index endpoint rather than rows. -------------------------------------------------------------------------------- Root cause -------------------------------------------------------------------------------- sql/opt_sum.cc, matching_cond(), lines 942-962 on trunk. The endpoint is stored with value->save_in_field_no_warnings(part->field, true) under a comment that says "A perfect save is necessary. Truncated / incorrect value can result in an incorrect index lookup", and the result is rejected unless it is TYPE_OK. But a temporal store that rounds the fractional part away is a documented, successful store: store_internal_adjust_frac() (sql/field.cc:5134) -> my_datetime_adjust_frac() (mysys/my_time.cc:2451, "/* Add half away from zero */") returns TYPE_OK. The guard asks whether the field ACCEPTED the value; it needs to ask whether the COMPARISON IS UNCHANGED. -------------------------------------------------------------------------------- Prior art -------------------------------------------------------------------------------- The nearest ticket is MariaDB's MDEV-39192 -- the same guard site reached by a different route, for a VARCHAR column with a numeric constant, confirmed six months ago. That it is the same code path for a different value class makes this instance easier to accept and harder to dismiss as documented rounding. Searches on bugs.mysql.com (all statuses) for combinations of MIN, MAX, index, fractional seconds, DATETIME, "No matching min/max row" and "Select tables optimized away" returned nothing describing this defect. See also #36300 (MIN/MAX with NOT BETWEEN, Verified, open) and #16249 (range analysis with invalid datetime constants, fixed 5.0.25) -- neither is this. How to repeat: Needs no data files. Against a stock server, default sql_mode: CREATE DATABASE IF NOT EXISTS t083; USE t083; CREATE TABLE t(d DATETIME, KEY(d)) ENGINE=InnoDB; INSERT INTO t VALUES ('2020-01-01 00:00:00'), ('2020-01-01 00:00:01'), ('2020-01-01 00:00:02'); -- 1. the aggregate is not among the rows counted beside it SELECT MAX(d), count(*) FROM t WHERE d <= '2020-01-01 00:00:01.5'; -- got MAX(d) = 2020-01-01 00:00:02, count(*) = 2 -- expected MAX(d) = 2020-01-01 00:00:01, count(*) = 2 SELECT d FROM t WHERE d <= '2020-01-01 00:00:01.5'; -- 2020-01-01 00:00:00, 2020-01-01 00:00:01 -- 00:00:02 is not among -- them SELECT '2020-01-01 00:00:02' <= '2020-01-01 00:00:01.5'; -- 0 -- 2. adding MIN(d) to the select list changes count(*) SELECT count(*) FROM t WHERE d > '2020-01-01 00:00:01.5'; -- 1 SELECT count(*), MIN(d) FROM t WHERE d > '2020-01-01 00:00:01.5'; -- 0, NULL EXPLAIN SELECT MIN(d) FROM t WHERE d > '2020-01-01 00:00:01.5'; -- Zero input rows (No matching min/max row), aggregated into one output row -- (trunk defaults explain_format=TREE; with SET explain_format=TRADITIONAL -- the Extra column of the MAX query reads "Select tables optimized away") -- 3. the same question, three correct answers on the same server SELECT MAX(d) FROM t IGNORE INDEX(d) WHERE d <= '2020-01-01 00:00:01.5'; -- 00:00:01 SELECT d FROM t WHERE d <= '2020-01-01 00:00:01.5' ORDER BY d DESC LIMIT 1; -- 00:00:01 SELECT DISTINCT MAX(d) OVER () FROM t WHERE d <= '2020-01-01 00:00:01.5'; -- 00:00:01 -- 4. control: a constant that fits the column's precision is correct SELECT MAX(d), count(*) FROM t WHERE d <= '2020-01-01 00:00:01'; -- 2020-01-01 00:00:01, 2 correct -- 5. the two operators that are correct, for contrast SELECT MAX(d) FROM t WHERE d < '2020-01-01 00:00:01.5'; -- 00:00:01 ok SELECT MIN(d) FROM t WHERE d >= '2020-01-01 00:00:01.5'; -- 00:00:02 ok TIMESTAMP behaves the same, and so does every precision DATETIME(1) ... DATETIME(5) with K one digit finer -- e.g. for DATETIME(3), rows '...01.000' and '...01.001' with K = '...01.0005': CREATE TABLE t3(d DATETIME(3), KEY(d)) ENGINE=InnoDB; INSERT INTO t3 VALUES ('2020-01-01 00:00:01.000'), ('2020-01-01 00:00:01.001'); SELECT MAX(d), count(*) FROM t3 WHERE d <= '2020-01-01 00:00:01.0005'; -- got 2020-01-01 00:00:01.001, 1 ; expected 2020-01-01 00:00:01.000, 1 verify-main.py in the attached folder sweeps all 96 combinations (DATETIME and TIMESTAMP, precisions 0-5, four operators, indexed vs IGNORE INDEX) and prints the transcript in main-verification-2026-09-20.log. Source build used: git clone https://github.com/mysql/mysql-server.git # trunk, fcf22d3 cmake ... -DCMAKE_BUILD_TYPE=RelWithDebInfo && make -j56 Suggested fix: In sql/opt_sum.cc matching_cond(), treat a narrowing store as a failed save for this purpose. Either: - compare the stored key back against the original constant and return false (declining the optimization) when they are not equal -- the safe, minimal change, which costs nothing on the overwhelmingly common case where the constant fits; or - do what the range optimizer already does: move the endpoint in the SAFE direction for the operator (round up for an upper bound written with <=, down for a lower bound written with >=) and, where that is not possible without a re-check, decline. The first is one comparison and covers every value class that can narrow, not only temporal ones. The same guard site is what MariaDB's MDEV-39192 (VARCHAR column, numeric constant) reaches by a different route. A regression test needs an indexed DATETIME(N) column, two rows one unit apart, and a constant with N+1 digits placed between them, asserting all four operators against IGNORE INDEX. Asserting that SELECT MAX(d), count(*) FROM t WHERE d <= K never returns a MAX(d) that fails d <= K is the cheapest single check.