Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.0, 13.1, 13.2
Description
CREATE TABLE t1 (i INT); |
INSERT INTO t1 VALUES (10), (20); |
CREATE TABLE t2 (y YEAR, KEY (y)); |
INSERT INTO t2 VALUES (1999), (2000), (2001), (2002), (2003); |
--let $c= query_get_value(SELECT COUNT(*) AS c FROM t1 STRAIGHT_JOIN t2 WHERE t2.y > t1.i, c, 1)
|
if ($c != 10) |
{
|
--die COUNT(*) for t2.y > t1.i is $c and not 10 |
}
|
DROP TABLE t1, t2; |
Leads to:
|
CS 13.1.2 87e62ca17270e163a6d41b3fc8d6f4d440f1bb88 (Debug, Clang 22.1.8-20260622) Build 08/10/2026 |
mysqltest: At line 8: COUNT(*) for t2.y > t1.i is 0 and not 10
|
The index changes the query result. Without it (IGNORE INDEX (y)), the result is 10. EXPLAIN shows "Range checked for each record" for t2. A VARCHAR column with '10', t1.i + 0, y > (SELECT MIN
FROM t1) and InnoDB give the same. t2.y < t1.i, t2.y = t1.i, t2.y <=> t1.i and a hash join give the right result. A constant, as in y > 10, and a prepared statement y > ? give the same result with and without the key. Optimized builds give the same result.
MySQL gives these counts, out of 10, the same with MyISAM and InnoDB:
|
MySQL |
+--------+-------------+-------------------------------+
|
| MySQL | t2.y > t1.i | t2.y > t1.i, IGNORE INDEX (y) |
|
+--------+-------------+-------------------------------+
|
| 5.5.62 | 10 | 10 |
|
| 5.6.51 | 10 | 10 |
|
| 5.7.44 | 0 | 10 |
|
| 8.0.36 | 0 | 10 |
|
| 9.1.0 | 0 | 10 |
|
+--------+-------------+-------------------------------+
|
From the code it seems that Field::get_mm_leaf_int() in sql/opt_range.cc makes the range from the value as stored in the YEAR field, and Field_year::store() in sql/field.cc stores 10 as 2010. The condition compares 10 itself. Only a constant is changed to its YEAR value, by convert_const_to_int() in sql/item_cmpfunc.cc.
