Incorrect results when use default current_date value in create table

[douzhihao111]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

##date:2024-06-03

create table t(a date default current_date);
insert into t values();
insert into values(date('2024-06-03 00:00:00.000000'));

select count(*) from t where a='2024-06-03';
select * from t where a like '2024-06-03';

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

select count(*) from t where a='2024-06-03';
result:2
select * from t where a like '2024-06-03';
result:2

3. What did you see instead (Required)

select count(*) from t where a='2024-06-03';
result:1
select * from t where a like '2024-06-03';
result:2

4. What is your TiDB version? (Required)

v7.1.1

[jebter]

`
TiDB [email protected]:test> create table t(a date default current_date);
Query OK, 0 rows affected
Time: 0.081s
TiDB [email protected]:test> insert into t values();
Query OK, 1 row affected
Time: 0.004s

TiDB [email protected]:test> select * from t;
+------------+
| a |
+------------+
| 2024-06-04 |
+------------+

1 row in set
Time: 0.004s

TiDB [email protected]:test> select * from t where a='2024-06-04';
+---+
| a |
+---+
+---+

0 rows in set
Time: 0.006s

TiDB [email protected]:test> select CURRENT_DATE = "2024-06-04", CURRENT_DATE;
+-----------------------------+--------------+
| CURRENT_DATE = "2024-06-04" | CURRENT_DATE |
+-----------------------------+--------------+
| 1 | 2024-06-04 |
+-----------------------------+--------------+

1 row in set
Time: 0.003s

`

[zanmato1984]

When filling default value using current_date, it actually calculates a datetime value with time portion:

mysql> select cast(a as datetime) from t;
+---------------------+
| cast(a as datetime) |
+---------------------+
| 2024-06-04 09:00:46 |
+---------------------+
1 row in set (0.00 sec)

When comparing a with 2024-06-04, the time portion is also compared (and does not match):

mysql> explain select * from t where a = '2024-06-04';
+-------------------------+---------+-----------+---------------+------------------------------------------+
| id                      | estRows | task      | access object | operator info                            |
+-------------------------+---------+-----------+---------------+------------------------------------------+
| TableReader_7           | 0.00    | root      |               | data:Selection_6                         |
| └─Selection_6           | 0.00    | cop[tikv] |               | eq(test.t.a, 2024-06-04 00:00:00.000000) |
|   └─TableFullScan_5     | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo           |
+-------------------------+---------+-----------+---------------+------------------------------------------+

Maybe default value should do a casting or trimming to the time portion. Redirect to sql-infra.

[Defined2014]

Dup with #53746, close this issue.