[MDEV-9521] Least function returns 0000-00-00 for null date columns instead of null Created: 2016-02-04  Updated: 2016-06-16  Resolved: 2016-04-18

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 5.5.47, 5.5
Fix Version/s: 5.5.49

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

mysql Ver 15.1 Distrib 5.5.47-MariaDB, for Linux (x86_64) using readline 5.1

Linux version 2.6.32-573.12.1.el6.x86_64 (mockbuild@x86-031.build.eng.bos.redhat.com) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-16) (GCC) ) #1 SMP Mon Nov 23 12:55:32 EST 2015
mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1

Issue Links:
Duplicate
is duplicated by MDEV-9374 having '2015-01-01 01:00:00.000001' >... Closed
PartOf
includes MDEV-9972 Least function retuns date in date ti... Closed

 Description   

sql_mode is set to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The problem can be reproduce with the following commands:

drop table if exists ml_intervaldates;
 
create table ml_intervaldates (
 
id bigint not null,
 
date_debut date not null,
 
date_fin date default null);
 
drop table if exists ml_intervaldates_specifiques;
 
create table ml_intervaldates_specifiques (
 
id bigint not null,
 
date_debut date not null,
 
date_fin date default null);
 

insert into ml_intervaldates values ( 1,'2016-01-01','2016-01-31');
 
insert into ml_intervaldates values ( 2,'2016-02-01',null);
 
insert into ml_intervaldates values ( 3,'2016-03-01','2016-03-31');
 
insert into ml_intervaldates values ( 4,'2016-04-01',null);
 
 
 
insert into ml_intervaldates_specifiques values ( 1,'2016-01-01','2016-01-31');
 
insert into ml_intervaldates_specifiques values ( 2,'2016-02-01','2016-01-28');
 
insert into ml_intervaldates_specifiques values ( 3,'2016-03-01',null);
 
insert into ml_intervaldates_specifiques values ( 4,'2016-04-01',null);
 

select t1.id, 
GREATEST(`t2`.`date_debut`, `t1`.`date_debut`) AS `date_debut`,
 
        LEAST(IFNULL(`t2`.`date_fin`, IFNULL(`t1`.`date_fin`, NULL)),
 
			  IFNULL(`t1`.`date_fin`, IFNULL(`t2`.`date_fin`, NULL))) AS `date_fin`
 
from ml_intervaldates t1 left join ml_intervaldates_specifiques t2 on (t1.id=t2.id);

On 5.5.44 result is:

# id, date_debut, date_fin
 
1, 2016-01-01, 2016-01-31
 
2, 2016-02-01, 2016-01-28
 
3, 2016-03-01, 2016-03-31
 
4, 2016-04-01,

The blank is a null.

On 5.5.47, result is:

# id, date_debut, date_fin
 
1, 2016-01-01, 2016-01-31 00:00:00
 
2, 2016-02-01, 2016-01-28 00:00:00
 
3, 2016-03-01, 2016-03-31 00:00:00
 
4, 2016-04-01, 0000-00-00 00:00:00

Notice also that date_fin column appears as a date_time column now in 5.5.47.

 Comments   
Comment by Elena Stepanova [ 2016-02-06 ]

Thanks for the report and test case.

I'm actually getting the same result on MariaDB 5.5.44, maybe you tried MySQL 5.5.44?
From what I see, the problem was introduced in 5.3 and merged to 5.5.33, more exactly by this commit:

commit c40de1df363da0bc04118b4957c94fd52afe20b0
 
Author: Alexander Barkov <bar@mariadb.org>
 
Date:   Thu Aug 22 15:20:27 2013 +0400
 
 
 
     MDEV-4804 Date comparing false result
 
 

However, it seems to be a 5.5-only problem, 10.0 and higher return NULL. Is it the same for you? If so, probably it makes no sense to fix it in 5.5 separately. I'll assign it to bar to check if it's really solved in 10.0, and if so, to decide whether anything needs to be done in 5.5.

Comment by Marc Langevin [ 2016-02-08 ]

Hi Elena,

The test was done on MariaDb 5.5.44.
[mysql@bdsandbox-master-dev01 outils]$ mysql -V
mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1

We have about 30 instances running on 5.5.x. From development to production instances.

Regards,
Marc

Comment by Marc Langevin [ 2016-02-08 ]

And we do not have any 10.x instance installed.

Marc

Comment by Alexander Barkov [ 2016-02-09 ]

A smaller scrip demonstrating the same problem:

SELECT
 
  LEAST(COALESCE(DATE(NULL), DATE(NULL)), COALESCE(DATE(NULL), DATE(NULL))) AS d0,
 
  LEAST(IFNULL(DATE(NULL), DATE(NULL)), IFNULL(DATE(NULL), DATE(NULL))) AS d1;

It returns:

+---------------------+---------------------+
 
| d0                  | d1                  |
 
+---------------------+---------------------+
 
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
 
+---------------------+---------------------+

Comment by Marc Langevin [ 2016-02-11 ]

For your information, although you probably already know, the problem is still there in 5.5.48.

Can we expect the fix to be included in 5.5.49?

Regards,
Marc

Comment by Sergei Golubchik [ 2016-02-11 ]

Yes, absolutely.

Unfortunately, the fix didn't make it into 5.5 before the 5.5.48 deadline. But the fix is ready, so it cannot miss 5.5.49

Comment by Marc Langevin [ 2016-02-11 ]

Thank you for your reply.

Is the 5.5.49 deadline known?

Comment by Sergei Golubchik [ 2016-02-12 ]

Strictly speaking — no. We can only release MariaDB-5.5.49 after MySQL-5.5.49, and Oracle doesn't make promises about specific release dates. But practically, MySQL (and we) were making 5.5 releases every second month. So, the tentative 5.5.49 release date is two months from now, 2016-04-14.

Comment by Marc Langevin [ 2016-02-12 ]

Thank you Sergei.

Comment by Marc [ 2016-04-18 ]

Hi,
The fix for this bug is confirmed for the upcoming 5.5.49 release?

Regards,

Marc

Comment by Alexander Barkov [ 2016-04-18 ]

Marc,
The fix is now pushed to the git tree and should be a part of the 5.5.49 release.

Comment by Marc [ 2016-04-18 ]

Thank you!

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