MIN/MAX index shortcut clamps a negative constant on an UNSIGNED key to 0

Affected versions:

10.6.28, 10.11.19, 11.4.13, 11.8.9, 12.1.2, 12.3.3, 13.0.2, 13.2.0 (main,
d45cf75c, 2026-09-30). Originally found on 10.6.23. Not a regression.

===

When MIN() or MAX() over an indexed UNSIGNED integer column is answered from the
index ("Select tables optimized away", sql/opt_sum.cc) and the WHERE clause
compares the column with a negative constant, the constant is clamped to 0 while
being stored into the key field and the clamped value is used as the range
endpoint WITH THE ORIGINAL OPERATOR. u > -1 is executed as u > 0, so MIN skips
the row holding 0; u = -1 and u <= -1 are executed as u = 0 / u <= 0, so MIN/MAX
return 0 for a predicate that no row satisfies. No warning is raised.

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

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

SELECT MIN(u), count(*) FROM t WHERE u > -1;

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

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

count in the same select list counts all four rows, including the one holding
0, and the row-returning forms agree:

SELECT u FROM t WHERE u > -1 ORDER BY u;                 -- 0, 1, 2, 3

SELECT u FROM t WHERE u > -1 ORDER BY u LIMIT 1;         -- 0

SELECT MIN(u) FROM t IGNORE INDEX(u) WHERE u > -1;       -- 0

SELECT (0 > -1), (0 = -1), (0 <= -1);                    -- 1, 0, 0

EXPLAIN SELECT MIN(u) FROM t WHERE u > -1;               -- Select tables optimized away

The shortcut also invents a value for an empty predicate:

SELECT MIN(u) FROM t WHERE u = -1;     -- 0      expected NULL (no row has u = -1)

SELECT MAX(u) FROM t WHERE u = -1;     -- 0      expected NULL

SELECT MAX(u) FROM t WHERE u <= -1;    -- 0      expected NULL

SELECT MIN(u) FROM t IGNORE INDEX(u) WHERE u = -1;   -- NULL

Which aggregate/operator pairs are wrong, measured over TINYINT, SMALLINT, INT
and BIGINT UNSIGNED and over the constants -1, -2, -0.5, -1e0 and '-1' (200
index-vs-IGNORE INDEX comparisons; identical on every width and spelling):

  MIN(u) WHERE u >  K    WRONG   returns 1, skips the row holding 0

  MIN(u) WHERE u =  K    WRONG   returns 0 for an empty predicate

  MAX(u) WHERE u =  K    WRONG   returns 0 for an empty predicate

  MAX(u) WHERE u <= K    WRONG   returns 0 for an empty predicate

  MIN(u) WHERE u >= K    ok      MAX(u) WHERE u >  K    ok

  MIN(u) WHERE u <  K    ok      MAX(u) WHERE u >= K    ok

  MIN(u) WHERE u <= K    ok      MAX(u) WHERE u <  K    ok

Constants at or above 0, including constants above the type's maximum
(4294967296 for INT UNSIGNED), are handled correctly in all eight combinations,
so this is the lower clamp specifically.

Also wrong, same way: DOUBLE UNSIGNED, FLOAT UNSIGNED and DECIMAL(10,2) UNSIGNED
columns (MIN(u) WHERE u > -1 -> 1, MIN(u) WHERE u = -1 -> 0); a NOT NULL PRIMARY
KEY column; MyISAM; the second column of a composite key (KEY(a,u), WHERE a = 1
AND u > -1 -> 1); and a prepared statement (PREPARE s FROM 'SELECT MIN(u) FROM t
WHERE u > ?'; SET @b = -1; EXECUTE s USING @b -> 1).

The select list decides whether the shortcut runs: SELECT MIN(u), count ...
WHERE u = -1 returns NULL (correct, because count forces a scan), while
SELECT MIN(u) ... WHERE u = -1 and SELECT MIN(u), MAX(u) ... WHERE u = -1 return
0. Note for anyone reproducing: adding count to inspect the row count hides
the u = -1 case; the u > -1 case is wrong with or without it.

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. For an unsigned integer field a
negative value is clamped to 0 by Field::convert_decimal2longlong() /
Field_xxx::store(longlong, unsigned) (the out-of-range warning is suppressed by
the _no_warnings variant), the key image is taken from the clamped field, and
range_fl keeps the original operator (NEAR_MIN for ">", equality for "=").

The range optimizer handles the same situation 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, on a bounded integer, calls
stored_field_make_mm_leaf_bounded_int(), whose comment reads

    (a) (unsigned_int [< | <=] negative_constant) == FALSE

    (b) (unsigned_int [> | >=] negative_constant) == TRUE

and returns SEL_ARG_IMPOSSIBLE for (a) and "no range" for (b), and
SEL_ARG_IMPOSSIBLE for "= out_of_range" (MDEV-15759). EXPLAIN on the plain
SELECT shows the effect: SELECT u FROM t WHERE u = -1 and WHERE u <= -1 give
"Impossible WHERE noticed after reading const tables"; WHERE u > -1 gives a full
index scan with "Using where". opt_sum.cc has no equivalent.

Related but different code path: MDEV-30594 / MDEV-40199 (DOUBLE UNSIGNED with a
negative STRING constant through the range optimizer's ref access) and
MDEV-39192 (VARCHAR column, numeric literal, same MIN/MAX shortcut). The DECIMAL
rounding variant of this same store (an integer key with a non-integral DECIMAL
bound, e.g. MIN WHERE i >= -0.5 -> -1) is reported separately as
[MDEV-xxxxx -- fill in after filing the BUG-076 draft]; a fix that checks the
result of the store, or re-derives the operator from the direction the stored key
moved, covers both.

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

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

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

SELECT MIN(u), count(*) FROM t WHERE u > -1;              -- 1

SELECT MIN(u) FROM t IGNORE INDEX(u) WHERE u > -1;        -- 2

SELECT u FROM t WHERE u > -1 ORDER BY u;                  -- 3

SELECT MIN(u) FROM t WHERE u = -1;                        -- 4

SELECT MAX(u) FROM t WHERE u <= -1;                       -- 5

SELECT count(*) FROM t WHERE u = -1;                      -- 6

EXPLAIN SELECT MIN(u) FROM t WHERE u > -1;                -- 7

EXPLAIN SELECT u FROM t WHERE u = -1;                     -- 8

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

1.  MIN(u) = 0, count(*) = 4

2.  0

3.  0, 1, 2, 3

4.  NULL

5.  NULL

6.  0

7.  (any plan)

8.  Impossible WHERE

MySQL 26.7.0 returns exactly 1-6 as above.

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

1.  MIN(u) = 1, count(*) = 4

2.  0

3.  0, 1, 2, 3

4.  0

5.  0

6.  0

7.  Select tables optimized away

8.  Impossible WHERE noticed after reading const tables

SHOW WARNINGS is empty after each statement.

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

In sql/opt_sum.cc matching_cond(), check the return value of
save_in_field_no_warnings(). When the store was not exact (out of range,
truncated, or clamped), either decline the MIN/MAX shortcut and let the range
optimizer / scan answer the query (they are already correct in every case here,
and the common case of an in-range constant is untouched), or adjust the
endpoint the way stored_field_make_mm_leaf_bounded_int() does: for an unsigned
key and a negative constant, "<", "<=" and "=" are impossible, and ">" / ">="
mean "no lower bound".

A regression test needs an indexed unsigned integer column holding 0 and a
negative constant, asserting MIN(u) WHERE u > K and MIN/MAX(u) WHERE u = K and
MAX(u) WHERE u <= K against IGNORE INDEX. The cheapest single check is that
SELECT MIN(u), count FROM t WHERE u > -1 never returns a MIN larger than the
smallest of the rows it counts.

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

repro.py (pymysql; exits 1 when MIN/MAX disagrees with IGNORE INDEX over the same
predicate; covers the four unsigned widths and five constant spellings and the
>= 0 controls). 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 extra probes
(DOUBLE/FLOAT/DECIMAL UNSIGNED, NOT NULL PK, MyISAM, composite key, prepared
statement, EXPLAIN of the plain range path).