doris通过catalog数据写入引起decimal类型故障

Viewed 172
2.0

doris版本2.0.13
目前已尝试保留小数位，剔除空值依旧无效，已验证没有超长值。
多运行几次，有时成功 有时失败

报错如下
SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = (172.31.3.98)[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#  doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> >::FieldType doris::vectorized::convert_to_decimal<doris::vectorized::DataTypeNumber<double>, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> >, false, false>(doris::vectorized::DataTypeNumber<double>::FieldType const&, unsigned int, unsigned int, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> >::FieldType const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> >::FieldType const&) at /home/zcp/repo_center/doris_release/doris/be/src/vec/data_types/data_type_decimal.h:0
2#  std::__detail::__variant::__gen_vtable_impl<std::__detail::__variant::_Multi_array<std::__detail::__variant::__deduce_visit_result<void> (*)(doris::vectorized::ConvertImpl<doris::vectorized::DataTypeNumber<double>, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> >, doris::vectorized::NameCast>::execute<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)::{lambda(auto:1, auto:2)#1}&&, std::variant<std::integral_constant<bool, false>, std::integral_constant<bool, true> >&&, std::variant<std::integral_constant<bool, false>, std::integral_constant<bool, true> >&&)>, std::integer_sequence<unsigned long, 0ul, 0ul> >::__visit_invoke(doris::vectorized::ConvertImpl<doris::vectorized::DataTypeNumber<double>, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> >, doris::vectorized::NameCast>::execute<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)::{lambda(auto:1, auto:2)#1}&&, std::variant<std::integral_constant<bool, false>, std::integral_constant<bool, true> >&&, std::variant<std::integral_constant<bool, false>, std::integral_constant<bool, true> >&&) at /home/zcp/repo_center/doris_release/doris/be/src/vec/functions/function_cast.h:348
3#  doris::Status doris::vectorized::ConvertImpl<doris::vectorized::DataTypeNumber<double>, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> >, doris::vectorized::NameCast>::execute<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 /var/local/ldb-toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/variant:0
4#  bool doris::vectorized::FunctionCast::create_decimal_wrapper<doris::vectorized::Decimal<doris::vectorized::Int128I> >(std::shared_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> > 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::DataTypeNumber<double>, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> > > >(doris::vectorized::TypePair<doris::vectorized::DataTypeNumber<double>, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> > > 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<doris::vectorized::Int128I> >, doris::vectorized::FunctionCast::create_decimal_wrapper<doris::vectorized::Decimal<doris::vectorized::Int128I> >(std::shared_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> > 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}>(doris::vectorized::TypeIndex, doris::vectorized::FunctionCast::create_decimal_wrapper<doris::vectorized::Decimal<doris::vectorized::Int128I> >(std::shared_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> > 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/vec/core/call_on_type_index.h:0
6#  doris::vectorized::FunctionCast::create_decimal_wrapper<doris::vectorized::Decimal<doris::vectorized::Int128I> >(std::shared_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> > 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 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<doris::vectorized::Int128I> >(std::shared_ptr<doris::vectorized::IDataType const> const&, doris::vectorized::DataTypeDecimal<doris::vectorized::Decimal<doris::vectorized::Int128I> > 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<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/std_function.h:291
8#  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
9#  doris::vectorized::PreparedFunctionImpl::execute_without_low_cardinality_columns(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long, bool) at /home/zcp/repo_center/doris_release/doris/be/src/vec/functions/function.cpp:0
10# doris::vectorized::PreparedFunctionImpl::execute(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long, bool) at /home/zcp/repo_center/doris_release/doris/be/src/vec/functions/function.cpp:274
11# doris::vectorized::IFunctionBase::execute(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator<unsigned long> > const&, unsigned long, unsigned long, bool) at /home/zcp/repo_center/doris_release/doris/be/src/vec/functions/function.h:183
12# doris::vectorized::VCastExpr::execute(doris::vectorized::VExprContext*, doris::vectorized::Block*, int*) at /home/zcp/repo_center/doris_release/doris/be/src/vec/exprs/vcast_expr.cpp:110
13# doris::vectorized::VExprContext::execute(doris::vectorized::Block*, int*) at /home/zcp/repo_center/doris_release/doris/be/src/common/status.h:357
14# doris::vectorized::VExprContext::get_output_block_after_execute_exprs(std::vector<std::shared_ptr<doris::vectorized::VExprContext>, std::allocator<std::shared_ptr<doris::vectorized::VExprContext> > > const&, doris::vectorized::Block const&, doris::vectorized::Block*, bool) at /home/zcp/repo_center/doris_release/doris/be/src/common/status.h:446
15# doris::stream_load::VOlapTableSink::send(doris::RuntimeState*, doris::vectorized::Block*, bool) at /home/zcp/repo_center/doris_release/doris/be/src/common/status.h:446
16# doris::PlanFragmentExecutor::open_vectorized_internal() at /home/zcp/repo_center/doris_release/doris/be/src/common/status.h:441
17# doris::PlanFragmentExecutor::open() at /home/zcp/repo_center/doris_release/doris/be/src/common/status.h:357
18# doris::FragmentExecState::execute() at /var/local/ldb-toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/tuple:180
19# doris::FragmentMgr::_exec_actual(std::shared_ptr<doris::FragmentExecState>, std::function<void (doris::RuntimeState*, doris::Status*)> const&) at /home/zcp/repo_center/doris_release/doris/be/src/common/status.h:446
20# std::_Function_handler<void (), doris::FragmentMgr::exec_plan_fragment(doris::TExecPlanFragmentParams const&, std::function<void (doris::RuntimeState*, doris::Status*)> const&)::$_0>::_M_invoke(std::_Any_data const&) at /var/local/ldb-toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/shared_ptr_base.h:701
21# doris::ThreadPool::dispatch_thread() at /home/zcp/repo_center/doris_release/doris/be/src/util/threadpool.cpp:0
22# doris::Thread::supervise_thread(void*) at /var/local/ldb-toolchain/bin/../usr/include/pthread.h:562
23# start_thread
24# clone
yky_young57
6 Answers

2.1.5版本没有此问题，
另外2.0版本经测试 可以改写sql 强转单个字段为 decimal 类型就可以了

yky_young57

看起来是在将double cast成decimal时溢出了。方便贴一下建表语句、查询语句以及测试数据吗？

jackteng21

catalog_wms_mysql.wms_wire.wms_api_picking_details的建表语句也提供一下，方便的话，catalog_wms_mysql.wms_wire.wms_api_picking_details的数据也贴一下，便于我们复现解决问题。

jackteng21

试一下这个查询，看看结果有没有超出DECIMAL(24, 6)表示范围的：

select id, coalesce(round(quantity*act_price,6),0) as total_amt from mysql_jdbc_catalog.test.wms_api_picking_details order by total_amt desc limit 10;

另外执行以下语句，贴一下plan:

explain verbose insert into tmp.tmp_decimal_test
select
id,
coalesce(round(quantity*act_price,6),0) as total_amt
from
catalog_wms_mysql.wms_wire.wms_api_picking_details
order by total_amt desc;
jackteng21

plan:

PLAN FRAGMENT 0
  OUTPUT EXPRS:
    cast(id as BIGINT)[#38]
    cast(coalesce(round((quantity * act_price), 6), 0) as DECIMALV3(24, 6))[#39]
    0[#40]
    cast(0 AS `0` as BIGINT)[#41]
  PARTITION: RANDOM

  HAS_COLO_PLAN_NODE: false

  OLAP TABLE SINK
    TUPLE ID: 2
    RANDOM

  228:VJdbcScanNode
     TABLE: `wms_wire`.`wms_api_picking_details`
     QUERY: SELECT `id`, `quantity`, `act_price` FROM `wms_wire`.`wms_api_picking_details`
     projections: CAST(id[#0] AS BIGINT), CAST(coalesce(round((quantity[#13] * act_price[#33]), 6), 0) AS DECIMALV3(24, 6)), 0, CAST(0 AS BIGINT)
     project output tuple id: 1
     tuple ids: 0 

Tuples:
TupleDescriptor{id=0, tbl=wms_api_picking_details, byteSize=32}
  SlotDescriptor{id=0, col=id, colUniqueId=-1, type=LARGEINT, nullable=false}
  SlotDescriptor{id=13, col=quantity, colUniqueId=-1, type=INT, nullable=true}
  SlotDescriptor{id=33, col=act_price, colUniqueId=-1, type=FLOAT, nullable=true}

TupleDescriptor{id=1, tbl=wms_api_picking_details, byteSize=48}
  SlotDescriptor{id=38, col=null, colUniqueId=null, type=BIGINT, nullable=false}
  SlotDescriptor{id=39, col=null, colUniqueId=null, type=DECIMALV3(24, 6), nullable=false}
  SlotDescriptor{id=40, col=null, colUniqueId=null, type=TINYINT, nullable=false}
  SlotDescriptor{id=41, col=null, colUniqueId=null, type=BIGINT, nullable=false}

TupleDescriptor{id=2, tbl=null, byteSize=48}
  SlotDescriptor{id=42, col=id, colUniqueId=0, type=BIGINT, nullable=true}
  SlotDescriptor{id=43, col=total_amt, colUniqueId=1, type=DECIMALV3(24, 6), nullable=true}
  SlotDescriptor{id=44, col=__DORIS_DELETE_SIGN__, colUniqueId=2, type=TINYINT, nullable=false}
  SlotDescriptor{id=45, col=__DORIS_VERSION_COL__, colUniqueId=3, type=BIGINT, nullable=false}
jackteng21

经过测试，如下查询也会有问题：

select
id,
cast(coalesce(round(quantity*act_price,6),0) as decimal(24,6)) as total_amt
from
catalog_wms_mysql.wms_wire.wms_api_picking_details
order by total_amt desc;

过滤掉NULL值没有问题：

select
id,
cast(coalesce(round(quantity*act_price,6),0) as decimal(24,6)) as total_amt
from
catalog_wms_mysql.wms_wire.wms_api_picking_details where quantity is not null and act_price is not null
order by total_amt desc;
jackteng21