[BE] insert...select语法某行数据报算术溢出([E-124][E-124] Arithmetic overflow), 但无法看出是哪一列

Viewed 103
2.0

问题

之前doris批量写数据的过程中, 如果出现字段相关的报错日志里面会给出具体报错字段对应的url, 但这次的算术溢出没有, 这是bug吗?

本次问题因为只有一个decimal字段有明显的精度差异, 如果数值类型的字段数量比较多的话, 人肉去核对比较困难, 能提示出具体是哪个字段报错方便开发人员去排查

ddl如下:

CREATE TABLE `dwd_pi_branch_fty_day_produ_plan` (
    `work_date` varchar(24) NOT NULL COMMENT '作业日期',
    `craft_unit` varchar(12) NOT NULL COMMENT '工艺单元',
    `unit_no` varchar(6) NOT NULL COMMENT '单元号',
    `produ_target_proj_code` varchar(60) NOT NULL COMMENT '生产指标项目代码',
    `produ_target_proj_chinese_name` varchar(3000) NULL COMMENT '生产指标项目中文名称',
    `produ_target_proj_value` DECIMAL(8, 3) NULL COMMENT '生产指标项目值',
    `plan_stat` varchar(6) NULL COMMENT '计划状态',
    `rec_creator` varchar(60) NULL COMMENT '记录创建责任者',
    `rec_create_time` varchar(42) NULL COMMENT '记录创建时间',
    `rec_revisor` varchar(60) NULL COMMENT '记录修改责任者',
    `rec_revise_time` varchar(42) NULL COMMENT '记录修改时间',
    `archive_flag` varchar(3) NULL COMMENT '归档标记',
    `rec_del_person` varchar(60) NULL COMMENT '记录删除责任者',
    `rec_del_time` varchar(42) NULL COMMENT '记录删除时间',
    `ver` int(11) NULL COMMENT '版本',
    `archive_stamp_no` varchar(30) NULL COMMENT '归档邮戳号',
    `etl_time` datetime NULL COMMENT '数据加载时间'
) 
CREATE TABLE `ods_tq1_tpsip01` (
    `PROD_PLAN_TYPE` varchar(6) NOT NULL COMMENT '生产计划类型',
    `PROC_UNIT` varchar(12) NOT NULL COMMENT '工艺单元',
    `UNIT_NO` varchar(6) NOT NULL COMMENT '单元号',
    `PROD_ITEM_CODE` varchar(60) NOT NULL COMMENT '生产指标项目代码',
    `REC_CREATOR` varchar(60) NULL COMMENT '记录创建责任者',
    `REC_CREATE_TIME` varchar(42) NULL COMMENT '记录创建时刻',
    `REC_REVISOR` varchar(60) NULL COMMENT '记录修改责任者',
    `REC_REVISE_TIME` varchar(42) NULL COMMENT '记录修改时刻',
    `ARCHIVE_FLAG` varchar(3) NULL COMMENT '归档标记',
    `DU_MAKER` varchar(60) NULL COMMENT '修改/删除责任者',
    `DU_TIME` varchar(42) NULL COMMENT '修改/删除时间',
    `VERSION` smallint(6) NULL COMMENT '版次',
    `ARCH_STAMP_NO` varchar(30) NULL COMMENT '归档邮戳号',
    `PROD_ITEM_CNAME` varchar(3000) NULL COMMENT '生产指标项目中文名称',
    `MEASURE_UNIT` varchar(30) NULL COMMENT '计量单位',
    `DECIMAL_DIGIT` smallint(6) NULL COMMENT '小数位数',
    `DIS_SEQ_NO` smallint(6) NULL COMMENT '计划项目排序',
    `REMARK` varchar(750) NULL COMMENT '备注',
    `PROD_ITEM_VALUE` DECIMAL(11, 3) NULL COMMENT '生产指标项目值',
    `etl_time` datetime NULL COMMENT '记录数据的写入时间'
) 

