MIN/MAX index shortcut rounds a DECIMAL bound onto an integer key, wrong result

When MIN() or MAX() over an indexed integer column is answered from the index
("Select tables optimized away", sql/opt_sum.cc) and the WHERE clause bounds the
column with a DECIMAL constant that the column cannot represent, the constant is
stored into the key field – which ROUNDS it half away from zero – and the
rounded value is used as the range endpoint with the original operator. The
correct conversion for an integer key is ceil for a lower bound and floor for an
upper bound, so whenever rounding moves the bound the wrong way MIN/MAX returns a
value that does not satisfy the WHERE clause, and for BETWEEN the shortcut
concludes the interval is empty and returns NULL while rows match. No warning is
raised.

CREATE TABLE t (i INT, KEY(i)) ENGINE=InnoDB;

INSERT INTO t VALUES (-3),(-2),(-1),(0),(1),(2),(3);

SELECT MIN(i), count(*) FROM t WHERE i >= -0.5;

MariaDB 13.2.0 (main), 13.0.2, 10.6.28:   MIN(i) = -1   count(*) = 4

expected (and MySQL 26.7.0):               MIN(i) =  0   count(*) = 4

-1 >= -0.5 is false, and the same server says so and lists the rows:

SELECT (-1 >= -0.5);                                        -- 0

SELECT group_concat(i ORDER BY i) FROM t WHERE i >= -0.5;   -- 0,1,2,3

SELECT MIN(i) FROM t IGNORE INDEX(i) WHERE i >= -0.5;       -- 0

SELECT i FROM t WHERE i >= -0.5 ORDER BY i LIMIT 1;         -- 0   (same index, "Using where; Using index")

EXPLAIN SELECT MIN(i) FROM t WHERE i >= -0.5;               -- Select tables optimized away

So MIN is not among the four rows count counts in the same select list.
The MAX end fails independently:

SELECT MAX(i) FROM t WHERE i <= 0.5;                        -- 1     expected 0

SELECT MIN(i), MAX(i) FROM t WHERE i >= -0.5 AND i <= 2.5;  -- (-1, 3)  expected (0, 2)

The rule, swept on main (idx = via index, correct = IGNORE INDEX):

  MIN(i) WHERE i >= K       K        idx   correct    round(K)  ceil(K)

                           -0.40      0       0           0        0

                           -0.49      0       0           0        0

                           -0.499999  0       0           0        0

                           -0.50     -1       0          -1        0     <<<

                           -0.500001 -1       0          -1        0     <<<

                           -0.51     -1       0          -1        0     <<<

                           -1.5      -2      -1          -2       -1     <<<

                           -2.5      -3      -2          -3       -2     <<<

                            0.01      0       1           0        1     <<<  (positive bound, no negative number anywhere)

                            0.5       1       1           1        1

  MAX(i) WHERE i <= K       0.49      0       0           0        0

                            0.50      1       0           1        0     <<<

                            0.60      1       0           1        0     <<<

                            1.5       2       1           2        1     <<<

MIN is wrong exactly when round(K) != ceil(K); MAX exactly when round(K) !=
floor(K). Strict operators are correct (i > -0.5 -> 0, i < 0.5 -> 0) because the
rounded endpoint is then excluded.

