order of placement rules on partitioned tables is too strict

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t1;
DROP PLACEMENT POLICY IF EXISTS companystandardpolicy;

CREATE PLACEMENT POLICY companystandardpolicy CONSTRAINTS="";

CREATE TABLE t1 (id INT, name VARCHAR(50), purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) ) (
  PARTITION p0 VALUES LESS THAN (2000),
  PARTITION p1 VALUES LESS THAN (2005),
  PARTITION p2 VALUES LESS THAN (2010),
  PARTITION p3 VALUES LESS THAN (2015),
  PARTITION p4 VALUES LESS THAN MAXVALUE
 )  PLACEMENT POLICY=companystandardpolicy; -- fails
 
CREATE TABLE t1 (id INT, name VARCHAR(50), purchased DATE)
 PLACEMENT POLICY=companystandardpolicy
 PARTITION BY RANGE( YEAR(purchased) ) (
  PARTITION p0 VALUES LESS THAN (2000),
  PARTITION p1 VALUES LESS THAN (2005),
  PARTITION p2 VALUES LESS THAN (2010),
  PARTITION p3 VALUES LESS THAN (2015),
  PARTITION p4 VALUES LESS THAN MAXVALUE
 ); -- works

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

Both examples should work

3. What did you see instead (Required)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 8 column 14 near "PLACEMENT POLICY=companystandardpolicy" 

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.3.0-alpha-1102-gbd518d580
Edition: Community
Git Commit Hash: bd518d5806050e2d470d14c3fc8111d5a5e9b52e
Git Branch: HEAD
UTC Build Time: 2021-10-14 17:00:56
GoVersion: go1.16.7
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[morgo]

We discussed this today, and decided that it is 'not a bug'. Consider this case from MySQL 8.0.24:

CREATE TABLE t1 (id INT, name VARCHAR(50), purchased DATE)   PARTITION BY RANGE( YEAR(purchased) ) (   PARTITION p0 VALUES LESS THAN (2000),   PARTITION p1 VALUES LESS THAN (2005),   PARTITION p2 VALUES LESS THAN (2010),   PARTITION p3 VALUES LESS THAN (2015),   PARTITION p4 VALUES LESS THAN MAXVALUE  ) engine=innodb; -- error

CREATE TABLE t1 (id INT, name VARCHAR(50), purchased DATE)  engine=innodb PARTITION BY RANGE( YEAR(purchased) ) (   PARTITION p0 VALUES LESS THAN (2000),   PARTITION p1 VALUES LESS THAN (2005),   PARTITION p2 VALUES LESS THAN (2010),   PARTITION p3 VALUES LESS THAN (2015),   PARTITION p4 VALUES LESS THAN MAXVALUE  ) ; -- valid

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.