CREATE TABLE `ods_tq1_tpsip24` (
    `WORK_DATE` varchar(24) NOT NULL COMMENT '作业日期',
    `PROC_UNIT` varchar(12) NOT NULL COMMENT '工艺单元',
    `UNIT_NO` varchar(6) NOT NULL COMMENT '单元号',
    `PROD_ITEM_CODE` varchar(60) NOT NULL COMMENT '生产指标项目代码',
    `REC_CREATOR` varchar(60) NULL COMMENT '记录创建责任者',
    `REC_CREATE_TIME` varchar(42) NULL COMMENT '记录创建时刻',
    `REC_REVISOR` varchar(60) NULL COMMENT '记录修改责任者',
    `REC_REVISE_TIME` varchar(42) NULL COMMENT '记录修改时刻',
    `ARCHIVE_FLAG` varchar(3) NULL COMMENT '归档标记',
    `DU_MAKER` varchar(60) NULL COMMENT '修改/删除责任者',
    `DU_TIME` varchar(42) NULL COMMENT '修改/删除时间',
    `VERSION` smallint(6) NULL COMMENT '版次',
    `ARCH_STAMP_NO` varchar(30) NULL COMMENT '归档邮戳号',
    `PROD_ITEM_VALUE` DECIMAL(11, 3) NULL COMMENT '生产指标项目值',
    `PLAN_STATUS` varchar(6) NULL COMMENT '计划状态',
    `etl_time` datetime NULL COMMENT '记录数据的写入时间'
)

sql如下:

INSERT INTO dwd_pi_branch_fty_day_produ_plan 
    (
        work_date, --作业日期
        craft_unit, --工艺单元
        unit_no, --单元号
        produ_target_proj_code, --生产指标项目代码
        produ_target_proj_chinese_name, --生产指标项目中文名称
        produ_target_proj_value, --生产指标项目值
        plan_stat, --计划状态
        rec_creator, --记录创建责任者
        rec_create_time, --记录创建时间
        rec_revisor, --记录修改责任者
        rec_revise_time, --记录修改时间
        archive_flag, --归档标记
        rec_del_person, --记录删除责任者
        rec_del_time, --记录删除时间
        ver, --版本
        archive_stamp_no, --归档邮戳号
        etl_time --数据加载时间
    )
SELECT A1.work_date AS work_date,
    A1.proc_unit AS craft_unit,
    A1.unit_no AS unit_no,
    A1.prod_item_code AS produ_target_proj_code,
    A2.prod_item_cname AS produ_target_proj_chinese_name,
    A1.prod_item_value AS produ_target_proj_value,
    A1.plan_status AS plan_stat,
    A1.rec_creator AS rec_creator,
    A1.rec_create_time AS rec_create_time,
    A1.rec_revisor AS rec_revisor,
    A1.rec_revise_time AS rec_revise_time,
    A1.archive_flag AS archive_flag,
    A1.du_maker AS rec_del_person,
    A1.du_time AS rec_del_time,
    A1.version AS ver,
    A1.arch_stamp_no AS archive_stamp_no,
    NOW() AS etl_time
FROM
    ods_prod.ods_tq1_tpsip24 A1
    LEFT JOIN (
        SELECT
            prod_item_code,
            prod_item_cname,
            etl_time
        FROM
            ods_prod.ods_tq1_tpsip01
        WHERE
            prod_plan_type = 22
    ) A2 
省略后续条件

报错如下:

java.sql.SQLException: errCode = 2, detailMessage = (10.8.173.240)[E-124][E-124] Arithmetic overflow

        0#  doris::Exception::Exception(int, std::basic_string_view<char, std::char_traits<char> >) at /var/local/ldb_toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/unique_ptr.h:173
        1#  std::__detail::__variant::__gen_vtable_impl<std::__detail::__variant::_Multi_array<std::__detail::__variant::__deduce_visit_result<void> (*)(doris::vectorized::convert_decimal_cols<doris::vectorized::DataTypeDecimal<doris
