Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0.8
-
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 |
|
+--------------+-----------------------+
|