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

CAST('2004-04-31' AS DATE) and CAST('2004-04-31' AS DATETIME) work differently

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 5.3.12, 5.5.34, 10.0.6
        • Fix Version/s: None
        • Component/s: None
        • Labels:
          None

          Description

          I insert an invalid date, e.g. '2004-04-31' using SQL_MODE=ALLOW_INVALID_DATES into a DATE column,
          then switch to SQL_MODE=DEFAULT, then cast the value
          to DATE and to DATETIME. CAST works differently for the two types.

          mysql> select version();
+-----------------------------------+
| version()                         |
+-----------------------------------+
| 5.3.13-MariaDB-valgrind-max-debug |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode=allow_invalid_dates;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a date);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values ('2004-04-31');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------------+
| a          |
+------------+
| 2004-04-31 |
+------------+
1 row in set (0.00 sec)

mysql> set sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------------+
| a          |
+------------+
| 2004-04-31 |
+------------+
1 row in set (0.00 sec)

mysql> select cast(a as date) from t1;
+-----------------+
| cast(a as date) |
+-----------------+
| NULL            |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast(a as datetime) from t1;
+---------------------+
| cast(a as datetime) |
+---------------------+
| 2004-04-31 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

          Notice, CAST(a AS DATE) correctly returns NULL,
          while CAST(a AS DATETIME) retuns a datetime value
          with an invalid date ignoring SQL_MODE.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

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

                    Dates

                    • Created:
                      Updated: