single quotes can not be used to quote literal string in partition table when using ANSI_QUOTES sql mode

[gengliqi]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> SET SESSION sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `compose_record_sxq` (
    ->   `id` bigint(20) unsigned NOT NULL,
    ->   `rel_namespace` bigint(20) unsigned NOT NULL,
    ->   `module_id` bigint(20) unsigned NOT NULL,
    ->   `values` json NOT NULL,
    ->   `owned_by` bigint(20) unsigned NOT NULL,
    ->   `created_at` datetime NOT NULL,
    ->   `updated_at` datetime DEFAULT NULL,
    ->   `deleted_at` datetime DEFAULT NULL,
    ->   `created_by` bigint(20) unsigned NOT NULL,
    ->   `updated_by` bigint(20) unsigned NOT NULL DEFAULT '0',
    ->   `deleted_by` bigint(20) unsigned NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`,`created_at`) /*T![clustered_index] CLUSTERED */,
    ->   KEY `namespace` (`rel_namespace`),
    ->   KEY `module` (`module_id`),
    ->   KEY `owner` (`owned_by`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    -> PARTITION BY RANGE COLUMNS(`created_at`)
    -> (PARTITION `p0` VALUES LESS THAN ('2021-12-01 00:00:00'),
    ->  PARTITION `p1` VALUES LESS THAN ('2022-01-01 00:00:00'),
    ->  PARTITION `p2` VALUES LESS THAN ('2022-02-01 00:00:00'),
    ->  PARTITION `p3` VALUES LESS THAN ('2022-03-01 00:00:00'),
    ->  PARTITION `p4` VALUES LESS THAN ('2022-04-01 00:00:00'),
    ->  PARTITION `p5` VALUES LESS THAN ('2022-05-01 00:00:00'),
    ->  PARTITION `p6` VALUES LESS THAN ('2022-06-01 00:00:00'),
    ->  PARTITION `p7` VALUES LESS THAN ('2022-07-01 00:00:00'),
    ->  PARTITION `p8` VALUES LESS THAN (MAXVALUE));
ERROR 1054 (42S22): Unknown column '2022-01-01 00:00:00' in 'expression'

mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.0.0
Edition: Community
Git Commit Hash: 36a9810441ca0e496cbd22064af274b3be771081
Git Branch: heads/refs/tags/v6.0.0
UTC Build Time: 2022-03-31 10:27:58
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

create table succeeds.

3. What did you see instead (Required)

failed.

4. What is your TiDB version? (Required)

showed above

[ti-chi-bot]

@gengliqi: The label(s) sig/sql-infra cannot be applied. These labels are supported: challenge-program, compatibility-breaker, first-time-contributor, contribution, require-LGT3, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-4.0, needs-cherry-pick-5.0, needs-cherry-pick-5.1, needs-cherry-pick-5.2, needs-cherry-pick-5.3, needs-cherry-pick-5.4, needs-cherry-pick-6.0, needs-cherry-pick-6.1, affects-4.0, affects-5.0, affects-5.1, affects-5.2, affects-5.3, affects-5.4, affects-6.0, affects-6.1, may-affects-4.0, may-affects-5.0, may-affects-5.1, may-affects-5.2, may-affects-5.3, may-affects-5.4, may-affects-6.0, may-affects-6.1.

In response to this:

/label sig/sql-infra

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[ti-chi-bot]

@gengliqi: The label(s) sig/sql-infra cannot be applied. These labels are supported: challenge-program, compatibility-breaker, first-time-contributor, contribution, require-LGT3, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-4.0, needs-cherry-pick-5.0, needs-cherry-pick-5.1, needs-cherry-pick-5.2, needs-cherry-pick-5.3, needs-cherry-pick-5.4, needs-cherry-pick-6.0, needs-cherry-pick-6.1, affects-4.0, affects-5.0, affects-5.1, affects-5.2, affects-5.3, affects-5.4, affects-6.0, affects-6.1, may-affects-4.0, may-affects-5.0, may-affects-5.1, may-affects-5.2, may-affects-5.3, may-affects-5.4, may-affects-6.0, may-affects-6.1.

In response to this:

/label sig/sql-infra

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[djshow832]

The reason is that 2022-01-01 00:00:00 is rewritten as "2022-01-01 00:00:00" in buildRangePartitionDefinitions and rewritten to select "2022-01-01 00:00:00" in ParseSimpleExprWithTableInfo. "2022-01-01 00:00:00" is taken as an identifier in select "2022-01-01 00:00:00" since sql_mode = 'ANSI_QUOTES'.