Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.0, 13.1, 13.2
Description
--source include/have_partition.inc
|
|
|
CREATE TABLE t1 (c YEAR) PARTITION BY RANGE (c) (PARTITION p0 VALUES LESS THAN (2000), PARTITION p1 VALUES LESS THAN MAXVALUE); |
INSERT INTO t1 VALUES (1999),(2000),(2155); |
--let $n= query_get_value(SELECT COUNT(*) AS n FROM t1 WHERE c < 9999, n, 1)
|
if ($n != 3) |
{
|
--die Only $n of the 3 rows of the table partitioned by a YEAR column are found by c < 9999 |
}
|
|
|
CREATE TABLE t2 (c YEAR, KEY(c)); |
INSERT INTO t2 VALUES (1999),(2000),(2155); |
--let $n= query_get_value(SELECT COUNT(*) AS n FROM t2 FORCE INDEX(c) WHERE c < 9999, n, 1)
|
if ($n != 3) |
{
|
--die Only $n of the 3 rows are found by c < 9999 through an index on a YEAR column |
}
|
DROP TABLE t1, t2; |
Leads to:
|
CS 13.1.2 87e62ca17270e163a6d41b3fc8d6f4d440f1bb88 (Debug, Clang 22.1.8-20260622) Build 06/10/2026 |
mysqltest: At line 8: Only 1 of the 3 rows of the table partitioned by a YEAR column are found by c < 9999
|
Partitioning changes the query result. Without partitioning, c < 9999 finds all 3 rows. An index changes the result as well. With the first check removed, the testcase gives:
|
CS 13.1.2 87e62ca17270e163a6d41b3fc8d6f4d440f1bb88 (Debug, Clang 22.1.8-20260622) Build 06/10/2026 |
mysqltest: At line 11: Only 0 of the 3 rows are found by c < 9999 through an index on a YEAR column
|
EXPLAIN PARTITIONS for c < 9999 reads p0 only. The same happens for any upper bound over 2155: c < 2156 and c <= 9999 find 1 row, and c BETWEEN 1990 AND 9999 finds none. c < 2155 finds the 2 rows it should. c + 0 < 9999 and IGNORE INDEX(c) find all 3 rows. With LIST partitioning, c < 9999 finds no row. With InnoDB and an extra row 0, FORCE INDEX(c) finds only the row 0. DELETE FROM t1 WHERE c < 9999 leaves 2 of the 3 rows, and UPDATE t2 SET c = 1990 WHERE c < 9999 changes none. Optimized builds give the same result.
MySQL gives these row counts, out of 3:
|
MySQL |
+--------+--------------+-----------------------------+-----------------------------+
|
| MySQL | t1, c < 9999 | t1, c BETWEEN 1990 AND 9999 | t2 FORCE INDEX(c), c < 9999 |
|
+--------+--------------+-----------------------------+-----------------------------+
|
| 5.5.62 | 1 | 0 | 3 |
|
| 5.6.51 | 0 | 0 | 0 |
|
| 5.7.44 | 0 | 0 | 0 |
|
| 8.0.36 | 0 | 0 | 3 |
|
| 9.1.0 | 0 | 0 | 3 |
|
+--------+--------------+-----------------------------+-----------------------------+
|
From the code it seems that Field::get_mm_leaf_int() in sql/opt_range.cc stores the constant in the field, and for an out-of-range integer Field::stored_field_make_mm_leaf_bounded_int() expects the stored value to be the field maximum. Field_year::store() in sql/field.cc stores 0 (the year 0000) for a value over 2155, so c < 9999 becomes c <= 0000.
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
-
-
- Closed
-
