Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7221

from_days fails after null value

    XMLWordPrintable

    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

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            pgreco Pablo Greco
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: