Can't recognize numeric literals when set 'ANSI_QUOTES' sql_mode

[Tomccat3]

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)

[morgo]

The problem is related to the table.column identifier, where the column is enclosed in quotes but the table is not. Here is a pasteable testcase with more examples:

SET SESSION sql_mode='ANSI_QUOTES';
DROP TABLE IF EXISTS test;
CREATE TABLE test (
  "CD_ID" bigint(20) NOT NULL,
  "INTEGER_IDX" int(11) NOT NULL,
  PRIMARY KEY ("CD_ID") 
 );
 
INSERT INTO test VALUES (54603, 1),(54604, 2);

SELECT COUNT(*) FROM "test" THIS WHERE THIS."CD_ID"=54603 AND THIS."INTEGER_IDX">=0; # fails
SELECT COUNT(*) FROM "test" THIS WHERE "THIS"."CD_ID"=54603 AND "THIS"."INTEGER_IDX">=0; # works
SELECT COUNT(*) FROM "test" THIS WHERE "CD_ID"=54603 AND "INTEGER_IDX">=0; # works
SELECT COUNT(*) FROM "test" t WHERE t."CD_ID"=54603 AND t."INTEGER_IDX">=0; # fails


mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-2792-g83f14bf7f-dirty
Edition: Community
Git Commit Hash: 83f14bf7ff45590b9b62b27b6c35a0b7b02ac7fe
Git Branch: security-enhanced-3
UTC Build Time: 2021-05-04 20:18:47
GoVersion: go1.13.15
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: true
1 row in set (0.00 sec)

[bb7133]

I found that this bug cannot be reproduced in the early releases(v5.0-rc & v4.0), I would call this bug as a regression.

Hi @morgo , please try to locate the PR that caused this bug(by github bisect), thanks!

[morgo]

I've bisected it to #21908 by @xiongjiwei

[bb7133]

I've bisected it to #21908 by @xiongjiwei

Thanks!

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions