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

Addition of a time interval reduces the resulting value

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 5.5.25
    • Fix Version/s: 5.5.27, 5.3.9
    • Component/s: None
    • Labels:
      None

      Description

      I presume it affects 5.3 too, but I didn't check.

      The issue comes from the fact that date and datetime values like '2012-00-00' are vaguely supported (on one hand, MySQL docs say that ranges for month and day are 1..12 and 1..31, respectively; on the other hand, it only mentions that zero values for month and day are invalid for TIMESTAMP, but doesn't say anything for DATE/DATETIME).

      So, this works, no errors or warnings:

      create table t1 (d date);
      # Query OK, 0 rows affected (0.08 sec)
       
      insert into t1 values ('2012-00-00');
      # Query OK, 1 row affected (0.04 sec)
       
      select * from t1;
      # +------------+
      # | d          |
      # +------------+
      # | 2012-00-00 |
      # +------------+

      Comparison works too:

      select * from t1 where d > 20120000;
      # Empty set (0.00 sec)
       
      select * from t1 where d >= 20120000;
      # +------------+
      # | d          |
      # +------------+
      # | 2012-00-00 |
      # +------------+
      # 1 row in set (0.00 sec)

      Now, we add 1 day to the value. Again, no warnings or errors:

      update t1 set d = adddate(d, interval 1 day);
      # Query OK, 1 row affected (0.04 sec)
      # Rows matched: 1  Changed: 1  Warnings: 0

      But the result is not what someone would expect:

       
      select * from t1 where d >= 20120000;
      # Empty set (0.00 sec)
       
      select * from t1;
      # +------------+
      # | d          |
      # +------------+
      # | 2011-12-01 |
      # +------------+
      # 1 row in set (0.00 sec)

      In MySQL (5.6, did not try earlier) it is also not perfect: adddate turns the value to NULL, again without a warning. But it seems somewhat less strange than reducing it.

        Attachments

          Activity

            People

            • Assignee:
              serg Sergei Golubchik
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: