Can't recognize numeric literals when set 'ANSI_QUOTES' sql_mode #24429
Closed
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE "test" (
"CD_ID" bigint(20) NOT NULL,
"INTEGER_IDX" int(11) NOT NULL,
PRIMARY KEY ("CD_ID")
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
INSERT INTO test VALUES (54603, 1),(54604, 2);
SET SESSION sql_mode='ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SELECT COUNT(*) FROM "test" THIS WHERE THIS."CD_ID"=54603 AND THIS."INTEGER_IDX">=0;
2. What did you expect to see? (Required)
On v3.0.3 version, it's ok.
MySQL [hive_tidb]> SELECT COUNT(*) FROM "test" THIS WHERE THIS."CD_ID"=54603 AND THIS."INTEGER_IDX">=0;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
3. What did you see instead (Required)
On v5.0.1:
mysql> SELECT COUNT(*) FROM "test" THIS WHERE THIS."CD_ID"=54603 AND THIS."INTEGER_IDX">=0;
ERROR 1054 (42S22): Unknown column '54603' in 'where clause'
4. What is your TiDB version? (Required)
mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.0.1
Edition: Community
Git Commit Hash: 1145e347d3469d8e89f88dce86f6926ca44b3cd8
Git Branch: heads/refs/tags/v5.0.1
UTC Build Time: 2021-04-23 05:51:17
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)