[MDEV-16489] when lead() returns null on a datetime field, the result is treated as the literal string '[NULL]' Created: 2018-06-14  Updated: 2018-06-20  Resolved: 2018-06-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.3.5, 10.3.7
Fix Version/s: 10.3.8, 10.4.0

Type: Bug Priority: Minor
Reporter: Corbin Bohne Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-15293 CAST(AS TIME) returns bad results for... Closed

 Description    

/* when lead() returns null on a datetime field, the result is treated as the literal string '[NULL]' */
 
 
 
	create temporary table d1(d datetime);
 
	insert into d1 values ('2018-01-01 00:00:00'),('2018-02-01 00:00:00');
 
	
	/* null record is not returned */
 
	select *
 
	from 
		(
 
			select *, lead(d) over (order by d) as x from d1
 
		) a
 
	where x is null
 
	
	/* null record is returned when treated as a string */
 
	select *
 
	from 
		(
 
			select *, lead(d) over (order by d) as x from d1
 
		) a
 
	where x = '[NULL]'
 
	
	drop table d1



 Comments   
Comment by Alice Sherepa [ 2018-06-14 ]

Here are results, that I get, seems to work correct
Please add your .cnf file(s) 

MariaDB [test]> select version();
 
+----------------+
 
| version()      |
 
+----------------+
 
| 10.3.0-MariaDB |
 
+----------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> CREATE TEMPORARY TABLE d1(d datetime);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> INSERT INTO d1 VALUES ('2018-01-01 00:00:00'),('2018-02-01 00:00:00');
 
Query OK, 2 rows affected (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT * FROM 
    -> (SELECT *, lead(d) over (ORDER BY d) AS x FROM d1) a
 
    -> WHERE x IS NULL;
 
+---------------------+------+
 
| d                   | x    |
 
+---------------------+------+
 
| 2018-02-01 00:00:00 | NULL |
 
+---------------------+------+
 
1 row in set, 2 warnings (0.00 sec)
 
 
 
Warning (Code 1292): Incorrect datetime value: ''
 
Warning (Code 1292): Incorrect datetime value: ''
 
 
 
MariaDB [test]> SELECT * FROM 
    -> (SELECT *, lead(d) over (ORDER BY d) AS x FROM d1) a
 
    -> WHERE x = 'NULL';
 
Empty set, 3 warnings (0.00 sec)
 
 
 
Warning (Code 1292): Incorrect datetime value: 'NULL'
 
Warning (Code 1292): Incorrect datetime value: ''
 
Warning (Code 1292): Incorrect datetime value: ''
 
 
 
MariaDB [test]> SELECT * FROM 
    -> (SELECT *, lead(d) over (ORDER BY d) AS x FROM d1) a
 
    -> WHERE x = '[NULL]';
 
Empty set, 3 warnings (0.00 sec)
 
 
 
Warning (Code 1292): Incorrect datetime value: '[NULL]'
 
Warning (Code 1292): Incorrect datetime value: ''
 
Warning (Code 1292): Incorrect datetime value: ''

Comment by Corbin Bohne [ 2018-06-19 ]

This was on a temporary install of MariaDB on AWS EC2. It's since been uninstalled and I can't recover the .cnf files. Let me talk to our DevOps to confirm the MariaDB version that we we had installed really quick and get back to you. Perhaps it was 10.3.x

Comment by Corbin Bohne [ 2018-06-19 ]

Okay, it was actually version 10.3.7. Sorry about the initial miscommunication. Test and let me know if you can replicate.

Comment by Alice Sherepa [ 2018-06-20 ]

corbin_mdb, thanks, reproducible on MariaDB 10.3.5 and higher
The behavior has changed after commit 95d075a0e5

CREATE TEMPORARY TABLE d1(d datetime);
 
INSERT INTO d1 VALUES ('2018-01-01 00:00:00'),('2018-02-01 00:00:00');
 
SELECT *, lead(d) over (ORDER BY d) AS x FROM d1;
 
d	x
 
2018-01-01 00:00:00	2018-02-01 00:00:00
 
2018-02-01 00:00:00	2018-02-01 00:00:00

before:

CREATE TEMPORARY TABLE d1(d datetime);
 
INSERT INTO d1 VALUES ('2018-01-01 00:00:00'),('2018-02-01 00:00:00');
 
SELECT *, lead(d) over (ORDER BY d) AS x FROM d1;
 
d	x
 
2018-01-01 00:00:00	2018-02-01 00:00:00
 
2018-02-01 00:00:00	NULL

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