[MDEV-8771] Comparison of a TIME column to various literals works differently Created: 2015-09-08  Updated: 2018-09-17

Status: Open
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This script:

SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
 
DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME);
 
INSERT INTO t1 VALUES ('10:20:30');
 
SELECT a=TIMESTAMP'2001-01-01 10:20:30' AS c1,a='2001-01-01 10:20:30' AS c2,a=20010101102030 AS c3,a=TIMESTAMP'2002-02-02 10:20:30' AS c4,a='2002-02-02 10:20:30' AS c5,a=20020202102030 AS c6 FROM t1;

returns

+------+------+------+------+------+------+
 
| c1   | c2   | c3   | c4   | c5   | c6   |
 
+------+------+------+------+------+------+
 
|    1 |    0 |    0 |    0 |    0 |    0 |
 
+------+------+------+------+------+------+

which means it performs the datetime-to-time conversion using CURRENT_DATE only when a TIME field is compared to a TIMESTAMP literal, and does not perform conversion otherwise. Perhaps it could do conversion in more cases, e.g. at least for TIMESTAMP-alike string literals '2001-01-01 10:20:30'. Not sure about the numbers.

MySQL-5.7 in the same scenario returns this result:

+------+------+------+------+------+------+
 
| c1   | c2   | c3   | c4   | c5   | c6   |
 
+------+------+------+------+------+------+
 
|    1 |    1 |    1 |    1 |    1 |    1 |
 
+------+------+------+------+------+------+

which means it does not perform TIME-to-DATETIME conversion at all, which is probably even worse.
Generated at Thu Feb 08 07:29:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.