|
Observing inconsistent behavior when casting invalid datetime values (when zero dates are allowed).
For example:
set sql_mode = '';
|
create table t1(vc varchar(10), d date);
|
insert into t1 values('abc', 'abc');
|
insert into t1 values('0000-00-00', '0000-00-00');
|
insert into t1 values('123', cast('123' as date));
|
insert into t1 values('456', '456');
|
insert into t1 values(null, null);
|
select * from t1;
|
+------------+------------+
|
| vc | d |
|
+------------+------------+
|
| abc | 0000-00-00 |
|
| 0000-00-00 | 0000-00-00 |
|
| 123 | NULL | --> so result of the explicit cast was NULL
|
| 456 | 0000-00-00 | --> but result of implicit/assignment cast was zero date
|
| NULL | NULL |
|
+------------+------------+
|
The result of the explicit cast being NULL makes sense, e.g.
select cast('abc' as date);
|
+---------------------+
|
| cast('abc' as date) |
|
+---------------------+
|
| NULL |
|
+---------------------+
|
What follows is confusing/inconsistent:
select d, vc, cast(vc as date) from t1 where d = cast(vc as date);
|
+------------+------------+------------------+
|
| d | vc | cast(vc as date) |
|
+------------+------------+------------------+
|
| 0000-00-00 | abc | NULL |
|
| 0000-00-00 | 0000-00-00 | 0000-00-00 |
|
| 0000-00-00 | 456 | NULL |
|
+------------+------------+------------------+
|
It looks as if the explicit cast result is zero when under predicate context and is NULL when under the projection context. Except:
select d, vc, cast(vc as date) from t1 where d = cast(vc as date) and cast(vc as date) is null;
|
+------------+------+------------------+
|
| d | vc | cast(vc as date) |
|
+------------+------+------------------+
|
| 0000-00-00 | abc | NULL |
|
| 0000-00-00 | 456 | NULL |
|
+------------+------+------------------+
|
So, the "d = cast(vc as date)" term acts as if result of the cast was '0000-00-00' but the "cast(vc as date) is null" term acts as if result of the cast was NULL.
Could someone please shed some light on this?
Most important is what is the intended semantics of converting invalid values to datetime (date, time, datetime, timestamp) when zero dates are allowed? Is the result a NULL value or a zero?
|
behavior where the same CAST in comparison returns 0000-00-00 but returns NULL when returning data or processing IS NULL predicate.