|
CREATE TABLE `table_a` (
|
`ID` INT(11) NOT NULL,
|
`DATE1` DATE NULL DEFAULT NULL,
|
PRIMARY KEY (`ID`)
|
)
|
COLLATE='latin1_swedish_ci'
|
ENGINE=InnoDB
|
;
|
INSERT INTO `table_a` VALUES (12, '2011-05-12');
|
INSERT INTO `table_a` VALUES (13, NULL);
|
INSERT INTO `table_a` VALUES (14, '2009-10-23');
|
INSERT INTO `table_a` VALUES (15, '2014-10-30');
|
INSERT INTO `table_a` VALUES (16, NULL);
|
INSERT INTO `table_a` VALUES (17, NULL);
|
INSERT INTO `table_a` VALUES (18, '2010-10-13');
|
executing
select a.id,a.date1,from_days(to_days(a.date1)-10) as date2, date_add(a.date1, interval -10 day),to_days(a.date1)-10
|
from table_a a
|
order by a.id;
|
should result in
+----+------------+------------+-------------------------------------+---------------------+
|
| id | date1 | date2 | date_add(a.date1, interval -10 day) | to_days(a.date1)-10 |
|
+----+------------+------------+-------------------------------------+---------------------+
|
| 12 | 2011-05-12 | 2011-05-02 | 2011-05-02 | 734624 |
|
| 13 | NULL | NULL | NULL | NULL |
|
| 14 | 2009-10-23 | 2009-10-13 | 2009-10-13 | 734058 |
|
| 15 | 2014-10-30 | 2014-10-20 | 2014-10-20 | 735891 |
|
| 16 | NULL | NULL | NULL | NULL |
|
| 17 | NULL | NULL | NULL | NULL |
|
| 18 | 2010-10-13 | 2010-10-03 | 2010-10-03 | 734413 |
|
+----+------------+------------+-------------------------------------+---------------------+
|
But instead it gives the following result:
+----+------------+------------+-------------------------------------+---------------------+
|
| id | date1 | date2 | date_add(a.date1, interval -10 day) | to_days(a.date1)-10 |
|
+----+------------+------------+-------------------------------------+---------------------+
|
| 12 | 2011-05-12 | 2011-05-02 | 2011-05-02 | 734624 |
|
| 13 | NULL | NULL | NULL | NULL |
|
| 14 | 2009-10-23 | NULL | 2009-10-13 | 734058 |
|
| 15 | 2014-10-30 | NULL | 2014-10-20 | 735891 |
|
| 16 | NULL | NULL | NULL | NULL |
|
| 17 | NULL | NULL | NULL | NULL |
|
| 18 | 2010-10-13 | NULL | 2010-10-03 | 734413 |
|
+----+------------+------------+-------------------------------------+---------------------+
|
Columns 2 and 3 should give the same result on all rows, but instead, after the first null value, column2 is always null.
Mysql 5.5.40 returns the correct set, but Mariadb 10.0.14 and Mariadb 5.5.40 give the wrong results.
|