cast real as datetime is not compatible with tidb/mysql

[SeaRise]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;

drop table test.t;
create table test.t(a float, b double);
insert into test.t values (0, 0);
insert into test.t values (111.0, 111.0);
insert into test.t values (111.1, 111.1);
insert into test.t values (202102, 20210201);
insert into test.t values (202102.1, 20210201.1);

alter table test.t set tiflash replica 1;

// tiflash
set tidb_enforce_mpp=1; set tidb_isolation_read_engines='tiflash';
select a, cast(a as datetime), b, cast(b as datetime) from t;

// tidb
set tidb_enforce_mpp=0; set tidb_isolation_read_engines='tikv';
select a, cast(a as datetime), b, cast(b as datetime) from t;

// mysql
// execute in mysql
select a, cast(a as datetime), b, cast(b as datetime) from t;

2. What did you expect to see? (Required)

-- mysql/tidb/tiflash
+-------------+---------------------+-------------------------+---------------------+
| a           | cast(a as datetime) | b                       | cast(b as datetime) |
+-------------+---------------------+-------------------------+---------------------+
|           0 | NULL                |                       0 | NULL                |
|  3.40282e38 | NULL                |  1.7976931348623157e308 | NULL                |
| 1.17549e-38 | NULL                | 2.2250738585072014e-308 | NULL                |
|         111 | 2000-01-11 00:00:00 |                     111 | 2000-01-11 00:00:00 |
|       111.1 | 2000-01-11 00:00:00 |                   111.1 | 2000-01-11 00:00:00 |
|      202102 | NULL                |                20210201 | 2021-02-01 00:00:00 |
|      202102 | NULL                |              20210201.1 | 2021-02-01 00:00:00 |
+-------------+---------------------+-------------------------+---------------------+

3. What did you see instead (Required)

-- mysql
+-------------+---------------------+-------------------------+---------------------+
|           0 | NULL                |                       0 | NULL                |
|  3.40282e38 | NULL                |  1.7976931348623157e308 | NULL                |
| 1.17549e-38 | NULL                | 2.2250738585072014e-308 | NULL                |
|         111 | 2000-01-11 00:00:00 |                     111 | 2000-01-11 00:00:00 |
|       111.1 | 2000-01-11 00:00:00 |                   111.1 | 2000-01-11 00:00:00 |
|      202102 | NULL                |                20210201 | 2021-02-01 00:00:00 |
|      202102 | NULL                |              20210201.1 | 2021-02-01 00:00:00 |
+-------------+---------------------+-------------------------+---------------------+

-- tidb
+-------------+---------------------+-------------------------+---------------------+
| a           | cast(a as datetime) | b                       | cast(b as datetime) |
+-------------+---------------------+-------------------------+---------------------+
|           0 | 0000-00-00 00:00:00 |                       0 | 0000-00-00 00:00:00 |
|         111 | NULL                |                     111 | NULL                |
|       111.1 | NULL                |                   111.1 | NULL                |
|      202102 | NULL                |                20210201 | 2021-02-01 00:00:00 |
|    202102.1 | NULL                |              20210201.1 | 2021-02-01 01:00:00 |
+-------------+---------------------+-------------------------+---------------------+

-- tiflash
+------------+---------------------+-------------------------+---------------------+
| a          | cast(a as datetime) | b                       | cast(b as datetime) |
+------------+---------------------+-------------------------+---------------------+
|          0 | NULL                |                       0 | NULL                |
|        111 | NULL                |                     111 | NULL                |
|      111.1 | NULL                |                   111.1 | NULL                |
|     202102 | 2021-08-02 00:00:00 |                20210201 | 2021-02-01 00:00:00 |
|   202102.1 | 2021-08-02 09:37:05 |              20210201.1 | 2021-02-01 01:00:00 |
+------------+---------------------+-------------------------+---------------------+

4. What is your TiFlash version? (Required)

master

[ilovesoup]

related to #3779 pingcap/tidb#31156

[gengliqi]

In current master.
The result is:

-- mysql 8.0
+--------+---------------------+------------+---------------------+
| a      | cast(a as datetime) | b          | cast(b as datetime) |
+--------+---------------------+------------+---------------------+
|      0 | NULL                |          0 | NULL                |
|    111 | 2000-01-11 00:00:00 |        111 | 2000-01-11 00:00:00 |
|  111.1 | 2000-01-11 00:00:00 |      111.1 | 2000-01-11 00:00:00 |
| 202102 | NULL                |   20210201 | 2021-02-01 00:00:00 |
| 202102 | NULL                | 20210201.1 | 2021-02-01 00:00:00 |
+--------+---------------------+------------+---------------------+

-- tidb(tikv)
+----------+---------------------+------------+---------------------+
| a        | cast(a as datetime) | b          | cast(b as datetime) |
+----------+---------------------+------------+---------------------+
|        0 | 0000-00-00 00:00:00 |          0 | 0000-00-00 00:00:00 |
|      111 | 2000-01-11 00:00:00 |        111 | 2000-01-11 00:00:00 |
|    111.1 | 2000-01-11 00:00:00 |      111.1 | 2000-01-11 00:00:00 |
|   202102 | NULL                |   20210201 | 2021-02-01 00:00:00 |
| 202102.1 | NULL                | 20210201.1 | 2021-02-01 00:00:00 |
+----------+---------------------+------------+---------------------+

-- tidb(tiflash)
+----------+---------------------+------------+---------------------+
| a        | cast(a as datetime) | b          | cast(b as datetime) |
+----------+---------------------+------------+---------------------+
|        0 | 0000-00-00 00:00:00 |          0 | 0000-00-00 00:00:00 |
|      111 | 2000-01-11 00:00:00 |        111 | 2000-01-11 00:00:00 |
|    111.1 | 2000-01-11 00:00:00 |      111.1 | 2000-01-11 00:00:00 |
|   202102 | NULL                |   20210201 | 2021-02-01 00:00:00 |
| 202102.1 | NULL                | 20210201.1 | 2021-02-01 00:00:00 |
+----------+---------------------+------------+---------------------+

I think the only issue left is cast(0.0 as datetime) is NULL or 0000-00-00 00:00:00.
And it's related to NO_ZERO_DATE sql_mode, which had an old issue in TiDB repo pingcap/tidb#10485.
I think we can close this issue for now.