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

Difference between DATE(int_expression) and DATE(int_field)

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 10.0.8
        • Fix Version/s: 10.0
        • Component/s: None
        • Labels:
          None

          Description

          This SQL script returns NULL with a warning:

          mysql> drop table if exists t1; create table t1 (a int); insert into t1 values (0); select date(a)from t1;
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.15 sec)

Query OK, 1 row affected (0.00 sec)

+---------+
| date(a) |
+---------+
| NULL    |
+---------+
1 row in set, 1 warning (1.37 sec)

mysql> show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1292 | Incorrect datetime value: '0' |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

          At the same time, this query returns '0000-00-00' without warnings.

          mysql> select date(0);
+------------+
| date(0)    |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

          One of the either should be fixed for equal results.

          The same problems is observed when converting 0 to DATETIME:

          drop table if exists t1;
create table t1 (a int);
insert into t1 values (0);
select timestamp(a),timestamp(0) from t1;
+--------------+---------------------+
| timestamp(a) | timestamp(0)        |
+--------------+---------------------+
| NULL         | 0000-00-00 00:00:00 |
+--------------+---------------------+
1 row in set, 1 warning (0.00 sec)

          The same problem is observed with the DECIMAL data type:

          drop table if exists t1;
create table t1 (a decimal);
insert into t1 values (0);
select timestamp(a),timestamp(0.0) from t1;
+--------------+-----------------------+
| timestamp(a) | timestamp(0.0)        |
+--------------+-----------------------+
| NULL         | 0000-00-00 00:00:00.0 |
+--------------+-----------------------+

            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: