Description:
When a TIME(N) value with N >= 1 is compared with a string that is NOT A
CONSTANT -- a VARCHAR column, or any expression the optimizer cannot fold -- the
comparison is performed on the RENDERED TEXT of the two operands instead of on
the time values.
CREATE TABLE m(s VARCHAR(20), t TIME(3));
INSERT INTO m VALUES ('13:00:00', '13:00:00');
SELECT t = s, t <= s, t >= s, t <> s FROM m;
t = s t <= s t >= s t <> s
MySQL 26.10.0 (trunk) 0 0 1 1
expected, and MariaDB 13.2.0 1 1 1 0
Both operands denote 13:00:00, yet the server reports t > s and t <> s. The
stored TIME(3) renders as '13:00:00.000', and as text '13:00:00.000' >
'13:00:00' -- the comparison is being done on the strings. No warning is
raised.
--------------------------------------------------------------------------------
It loses every row of an ordinary join
--------------------------------------------------------------------------------
CREATE TABLE sched(id INT, slot TIME(3));
CREATE TABLE imp(id INT, slot VARCHAR(20));
INSERT INTO sched VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00');
INSERT INTO imp VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00');
SELECT count(*) FROM sched JOIN imp ON sched.slot = imp.slot;
-- 0 (MariaDB: 3)
Joining a temporal column against a staging or import table typed as text is an
everyday shape, and it silently produces the empty set. Adding an explicit
CAST(imp.slot AS TIME(3)) restores all 3 rows.
--------------------------------------------------------------------------------
Comparison with a TIME value is not reflexive
--------------------------------------------------------------------------------
SELECT CAST(TIME'13:00:00' AS TIME(6)) <= '13:00:00'; -- 0
A value is not <= itself. Swept over every legal precision, on trunk:
fsp N t = s t <= s t >= s the stored value renders as
0 1 1 1 '13:00:00'
1 0 0 1 '13:00:00.0'
2 0 0 1 '13:00:00.00'
3 0 0 1 '13:00:00.000'
4 0 0 1 '13:00:00.0000'
5 0 0 1 '13:00:00.00000'
6 0 0 1 '13:00:00.000000'
TIME(0) is correct only because its rendering happens to match the string
character for character. Every precision that appends a fractional part fails.
--------------------------------------------------------------------------------
Proof that string ordering, not time ordering, is applied
--------------------------------------------------------------------------------
The two semantics disagree on this one, which settles it:
SELECT CAST(TIME'13:00:00' AS TIME(6)) < '2';
- as a TIME comparison: false -- 13:00:00 is not before 00:00:02.
- as a STRING comparison: true -- '1...' < '2'.
MySQL trunk returns 1; MariaDB returns 0. Confirming from the other side, only
the spelling that matches character for character compares equal:
SELECT CAST(TIME'13:00:00' AS TIME(6)) = '13:00:00.000000'; -- 1
SELECT CAST(TIME'13:00:00' AS TIME(6)) = '13:00:00.0'; -- 0
SELECT CAST(TIME'13:00:00' AS TIME(6)) = '13:00:00'; -- 0
It is not the documented floating-point fallback either. This needs saying
because the 0 / 0 / 1 pattern on its own is also what a float comparison would
produce -- TIME renders as 130000.000000 in numeric context while '13:00:00'
converts to 13 -- so the first table does not by itself distinguish the two.
Two things do:
- CAST(TIME'13:00:00' AS TIME(6)) < '2' is 1. As floats that is
130000.000000 < 2.0, which is false. Only string ordering gives 1.
- No warning is raised. Converting '13:00:00' to a double raises
Warning 1292 Truncated incorrect DOUBLE value: '13:00:00' --
SELECT '13:00:00' + 0 does, and returns 13. SELECT t = s FROM m raises
nothing, so no such conversion happens.
So the comparison is on the rendered text, which no rule on the Type Conversion
in Expression Evaluation manual page authorises: "if both arguments ... are
strings" does not apply (a TIME column is not a string), there is no TIME
counterpart to the TIMESTAMP/DATETIME constant rule, and the "in all other cases
... compared as floating-point" fallback is demonstrably not what runs. In any
case no documented rule can make x <= x false.
--------------------------------------------------------------------------------
When the correct path is taken
--------------------------------------------------------------------------------
SELECT t = '13:00:00' FROM m; -- 1 correct: a literal is
folded to TIME
SELECT t = s FROM m; -- 0 wrong: a string column
SELECT CAST(t AS TIME(6)) = '13:00:00' FROM m; -- 0 wrong: a CAST result
on the left
A LITERAL on the right is converted to a temporal value by
Item_bool_func2::convert_constant_arg(), so hand-written SQL tested against a
literal works. The defect appears as soon as the string side is not a constant,
or the temporal side is the result of CAST(... AS TIME(N)). That is what makes
it easy to miss: the query behaves correctly in a console test and fails against
real data in a column.
--------------------------------------------------------------------------------
It is also an index-neutrality violation
--------------------------------------------------------------------------------
A functional index over the expression stores real TIME values, so the index
lookup converts the constant correctly while a scan applies the string
comparison. The same query then returns different answers depending on whether
the index is used:
CREATE TABLE e2(id INT PRIMARY KEY, t TIME(3));
INSERT INTO e2 VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00');
CREATE INDEX fx ON e2((CAST(t AS TIME(6))));
SELECT id FROM e2
WHERE CAST(t AS TIME(6)) = '13:00:00'; -- 2 correct
SELECT id FROM e2 IGNORE INDEX(fx)
WHERE CAST(t AS TIME(6)) = '13:00:00'; -- empty
Ground truth is row 2. Creating or dropping an index must not change which rows
a query returns, so an application can start returning different results after a
routine index change.
--------------------------------------------------------------------------------
Not general to the temporal types
--------------------------------------------------------------------------------
DATE and DATETIME are correct on trunk, including the column-versus-column case:
SELECT CAST(TIMESTAMP'2020-01-03 12:00:00' AS DATETIME(6))
= '2020-01-03 12:00:00'; -- 1
SELECT CAST(DATE'2020-01-03' AS DATETIME(6)) = '2020-01-03'; -- 1
-- DATETIME(3) column = VARCHAR column holding the same instant -- 1
Only TIME with a fractional-seconds precision is affected.
How to repeat:
Needs no data files. Against a stock server, default sql_mode:
CREATE DATABASE IF NOT EXISTS t081; USE t081;
-- 1. the same instant in a TIME(3) column and a VARCHAR column
CREATE TABLE m(s VARCHAR(20), t TIME(3));
INSERT INTO m VALUES ('13:00:00', '13:00:00');
SELECT t = s, t <= s, t >= s, t <> s FROM m;
-- got 0, 0, 1, 1
-- expected 1, 1, 1, 0
SHOW WARNINGS; -- empty
-- 2. reflexivity
SELECT CAST(TIME'13:00:00' AS TIME(6)) <= '13:00:00'; -- got 0, expect 1
-- 3. string ordering or time ordering?
SELECT CAST(TIME'13:00:00' AS TIME(6)) < '2'; -- got 1;
-- as a time comparison this is 0. Only string ordering gives 1.
-- 4. an ordinary join loses every row
CREATE TABLE sched(id INT, slot TIME(3));
CREATE TABLE imp(id INT, slot VARCHAR(20));
INSERT INTO sched VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00');
INSERT INTO imp VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00');
SELECT count(*) FROM sched JOIN imp ON sched.slot = imp.slot;
-- got 0, expected 3
SELECT count(*) FROM sched JOIN imp
ON sched.slot = CAST(imp.slot AS TIME(3)); -- 3
-- 5. an index changes the answer
CREATE TABLE e2(id INT PRIMARY KEY, t TIME(3));
INSERT INTO e2 VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00');
CREATE INDEX fx ON e2((CAST(t AS TIME(6))));
SELECT id FROM e2
WHERE CAST(t AS TIME(6)) = '13:00:00'; -- 2
SELECT id FROM e2 IGNORE INDEX(fx)
WHERE CAST(t AS TIME(6)) = '13:00:00'; -- empty
-- 6. controls, all correct on the same server
SELECT t = '13:00:00' FROM m; -- 1
SELECT CAST(DATE'2020-01-03' AS DATETIME(6)) = '2020-01-03'; -- 1
The precision sweep (TIME(0) through TIME(6)) and the same script against
MariaDB are in verify-main.py in the attached folder;
main-verification-2026-09-20.log is its output on all five servers listed under
Version.
Source build used:
git clone https://github.com/mysql/mysql-server.git # trunk, fcf22d3
cmake ... -DCMAKE_BUILD_TYPE=RelWithDebInfo && make -j56
Suggested fix:
Let a TIME item paired with a non-constant string reach the TIME comparator,
exactly as DATE and DATETIME items already reach theirs. Either:
- in Arg_comparator::set_cmp_func() (sql/item_cmpfunc.cc:1260), extend the
MYSQL_TYPE_TIME branch at line 1281 so that a TIME item opposite a
STRING_RESULT item selects compare_time with get_time_value on both sides --
the same shape the can_compare_as_dates() branch uses for DATETIME opposite
a string; or
- make Arg_comparator::inject_cast_nodes() reach this pair. Note the existing
wrap_in_cast(..., MYSQL_TYPE_TIME) sits INSIDE the func == &compare_datetime
branch (it is the tail of the DATETIME/DATE/TIME chain that begins at line
1544), so this fix means lifting that TIME tail out into a condition that
also covers a TIME item opposite a string comparator -- not merely calling
the function, which today returns false for this pair.
cast_incompatible_args already walks every WHERE and join condition
unconditionally (sql/sql_optimizer.cc:781 and :808), so the call site needs
no change.
The first is closer to the fix already made for #29555. Either makes the
comparison reflexive and makes the join above match its 3 rows.
A regression test needs a TIME(N) column with N >= 1 compared against a VARCHAR
column, not against a literal -- the existing coverage compares against
literals, which take the constant-folding path and are correct, which is why
this survived. Asserting
SELECT CAST(TIME'13:00:00' AS TIME(6)) <= '13:00:00'
is 1 is the cheapest single assertion.
© 2026, Oracle Corporation and/or its affiliates
Description: When a TIME(N) value with N >= 1 is compared with a string that is NOT A CONSTANT -- a VARCHAR column, or any expression the optimizer cannot fold -- the comparison is performed on the RENDERED TEXT of the two operands instead of on the time values. CREATE TABLE m(s VARCHAR(20), t TIME(3)); INSERT INTO m VALUES ('13:00:00', '13:00:00'); SELECT t = s, t <= s, t >= s, t <> s FROM m; t = s t <= s t >= s t <> s MySQL 26.10.0 (trunk) 0 0 1 1 expected, and MariaDB 13.2.0 1 1 1 0 Both operands denote 13:00:00, yet the server reports t > s and t <> s. The stored TIME(3) renders as '13:00:00.000', and as text '13:00:00.000' > '13:00:00' -- the comparison is being done on the strings. No warning is raised. -------------------------------------------------------------------------------- It loses every row of an ordinary join -------------------------------------------------------------------------------- CREATE TABLE sched(id INT, slot TIME(3)); CREATE TABLE imp(id INT, slot VARCHAR(20)); INSERT INTO sched VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00'); INSERT INTO imp VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00'); SELECT count(*) FROM sched JOIN imp ON sched.slot = imp.slot; -- 0 (MariaDB: 3) Joining a temporal column against a staging or import table typed as text is an everyday shape, and it silently produces the empty set. Adding an explicit CAST(imp.slot AS TIME(3)) restores all 3 rows. -------------------------------------------------------------------------------- Comparison with a TIME value is not reflexive -------------------------------------------------------------------------------- SELECT CAST(TIME'13:00:00' AS TIME(6)) <= '13:00:00'; -- 0 A value is not <= itself. Swept over every legal precision, on trunk: fsp N t = s t <= s t >= s the stored value renders as 0 1 1 1 '13:00:00' 1 0 0 1 '13:00:00.0' 2 0 0 1 '13:00:00.00' 3 0 0 1 '13:00:00.000' 4 0 0 1 '13:00:00.0000' 5 0 0 1 '13:00:00.00000' 6 0 0 1 '13:00:00.000000' TIME(0) is correct only because its rendering happens to match the string character for character. Every precision that appends a fractional part fails. -------------------------------------------------------------------------------- Proof that string ordering, not time ordering, is applied -------------------------------------------------------------------------------- The two semantics disagree on this one, which settles it: SELECT CAST(TIME'13:00:00' AS TIME(6)) < '2'; - as a TIME comparison: false -- 13:00:00 is not before 00:00:02. - as a STRING comparison: true -- '1...' < '2'. MySQL trunk returns 1; MariaDB returns 0. Confirming from the other side, only the spelling that matches character for character compares equal: SELECT CAST(TIME'13:00:00' AS TIME(6)) = '13:00:00.000000'; -- 1 SELECT CAST(TIME'13:00:00' AS TIME(6)) = '13:00:00.0'; -- 0 SELECT CAST(TIME'13:00:00' AS TIME(6)) = '13:00:00'; -- 0 It is not the documented floating-point fallback either. This needs saying because the 0 / 0 / 1 pattern on its own is also what a float comparison would produce -- TIME renders as 130000.000000 in numeric context while '13:00:00' converts to 13 -- so the first table does not by itself distinguish the two. Two things do: - CAST(TIME'13:00:00' AS TIME(6)) < '2' is 1. As floats that is 130000.000000 < 2.0, which is false. Only string ordering gives 1. - No warning is raised. Converting '13:00:00' to a double raises Warning 1292 Truncated incorrect DOUBLE value: '13:00:00' -- SELECT '13:00:00' + 0 does, and returns 13. SELECT t = s FROM m raises nothing, so no such conversion happens. So the comparison is on the rendered text, which no rule on the Type Conversion in Expression Evaluation manual page authorises: "if both arguments ... are strings" does not apply (a TIME column is not a string), there is no TIME counterpart to the TIMESTAMP/DATETIME constant rule, and the "in all other cases ... compared as floating-point" fallback is demonstrably not what runs. In any case no documented rule can make x <= x false. -------------------------------------------------------------------------------- When the correct path is taken -------------------------------------------------------------------------------- SELECT t = '13:00:00' FROM m; -- 1 correct: a literal is folded to TIME SELECT t = s FROM m; -- 0 wrong: a string column SELECT CAST(t AS TIME(6)) = '13:00:00' FROM m; -- 0 wrong: a CAST result on the left A LITERAL on the right is converted to a temporal value by Item_bool_func2::convert_constant_arg(), so hand-written SQL tested against a literal works. The defect appears as soon as the string side is not a constant, or the temporal side is the result of CAST(... AS TIME(N)). That is what makes it easy to miss: the query behaves correctly in a console test and fails against real data in a column. -------------------------------------------------------------------------------- It is also an index-neutrality violation -------------------------------------------------------------------------------- A functional index over the expression stores real TIME values, so the index lookup converts the constant correctly while a scan applies the string comparison. The same query then returns different answers depending on whether the index is used: CREATE TABLE e2(id INT PRIMARY KEY, t TIME(3)); INSERT INTO e2 VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00'); CREATE INDEX fx ON e2((CAST(t AS TIME(6)))); SELECT id FROM e2 WHERE CAST(t AS TIME(6)) = '13:00:00'; -- 2 correct SELECT id FROM e2 IGNORE INDEX(fx) WHERE CAST(t AS TIME(6)) = '13:00:00'; -- empty Ground truth is row 2. Creating or dropping an index must not change which rows a query returns, so an application can start returning different results after a routine index change. -------------------------------------------------------------------------------- Not general to the temporal types -------------------------------------------------------------------------------- DATE and DATETIME are correct on trunk, including the column-versus-column case: SELECT CAST(TIMESTAMP'2020-01-03 12:00:00' AS DATETIME(6)) = '2020-01-03 12:00:00'; -- 1 SELECT CAST(DATE'2020-01-03' AS DATETIME(6)) = '2020-01-03'; -- 1 -- DATETIME(3) column = VARCHAR column holding the same instant -- 1 Only TIME with a fractional-seconds precision is affected. How to repeat: Needs no data files. Against a stock server, default sql_mode: CREATE DATABASE IF NOT EXISTS t081; USE t081; -- 1. the same instant in a TIME(3) column and a VARCHAR column CREATE TABLE m(s VARCHAR(20), t TIME(3)); INSERT INTO m VALUES ('13:00:00', '13:00:00'); SELECT t = s, t <= s, t >= s, t <> s FROM m; -- got 0, 0, 1, 1 -- expected 1, 1, 1, 0 SHOW WARNINGS; -- empty -- 2. reflexivity SELECT CAST(TIME'13:00:00' AS TIME(6)) <= '13:00:00'; -- got 0, expect 1 -- 3. string ordering or time ordering? SELECT CAST(TIME'13:00:00' AS TIME(6)) < '2'; -- got 1; -- as a time comparison this is 0. Only string ordering gives 1. -- 4. an ordinary join loses every row CREATE TABLE sched(id INT, slot TIME(3)); CREATE TABLE imp(id INT, slot VARCHAR(20)); INSERT INTO sched VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00'); INSERT INTO imp VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00'); SELECT count(*) FROM sched JOIN imp ON sched.slot = imp.slot; -- got 0, expected 3 SELECT count(*) FROM sched JOIN imp ON sched.slot = CAST(imp.slot AS TIME(3)); -- 3 -- 5. an index changes the answer CREATE TABLE e2(id INT PRIMARY KEY, t TIME(3)); INSERT INTO e2 VALUES (1,'09:00:00'),(2,'13:00:00'),(3,'17:30:00'); CREATE INDEX fx ON e2((CAST(t AS TIME(6)))); SELECT id FROM e2 WHERE CAST(t AS TIME(6)) = '13:00:00'; -- 2 SELECT id FROM e2 IGNORE INDEX(fx) WHERE CAST(t AS TIME(6)) = '13:00:00'; -- empty -- 6. controls, all correct on the same server SELECT t = '13:00:00' FROM m; -- 1 SELECT CAST(DATE'2020-01-03' AS DATETIME(6)) = '2020-01-03'; -- 1 The precision sweep (TIME(0) through TIME(6)) and the same script against MariaDB are in verify-main.py in the attached folder; main-verification-2026-09-20.log is its output on all five servers listed under Version. Source build used: git clone https://github.com/mysql/mysql-server.git # trunk, fcf22d3 cmake ... -DCMAKE_BUILD_TYPE=RelWithDebInfo && make -j56 Suggested fix: Let a TIME item paired with a non-constant string reach the TIME comparator, exactly as DATE and DATETIME items already reach theirs. Either: - in Arg_comparator::set_cmp_func() (sql/item_cmpfunc.cc:1260), extend the MYSQL_TYPE_TIME branch at line 1281 so that a TIME item opposite a STRING_RESULT item selects compare_time with get_time_value on both sides -- the same shape the can_compare_as_dates() branch uses for DATETIME opposite a string; or - make Arg_comparator::inject_cast_nodes() reach this pair. Note the existing wrap_in_cast(..., MYSQL_TYPE_TIME) sits INSIDE the func == &compare_datetime branch (it is the tail of the DATETIME/DATE/TIME chain that begins at line 1544), so this fix means lifting that TIME tail out into a condition that also covers a TIME item opposite a string comparator -- not merely calling the function, which today returns false for this pair. cast_incompatible_args already walks every WHERE and join condition unconditionally (sql/sql_optimizer.cc:781 and :808), so the call site needs no change. The first is closer to the fix already made for #29555. Either makes the comparison reflexive and makes the join above match its 3 rows. A regression test needs a TIME(N) column with N >= 1 compared against a VARCHAR column, not against a literal -- the existing coverage compares against literals, which take the constant-folding path and are correct, which is why this survived. Asserting SELECT CAST(TIME'13:00:00' AS TIME(6)) <= '13:00:00' is 1 is the cheapest single assertion.