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

Warning caused by NO_ZERO_DATE is different for TIMESTAMP and other date-related columns

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2
    • Fix Version/s: 10.2
    • Component/s: Temporal Types
    • Labels:
      None

      Description

      MariaDB [test]> create table t1 (d date, dt datetime, t timestamp);
      Query OK, 0 rows affected (0.29 sec)
       
      MariaDB [test]> set sql_mode='NO_ZERO_DATE';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00");
      Query OK, 1 row affected, 3 warnings (0.07 sec)
       
      MariaDB [test]> show warnings;
      +---------+------+---------------------------------------------+
      | Level   | Code | Message                                     |
      +---------+------+---------------------------------------------+
      | Warning | 1264 | Out of range value for column 'd' at row 1  |
      | Warning | 1264 | Out of range value for column 'dt' at row 1 |
      | Warning | 1265 | Data truncated for column 't' at row 1      |
      +---------+------+---------------------------------------------+
      3 rows in set (0.00 sec)
      

      In MySQL 5.7 they are all the same:

      MySQL [test]> create table t1 (d date, dt datetime, t timestamp);
      Query OK, 0 rows affected (0.35 sec)
       
      MySQL [test]> set sql_mode='NO_ZERO_DATE';
      Query OK, 0 rows affected, 1 warning (0.00 sec)
       
      MySQL [test]> insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00");
      Query OK, 1 row affected, 3 warnings (0.07 sec)
       
      MySQL [test]> show warnings;
      +---------+------+---------------------------------------------+
      | Level   | Code | Message                                     |
      +---------+------+---------------------------------------------+
      | Warning | 1264 | Out of range value for column 'd' at row 1  |
      | Warning | 1264 | Out of range value for column 'dt' at row 1 |
      | Warning | 1264 | Out of range value for column 't' at row 1  |
      +---------+------+---------------------------------------------+
      3 rows in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: