Description:
When a TIME(N) column is compared with a STRING CONSTANT that carries more
fractional-second digits than N, the server rounds the constant onto the
column's precision and compares the rounded value. Rows are returned for
constants they do not equal, and rows that genuinely differ from the constant
are dropped. No index is involved -- the table can have no index at all -- and
no warning is raised.
The conversion is visible in the optimizer trace before any plan is chosen:
"expanded_query": "select `t0`.`id` AS `id` from `t0`
where (`t0`.`v` = TIME'13:00:00')"
The constant '13:00:00.4' is gone by the time the query is expanded; what the
executor evaluates is v = TIME'13:00:00'.
A DATETIME column in the same server is exact. Its trace keeps the fraction:
"original_condition": "(`dn`.`v` = TIMESTAMP'2020-01-01 00:00:00.4')"
--------------------------------------------------------------------------------
Mechanism
--------------------------------------------------------------------------------
The asymmetry is in Item_bool_func2::convert_constant_arg()
(sql/item_cmpfunc.cc:733-736 at fcf22d38), which declines the conversion for a
date-bearing field compared with a string:
if (field_item->field->can_be_compared_as_longlong() &&
!(field_item->is_temporal_with_date() &&
(*item)->result_type() == STRING_RESULT)) {
if (convert_constant_item(thd, field_item, item, converted)) return true;
MYSQL_TYPE_TIME is temporal WITHOUT date, so it is not excluded.
convert_constant_item() then stores the string INTO THE FIELD ITSELF -- which
applies the column's fsp -- and rebuilds the constant from what the field now
holds (sql/item_cmpfunc.cc:672 and :698):
int rc = (*item)->save_in_field(field, true);
...
if (field->type() == MYSQL_TYPE_TIME) {
Time_val time;
if (field->val_time(&time)) { ... }
tmp = new Item_time_literal(time, field->decimals());
field->decimals() is the declared N, so every digit beyond N is rounded away and
the comparison is then exact against the wrong value.
Arg_comparator::can_compare_as_dates() (sql/item_cmpfunc.cc:1183) shows the same
split: a date-bearing field versus a string is compared as dates at full
precision, a TIME field versus a string is not.
The TIME_TRUNCATE_FRACTIONAL SQL mode changes the direction (the constant is
truncated instead of rounded) but not the defect: the constant still lands on a
value the column can hold, and still matches a row it is not equal to.
--------------------------------------------------------------------------------
Why this is not the documented "fractional seconds are rounded" rule
--------------------------------------------------------------------------------
The manual's rounding rule (13.2.6 Fractional Seconds in Time Values) is about
STORING a value into a column of lower precision. This is a COMPARISON against
a column, where rounding has no value to store into and can only produce a false
answer. Three things in the same server show it is not a deliberate comparison
rule:
- DATETIME(0) compared with '2020-01-01 00:00:00.4' is exact and returns
nothing.
- A typed TIME'13:00:00.4' literal is exact and returns nothing.
- Comparing the same TIME(0) column with a VARCHAR COLUMN holding '13:00:00.4'
(a join, not a constant) returns nothing.
Only the string-constant-against-TIME path rounds. And the rounded answer is
not even a consistent reading of the constant -- see section 6 of How to repeat,
where one result set contradicts itself.
--------------------------------------------------------------------------------
Relationship to BUG report on TIME vs a non-constant string
--------------------------------------------------------------------------------
This report is about a string CONSTANT, where the constant is converted and the
comparison is then exact against the wrong value. The converse case -- a TIME(N)
column against a non-constant string, where no conversion happens at all and the
comparison is done on rendered text -- is a separate defect and is reported
separately. The two are complementary halves of the same split in
Arg_comparator: neither path compares a TIME column against a string correctly,
one by converting too much and the other by converting not at all.
How to repeat:
Stock server, default sql_mode, no configuration changes. 13:00:00.4 lies
strictly between the two stored values, so every operator has an unambiguous
correct answer.
CREATE DATABASE IF NOT EXISTS b088; USE b088;
CREATE TABLE t0(id INT PRIMARY KEY, v TIME(0)); -- no index on v
INSERT INTO t0 VALUES (1,'13:00:00'), (2,'13:00:01');
SELECT id, v FROM t0 WHERE v = '13:00:00.4';
Expected: empty set -- no row holds 13:00:00.4.
Actual (trunk 26.10.0, and 8.0.46 through 9.7.2):
+----+----------+
| id | v |
+----+----------+
| 1 | 13:00:00 |
+----+----------+
SHOW WARNINGS is empty.
1. THE SERVER CONTRADICTS ITS OWN EQUALITY. Nudge the constant over the
half-way mark and the OTHER row comes back -- and the server will then tell
you that row is not equal to the constant:
SELECT id, v FROM t0 WHERE v = '13:00:00.5'; -- id 2, v = 13:00:01
SELECT TIME'13:00:01' = '13:00:00.5'; -- 0
2. WHICH ROW IS RETURNED DEPENDS ON A DIGIT TIME(0) CANNOT STORE:
constant matches
'13:00:00.1' 1
'13:00:00.4' 1
'13:00:00.49' 1
'13:00:00.5' 2 <- the other row
'13:00:00.51' 2
'13:00:00.9' 2
'13:00:00.99' 2
3. FOUR OF THE SIX COMPARISON OPERATORS RETURN THE WRONG ROWS for '13:00:00.4':
operator returns correct
v = K 1 (empty) WRONG
v < K (empty) 1 WRONG
v <= K 1 1
v > K 2 2
v >= K 1,2 2 WRONG
v <> K 2 1,2 WRONG
v < K drops row 1 although 13:00:00 is genuinely earlier than 13:00:00.4, and
v <> K drops row 1 although it genuinely differs from it. The six answers
are mutually consistent -- they are the correct answers to a DIFFERENT query,
the one with K silently replaced by 13:00:00 -- which is why nothing inside
the matrix flags the problem. The server's own comparison of the same two
values outside the table does flag it:
SELECT TIME'13:00:00' < '13:00:00.4'; -- 1 (row 1 IS earlier)
SELECT TIME'13:00:00' <> '13:00:00.4'; -- 1 (row 1 IS different)
SELECT TIME'13:00:00' = '13:00:00.4'; -- 0 (row 1 is NOT equal)
All three contradict what the same comparison returns when the left side is
the column holding that value.
4. EVERY DECLARED PRECISION, N = 0 through 6. One row at the column's own zero
fraction, probed with a constant one digit finer -- every one matches, and
none should:
TIME(0) '13:00:00.4' matches
TIME(1) '13:00:00.04' matches
TIME(2) '13:00:00.004' matches
TIME(3) '13:00:00.0004' matches
TIME(4) '13:00:00.00004' matches
TIME(5) '13:00:00.000004' matches
TIME(6) '13:00:00.0000004' matches
Negative values round half away from zero the same way: with rows -13:00:00
and -13:00:01, WHERE v = '-13:00:00.6' returns the -13:00:01 row.
5. EVERY SHAPE THAT CARRIES THE CONSTANT IS AFFECTED, AND UPDATE REACHES THE
WRONG ROW:
SELECT id FROM t0 WHERE v IN ('13:00:00.4'); -- 1
SELECT id FROM t0 WHERE '13:00:00.4' = v; -- 1
SELECT id FROM t0 WHERE v BETWEEN '13:00:00.4' AND '13:00:00.9'; -- 1, 2
SELECT id FROM t0 WHERE v = CAST('13:00:00.4' AS TIME(6)); -- 1
SELECT id FROM t0 WHERE v = CONCAT('13:00:00','.4'); -- 1
SELECT id FROM t0 WHERE v = (SELECT '13:00:00.4'); -- 1
CREATE TABLE tu LIKE t0; INSERT INTO tu SELECT * FROM t0;
UPDATE tu SET id = id + 100 WHERE v = '13:00:00.4';
SELECT * FROM tu; -- the 13:00:00 row was updated
CAST(... AS TIME(6)) is worth a look: an explicit request for microsecond
precision is rounded to TIME(0) anyway, because the cast is a constant
expression and is converted like any other.
Adding KEY(v) changes nothing, and neither does IGNORE INDEX -- both return
row 1. The defect is in the constant, not in index access.
6. ON 9.7.0 AND LATER, INCLUDING TRUNK, A SINGLE RESULT SET CONTRADICTS ITSELF.
The same comparison is true in the WHERE clause and false in the select list:
SET @k = '13:00:00.4';
PREPARE s FROM 'SELECT id, v, (v = ?) AS says FROM t0 WHERE v = ?';
EXECUTE s USING @k, @k;
+----+----------+------+
| id | v | says |
+----+----------+------+
| 1 | 13:00:00 | 0 |
+----+----------+------+
The row is in the result because v = ? was true, and the same v = ? evaluated
on that row returns 0. The trace shows why: the parameter reaches the
optimizer intact (multiple equal('13:00:00.4', t0.v)), and when the multiple
equality is expanded back into a per-table predicate the rebuilt v = ? goes
through the same constant conversion and comes out as (t0.v = TIME'13:00:00'),
while the select-list copy is never converted.
Through 9.6.0 the parameter escaped the conversion entirely and this query
returned no rows, which is correct. So 9.7.0 removed the only workaround
(send the value as a parameter) and introduced a self-inconsistent result in
the process.
7. THE NEIGHBOURING PATHS THAT ARE EXACT, as controls on the same server and the
same data:
SELECT id FROM t0 WHERE v = TIME'13:00:00.4'; -- empty, correct
CREATE TABLE dn(id INT PRIMARY KEY, v DATETIME(0));
INSERT INTO dn VALUES (1,'2020-01-01 00:00:00');
SELECT id FROM dn WHERE v = '2020-01-01 00:00:00.4'; -- empty, correct
CREATE TABLE s0(k VARCHAR(20)); INSERT INTO s0 VALUES ('13:00:00.4');
SELECT t0.id FROM t0 JOIN s0 ON t0.v = s0.k; -- empty, correct
MariaDB 13.0.2 answers =, < and <> correctly on the first fixture
(empty / 1 / 1,2).
Reproduction script: repro.py in the attached folder. Verbatim trunk session:
verify-trunk-26.10.0.txt.
Suggested fix:
Compare the TIME value against the constant EXACTLY, the way the DATETIME path
already does.
Concretely: in Item_bool_func2::convert_constant_arg() the guard that declines
the conversion for a string constant currently covers only date-bearing fields
!(field_item->is_temporal_with_date() &&
(*item)->result_type() == STRING_RESULT)
and should cover MYSQL_TYPE_TIME as well, so that a TIME field versus a string
constant is compared at full precision through get_time_value() /
str_to_time_with_warn() -- which already parses all six digits and does not
round -- instead of through a constant that was pushed into the field and back
out at field->decimals().
A constant whose resolution is finer than the column can never equal any stored
value, so = must be false and <> true for every row. For <, <=, >, >= the extra
digits decide the answer and must be honoured rather than rounded away. If a
conversion of the constant is wanted for index access, it has to be a
range-preserving one (round the bound outward and keep the residual predicate),
not a value-replacing one.
Whatever the resolution, WHERE v = K and SELECT v = K must not disagree within
one result set (section 6 above).
A regression test needs a TIME(N) column, two rows one unit apart, and a string
constant with N+1 fractional digits placed between them, asserting all six
operators. Asserting that
SELECT count(*) FROM t0 WHERE v = '13:00:00.4'
is 0 is the cheapest single check. Existing coverage uses constants that fit
the declared precision, which convert exactly and are correct, which is why this
survived.
© 2026, Oracle Corporation and/or its affiliates
Description: When a TIME(N) column is compared with a STRING CONSTANT that carries more fractional-second digits than N, the server rounds the constant onto the column's precision and compares the rounded value. Rows are returned for constants they do not equal, and rows that genuinely differ from the constant are dropped. No index is involved -- the table can have no index at all -- and no warning is raised. The conversion is visible in the optimizer trace before any plan is chosen: "expanded_query": "select `t0`.`id` AS `id` from `t0` where (`t0`.`v` = TIME'13:00:00')" The constant '13:00:00.4' is gone by the time the query is expanded; what the executor evaluates is v = TIME'13:00:00'. A DATETIME column in the same server is exact. Its trace keeps the fraction: "original_condition": "(`dn`.`v` = TIMESTAMP'2020-01-01 00:00:00.4')" -------------------------------------------------------------------------------- Mechanism -------------------------------------------------------------------------------- The asymmetry is in Item_bool_func2::convert_constant_arg() (sql/item_cmpfunc.cc:733-736 at fcf22d38), which declines the conversion for a date-bearing field compared with a string: if (field_item->field->can_be_compared_as_longlong() && !(field_item->is_temporal_with_date() && (*item)->result_type() == STRING_RESULT)) { if (convert_constant_item(thd, field_item, item, converted)) return true; MYSQL_TYPE_TIME is temporal WITHOUT date, so it is not excluded. convert_constant_item() then stores the string INTO THE FIELD ITSELF -- which applies the column's fsp -- and rebuilds the constant from what the field now holds (sql/item_cmpfunc.cc:672 and :698): int rc = (*item)->save_in_field(field, true); ... if (field->type() == MYSQL_TYPE_TIME) { Time_val time; if (field->val_time(&time)) { ... } tmp = new Item_time_literal(time, field->decimals()); field->decimals() is the declared N, so every digit beyond N is rounded away and the comparison is then exact against the wrong value. Arg_comparator::can_compare_as_dates() (sql/item_cmpfunc.cc:1183) shows the same split: a date-bearing field versus a string is compared as dates at full precision, a TIME field versus a string is not. The TIME_TRUNCATE_FRACTIONAL SQL mode changes the direction (the constant is truncated instead of rounded) but not the defect: the constant still lands on a value the column can hold, and still matches a row it is not equal to. -------------------------------------------------------------------------------- Why this is not the documented "fractional seconds are rounded" rule -------------------------------------------------------------------------------- The manual's rounding rule (13.2.6 Fractional Seconds in Time Values) is about STORING a value into a column of lower precision. This is a COMPARISON against a column, where rounding has no value to store into and can only produce a false answer. Three things in the same server show it is not a deliberate comparison rule: - DATETIME(0) compared with '2020-01-01 00:00:00.4' is exact and returns nothing. - A typed TIME'13:00:00.4' literal is exact and returns nothing. - Comparing the same TIME(0) column with a VARCHAR COLUMN holding '13:00:00.4' (a join, not a constant) returns nothing. Only the string-constant-against-TIME path rounds. And the rounded answer is not even a consistent reading of the constant -- see section 6 of How to repeat, where one result set contradicts itself. -------------------------------------------------------------------------------- Relationship to BUG report on TIME vs a non-constant string -------------------------------------------------------------------------------- This report is about a string CONSTANT, where the constant is converted and the comparison is then exact against the wrong value. The converse case -- a TIME(N) column against a non-constant string, where no conversion happens at all and the comparison is done on rendered text -- is a separate defect and is reported separately. The two are complementary halves of the same split in Arg_comparator: neither path compares a TIME column against a string correctly, one by converting too much and the other by converting not at all. How to repeat: Stock server, default sql_mode, no configuration changes. 13:00:00.4 lies strictly between the two stored values, so every operator has an unambiguous correct answer. CREATE DATABASE IF NOT EXISTS b088; USE b088; CREATE TABLE t0(id INT PRIMARY KEY, v TIME(0)); -- no index on v INSERT INTO t0 VALUES (1,'13:00:00'), (2,'13:00:01'); SELECT id, v FROM t0 WHERE v = '13:00:00.4'; Expected: empty set -- no row holds 13:00:00.4. Actual (trunk 26.10.0, and 8.0.46 through 9.7.2): +----+----------+ | id | v | +----+----------+ | 1 | 13:00:00 | +----+----------+ SHOW WARNINGS is empty. 1. THE SERVER CONTRADICTS ITS OWN EQUALITY. Nudge the constant over the half-way mark and the OTHER row comes back -- and the server will then tell you that row is not equal to the constant: SELECT id, v FROM t0 WHERE v = '13:00:00.5'; -- id 2, v = 13:00:01 SELECT TIME'13:00:01' = '13:00:00.5'; -- 0 2. WHICH ROW IS RETURNED DEPENDS ON A DIGIT TIME(0) CANNOT STORE: constant matches '13:00:00.1' 1 '13:00:00.4' 1 '13:00:00.49' 1 '13:00:00.5' 2 <- the other row '13:00:00.51' 2 '13:00:00.9' 2 '13:00:00.99' 2 3. FOUR OF THE SIX COMPARISON OPERATORS RETURN THE WRONG ROWS for '13:00:00.4': operator returns correct v = K 1 (empty) WRONG v < K (empty) 1 WRONG v <= K 1 1 v > K 2 2 v >= K 1,2 2 WRONG v <> K 2 1,2 WRONG v < K drops row 1 although 13:00:00 is genuinely earlier than 13:00:00.4, and v <> K drops row 1 although it genuinely differs from it. The six answers are mutually consistent -- they are the correct answers to a DIFFERENT query, the one with K silently replaced by 13:00:00 -- which is why nothing inside the matrix flags the problem. The server's own comparison of the same two values outside the table does flag it: SELECT TIME'13:00:00' < '13:00:00.4'; -- 1 (row 1 IS earlier) SELECT TIME'13:00:00' <> '13:00:00.4'; -- 1 (row 1 IS different) SELECT TIME'13:00:00' = '13:00:00.4'; -- 0 (row 1 is NOT equal) All three contradict what the same comparison returns when the left side is the column holding that value. 4. EVERY DECLARED PRECISION, N = 0 through 6. One row at the column's own zero fraction, probed with a constant one digit finer -- every one matches, and none should: TIME(0) '13:00:00.4' matches TIME(1) '13:00:00.04' matches TIME(2) '13:00:00.004' matches TIME(3) '13:00:00.0004' matches TIME(4) '13:00:00.00004' matches TIME(5) '13:00:00.000004' matches TIME(6) '13:00:00.0000004' matches Negative values round half away from zero the same way: with rows -13:00:00 and -13:00:01, WHERE v = '-13:00:00.6' returns the -13:00:01 row. 5. EVERY SHAPE THAT CARRIES THE CONSTANT IS AFFECTED, AND UPDATE REACHES THE WRONG ROW: SELECT id FROM t0 WHERE v IN ('13:00:00.4'); -- 1 SELECT id FROM t0 WHERE '13:00:00.4' = v; -- 1 SELECT id FROM t0 WHERE v BETWEEN '13:00:00.4' AND '13:00:00.9'; -- 1, 2 SELECT id FROM t0 WHERE v = CAST('13:00:00.4' AS TIME(6)); -- 1 SELECT id FROM t0 WHERE v = CONCAT('13:00:00','.4'); -- 1 SELECT id FROM t0 WHERE v = (SELECT '13:00:00.4'); -- 1 CREATE TABLE tu LIKE t0; INSERT INTO tu SELECT * FROM t0; UPDATE tu SET id = id + 100 WHERE v = '13:00:00.4'; SELECT * FROM tu; -- the 13:00:00 row was updated CAST(... AS TIME(6)) is worth a look: an explicit request for microsecond precision is rounded to TIME(0) anyway, because the cast is a constant expression and is converted like any other. Adding KEY(v) changes nothing, and neither does IGNORE INDEX -- both return row 1. The defect is in the constant, not in index access. 6. ON 9.7.0 AND LATER, INCLUDING TRUNK, A SINGLE RESULT SET CONTRADICTS ITSELF. The same comparison is true in the WHERE clause and false in the select list: SET @k = '13:00:00.4'; PREPARE s FROM 'SELECT id, v, (v = ?) AS says FROM t0 WHERE v = ?'; EXECUTE s USING @k, @k; +----+----------+------+ | id | v | says | +----+----------+------+ | 1 | 13:00:00 | 0 | +----+----------+------+ The row is in the result because v = ? was true, and the same v = ? evaluated on that row returns 0. The trace shows why: the parameter reaches the optimizer intact (multiple equal('13:00:00.4', t0.v)), and when the multiple equality is expanded back into a per-table predicate the rebuilt v = ? goes through the same constant conversion and comes out as (t0.v = TIME'13:00:00'), while the select-list copy is never converted. Through 9.6.0 the parameter escaped the conversion entirely and this query returned no rows, which is correct. So 9.7.0 removed the only workaround (send the value as a parameter) and introduced a self-inconsistent result in the process. 7. THE NEIGHBOURING PATHS THAT ARE EXACT, as controls on the same server and the same data: SELECT id FROM t0 WHERE v = TIME'13:00:00.4'; -- empty, correct CREATE TABLE dn(id INT PRIMARY KEY, v DATETIME(0)); INSERT INTO dn VALUES (1,'2020-01-01 00:00:00'); SELECT id FROM dn WHERE v = '2020-01-01 00:00:00.4'; -- empty, correct CREATE TABLE s0(k VARCHAR(20)); INSERT INTO s0 VALUES ('13:00:00.4'); SELECT t0.id FROM t0 JOIN s0 ON t0.v = s0.k; -- empty, correct MariaDB 13.0.2 answers =, < and <> correctly on the first fixture (empty / 1 / 1,2). Reproduction script: repro.py in the attached folder. Verbatim trunk session: verify-trunk-26.10.0.txt. Suggested fix: Compare the TIME value against the constant EXACTLY, the way the DATETIME path already does. Concretely: in Item_bool_func2::convert_constant_arg() the guard that declines the conversion for a string constant currently covers only date-bearing fields !(field_item->is_temporal_with_date() && (*item)->result_type() == STRING_RESULT) and should cover MYSQL_TYPE_TIME as well, so that a TIME field versus a string constant is compared at full precision through get_time_value() / str_to_time_with_warn() -- which already parses all six digits and does not round -- instead of through a constant that was pushed into the field and back out at field->decimals(). A constant whose resolution is finer than the column can never equal any stored value, so = must be false and <> true for every row. For <, <=, >, >= the extra digits decide the answer and must be honoured rather than rounded away. If a conversion of the constant is wanted for index access, it has to be a range-preserving one (round the bound outward and keep the residual predicate), not a value-replacing one. Whatever the resolution, WHERE v = K and SELECT v = K must not disagree within one result set (section 6 above). A regression test needs a TIME(N) column, two rows one unit apart, and a string constant with N+1 fractional digits placed between them, asserting all six operators. Asserting that SELECT count(*) FROM t0 WHERE v = '13:00:00.4' is 0 is the cheapest single check. Existing coverage uses constants that fit the declared precision, which convert exactly and are correct, which is why this survived.