::vectorized::Decimal<long> >, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> > >(doris::vectorized::ColumnDecimal<doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<long> >::FieldType>::Container::valu
e_type const*, doris::vectorized::ColumnDecimal<doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> >::FieldType>::Container::value_type*, unsigned int, unsigned int, unsigned int, unsigned int, unsigned long)::{lambda
(auto:1)#1}&&, std::variant<std::integral_constant<bool, false>, std::integral_constant<bool, true> >&&)>, std::integer_sequence<unsigned long, 1ul> >::__visit_invoke(doris::vectorized::convert_decimal_cols<doris::vectorized::DataTyp
eDecimal<doris::vectorized::Decimal<long> >, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> > >(doris::vectorized::ColumnDecimal<doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<long> >::FieldType>::C
ontainer::value_type const*, doris::vectorized::ColumnDecimal<doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> >::FieldType>::Container::value_type*, unsigned int, unsigned int, unsigned int, unsigned int, unsigned 
long)::{lambda(auto:1)#1}&&, std::variant<std::integral_constant<bool, false>, std::integral_constant<bool, true> >&&) at /home/zcp/repo_center/doris_release/doris/be/src/vec/data_types/data_type_decimal.h:527
        2#  void doris::vectorized::convert_decimal_cols<doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<long> >, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> > >(doris::vectorized::ColumnDecimal<d
oris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<long> >::FieldType>::Container::value_type const*, doris::vectorized::ColumnDecimal<doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> >::FieldType>::Contai
ner::value_type*, unsigned int, unsigned int, unsigned int, unsigned int, unsigned long) at /var/local/ldb_toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/variant:0
        3#  doris::Status doris::vectorized::ConvertImpl<doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<long> >, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> >, doris::vectorized::NameCast>::execu
te<doris::vectorized::PrecisionScaleArg>(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long, bool, doris::vectorized::PrecisionScaleArg)
 at /home/zcp/repo_center/doris_release/doris/be/src/vec/functions/function_cast.h:0
        4#  bool doris::vectorized::FunctionCast::create_decimal_wrapper<doris::vectorized::Decimal<int> >(std::shared_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int>
 > const*) const::{lambda(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long)#1}::operator()(doris::FunctionContext*, doris::vectorized:
:Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long) const::{lambda(auto:1 const&)#1}::operator()<doris::vectorized::TypePair<doris::vectorized::DataTypeDecimal<doris::vectorized::
Decimal<long> >, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> > > >(doris::vectorized::TypePair<doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<long> >, doris::vectorized::DataTypeDecimal<doris::ve
ctorized::Decimal<int> > > const&) const at /home/zcp/repo_center/doris_release/doris/be/src/vec/functions/function_cast.h:1698
        5#  bool doris::vectorized::call_on_index_and_data_type<doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> >, doris::vectorized::FunctionCast::create_decimal_wrapper<doris::vectorized::Decimal<int> >(std::shar
ed_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> > const*) const::{lambda(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::alloca
tor<unsigned long> > const&, unsigned long, unsigned long)#1}::operator()(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long) const::{la
mbda(auto:1 const&)#1}>(doris::vectorized::TypeIndex, doris::vectorized::FunctionCast::create_decimal_wrapper<doris::vectorized::Decimal<int> >(std::shared_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDe
cimal<doris::vectorized::Decimal<int> > const*) const::{lambda(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long)#1}::operator()(doris:
:FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long) const::{lambda(auto:1 const&)#1}&&) at /home/zcp/repo_center/doris_release/doris/be/src/ve
c/core/call_on_type_index.h:0
        6#  doris::vectorized::FunctionCast::create_decimal_wrapper<doris::vectorized::Decimal<int> >(std::shared_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> > co
nst*) const::{lambda(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long)#1}::operator()(doris::FunctionContext*, doris::vectorized::Bloc
k&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long) const at /home/zcp/repo_center/doris_release/doris/be/src/vec/functions/function_cast.h:1708
        7#  std::_Function_handler<doris::Status (doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long), doris::vectorized::FunctionCast::
create_decimal_wrapper<doris::vectorized::Decimal<int> >(std::shared_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<int> > const*) const::{lambda(doris::FunctionContext*,
 doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long)#1}>::_M_invoke(std::_Any_data const&, doris::FunctionContext*&&, doris::vectorized::Block&, std::vector<unsi
gned long, std::allocator<unsigned long> > const&, unsigned long&&, unsigned long&&) at /var/local/ldb_toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/std_function.h:291
        8#  doris::vectorized::FunctionCast::prepare_remove_nullable(doris::FunctionContext*, std::shared_ptr<doris::vectorized::IDataType const> const&, std::shared_ptr<doris::vectorized::IDataType const> const&, bool) const::{lambd
a(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long)#1}::operator()(doris::FunctionContext*, doris::vectorized::Block&, std::vector<uns
igned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long) const at /var/local/ldb_toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/stl_vector.h:335
        9#  std::_Function_handler<doris::Status (doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long), doris::vectorized::FunctionCast::
prepare_remove_nullable(doris::FunctionContext*, std::shared_ptr<doris::vectorized::IDataType const> const&, std::shared_ptr<doris::vectorized::IDataType const> const&, bool) const::{lambda(doris::FunctionContext*, doris::vectorized:
:Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long)#1}>::_M_invoke(std::_Any_data const&, doris::FunctionContext*&&, doris::vectorized::Block&, std::vector<unsigned long, std::all
ocator<unsigned long> > const&, unsigned long&&, unsigned long&&) at /var/local/ldb_toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/std_function.h:291
        10# doris::vectorized::PreparedFunctionCast::execute_impl(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long) at /var/local/ldb_
toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/stl_vector.h:335
        11# doris::vectorized::PreparedFunctionImpl::execute_without_low_cardinality_columns(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsign
ed long, bool) at /home/zcp/repo_center/doris_release/doris/be/src/vec/functions/function.cpp:0
:
yadao29
1 Answers

produ_target_proj_value 范围超了吧

yky_young67