[MDEV-6884] greatest and coalesce on null date does not produce null Created: 2014-10-17  Updated: 2022-11-08  Resolved: 2022-11-08

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.5.40, 10.0.14
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Marcel Schneider Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: None


 Description   

I create a table and insert one row like this:

create table datetest (nr decimal(1), date1 date);
insert into datetest values (1,null);

The bug can be reproduced with this statement:

least(coalesce(date1), coalesce(date1)) 

this should return null, but it doesnt.
E.g.

select * from datetest where least(coalesce(date1), coalesce(date1)) is not null; 

==> Returns one row

It is also possible to insert this "corrupt null" into a table:

insert into datetest select 2, least(coalesce(date1), coalesce(date1))  from datetest;

if you select from this table with a select * from datetest where date1 is not null, you will get the row with nr=2

Additional info:
I get the same error using "greatest" instead of "least".
I get the same error if I use more than one parameter for coalesce.
I do not get this error if I use another datataype, like varchar instead of date.
I do not get this error on another database like oracle 11g.



 Comments   
Comment by Elena Stepanova [ 2014-10-17 ]

Thanks for the report.

Comment by Sergei Golubchik [ 2022-11-08 ]

fixed before 10.3.37

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