[MDEV-20431] GREATEST(int_col,date_col) returns wrong results in a view Created: 2019-08-27  Updated: 2019-08-27  Resolved: 2019-08-27

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.1, 10.2, 10.3
Fix Version/s: 10.2.27, 10.1.42, 10.3.18

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

Issue Links:
Relates
relates to MDEV-19699 Server crashes in Item_null_result::f... Closed

 Description    

DROP TABLE IF EXISTS t1;
 
DROP VIEW IF EXISTS v1;
 
CREATE TABLE t1 (pk INT NOT NULL, d DATE NOT NULL);
 
CREATE VIEW v1 AS SELECT * FROM t1;
 
INSERT INTO t1 VALUES (1,'2018-06-22'),(2,'2018-07-11');


SELECT GREATEST(pk, d) FROM t1;


+-----------------+
 
| GREATEST(pk, d) |
 
+-----------------+
 
| 2018-06-22      |
 
| 2018-07-11      |
 
+-----------------+


SELECT GREATEST(pk, d) FROM v1;


+-----------------+
 
| GREATEST(pk, d) |
 
+-----------------+
 
| NULL            |
 
| NULL            |
 
+-----------------+

Looks wrong. Expected to get the same results with the table and with the view.

 Comments   
Comment by Alexander Barkov [ 2019-08-27 ]

Also repeatable with DATETIME:

DROP TABLE IF EXISTS t1;
 
DROP VIEW IF EXISTS v1;
 
CREATE TABLE t1 (pk INT NOT NULL, d DATETIME NOT NULL);
 
CREATE VIEW v1 AS SELECT * FROM t1;
 
INSERT INTO t1 VALUES (1,'2018-06-22 00:00:00'),(2,'2018-07-11 00:00:00');
 
SELECT GREATEST(pk, d) FROM t1;
 
SELECT GREATEST(pk, d) FROM v1;

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