Description:
A YEAR column compared against a non-integral constant has the constant silently
converted to an integer year before the comparison, and the conversion mode
depends on how the literal was spelled. Rows that do not satisfy the predicate
are returned, rows that do satisfy it are dropped, and no warning is raised
under STRICT_TRANS_TABLES.
No index is involved -- this is expression evaluation, not the range optimiser.
IGNORE INDEX and a table with no index give the same wrong answer.
CREATE TABLE y(v YEAR);
INSERT INTO y VALUES (2000),(2001),(2002),(2003),(2004),(2005);
SELECT v FROM y WHERE v = 2003.5; -- returns 2004
SELECT v FROM y WHERE v = 2003.5e0; -- returns 2003
Expected: the empty set for both -- no YEAR value equals 2003.5.
Actual: each returns a row, and THE TWO SPELLINGS OF THE SAME NUMBER RETURN
DIFFERENT ROWS.
The same values in an INT column behave correctly:
SELECT count(*) FROM y WHERE v <= 2003.5; -- 5 <- includes 2004
SELECT count(*) FROM i2 WHERE v <= 2003.5; -- 4 <- correct
2004 <= 2003.5 is reported TRUE for a YEAR column. SHOW WARNINGS is empty in
every case above.
--------------------------------------------------------------------------------
Rows that do match are also dropped
--------------------------------------------------------------------------------
Not only are extra rows returned -- the symmetric operator loses rows:
SELECT v FROM y WHERE v > 2003.5; -- 2005 <- 2004 is missing
SELECT v FROM i2 WHERE v > 2003.5; -- 2004, 2005 <- correct
--------------------------------------------------------------------------------
The rule
--------------------------------------------------------------------------------
The constant is converted to an integer year first, and the conversion mode
follows the literal's type:
constant spelled as conversion v = 2003.5 returns
------------------------------- ------------------------- ------------------
2003.5 (DECIMAL) round, half away from zero 2004
'2003.5' (string) round, half away from zero 2004
2003.5e0 (DOUBLE) truncate toward zero 2003
CAST(2003.5 AS DECIMAL(10,1)) round 2004
CAST(2003.5 AS DOUBLE) truncate 2003
Verified on 26.10.0 against every combination of 13 constants and the operators
=, <=, >=, <, >: the rounding rule mispredicts 0 of 65 for DECIMAL and string
constants, the truncation rule 0 of 65 for DOUBLE constants, and YEAR disagrees
with an INT column holding the same values on 36 of 65.
Per-operator, on 26.10.0 (YEAR vs INT, same six values):
constant op YEAR INT
2003.5 = [2004] [] DIFFER
2003.5 <= [2000,2001,2002,2003,2004] [2000,2001,2002,2003] DIFFER
2003.5 > [2005] [2004,2005] DIFFER
2003.5e0 = [2003] [] DIFFER
2003.5e0 < [2000,2001,2002] [2000,2001,2002,2003] DIFFER
2003.5e0 >= [2003,2004,2005] [2004,2005] DIFFER
--------------------------------------------------------------------------------
Only YEAR, among the narrow types
--------------------------------------------------------------------------------
Checked with the oracle "c OP k must select the same rows as
CAST(c AS DECIMAL(40,15)) OP k", since widening the column cannot change an
exact numeric comparison -- 2 880 checks:
YEAR 84 narrowing disagreements
TINYINT 0 TINYINT UNSIGNED 0
SMALLINT 0 INT 0
INT UNSIGNED 0 BIGINT 0
DECIMAL(10,0) 0 DECIMAL(10,1) 0
BIT(8) 0
YEAR is the only narrow type that narrows the constant, which is what makes this
type-specific rather than a general comparison rule.
--------------------------------------------------------------------------------
Only constants -- and a bound parameter is a constant
--------------------------------------------------------------------------------
A YEAR column compared to a DECIMAL COLUMN is correct:
CREATE TABLE lim(name VARCHAR(10), cutoff DECIMAL(10,1));
INSERT INTO lim VALUES ('h1', 2003.5);
SELECT v FROM y, lim WHERE lim.name='h1' AND y.v <= lim.cutoff;
-- 2000,2001,2002,2003 correct
So the defect is in the conversion applied to a CONSTANT, not in YEAR comparison
generally. A bound parameter is a constant, and which path it takes depends on
the type the client sends. Measured on 26.10.0, SELECT v FROM y WHERE v <= ?:
parameter sent as result verdict
----------------------------------- --------------------------- ---------
DECIMAL 2003.5 2000,2001,2002,2003,2004 wrong
string '2003.5' 2000,2001,2002,2003,2004 wrong
DOUBLE 2003.5 (float) 2000,2001,2002,2003 correct
server PREPARE with @k = 2003.5 2000,2001,2002,2003 correct
the same parameters, INT column 2000,2001,2002,2003 correct
Two things follow, and the second is the one worth acting on:
- A client that sends the value as a DECIMAL or a string gets the wrong rows.
Which of those a driver does is not the application's choice and is not
visible in the SQL.
- The DOUBLE and server-side-PREPARE rows are NOT a workaround. They are
correct on <= only because truncation happens to fall the right way for that
operator. The DOUBLE path is wrong on =, < and >= -- see the per-operator
table above, where v = 2003.5e0 returns 2003 and v < 2003.5e0 drops it.
--------------------------------------------------------------------------------
Consequences
--------------------------------------------------------------------------------
count(*) WHERE v <= 2003.5 YEAR=5 INT=4
max(v) WHERE v <= 2003.5 YEAR=2004 INT=2003
sum(v) WHERE v <= 2003.5 YEAR=10010 INT=8006
max(v) returns 2004 for a query whose own predicate is v <= 2003.5.
--------------------------------------------------------------------------------
The caveat a triager will reach for, addressed up front
--------------------------------------------------------------------------------
MySQL documents that a TWO-DIGIT YEAR literal maps to 1970-2069, so v = 1
legitimately matching 2001 is by design. That rule is not the subject of this
report, and the four claims below do not depend on it:
1. 2003.5 and 2003.5e0 -- the same number -- select DIFFERENT rows.
2. An equality against a value the type cannot represent returns rows at all.
3. v > 2003.5 DROPS 2004, a row that satisfies the predicate.
4. No warning is raised, under STRICT_TRANS_TABLES.
(The documented two-digit rule is why SELECT v FROM y WHERE v = 0.5 returns
2001: 0.5 is rounded to the literal 1 first, and 1 then maps to 2001. The
rounding is the part that is not documented.)
How to repeat:
Needs no data files. Against a stock server, default sql_mode (which includes
STRICT_TRANS_TABLES). No index anywhere:
CREATE DATABASE IF NOT EXISTS t080; USE t080;
CREATE TABLE y(v YEAR);
INSERT INTO y VALUES (2000),(2001),(2002),(2003),(2004),(2005);
SELECT v FROM y WHERE v = 2003.5; -- got 2004, expected empty
SELECT v FROM y WHERE v = 2003.5e0; -- got 2003, expected empty
Two spellings of one number, two different rows. SHOW WARNINGS is empty for
both.
The control -- the same values in an INT column, same server, same session:
CREATE TABLE i2(v INT);
INSERT INTO i2 VALUES (2000),(2001),(2002),(2003),(2004),(2005);
SELECT count(*) FROM y WHERE v <= 2003.5; -- 5, includes 2004
SELECT count(*) FROM i2 WHERE v <= 2003.5; -- 4, correct
Rows that satisfy the predicate are dropped:
SELECT v FROM y WHERE v > 2003.5; -- 2005 (2004 is missing)
SELECT v FROM i2 WHERE v > 2003.5; -- 2004, 2005 correct
Aggregates inherit it:
SELECT count(*), max(v), sum(v) FROM y WHERE v <= 2003.5; -- 5, 2004, 10010
SELECT count(*), max(v), sum(v) FROM i2 WHERE v <= 2003.5; -- 4, 2003, 8006
max(v) is 2004 for a query whose predicate is v <= 2003.5.
A DECIMAL COLUMN on the right-hand side is correct, which shows the defect is in
the constant conversion and not in YEAR comparison generally:
CREATE TABLE lim(name VARCHAR(10), cutoff DECIMAL(10,1));
INSERT INTO lim VALUES ('h1', 2003.5);
SELECT v FROM y, lim WHERE lim.name='h1' AND y.v <= lim.cutoff;
-- 2000,2001,2002,2003 correct
No index is involved -- adding one changes nothing:
ALTER TABLE y ADD KEY(v);
SELECT v FROM y WHERE v = 2003.5; -- 2004
SELECT v FROM y IGNORE INDEX(v) WHERE v = 2003.5; -- 2004
Reproduction script: repro.py in the attached folder, run as
python3 repro.py --port <port>
It sweeps 13 constants against =, <=, >=, <, > for YEAR and for INT, prints the
per-operator table above, and exits non-zero on any YEAR/INT disagreement.
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:
Compare a YEAR column against a non-integral constant by its exact numeric value
-- as INT already does -- rather than narrowing the constant first.
At minimum, make the DECIMAL and DOUBLE paths agree with each other and raise a
truncation warning, so that the same number spelled two ways cannot select two
different row sets and so that the narrowing is at least visible.
A regression test needs a YEAR column and a constant with a fractional part,
asserted against an INT column holding the same values, over all six comparison
operators and both literal spellings. The cheapest single assertion is that
SELECT count(*) FROM y WHERE v = 2003.5
is 0. Existing coverage uses integral constants, which narrow exactly and are
correct, which is why this survived the #49480 rewrite.
© 2026, Oracle Corporation and/or its affiliates
Description: A YEAR column compared against a non-integral constant has the constant silently converted to an integer year before the comparison, and the conversion mode depends on how the literal was spelled. Rows that do not satisfy the predicate are returned, rows that do satisfy it are dropped, and no warning is raised under STRICT_TRANS_TABLES. No index is involved -- this is expression evaluation, not the range optimiser. IGNORE INDEX and a table with no index give the same wrong answer. CREATE TABLE y(v YEAR); INSERT INTO y VALUES (2000),(2001),(2002),(2003),(2004),(2005); SELECT v FROM y WHERE v = 2003.5; -- returns 2004 SELECT v FROM y WHERE v = 2003.5e0; -- returns 2003 Expected: the empty set for both -- no YEAR value equals 2003.5. Actual: each returns a row, and THE TWO SPELLINGS OF THE SAME NUMBER RETURN DIFFERENT ROWS. The same values in an INT column behave correctly: SELECT count(*) FROM y WHERE v <= 2003.5; -- 5 <- includes 2004 SELECT count(*) FROM i2 WHERE v <= 2003.5; -- 4 <- correct 2004 <= 2003.5 is reported TRUE for a YEAR column. SHOW WARNINGS is empty in every case above. -------------------------------------------------------------------------------- Rows that do match are also dropped -------------------------------------------------------------------------------- Not only are extra rows returned -- the symmetric operator loses rows: SELECT v FROM y WHERE v > 2003.5; -- 2005 <- 2004 is missing SELECT v FROM i2 WHERE v > 2003.5; -- 2004, 2005 <- correct -------------------------------------------------------------------------------- The rule -------------------------------------------------------------------------------- The constant is converted to an integer year first, and the conversion mode follows the literal's type: constant spelled as conversion v = 2003.5 returns ------------------------------- ------------------------- ------------------ 2003.5 (DECIMAL) round, half away from zero 2004 '2003.5' (string) round, half away from zero 2004 2003.5e0 (DOUBLE) truncate toward zero 2003 CAST(2003.5 AS DECIMAL(10,1)) round 2004 CAST(2003.5 AS DOUBLE) truncate 2003 Verified on 26.10.0 against every combination of 13 constants and the operators =, <=, >=, <, >: the rounding rule mispredicts 0 of 65 for DECIMAL and string constants, the truncation rule 0 of 65 for DOUBLE constants, and YEAR disagrees with an INT column holding the same values on 36 of 65. Per-operator, on 26.10.0 (YEAR vs INT, same six values): constant op YEAR INT 2003.5 = [2004] [] DIFFER 2003.5 <= [2000,2001,2002,2003,2004] [2000,2001,2002,2003] DIFFER 2003.5 > [2005] [2004,2005] DIFFER 2003.5e0 = [2003] [] DIFFER 2003.5e0 < [2000,2001,2002] [2000,2001,2002,2003] DIFFER 2003.5e0 >= [2003,2004,2005] [2004,2005] DIFFER -------------------------------------------------------------------------------- Only YEAR, among the narrow types -------------------------------------------------------------------------------- Checked with the oracle "c OP k must select the same rows as CAST(c AS DECIMAL(40,15)) OP k", since widening the column cannot change an exact numeric comparison -- 2 880 checks: YEAR 84 narrowing disagreements TINYINT 0 TINYINT UNSIGNED 0 SMALLINT 0 INT 0 INT UNSIGNED 0 BIGINT 0 DECIMAL(10,0) 0 DECIMAL(10,1) 0 BIT(8) 0 YEAR is the only narrow type that narrows the constant, which is what makes this type-specific rather than a general comparison rule. -------------------------------------------------------------------------------- Only constants -- and a bound parameter is a constant -------------------------------------------------------------------------------- A YEAR column compared to a DECIMAL COLUMN is correct: CREATE TABLE lim(name VARCHAR(10), cutoff DECIMAL(10,1)); INSERT INTO lim VALUES ('h1', 2003.5); SELECT v FROM y, lim WHERE lim.name='h1' AND y.v <= lim.cutoff; -- 2000,2001,2002,2003 correct So the defect is in the conversion applied to a CONSTANT, not in YEAR comparison generally. A bound parameter is a constant, and which path it takes depends on the type the client sends. Measured on 26.10.0, SELECT v FROM y WHERE v <= ?: parameter sent as result verdict ----------------------------------- --------------------------- --------- DECIMAL 2003.5 2000,2001,2002,2003,2004 wrong string '2003.5' 2000,2001,2002,2003,2004 wrong DOUBLE 2003.5 (float) 2000,2001,2002,2003 correct server PREPARE with @k = 2003.5 2000,2001,2002,2003 correct the same parameters, INT column 2000,2001,2002,2003 correct Two things follow, and the second is the one worth acting on: - A client that sends the value as a DECIMAL or a string gets the wrong rows. Which of those a driver does is not the application's choice and is not visible in the SQL. - The DOUBLE and server-side-PREPARE rows are NOT a workaround. They are correct on <= only because truncation happens to fall the right way for that operator. The DOUBLE path is wrong on =, < and >= -- see the per-operator table above, where v = 2003.5e0 returns 2003 and v < 2003.5e0 drops it. -------------------------------------------------------------------------------- Consequences -------------------------------------------------------------------------------- count(*) WHERE v <= 2003.5 YEAR=5 INT=4 max(v) WHERE v <= 2003.5 YEAR=2004 INT=2003 sum(v) WHERE v <= 2003.5 YEAR=10010 INT=8006 max(v) returns 2004 for a query whose own predicate is v <= 2003.5. -------------------------------------------------------------------------------- The caveat a triager will reach for, addressed up front -------------------------------------------------------------------------------- MySQL documents that a TWO-DIGIT YEAR literal maps to 1970-2069, so v = 1 legitimately matching 2001 is by design. That rule is not the subject of this report, and the four claims below do not depend on it: 1. 2003.5 and 2003.5e0 -- the same number -- select DIFFERENT rows. 2. An equality against a value the type cannot represent returns rows at all. 3. v > 2003.5 DROPS 2004, a row that satisfies the predicate. 4. No warning is raised, under STRICT_TRANS_TABLES. (The documented two-digit rule is why SELECT v FROM y WHERE v = 0.5 returns 2001: 0.5 is rounded to the literal 1 first, and 1 then maps to 2001. The rounding is the part that is not documented.) How to repeat: Needs no data files. Against a stock server, default sql_mode (which includes STRICT_TRANS_TABLES). No index anywhere: CREATE DATABASE IF NOT EXISTS t080; USE t080; CREATE TABLE y(v YEAR); INSERT INTO y VALUES (2000),(2001),(2002),(2003),(2004),(2005); SELECT v FROM y WHERE v = 2003.5; -- got 2004, expected empty SELECT v FROM y WHERE v = 2003.5e0; -- got 2003, expected empty Two spellings of one number, two different rows. SHOW WARNINGS is empty for both. The control -- the same values in an INT column, same server, same session: CREATE TABLE i2(v INT); INSERT INTO i2 VALUES (2000),(2001),(2002),(2003),(2004),(2005); SELECT count(*) FROM y WHERE v <= 2003.5; -- 5, includes 2004 SELECT count(*) FROM i2 WHERE v <= 2003.5; -- 4, correct Rows that satisfy the predicate are dropped: SELECT v FROM y WHERE v > 2003.5; -- 2005 (2004 is missing) SELECT v FROM i2 WHERE v > 2003.5; -- 2004, 2005 correct Aggregates inherit it: SELECT count(*), max(v), sum(v) FROM y WHERE v <= 2003.5; -- 5, 2004, 10010 SELECT count(*), max(v), sum(v) FROM i2 WHERE v <= 2003.5; -- 4, 2003, 8006 max(v) is 2004 for a query whose predicate is v <= 2003.5. A DECIMAL COLUMN on the right-hand side is correct, which shows the defect is in the constant conversion and not in YEAR comparison generally: CREATE TABLE lim(name VARCHAR(10), cutoff DECIMAL(10,1)); INSERT INTO lim VALUES ('h1', 2003.5); SELECT v FROM y, lim WHERE lim.name='h1' AND y.v <= lim.cutoff; -- 2000,2001,2002,2003 correct No index is involved -- adding one changes nothing: ALTER TABLE y ADD KEY(v); SELECT v FROM y WHERE v = 2003.5; -- 2004 SELECT v FROM y IGNORE INDEX(v) WHERE v = 2003.5; -- 2004 Reproduction script: repro.py in the attached folder, run as python3 repro.py --port <port> It sweeps 13 constants against =, <=, >=, <, > for YEAR and for INT, prints the per-operator table above, and exits non-zero on any YEAR/INT disagreement. 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: Compare a YEAR column against a non-integral constant by its exact numeric value -- as INT already does -- rather than narrowing the constant first. At minimum, make the DECIMAL and DOUBLE paths agree with each other and raise a truncation warning, so that the same number spelled two ways cannot select two different row sets and so that the narrowing is at least visible. A regression test needs a YEAR column and a constant with a fractional part, asserted against an INT column holding the same values, over all six comparison operators and both literal spellings. The cheapest single assertion is that SELECT count(*) FROM y WHERE v = 2003.5 is 0. Existing coverage uses integral constants, which narrow exactly and are correct, which is why this survived the #49480 rewrite.