insert into select 外部catalog表 插入数据,报要求字段not null的错误,但字段都是定义允许null

Viewed 16

我用insert into select 外部catalog表 插入数据,报错:
失败: errCode = 2, detailMessage = (10.10.0.64)[RUNTIME_ERROR]JdbcExecutorException: jdbc get block address: | CAUSED BY: RuntimeException: the result of 2185 row is null, but the return type is not nullable, please check the always_nullable property in create function statement, it's should be true

按意思就算有字段是null的,但我定义字段类型是not null的,但按以下DDL:
CREATE TABLE price (
id bigint NOT NULL COMMENT "唯一标识ID(非空)",
company_id bigint NULL COMMENT "公司ID",
type bigint NULL COMMENT "类型(关键字用反引号包裹)",
subtype bigint NULL COMMENT "子类型",
level bigint NULL COMMENT "层级",
price_source bigint NULL COMMENT "价格来源",
product_id bigint NULL COMMENT "产品ID",
product_code varchar(64) NULL COMMENT "产品编码",
product_name varchar(128) NULL COMMENT "产品名称",
color_id bigint NULL COMMENT "颜色ID",
color_code varchar(32) NULL COMMENT "颜色编码",
color_name varchar(32) NULL COMMENT "颜色名称",
color_category varchar(64) NULL COMMENT "颜色分类",
workplace_id bigint NULL COMMENT "工作场所ID",
workplace_code varchar(64) NULL COMMENT "工作场所编码",
workplace_name varchar(128) NULL COMMENT "工作场所名称",
customer_id bigint NULL COMMENT "客户ID",
customer_code varchar(64) NULL COMMENT "客户编码",
customer_name varchar(128) NULL COMMENT "客户名称",
xima_unit bigint NULL COMMENT "xima单位",
money_unit bigint NULL COMMENT "货币单位(1=人民币,2=美元等)",
price_category bigint NULL COMMENT "价格分类",
listing_adjust_number decimal(20,6) NULL COMMENT "挂牌调整数值",
listing_number decimal(20,6) NULL COMMENT "挂牌数值(如挂牌价)",
base_number decimal(20,6) NULL COMMENT "基础数值(如基础价)",
be_special bigint NULL COMMENT "是否特殊(1=是,0=否)",
special_number decimal(20,6) NULL COMMENT "特殊数值(如特殊价)",
be_xima_discount_forbidden bigint NULL COMMENT "是否禁止xima折扣(1=是,0=否)",
be_price_discount_forbidden bigint NULL COMMENT "是否禁止价格折扣(1=是,0=否)",
remark varchar(512) NULL COMMENT "备注",
level_attr_name varchar(64) NULL COMMENT "层级属性名称",
price_changed_time datetime NULL COMMENT "价格变更时间",
price_changed_state bigint NULL COMMENT "价格变更状态",
rel_price_id bigint NULL COMMENT "关联价格ID",
updated_at datetime NULL COMMENT "更新时间",
created_at datetime NULL COMMENT "创建时间",
listing_number_before decimal(20,6) NULL COMMENT "变更前挂牌数值",
listing_adjust_number_before decimal(20,6) NULL COMMENT "变更前挂牌调整数值",
base_number_before decimal(20,6) NULL COMMENT "变更前基础数值",
special_number_before decimal(20,6) NULL COMMENT "变更前特殊数值",
warning_line_inventory decimal(20,6) NULL COMMENT "库存预警线数量",
dyeing_factory_inventory decimal(20,6) NULL COMMENT "染厂库存数量",
alert_processed bigint NULL COMMENT "预警是否处理(1=是,0=否)",
is_auto_lock bigint NULL COMMENT "是否自动锁定(1=是,0=否)",
is_forbid_discount bigint NULL COMMENT "是否禁止折扣(1=是,0=否)",
adjusted_price_before decimal(20,6) NULL COMMENT "变更前调整后价格",
adjusted_price decimal(20,6) NULL COMMENT "调整后价格",
adjusted_price_start_at datetime NULL COMMENT "调整后价格生效时间",
adjusted_price_end_at datetime NULL COMMENT "调整后价格失效时间",
is_promotion bigint NULL COMMENT "是否促销(1=是,0=否)",
creator_id bigint NULL COMMENT "创建人ID"
) ENGINE=OLAP
UNIQUE KEY(id)
COMMENT 'price'
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"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"
);

首先除了key ID 之外,所有字段都定义是可以null的,key肯定不为null的,而且我在select中其他的字段也用COALESCE 做了处理,也不可能是空的,57efb5e5a8baa2aac4ff4531c7041d64.png

我目前知道的是 adjusted_price_end_at 这字段造成的,只要where 上加上 adjusted_price_end_at is not null ,就都能写入,但观察不出这个字段和其他字段有什么区别,就是直接加默认值也不行,请问大概是什么问题?是bug吗?还是需要有些特殊处理技巧?

2 Answers

你这个是什么版本的doris?用的什么catalog?

阿里云的基于mysql的analytic db,doris是3.0版本