BETWEEN – both ends go through the same store, and here the shortcut returns
NULL, the value meaning "no rows", while rows plainly match (this half is also
MySQL Bug #121325, where MySQL 26.7.0 behaves the same):

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   (0, 1, 2)

SELECT MIN(i), count(*) FROM t WHERE i BETWEEN -0.5 AND 2.5;   -- (NULL, 0)

SELECT MIN(i)   FROM t WHERE i BETWEEN 0.01 AND 2.0;    -- NULL  expected 1

SELECT MAX(i)   FROM t WHERE i BETWEEN -2.0 AND 0.50;   -- NULL  expected 0

EXPLAIN SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5;   -- No matching min/max row

Once an affected MIN/MAX is in the select list the whole statement is optimised
away and count beside it becomes 0:

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

SELECT MIN(score), MAX(score), count(*) FROM orders

 WHERE score BETWEEN 0.5 AND 3.5;                                            -- (NULL, NULL, 0)   expected (1, 3, 3)

SELECT MIN(score), MAX(score), count(*) FROM orders

 WHERE score >= 0.5 AND score <= 3.5;                                        -- (1, 4, 3)         expected (1, 3, 3)

The last line shows the two spellings of one range giving two different wrong
answers on the same server (BETWEEN: NULL; conjunction: MAX = 4, which is > 3.5).

It is the DECIMAL constant specifically. A DOUBLE constant takes a different
path and is correct; a string constant and a prepared-statement parameter are
DECIMAL and are wrong:

SELECT MIN(i) FROM t WHERE i >= -0.5e0;                     -- 0    (DOUBLE, correct)

SELECT MIN(i) FROM t WHERE i >= '-0.5';                     -- -1

SELECT MIN(i) FROM t WHERE i >= CAST(-0.5 AS DECIMAL(3,1)); -- -1

PREPARE s FROM 'SELECT MIN(i) FROM t WHERE i >= ?';

SET @b = -0.5;   EXECUTE s USING @b;                        -- -1

SET @b = -0.5e0; EXECUTE s USING @b;                        -- 0

Scope, measured on main: TINYINT, SMALLINT, INT and BIGINT all give -1 for
i >= -0.5; a 10001-row table with no hint gives the same (-1, "Select tables
optimized away"); DECIMAL(10,0) with KEY(c) and rows 1,2,3 gives MAX(c) WHERE
c <= 1.5 -> 2 (expected 1); and a DATE key with a DATETIME constant narrows the
same way:

CREATE TABLE dd (dt DATE, KEY(dt)) ENGINE=InnoDB;

INSERT INTO dd VALUES ('2020-01-01'),('2020-01-02'),('2020-01-03'),

                      ('2020-01-04'),('2020-01-05'),('2020-01-06');

SELECT MIN(dt) FROM dd WHERE dt >= '2020-01-01 12:00:00';            -- 2020-01-01  expected 2020-01-02

SELECT MIN(dt), MAX(dt) FROM dd

 WHERE dt BETWEEN '2020-01-01 12:00:00' AND '2020-01-05 12:00:00';   -- (NULL, NULL)  expected (2020-01-02, 2020-01-05)

SELECT CAST('2020-01-01' AS DATE) >= '2020-01-01 12:00:00';          -- 0

(MySQL 26.7.0 is correct on both DATE statements.) count, sum(), avg(),
group_concat(), SELECT * and ORDER BY ... LIMIT 1 over the identical predicates
are correct when no affected MIN/MAX is in the same select list.

Where it happens (main, d45cf75c): sql/opt_sum.cc, matching_cond(), line 890:

      /* Update endpoints for MAX/MIN, see function comment. */

      Item *value= args[between && max_fl ? 2 : 1];

      value->save_in_field_no_warnings(part->field, 1);

      if (part->null_bit)

        *key_ptr++= (uchar) MY_TEST(part->field->is_null());

      part->field->get_key_image(key_ptr, part->length, Field::itRAW);

The return value of the store is ignored. Field_int::store_decimal() ->
Field::convert_decimal2longlong() -> my_decimal2int(..., HALF_UP) rounds -0.5 to
-1 and 0.5 to 1 and reports E_DEC_TRUNCATED, which nobody reads here; the key
image is taken from the rounded field and range_fl keeps the operator that was
written (NEAR_MIN cleared for ">=", so the rounded value is included). For
BETWEEN both ends are rounded outward-or-inward independently, and an interval
whose rounded ends cross is reported as "No matching min/max row".

The range optimizer handles the same constants correctly, which is why every
non-MIN/MAX form above is right: Field::get_mm_leaf_int() (sql/opt_range.cc)
checks the store's return value and, when err > 0 and the constant is not an
integer, calls stored_field_make_mm_leaf_truncated(), which declines to build an
exact range (and returns SEL_ARG_IMPOSSIBLE for "="). opt_sum.cc has no
equivalent of that check.

Related, not duplicates: MDEV-39192 (MAX on an indexed VARCHAR with a numeric
literal, same shortcut, different value class, Confirmed); MDEV-39187 (the
equality form i = 1.2 on a DECIMAL column through the range optimizer,
Confirmed); MDEV-16784 (range optimizer declines a range for DECIMAL(10,2) <
10.999 – the opposite, performance-only, complaint); MySQL Bug #121325 (the
BETWEEN half of this report, on MySQL). A negative constant against an UNSIGNED
key is clamped by the same store and is reported separately as
[MDEV-xxxxx -- fill in after filing the BUG-084 draft].

================================================================================
STEPS TO REPRODUCE
================================================================================

CREATE TABLE t (i INT, KEY(i)) ENGINE=InnoDB;

INSERT INTO t VALUES (-3),(-2),(-1),(0),(1),(2),(3);

SELECT MIN(i), count(*) FROM t WHERE i >= -0.5;                    -- 1

SELECT MIN(i) FROM t IGNORE INDEX(i) WHERE i >= -0.5;              -- 2

SELECT group_concat(i ORDER BY i) FROM t WHERE i >= -0.5;          -- 3

SELECT MAX(i) FROM t WHERE i <= 0.5;                               -- 4

SELECT MIN(i) FROM t WHERE i >= 0.01;                              -- 5

SELECT MIN(i) FROM t WHERE i >= -0.5e0;                            -- 6

SELECT MIN(i), count(*) FROM t WHERE i BETWEEN -0.5 AND 2.5;       -- 7

SELECT count(*) FROM t WHERE i BETWEEN -0.5 AND 2.5;               -- 8

EXPLAIN SELECT MIN(i) FROM t WHERE i >= -0.5;                      -- 9

EXPLAIN SELECT MIN(i) FROM t WHERE i BETWEEN -0.5 AND 2.5;         -- 10

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;       -- 11

SELECT MIN(score), MAX(score), count(*) FROM orders WHERE score >= 0.5 AND score <= 3.5;   -- 12

================================================================================
EXPECTED
================================================================================

1.  (0, 4)

2.  0

3.  0,1,2,3

4.  0

5.  1

6.  0

7.  (0, 3)

8.  3

9.  (any plan)

10. (any plan)

11. (1, 3, 3)

12. (1, 3, 3)

MySQL 26.7.0 returns 1-6, 8 and 12 as above; it shares 7, 10 and 11 (Bug #121325).

================================================================================
ACTUAL (13.2.0 main d45cf75c; identical on 13.0.2 and 10.6.28 ... 12.3.3)
================================================================================

1.  (-1, 4)

2.  0

3.  0,1,2,3

4.  1

5.  0

6.  0

7.  (NULL, 0)

8.  3

9.  Select tables optimized away

10. No matching min/max row

11. (NULL, NULL, 0)

12. (1, 4, 3)

SHOW WARNINGS is empty after each statement.

================================================================================
SUGGESTED FIX
================================================================================

In sql/opt_sum.cc matching_cond(), do not use a stored bound that is not equal to
the constant. Either

• check the return value of save_in_field_no_warnings() (or compare the stored
  field back against the constant with stored_field_cmp_to_item()) and decline
  the MIN/MAX shortcut when the store was inexact – the range scan is already
  correct on every case in this report and the common case of an exactly
  representable constant is untouched; or

• convert with the right direction for the operator: ceil for a lower bound
  (">=", BETWEEN's first argument) and floor for an upper bound ("<=", BETWEEN's
  second argument), i.e. what the range optimizer's
  stored_field_make_mm_leaf_truncated() / _bounded_int() achieve, and treat a
  crossed BETWEEN as empty only after that.

The first is one check and also covers the other value classes that narrow at
this line (temporal precision, unsigned clamp, VARCHAR vs numeric – MDEV-39192).

A regression test needs an indexed integer column, a lower bound K with
round(K) != ceil(K) (for example -0.5 or 0.01) and an upper bound K with
round(K) != floor(K) (0.5), asserting MIN/MAX via the index against IGNORE INDEX
for ">=", "<=" and BETWEEN. The cheapest single check is that
SELECT MIN, count FROM t WHERE i >= K never returns a MIN that fails
i >= K, and that SELECT MIN, count ... BETWEEN never returns NULL beside a
non-zero count.

================================================================================
ATTACHMENTS
================================================================================

repro.py (pymysql; the >= / <= sweep and the equality probes; exits 1 on the
MIN/MAX disagreements – note the equality probes pass on 12.3.2+, see the working
note). VERIFY-main-d45cf75c-2026-09-30.txt: full transcript on main, 13.0.2,
MySQL 26.7.0 and the six older images, including the BETWEEN, DATE, DECIMAL,
prepared-statement and 10001-row probes.