doris错误信息Invalid range value format

最新推荐文章于 2024-09-08 19:04:04 发布

程裕强

最新推荐文章于 2024-09-08 19:04:04 发布

阅读量1k

点赞数

分类专栏： Doris 文章标签： doris

本文链接：https://blog.csdn.net/chengyuqiang/article/details/131110934

版权

Doris 专栏收录该内容

21 篇文章 7 订阅

订阅专栏

错误信息

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)