Should not omit cast function when convert date/timestamp type to datetime in if/ifnull/case when function

[yuqi1129]

Bug Report!

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

run master

select if (1, date '2020-12-12', time '12:12:12');
select if (0, date '2020-12-12', time '12:12:12');

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

In MySQL, no matter the if conditions, the return type is determinate, no matter condition is true or not

3. What did you see instead (Required)

in TiDB, return type of if function is variable and thus cause the result is wrong.

4. What is your TiDB version? (Required)

master branch

[yuqi1129]

The problem lies here.

When cast date/timestamp to datetime, we deliberately omit the cast function. when execute the sql

select if (1, date '2020-12-12', time '12:12:12');

the optimizer will use foldConstant rule and convert it to select date '2020-12-12' and thus return type is date

Maybe we should not just abandon the cast function when convert date/timstamp to datetime, but if we simply omit the that logic, the plan of many SQL will change(plan will be add cast function ). so, what should we fix this problem?

[jebter]

TiDB root@127.0.0.1:test> select if (1, date '2020-12-12', time '12:12:12');
+--------------------------------------------+
| if (1, date '2020-12-12', time '12:12:12') |
+--------------------------------------------+
| 2020-12-12 00:00:00                        |
+--------------------------------------------+

1 row in set
Time: 0.005s
TiDB root@127.0.0.1:test> select if (0, date '2020-12-12', time '12:12:12');
+--------------------------------------------+
| if (0, date '2020-12-12', time '12:12:12') |
+--------------------------------------------+
| 2024-06-25 12:12:12                        |
+--------------------------------------------+

1 row in set
Time: 0.003s
TiDB root@127.0.0.1:test> select tidb_version() \G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v8.1.0
Edition: Community
Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23
Git Branch: HEAD
UTC Build Time: 2024-05-21 03:52:40
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

Please try the latest version. I will close it,if there are any updates, you can reopen it.