Bug #91594 Inconsistent rounding of fraction decimals in TIME, DATETIME/TIMESTAMP constant

• Submitted: 10 Jul 2018 15:35
• Reporter: Dag Wanvik
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8
• OS: Any
• CPU Architecture: Any

[10 Jul 2018 15:35] Dag Wanvik

Description:
When comparing a TIME field to a constant, the time fraction part of a TIME constant is rounded to the number of decimals of the resolved type before the comparison happens. This does not happen for DATETIME or TIMESTAMP. This leads to inconsistent results.

How to repeat:
CREATE TABLE t(t TIME(3), dt DATETIME(3), ts TIMESTAMP(3));
INSERT INTO t VALUES ('12:00:00.555', '2018-12-12 12:00:00.555', '2018-12-12 12:00:00.555');
SELECT * from t;
+--------------+-------------------------+-------------------------+
| t            | dt                      | ts                      |
+--------------+-------------------------+-------------------------+
| 12:00:00.555 | 2018-12-12 12:00:00.555 | 2018-12-12 12:00:00.555 |
+--------------+-------------------------+-------------------------+
1 row in set (0.00 sec)

SELECT * FROM t WHERE t = '12:00:00.555444';
+--------------+-------------------------+-------------------------+
| t            | dt                      | ts                      |
+--------------+-------------------------+-------------------------+
| 12:00:00.555 | 2018-12-12 12:00:00.555 | 2018-12-12 12:00:00.555 |
+--------------+-------------------------+-------------------------+
1 row in set (0.00 sec)

SELECT * FROM t WHERE dt = '2018-12-12 12:00:00.555444';
Empty set (0.01 sec)

SELECT * FROM t WHERE ts = '2018-12-12 12:00:00.555444';
Empty set (0.00 sec)

Using explain:

EXPLAIN SELECT * FROM t WHERE t = '12:00:00.555444';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `mysql`.`t`.`t` AS `t`,`mysql`.`t`.`dt` AS `dt`,`mysql`.`t`.`ts` AS `ts` from `mysql`.`t` where (`mysql`.`t`.`t` = '12:00:00.555')

where as for the DATETIME/TIMESTAMP case, it is not:

 
EXPLAIN SELECT * FROM t WHERE ts = '2018-12-12 12:00:00.555444';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

Note (Code 1003): /* select#1 */ select `mysql`.`t`.`t` AS `t`,`mysql`.`t`.`dt` AS `dt`,`mysql`.`t`.`ts` AS `ts` from `mysql`.`t` where (`mysql`.`t`.`ts` = '2018-12-12 12:00:00.555444')

Suggested fix:
Make TIME behave the same as DATETIME and TIMESTAMP: retain the constant's precision in a comparison

[10 Jul 2018 15:58] Dag Wanvik

Posted by developer:
 
Revised Description:
When comparing a TIME field to a constant, the time fraction part of a TIME
constant is rounded to the number of decimals *of the field* before the
comparison happens. This does not happen for DATETIME or TIMESTAMP. This
leads to inconsistent results.