unnecessary projection In the query

[hawkingrei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1 (c1 int primary key);
insert into t1 (c1) values (575932053), (-258025139);
analyze table t1;
SELECT DISTINCT
  cast(c1 as decimal) as c3,
  cast(c1 as signed) as c4
FROM t1;

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

tidb> explain select distinct cast(c1 as decimal), cast(c1 as signed) from t1; select distinct cast(c1 as decimal), cast(c1 as signed) from t1;
+------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------+
| id                     | estRows | task      | access object | operator info                                                                                           |
+------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------+
| HashAgg_6              | 2.00    | root      |               | group by:Column#10, Column#11, funcs:firstrow(Column#10)->Column#2, funcs:firstrow(Column#11)->Column#3 |
| └─Projection_12        | 2.00    | root      |               | cast(test.t1.c1, decimal(10,0) BINARY)->Column#10, cast(test.t1.c1, bigint(22) BINARY)->Column#11       |
|   └─TableReader_11     | 2.00    | root      |               | data:TableFullScan_10                                                                                   |
|     └─TableFullScan_10 | 2.00    | cop[tikv] | table:t1      | keep order:false                                                                                        |
+------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

+--------------------+-------------------+
| cast(c as decimal) | cast(c as signed) |
+--------------------+-------------------+
|         -258025139 |        -258025139 |
|          575932053 |         575932053 |
+--------------------+-------------------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

tidb> explain select distinct cast(c1 as decimal), cast(c1 as signed) from t1; select distinct cast(c1 as decimal), cast(c1 as signed) from t1;
+----------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                         | estRows | task      | access object | operator info                                                                                                                                                                                    |
+----------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_6                  | 1.60    | root      |               | group by:Column#13, Column#14, funcs:firstrow(Column#11)->Column#3, funcs:firstrow(Column#12)->Column#4                                                                                          |
| └─Projection_12            | 2.00    | root      |               | cast(test.t1.c1, decimal(10,0) BINARY)->Column#11, cast(test.t1.c1, bigint(22) BINARY)->Column#12, cast(test.t1.c1, decimal(10,0) BINARY)->Column#13, cast(test.t1.c1, bigint(22) BINARY)->Column#14 |
|   └─TableReader_11         | 2.00    | root      |               | data:TableFullScan_10                                                                                                                                                                            |
|     └─TableFullScan_10     | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                                                                                   |
+----------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

+--------------------+-------------------+
| cast(c as decimal) | cast(c as signed) |
+--------------------+-------------------+
|         -258025139 |        -258025139 |
|          575932053 |         575932053 |
+--------------------+-------------------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

[hawkingrei]

ref #53726