[MDEV-7221] from_days fails after null value - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.40, 10.0.14
Fix Version/s: 5.5.41
Component/s: Temporal Types
Labels:
None

Description

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.

Attachments

Activity

Ascending order - Click to sort in descending order

Pablo Greco created issue - 2014-11-26 19:17

Elena Stepanova made changes - 2014-11-26 19:20

Field	Original Value	New Value
Due Date		2014-12-05

Elena Stepanova made changes - 2014-11-27 13:35

Description

{code:sql}
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');
{code}

executing
{code:sql}
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;
{code}
should result in
{noformat}
+----+------------+------------+-------------------------------------+---------------------+
| 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 |
+----+------------+------------+-------------------------------------+---------------------+
{noformat}
But instead it gives the following result:
{noformat}
+----+------------+------------+-------------------------------------+---------------------+
| 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 |
+----+------------+------------+-------------------------------------+---------------------+
{noformat}
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.

Elena Stepanova made changes - 2014-11-27 13:37

Due Date

2014-12-05

Elena Stepanova made changes - 2014-11-27 13:37

Fix Version/s		10.0 [ 16000 ]
Fix Version/s		5.5 [ 15800 ]
Assignee		Alexander Barkov [ bar ]

Pablo Greco made changes - 2014-11-27 14:03

Comment

[ Sorry for not posting correctly the first time, my first bug report here :( ]

Pablo Greco made changes - 2014-11-27 14:09

Comment

[ 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. ]

Alexander Barkov made changes - 2014-12-16 13:35

Component/s		OTHER [ 10125 ]
Fix Version/s		5.5.41 [ 17600 ]
Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.0 [ 16000 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2015-01-06 16:34

Component/s

Temporal Types [ 11000 ]

Sergei Golubchik made changes - 2015-01-06 16:34

Component/s

OTHER [ 10125 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 58741 ]

MariaDB v3 [ 65328 ]

Sergei Golubchik made changes - 2021-12-06 21:40

Workflow

MariaDB v3 [ 65328 ]

MariaDB v4 [ 148555 ]

People

Assignee:: Alexander Barkov

Reporter:: Pablo Greco

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2014-11-26 19:17

Updated:: 2015-01-06 16:34

Resolved:: 2014-12-16 13:35

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration