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

Least function returns 0000-00-00 for null date columns instead of null

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.47, 5.5(EOL)
    • 5.5.49
    • None

    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.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              Marc Langevin Marc Langevin
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.