Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0, 10.1, 10.2, 10.3, 10.4
-
None
Description
This script demonstrates that although TIME'00:00:00.0' and TIME'00:00:00.00' are equal to each other for comparison, they are still not equal in other contexts:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a TIME); |
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'),('00:00:02'); |
SELECT LENGTH(COALESCE(TIME'00:00:00.0',a)),LENGTH(COALESCE(TIME'00:00:00.00',a)) FROM t1; |
+--------------------------------------+---------------------------------------+
|
| LENGTH(COALESCE(TIME'00:00:00.0',a)) | LENGTH(COALESCE(TIME'00:00:00.00',a)) |
|
+--------------------------------------+---------------------------------------+
|
| 10 | 11 |
|
| 10 | 11 |
|
| 10 | 11 |
|
+--------------------------------------+---------------------------------------+
|
Notice, the two expressions return a different result (as expected), although TIME literals are equivalent for each other for comparison.
Now I put the same expressions into a WHERE condition with <=>:
SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.00',a)); |
+----------+
|
| a |
|
+----------+
|
| 00:00:00 |
|
| 00:00:01 |
|
| 00:00:02 |
|
+----------+
|
It returns all rows. This is wrong. The condition is expected to be false, and the query should return empty set.
EXPLAIN for the same query tells that the condition was erroneously simplified to true by the optimizer:
EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME'00:00:00.0',a))<=>LENGTH(COALESCE(TIME'00:00:00.00',a)); |
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |
|
+-------+------+--------------------------------------------------------+
|
Attachments
Issue Links
- blocks
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
- relates to
-
-
- Open
-
-
-
- Closed
-