Description:
On an indexed integer column, MIN() or MAX() restricted by BETWEEN lo AND hi
returns NULL -- the value meaning "no rows at all" -- while rows plainly satisfy
the predicate.
The MIN/MAX index optimisation converts each BETWEEN bound onto the column's
integer type by ROUNDING HALF AWAY FROM ZERO, where the correct conversion is
ceil for the lower bound and floor for the upper. When rounding moves a bound
the wrong way, the optimiser concludes the interval is empty and skips the
table.
count(*) over the identical predicate returns the right answer in the same
session, so the server contradicts itself. No index hint, no session variable
and no tuning is involved.
SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- NULL
SELECT count(*) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- 3
SELECT MIN(i) FROM t IGNORE INDEX(i)
WHERE i BETWEEN -0.5 AND 2.5; -- 0, correct
EXPLAIN on 26.10.0:
-> Zero input rows (No matching min/max row), aggregated into one output row
(cost=0..0 rows=1)
SHOW WARNINGS is empty.
--------------------------------------------------------------------------------
The rule, and it is exact
--------------------------------------------------------------------------------
For an integer column, i BETWEEN lo AND hi must use the integer interval
[ceil(lo), floor(hi)]. Each bound is instead rounded to nearest, half away from
zero. The two ends are independent and each breaks its own aggregate:
MIN(i) wrong when round(lo) != ceil(lo) correct conversion: ceil
MAX(i) wrong when round(hi) != floor(hi) correct conversion: floor
The rule is exact for bounds that lie INSIDE the range of the indexed data.
Once a bound falls outside that range, rounding moves it further away from every
row, which cannot lose anything, and the rule no longer applies. Swept on
26.10.0 over a 20-value grid confined to the data's range, against a 7-row table
holding -3..3:
grid confined to [-3, 3] 187 non-empty bound pairs
105 lose every matching row
0 mispredictions
the same grid plus the
out-of-range bounds -3.5, 3.5 228 pairs, 118 losing, 41 mispredictions
e.g. lo = -3.5, hi = -3: the rule predicts MIN NULL; MIN is -3, correct
Lower bound swept, upper fixed at 2.0 so MIN alone is under test:
lower MIN via index correct round(lo) ceil(lo)
-0.40 0 0 0 0
-0.49 0 0 0 0
-0.50 NULL 0 -1 0 <<<
-0.51 NULL 0 -1 0 <<<
-1.49 -1 -1 -1 -1
-1.50 NULL -1 -2 -1 <<<
0.0 0 0 0 0
0.01 NULL 1 0 1 <<<
0.49 NULL 1 0 1 <<<
0.50 1 1 1 1
1.01 NULL 2 1 2 <<<
Note 0.01 and 0.49: a POSITIVE lower bound, with no negative number anywhere in
the query, still loses every row.
Upper bound swept, lower fixed at -2.0 so MAX alone is under test:
upper MAX via index correct round(hi) floor(hi)
-0.51 -1 -1 -1 -1
-0.50 -1 -1 -1 -1
-0.49 NULL -1 0 -1 <<<
0.49 0 0 0 0
0.50 NULL 0 1 0 <<<
1.49 1 1 1 1
1.50 NULL 1 2 1 <<<
--------------------------------------------------------------------------------
It is the DECIMAL bound specifically
--------------------------------------------------------------------------------
The constant's type decides it -- a DOUBLE bound takes a different, correct
path. This is the sharpest localisation in the report: the claim is not
"BETWEEN is broken" but "the DECIMAL-to-integer bound conversion in
opt_sum_query uses the wrong rounding mode".
SELECT MIN(i) FROM big WHERE i BETWEEN -0.5 AND 2.5; -- NULL (DECIMAL)
SELECT MIN(i) FROM big WHERE i BETWEEN -0.5e0 AND 2.5e0; -- 0 (DOUBLE,
correct)
SELECT MIN(i) FROM big
WHERE i BETWEEN CAST(-0.5 AS DECIMAL(10,1))
AND CAST(2.5 AS DECIMAL(10,1)); -- NULL
Server-side PREPARE ... EXECUTE ... USING @lo, @hi with SET @lo = -0.5 gives
NULL, so parameterised queries are exposed whenever the driver sends a DECIMAL.
A driver that renders floats in exponent form, as PyMySQL does, accidentally
avoids it -- that is a property of the driver, not a fix.
--------------------------------------------------------------------------------
A realistic shape -- and count(*) goes with it
--------------------------------------------------------------------------------
The two ends fail independently, so a single statement can have one bound clean
and the other not. When that happens the whole statement is optimised away, and
count(*) in the same select list returns 0 while the rows are plainly there:
CREATE TABLE orders(id INT PRIMARY KEY, score INT, KEY(score)) ENGINE=InnoDB;
INSERT INTO orders VALUES (1,0),(2,1),(3,2),(4,3),(5,4);
SELECT MIN(score), MAX(score), count(*) FROM orders
WHERE score BETWEEN 0.5 AND 3.5;
-- got (NULL, NULL, 0) correct is (1, 3, 3)
Here 0.5 is a clean lower bound (round(0.5) = ceil(0.5) = 1) and 3.5 is not
(round(3.5) = 4, floor(3.5) = 3). Taken apart, the same server gets two of the
three right:
SELECT MIN(score) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- 1 ok
SELECT MAX(score) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- NULL wrong
SELECT count(*) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- 3 ok
SELECT MIN(score), MAX(score), count(*) FROM orders
WHERE score BETWEEN 0.5 AND 3.5; -- (NULL, NULL, 0)
SELECT MIN(score), MAX(score), count(*) FROM orders IGNORE INDEX(score)
WHERE score BETWEEN 0.5 AND 3.5; -- (1, 3, 3)
--------------------------------------------------------------------------------
Scope, as measured on 26.10.0
--------------------------------------------------------------------------------
Only the MIN/MAX shortcut -- but it can take the rest of the select list with
it. count(*), sum(), avg(), group_concat(), SELECT * and ordinary range scans
over the identical predicate are correct WHEN NO AFFECTED MIN/MAX IS IN THE
SAME SELECT LIST. When one is, the statement is optimised away and those
aggregates are wrong too. The range scan itself always converts the bounds
correctly.
Every integer type: TINYINT, SMALLINT, INT, BIGINT and INT UNSIGNED
(ui BETWEEN 0.5 AND 2.5 -> NULL, correct (1, 2)). DECIMAL and DOUBLE COLUMNS
are not affected -- there is no narrowing to perform.
BETWEEN contradicts the conjunction it is defined as: i >= -0.5 AND i <= 2.5
returns the correct 0 while i BETWEEN -0.5 AND 2.5 returns NULL. Two
spellings of one range, two answers.
Scale and hints are irrelevant. Identical on a 7-row table with no hint, on a
10 000-row table with the optimizer choosing the index itself, and with
FORCE INDEX.
How to repeat:
Needs no data files. Against a stock server, default sql_mode:
CREATE DATABASE IF NOT EXISTS t079; USE t079;
CREATE TABLE t(i INT, KEY(i)) ENGINE=InnoDB;
INSERT INTO t VALUES (-3),(-2),(-1),(0),(1),(2),(3);
SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5;
-- got NULL, expected 0 -- the rows 0, 1, 2 satisfy the predicate
SELECT count(*) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- 3
SELECT MIN(i) FROM t IGNORE INDEX(i)
WHERE i BETWEEN -0.5 AND 2.5; -- 0, correct
EXPLAIN SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5;
-- Zero input rows (No matching min/max row)
A positive lower bound with no negative number anywhere, to show the sign is not
the trigger:
SELECT MIN(i) FROM t WHERE i BETWEEN 0.01 AND 2.0; -- NULL, expected 1
The MAX end, which fails independently:
SELECT MAX(i) FROM t WHERE i BETWEEN -2.0 AND 0.50; -- NULL, expected 0
The DECIMAL / DOUBLE split, which localises the conversion:
SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- NULL DECIMAL
SELECT MIN(i) FROM t WHERE i BETWEEN -0.5e0 AND 2.5e0; -- 0 DOUBLE, ok
BETWEEN against the conjunction it is defined as:
SELECT MIN(i) FROM t WHERE i >= -0.5 AND i <= 2.5; -- 0, correct
SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- NULL
count(*) dragged down by an affected aggregate beside it:
CREATE TABLE orders(id INT PRIMARY KEY, score INT, KEY(score)) ENGINE=InnoDB;
INSERT INTO orders VALUES (1,0),(2,1),(3,2),(4,3),(5,4);
SELECT count(*) FROM orders WHERE score BETWEEN 0.5 AND 3.5;
-- 3, correct
SELECT MIN(score), MAX(score), count(*) FROM orders
WHERE score BETWEEN 0.5 AND 3.5;
-- (NULL, NULL, 0), correct is (1, 3, 3)
Scale is irrelevant -- the same on a 10 000-row table with no hint:
CREATE TABLE big(i INT, KEY(i)) ENGINE=InnoDB;
INSERT INTO big WITH RECURSIVE s(n) AS
(SELECT -5000 UNION ALL SELECT n+1 FROM s WHERE n < 5000)
SELECT n FROM s;
SELECT MIN(i) FROM big WHERE i BETWEEN -0.5 AND 2.5; -- NULL, expected 0
Reproduction scripts in the attached folder: repro.py (the core case),
rule079.py and rule079b.py (the ceil/floor rule swept over a bound grid),
verify.py and extra.py (type coverage and the DECIMAL/DOUBLE split).
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:
In opt_sum_query, convert a BETWEEN lower bound with ceil and an upper bound
with floor when narrowing a DECIMAL constant onto an integer key, rather than
rounding to nearest.
Alternatively, decline the shortcut when the conversion is inexact and let the
range scan handle it -- the range scan is already correct on every case in this
report, and declining costs nothing on the common case where the bound is
integral.
A regression test needs an indexed integer column, a BETWEEN whose lower bound
has round(lo) != ceil(lo) (for example -0.5, or 0.01), and an assertion that
MIN/MAX match the IGNORE INDEX answer. Asserting that
SELECT MIN(i), count(*) FROM t WHERE i BETWEEN lo AND hi
never returns NULL alongside a non-zero count is the cheapest single check, and
it also covers #36300's NOT BETWEEN case if the two share a fix.
© 2026, Oracle Corporation and/or its affiliates
Description: On an indexed integer column, MIN() or MAX() restricted by BETWEEN lo AND hi returns NULL -- the value meaning "no rows at all" -- while rows plainly satisfy the predicate. The MIN/MAX index optimisation converts each BETWEEN bound onto the column's integer type by ROUNDING HALF AWAY FROM ZERO, where the correct conversion is ceil for the lower bound and floor for the upper. When rounding moves a bound the wrong way, the optimiser concludes the interval is empty and skips the table. count(*) over the identical predicate returns the right answer in the same session, so the server contradicts itself. No index hint, no session variable and no tuning is involved. SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- NULL SELECT count(*) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- 3 SELECT MIN(i) FROM t IGNORE INDEX(i) WHERE i BETWEEN -0.5 AND 2.5; -- 0, correct EXPLAIN on 26.10.0: -> Zero input rows (No matching min/max row), aggregated into one output row (cost=0..0 rows=1) SHOW WARNINGS is empty. -------------------------------------------------------------------------------- The rule, and it is exact -------------------------------------------------------------------------------- For an integer column, i BETWEEN lo AND hi must use the integer interval [ceil(lo), floor(hi)]. Each bound is instead rounded to nearest, half away from zero. The two ends are independent and each breaks its own aggregate: MIN(i) wrong when round(lo) != ceil(lo) correct conversion: ceil MAX(i) wrong when round(hi) != floor(hi) correct conversion: floor The rule is exact for bounds that lie INSIDE the range of the indexed data. Once a bound falls outside that range, rounding moves it further away from every row, which cannot lose anything, and the rule no longer applies. Swept on 26.10.0 over a 20-value grid confined to the data's range, against a 7-row table holding -3..3: grid confined to [-3, 3] 187 non-empty bound pairs 105 lose every matching row 0 mispredictions the same grid plus the out-of-range bounds -3.5, 3.5 228 pairs, 118 losing, 41 mispredictions e.g. lo = -3.5, hi = -3: the rule predicts MIN NULL; MIN is -3, correct Lower bound swept, upper fixed at 2.0 so MIN alone is under test: lower MIN via index correct round(lo) ceil(lo) -0.40 0 0 0 0 -0.49 0 0 0 0 -0.50 NULL 0 -1 0 <<< -0.51 NULL 0 -1 0 <<< -1.49 -1 -1 -1 -1 -1.50 NULL -1 -2 -1 <<< 0.0 0 0 0 0 0.01 NULL 1 0 1 <<< 0.49 NULL 1 0 1 <<< 0.50 1 1 1 1 1.01 NULL 2 1 2 <<< Note 0.01 and 0.49: a POSITIVE lower bound, with no negative number anywhere in the query, still loses every row. Upper bound swept, lower fixed at -2.0 so MAX alone is under test: upper MAX via index correct round(hi) floor(hi) -0.51 -1 -1 -1 -1 -0.50 -1 -1 -1 -1 -0.49 NULL -1 0 -1 <<< 0.49 0 0 0 0 0.50 NULL 0 1 0 <<< 1.49 1 1 1 1 1.50 NULL 1 2 1 <<< -------------------------------------------------------------------------------- It is the DECIMAL bound specifically -------------------------------------------------------------------------------- The constant's type decides it -- a DOUBLE bound takes a different, correct path. This is the sharpest localisation in the report: the claim is not "BETWEEN is broken" but "the DECIMAL-to-integer bound conversion in opt_sum_query uses the wrong rounding mode". SELECT MIN(i) FROM big WHERE i BETWEEN -0.5 AND 2.5; -- NULL (DECIMAL) SELECT MIN(i) FROM big WHERE i BETWEEN -0.5e0 AND 2.5e0; -- 0 (DOUBLE, correct) SELECT MIN(i) FROM big WHERE i BETWEEN CAST(-0.5 AS DECIMAL(10,1)) AND CAST(2.5 AS DECIMAL(10,1)); -- NULL Server-side PREPARE ... EXECUTE ... USING @lo, @hi with SET @lo = -0.5 gives NULL, so parameterised queries are exposed whenever the driver sends a DECIMAL. A driver that renders floats in exponent form, as PyMySQL does, accidentally avoids it -- that is a property of the driver, not a fix. -------------------------------------------------------------------------------- A realistic shape -- and count(*) goes with it -------------------------------------------------------------------------------- The two ends fail independently, so a single statement can have one bound clean and the other not. When that happens the whole statement is optimised away, and count(*) in the same select list returns 0 while the rows are plainly there: CREATE TABLE orders(id INT PRIMARY KEY, score INT, KEY(score)) ENGINE=InnoDB; INSERT INTO orders VALUES (1,0),(2,1),(3,2),(4,3),(5,4); SELECT MIN(score), MAX(score), count(*) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- got (NULL, NULL, 0) correct is (1, 3, 3) Here 0.5 is a clean lower bound (round(0.5) = ceil(0.5) = 1) and 3.5 is not (round(3.5) = 4, floor(3.5) = 3). Taken apart, the same server gets two of the three right: SELECT MIN(score) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- 1 ok SELECT MAX(score) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- NULL wrong SELECT count(*) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- 3 ok SELECT MIN(score), MAX(score), count(*) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- (NULL, NULL, 0) SELECT MIN(score), MAX(score), count(*) FROM orders IGNORE INDEX(score) WHERE score BETWEEN 0.5 AND 3.5; -- (1, 3, 3) -------------------------------------------------------------------------------- Scope, as measured on 26.10.0 -------------------------------------------------------------------------------- Only the MIN/MAX shortcut -- but it can take the rest of the select list with it. count(*), sum(), avg(), group_concat(), SELECT * and ordinary range scans over the identical predicate are correct WHEN NO AFFECTED MIN/MAX IS IN THE SAME SELECT LIST. When one is, the statement is optimised away and those aggregates are wrong too. The range scan itself always converts the bounds correctly. Every integer type: TINYINT, SMALLINT, INT, BIGINT and INT UNSIGNED (ui BETWEEN 0.5 AND 2.5 -> NULL, correct (1, 2)). DECIMAL and DOUBLE COLUMNS are not affected -- there is no narrowing to perform. BETWEEN contradicts the conjunction it is defined as: i >= -0.5 AND i <= 2.5 returns the correct 0 while i BETWEEN -0.5 AND 2.5 returns NULL. Two spellings of one range, two answers. Scale and hints are irrelevant. Identical on a 7-row table with no hint, on a 10 000-row table with the optimizer choosing the index itself, and with FORCE INDEX. How to repeat: Needs no data files. Against a stock server, default sql_mode: CREATE DATABASE IF NOT EXISTS t079; USE t079; CREATE TABLE t(i INT, KEY(i)) ENGINE=InnoDB; INSERT INTO t VALUES (-3),(-2),(-1),(0),(1),(2),(3); SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- got NULL, expected 0 -- the rows 0, 1, 2 satisfy the predicate SELECT count(*) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- 3 SELECT MIN(i) FROM t IGNORE INDEX(i) WHERE i BETWEEN -0.5 AND 2.5; -- 0, correct EXPLAIN SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- Zero input rows (No matching min/max row) A positive lower bound with no negative number anywhere, to show the sign is not the trigger: SELECT MIN(i) FROM t WHERE i BETWEEN 0.01 AND 2.0; -- NULL, expected 1 The MAX end, which fails independently: SELECT MAX(i) FROM t WHERE i BETWEEN -2.0 AND 0.50; -- NULL, expected 0 The DECIMAL / DOUBLE split, which localises the conversion: SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- NULL DECIMAL SELECT MIN(i) FROM t WHERE i BETWEEN -0.5e0 AND 2.5e0; -- 0 DOUBLE, ok BETWEEN against the conjunction it is defined as: SELECT MIN(i) FROM t WHERE i >= -0.5 AND i <= 2.5; -- 0, correct SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5; -- NULL count(*) dragged down by an affected aggregate beside it: CREATE TABLE orders(id INT PRIMARY KEY, score INT, KEY(score)) ENGINE=InnoDB; INSERT INTO orders VALUES (1,0),(2,1),(3,2),(4,3),(5,4); SELECT count(*) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- 3, correct SELECT MIN(score), MAX(score), count(*) FROM orders WHERE score BETWEEN 0.5 AND 3.5; -- (NULL, NULL, 0), correct is (1, 3, 3) Scale is irrelevant -- the same on a 10 000-row table with no hint: CREATE TABLE big(i INT, KEY(i)) ENGINE=InnoDB; INSERT INTO big WITH RECURSIVE s(n) AS (SELECT -5000 UNION ALL SELECT n+1 FROM s WHERE n < 5000) SELECT n FROM s; SELECT MIN(i) FROM big WHERE i BETWEEN -0.5 AND 2.5; -- NULL, expected 0 Reproduction scripts in the attached folder: repro.py (the core case), rule079.py and rule079b.py (the ceil/floor rule swept over a bound grid), verify.py and extra.py (type coverage and the DECIMAL/DOUBLE split). 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: In opt_sum_query, convert a BETWEEN lower bound with ceil and an upper bound with floor when narrowing a DECIMAL constant onto an integer key, rather than rounding to nearest. Alternatively, decline the shortcut when the conversion is inexact and let the range scan handle it -- the range scan is already correct on every case in this report, and declining costs nothing on the common case where the bound is integral. A regression test needs an indexed integer column, a BETWEEN whose lower bound has round(lo) != ceil(lo) (for example -0.5, or 0.01), and an assertion that MIN/MAX match the IGNORE INDEX answer. Asserting that SELECT MIN(i), count(*) FROM t WHERE i BETWEEN lo AND hi never returns NULL alongside a non-zero count is the cheapest single check, and it also covers #36300's NOT BETWEEN case if the two share a fix.