doris错误信息Invalid range value format

程裕强

发布于 2023-10-18 17:32:16

3740

发布于 2023-10-18 17:32:16

错误信息

ERROR 1105 (HY000): errCode = 2, detailMessage = Invalid range value format： errCode = 2, detailMessage = date literal [2017-03-01] is invalid: errCode = 2, detailMessage = Invalid datetime value: 2017-03-01

mysql> create table mall_dw.fact_order_info
    -> (
    ->    order_id             int  ,
    ->    order_number         varchar(35) NOT NULL,
    ->    order_date           DATETIME,
    ->    customer_id          int  ,
    ->    product_id           int  ,
    ->    order_amount         DECIMAL(18,2)
    -> )
    -> UNIQUE KEY(order_id,order_number,order_date,customer_id,product_id)
    -> PARTITION BY RANGE(order_date)
    -> (
    ->     PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
    ->     PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
    -> )
    -> DISTRIBUTED BY HASH(order_id) BUCKETS 10
    -> PROPERTIES (
    ->     "replication_num" = "1",
    ->     "dynamic_partition.enable" = "true",
    ->     "dynamic_partition.time_unit" = "MONTH",
    ->     "dynamic_partition.time_zone" = "Asia/Shanghai",
    ->     "dynamic_partition.start" = "-2147483648",
    ->     "dynamic_partition.end" = "2",
    ->     "dynamic_partition.prefix" = "P_"
    -> );
ERROR 1105 (HY000): errCode = 2, detailMessage = Invalid range value format： errCode = 2, detailMessage = date literal [2017-03-01] is invalid: errCode = 2, detailMessage = Invalid datetime value: 2017-03-01

原来是order_date的类型为DATETIME，所以分区范围需要给出日期时间对应的格式

mysql> create table mall_dw.fact_order_info
    -> (
    ->    order_id             int  ,
    ->    order_number         varchar(35) NOT NULL,
    ->    order_date           DATETIME,
    ->    customer_id          int  ,
    ->    product_id           int  ,
    ->    order_amount         DECIMAL(18,2)
    -> )
    -> UNIQUE KEY(order_id,order_number,order_date,customer_id,product_id)
    -> PARTITION BY RANGE(order_date)
    -> (
    ->     PARTITION P_202111 VALUES [('2021-11-01 00:00:00'), ('2021-12-01 00:00:00'))
    -> )
    -> DISTRIBUTED BY HASH(order_id) BUCKETS 10
    -> PROPERTIES (
    ->     "replication_num" = "1",
    ->     "dynamic_partition.enable" = "true",
    ->     "dynamic_partition.time_unit" = "MONTH",
    ->     "dynamic_partition.time_zone" = "Asia/Shanghai",
    ->     "dynamic_partition.end" = "2",
    ->     "dynamic_partition.prefix" = "P_"
    -> );
Query OK, 0 rows affected (0.02 sec)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2023-10-12，如有侵权请联系 cloudcommunity@tencent.com 删除

date