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

from_days fails after null value

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.40, 10.0.14
    • 5.5.41
    • Temporal Types
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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