Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.3.5, 10.3.7
-
None
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 |
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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
Okay, it was actually version 10.3.7. Sorry about the initial miscommunication. Test and let me know if you can replicate.
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
|
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: 0MariaDB [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: ''