【BUG】 聚合模型表，建表语句中增加 DEFAULT， COMMENT 不会触发部分列更新操作。

Viewed 24
2.1

版本：2.1.5
问题描述：聚合模型表，建表语句中增加 COMMENT 不会触发部分列更新操作

--建表语句 1：
CREATE TABLE dws.order_tbl_1 (

  `day` INT NOT NULL,
  `platform` VARCHAR(65533) NOT NULL,
  `shop_id` VARCHAR(65533) NOT NULL,
  `total_orders` BIGINT REPLACE_IF_NOT_NULL NULL ,
  `active_subnick_count` BIGINT REPLACE_IF_NOT_NULL NULL  
  
  ) ENGINE=OLAP
AGGREGATE KEY(`day`,`platform`, `shop_id`)
partition by range(`day`) ()
DISTRIBUTED BY HASH(`day`,`platform`,`shop_id`) BUCKETS auto 
PROPERTIES
(
    "replication_allocation" = "tag.location.default: 3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.history_partition_num" = "30",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "estimate_partition_size" = "100M"
);

--建表语句 2:

CREATE TABLE dws.order_tbl_2 (

  `day` INT NOT NULL,
  `platform` VARCHAR(65533) NOT NULL,
  `shop_id` VARCHAR(65533) NOT NULL,
  `total_orders` BIGINT REPLACE_IF_NOT_NULL NULL DEFAULT "0",
  `active_subnick_count` BIGINT REPLACE_IF_NOT_NULL NULL DEFAULT "0" COMMENT 'asd'
  
  ) ENGINE=OLAP
AGGREGATE KEY(`day`,`platform`, `shop_id`)
partition by range(`day`) ()
DISTRIBUTED BY HASH(`day`,`platform`,`shop_id`) BUCKETS auto 
PROPERTIES
(
    "replication_allocation" = "tag.location.default: 3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.history_partition_num" = "30",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "estimate_partition_size" = "100M"
);

step1:写入测试表 dws.order_tbl_1

INSERT INTO  dws.order_tbl_1 (
day,
platform,
shop_id,
total_orders)
SELECT
20241210 as  day,
'tb' as platform,
'001' as shop_id,
100  total_orders;

image.png

INSERT INTO   dws.order_tbl_1 
(day,platform,shop_id,active_subnick_count
            )
SELECT
20241210 as  day,
'tb' as platform,
'001' as shop_id,
3 as  active_subnick_count;

image.png
结果：部分列更新成功

step2:写入测试表 dws.order_tbl_2

INSERT INTO  dws.order_tbl_2 (
day,
platform,
shop_id,
total_orders)
SELECT
20241210 as  day,
'tb' as platform,
'001' as shop_id,
100  total_orders;

image.png


INSERT INTO   dws.order_tbl_2
(day,platform,shop_id,active_subnick_count
            )
SELECT
20241210 as  day,
'tb' as platform,
'001' as shop_id,
3 as  active_subnick_count;

image.png

结果：部分列更新失败。

Yangboxiao47
1 Answers

当前部分列更新语法仅支持unique mow模型，agg可参考：聚合模型的导入更新

例如：

INSERT INTO order_tbl_2 (
day,
platform,
shop_id,
active_subnick_count,
total_orders)
SELECT
20241210 as  day,
'tb' as platform,
'001' as shop_id,
null ,
100  total_orders;
张彬华7238