Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.47, 5.5(EOL)
-
None
-
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
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
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.