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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.47, 5.5
    • Fix Version/s: 5.5.49
    • Labels:
      None
    • Environment:

      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

              • Assignee:
                bar Alexander Barkov
                Reporter:
                Marc Langevin Marc Langevin
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: