double to decimal type conversion error

[jebter]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t (
COL1 set('a','b','ccc') DEFAULT NULL,
COL2 decimal(20,0) DEFAULT NULL,
COL3 timestamp NULL DEFAULT NULL,
KEY U_M_COL4 (COL1,COL2),
KEY U_M_COL5 (COL3,COL2)
)

select * from t where col2 <=> -32299374332806449915. and col1 is not null and col3 = "2030-10-30 6:39:35";

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

success

3. What did you see instead (Required)

(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 57 near "and col1 is not null and col3 = "2030-10-30 6:39:35"" ')

4. What is your TiDB version? (Required)

tidb_version() | Release Version: v7.4.0
Edition: Community
Git Commit Hash: 04ac200
Git Branch: heads/refs/tags/v7.4.0
UTC Build Time: 2023-10-09 09:17:37
GoVersion: go1.21.1
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[jebter]

in 7.1.1 & 7.3.0
MySQL ....:test> CREATE TABLE t (
-> COL1 set('a','b','ccc') DEFAULT NULL,
-> COL2 decimal(20,0) DEFAULT NULL,
-> COL3 timestamp NULL DEFAULT NULL,
-> KEY U_M_COL4 (COL1,COL2),
-> KEY U_M_COL5 (COL3,COL2)
-> )
Query OK, 0 rows affected
Time: 0.231s
MySQL ...:test> select * from t where col2 <=> -32299374332806449915. and col1 is not null and col3 = "2030-10-30 6:39:35";
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
0 rows in set
Time: 0.015s

[jebter]

duplicate #46778