自动分区 range 设置不正确，第一次建表成功，同样的语句再创建，检查自动分区的合理性

Viewed 38
2.1

doris版本：2.1.5
期待结果：设置不正确，是不是就应该就不能创建表成功

mysql> CREATE TABLE awh_test_range_auto (
-> DATE_ID BIGINT NOT NULL COMMENT 'DATE_ID',
-> LAST_UPLOAD_TIME DATETIME COMMENT 'LAST_UPLOAD_TIME'
-> ) UNIQUE KEY(DATE_ID)
-> AUTO PARTITION BY RANGE(DATE_ID)()
-> DISTRIBUTED BY HASH(DATE_ID) BUCKETS AUTO
-> PROPERTIES (
-> "replication_num" = "3",
-> "min_load_replica_num" = "1"
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc awh_test_range_auto;
+------------------+----------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------+------+-------+---------+-------+
| DATE_ID | BIGINT | No | true | NULL | |
| LAST_UPLOAD_TIME | DATETIME | Yes | false | NULL | NONE |
+------------------+----------+------+-------+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table awh_test_range_auto;
+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| awh_test_range_auto | CREATE TABLE awh_test_range_auto (
DATE_ID BIGINT NOT NULL COMMENT 'DATE_ID',
LAST_UPLOAD_TIME DATETIME NULL COMMENT 'LAST_UPLOAD_TIME'
) ENGINE=OLAP
UNIQUE KEY(DATE_ID)
AUTO PARTITION BY RANGE (DATE_ID)
()
DISTRIBUTED BY HASH(DATE_ID) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
); |
+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE TABLE awh_test_range_auto (
-> DATE_ID BIGINT NOT NULL COMMENT 'DATE_ID',
-> LAST_UPLOAD_TIME DATETIME COMMENT 'LAST_UPLOAD_TIME'
-> ) UNIQUE KEY(DATE_ID)
-> AUTO PARTITION BY RANGE(DATE_ID)()
-> DISTRIBUTED BY HASH(DATE_ID) BUCKETS AUTO
-> PROPERTIES (
-> "replication_num" = "3",
-> "min_load_replica_num" = "1"
-> );
ERROR 1105 (HY000): errCode = 2, detailMessage = Syntax error in line 5:
AUTO PARTITION BY RANGE(DATE_ID)()
^
Encountered: )
Expected: .

anan012015
2 Answers

这个是回退老优化器导致的，老优化器上可能有各种遗留问题，即将被废弃，这上面的问题不再修了。请

set global enable_fallback_to_original_planner=false;

来防止这一行为。
新优化器上的报错是对的：

mysql> CREATE TABLE awh_test_range_auto (
    -> DATE_ID BIGINT NOT NULL COMMENT 'DATE_ID',
    -> LAST_UPLOAD_TIME DATETIME COMMENT 'LAST_UPLOAD_TIME'
    -> ) UNIQUE KEY(DATE_ID)
    -> AUTO PARTITION BY RANGE(DATE_ID)()
    -> DISTRIBUTED BY HASH(DATE_ID) BUCKETS AUTO
    -> PROPERTIES (
    -> "replication_num" = "3",
    -> "min_load_replica_num" = "1"
    -> ); 
ERROR 1105 (HY000): errCode = 2, detailMessage = errCode = 2, detailMessage = Table 'awh_test_range_auto' already exists

另外我们auto range partition需要使用date_trunc函数建表，这个表在插入数据的时候也会报错

now range partition only support FunctionCallExpr

后面会把这个check提前到建表时。

zclllyybb436

行为比较奇怪，稳定复现，已经在内部跟进中，后续fix后会同步

阿渊@SelectDB5990