[MDEV-8860] Wrong result for WHERE 2016 < SOME (SELECT CAST(time_column AS DATETIME) FROM t1) Created: 2015-09-28  Updated: 2015-09-30  Resolved: 2015-09-30

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Temporal Types
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.1.8

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: upstream

Sprint: 10.1.8-4

 Description    

SET timestamp=UNIX_TIMESTAMP('2015-01-01 00:00:00');
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a TIME);
 
INSERT INTO t1 VALUES ('00:00:00'),('00:01:00');
 
SELECT 1 FROM t1 WHERE 2016 > SOME (SELECT CAST(a AS DATETIME) FROM t1);

returns two rows with no warnings:

+---+
 
| 1 |
 
+---+
 
| 1 |
 
| 1 |
 
+---+
 
2 rows in set (0.01 sec)

Now let's check which of the rows in SOME actually generated true condition:

SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME);
 
SHOW WARNINGS;

Opps. It returns empty set with a warning:

+---------+------+----------------------------------+
 
| Level   | Code | Message                          |
 
+---------+------+----------------------------------+
 
| Warning | 1292 | Incorrect datetime value: '2016' |
 
+---------+------+----------------------------------+
 
1 row in set (0.00 sec)

The result of the query with SOME is obviously wrong. It should have returned empty set with warnings.

 Comments   
Comment by Alexander Barkov [ 2015-09-30 ]

Also repeatable in MySQL-5.7.8

Generated at Thu Feb 08 07:30:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.