[MDEV-17563] Different results using table or view when comparing values of time type Created: 2018-10-29  Updated: 2018-11-08  Resolved: 2018-11-08

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.4
Fix Version/s: 10.4.0

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-4667 DATE('string') incompability between ... Closed
relates to MDEV-17342 Mariadb is giving warning for this qu... Open
relates to MDEV-17478 Wrong result for TIME('+100:20:30') Closed
relates to MDEV-17625 Different warnings when comparing a g... Closed
relates to MDEV-17634 Regression: TIME(0)=TIME('z') returns... Closed
relates to MDEV-17632 Inconsistent behavior of conversion t... Open

 Description   

10.4, reproducible after commit f6a20205148853f4cd352a21de3b77f2372ad50d 

CREATE TABLE t1 (pk int, x1 time, x2 varchar(1));
 
INSERT INTO t1 VALUES (17,'09:16:37','k'),(70,'19:44:22','k');
 
CREATE VIEW v1 AS SELECT * FROM t1;
 
 
 
SELECT pk FROM t1 WHERE x1 >x2;
 
SELECT pk FROM v1 WHERE x1 >x2;


MariaDB [test2]> SELECT pk FROM t1 WHERE x1 >x2;
 
+------+
 
| pk   |
 
+------+
 
|   17 |
 
|   70 |
 
+------+
 
2 rows in set, 2 warnings (0.002 sec)
 
Warning (Code 1292): Truncated incorrect time value: 'k'
 
Warning (Code 1292): Truncated incorrect time value: 'k'
 
 
 
MariaDB [test2]> SELECT pk FROM v1 WHERE x1 >x2;
 
Empty set, 2 warnings (0.004 sec)
 
 
 
Warning (Code 1292): Truncated incorrect time value: 'k'
 
Warning (Code 1292): Truncated incorrect time value: 'k'



 Comments   
Comment by Alexander Barkov [ 2018-10-31 ]

The problem is repeatable in all versions starting from 5.5, but for an empty string in x2 (instead of garbage like 'k'):

DROP TABLE IF EXISTS t1;
 
DROP VIEW IF EXISTS v1;
 
CREATE TABLE t1 (pk int, x1 time, x2 varchar(1));
 
INSERT INTO t1 VALUES (17,'09:16:37','');
 
CREATE VIEW v1 AS SELECT * FROM t1;
 
SELECT pk FROM t1 WHERE x1 >x2;
 
SELECT pk FROM v1 WHERE x1 >x2;

The first SELECT returns:

+------+
 
| pk   |
 
+------+
 
|   17 |
 
+------+
 
1 row in set, 1 warning (0.00 sec)

The second SELECT returns:

Empty set, 1 warning (0.00 sec)

The problem is also repeatable for DATETIME in all versions starting with 5.5, for both empty string and garbage:

DROP TABLE IF EXISTS t1;
 
DROP VIEW IF EXISTS v1;
 
CREATE TABLE t1 (pk int, x1 datetime, x2 varchar(1));
 
INSERT INTO t1 VALUES (17,'2001-01-01 09:16:37','');
 
INSERT INTO t1 VALUES (18,'2001-01-01 09:16:37','k');
 
CREATE VIEW v1 AS SELECT * FROM t1;
 
SELECT pk FROM t1 WHERE x1 >x2;
 
SELECT pk FROM v1 WHERE x1 >x2;

The first SELECT returns two rows:

+------+
 
| pk   |
 
+------+
 
|   17 |
 
|   18 |
 
+------+
 
2 rows in set, 2 warnings (0.00 sec)

The second SELECT returns empty set.

Generated at Thu Feb 08 08:37:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.