Incorrect datatype transformation when compare decimal and float

[Syang111]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t1(c0 FLOAT,c1 FLOAT);
INSERT INTO t1 VALUES (0, 1.1);
CREATE VIEW v0(c1, c2) AS SELECT t1.c0, CAST(t1.c1 AS DECIMAL) FROM t1;

SELECT v0.c2 FROM v0 WHERE (CASE v0.c2 WHEN v0.c1 THEN 1 ELSE 1 END );

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

mysql> SELECT v0.c2 FROM v0;
+------+
| c2   |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

Since v0.c2 is 1, so the following sql should return 1.

SELECT v0.c2 FROM v0 WHERE (CASE v0.c2 WHEN v0.c1 THEN 1 ELSE 1 END );

3. What did you see instead (Required)

However, it returns 1.100000023841858000000000000000.

mysql> SELECT v0.c2 FROM v0 WHERE (CASE v0.c2 WHEN v0.c1 THEN 1 ELSE 1 END );
+----------------------------------+
| c2                               |
+----------------------------------+
| 1.100000023841858000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

If you execute sql SELECT v0.c2 FROM v0;,it works well

mysql> SELECT v0.c2 FROM v0;
+------+
| c2   |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

4. What is your TiDB version? (Required)

TiDB-v7.6.0

[aytrack]

[16:34:51]TiDB root:test> explain SELECT v0.c2 FROM v0 WHERE (CASE v0.c2 WHEN v0.c1 THEN 1 ELSE 1 END );
+-----------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------+
| id                    | estRows  | task      | access object | operator info                                                                            |
+-----------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------+
| Projection_7          | 8000.00  | root      |               | cast(test.t1.c1, decimal(48,30) BINARY)->Column#4                                        |
| └─TableReader_10      | 8000.00  | root      |               | data:Selection_9                                                                         |
|   └─Selection_9       | 8000.00  | cop[tikv] |               | case(eq(cast(cast(test.t1.c1, decimal(48,30) BINARY), double BINARY), test.t1.c0), 1, 1) |
|     └─TableFullScan_8 | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                           |
+-----------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------+

[windtalker]

It is the same issue as #51096