[bug] Doris2.0.2 执行固定SQL后，BE报错，甚至导致所有BE宕机。

[DA1OOO]

Discussed in #25643

^{Originally posted by DA1OOO October 19, 2023}
自建Doris 2.0.2集群，1FE + 3BE，enable_vectorized_engine=true

SELECT not ISNULL(CASE WHEN IFNULL ((t1.region IN ('US')),0) THEN t1.region ELSE 'other' END) AS account_id, count(*) FROM (select 'US' AS region) as t1 group by 1
该SQL执行后，出现可复现的报错，但BE未宕机：

Execution failed: Error Failed to execute sql: java.sql.SQLException: (conn=82) errCode = 2, detailMessage = (10.252.155.201)[CANCELLED][INVALID_ARGUMENT]Illegal column Int8 of first argument of function if,Must be ColumnUInt8 or ColumnConstUInt8. 0. /root/src/doris-2.0/be/src/common/stack_trace.cpp:302: StackTrace::tryCapture() @ 0x000000000ba1f197 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 1. /root/src/doris-2.0/be/src/common/stack_trace.h:0: doris::get_stack_traceabi:cxx11 @ 0x000000000ba1d72d in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 2. /var/local/ldb-toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/unique_ptr.h:173: doris::Status doris::Status::Error<true, std::__cxx11::basic_string<char, std::char_traits, std::allocator >, std::__cxx11::basic_string<char, std::char_traits, std::allocator > >(int, std::basic_string_view<char, std::char_traits >, std::__cxx11::basic_string<char, std::char_traits, std::allocator >&&, std::__cxx11::basic_string<char, std::char_traits, std::allocator >&&) @ 0x000000000b6b7ae2 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 3. /var/local/ldb-toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/basic_string.h:187: doris::vectorized::FunctionIf::execute_impl(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator > const&, unsigned long, unsigned long) @ 0x0000000011087f13 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 4. /root/src/doris-2.0/be/src/vec/functions/function.h:501: doris::vectorized::DefaultExecutable::execute_impl(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator > const&, unsigned long, unsigned long) @ 0x000000000f43e09f in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 5. /root/src/doris-2.0/be/src/vec/common/cow.h:0: doris::vectorized::PreparedFunctionImpl::default_implementation_for_constant_arguments(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator > const&, unsigned long, unsigned long, bool, bool*) @ 0x000000000ff8891e in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 6. /root/src/doris-2.0/be/src/vec/functions/function.cpp:0: doris::vectorized::PreparedFunctionImpl::execute_without_low_cardinality_columns(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator > const&, unsigned long, unsigned long, bool) @ 0x000000000ff88ed1 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 7. /root/src/doris-2.0/be/src/vec/functions/function.cpp:268: doris::vectorized::PreparedFunctionImpl::execute(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator > const&, unsigned long, unsigned long, bool) @ 0x000000000ff89046 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 8. /root/src/doris-2.0/be/src/vec/functions/function.h:172: doris::vectorized::IFunctionBase::execute(doris::FunctionContext*, doris::vectorized::Block&, std::vector<unsigned long, std::allocator > const&, unsigned long, unsigned long, bool) @ 0x000000000f43d8b0 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 9. /root/src/doris-2.0/be/src/vec/exprs/vectorized_fn_call.cpp:159: doris::vectorized::VectorizedFnCall::execute(doris::vectorized::VExprContext*, doris::vectorized::Block*, int*) @ 0x000000000f43a9f9 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 10. /root/src/doris-2.0/be/src/common/status.h:432: doris::vectorized::VExpr::get_const_col(doris::vectorized::VExprContext*, std::shared_ptrdoris::ColumnPtrWrapper) @ 0x000000000f4444bf in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 11. /root/src/doris-2.0/be/src/common/status.h:432: doris::vectorized::VExpr::init_function_context(doris::vectorized::VExprContext, doris::FunctionContext::FunctionStateScope, std::shared_ptrdoris::vectorized::IFunctionBase const&) const @ 0x000000000f444bb0 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 12. /root/src/doris-2.0/be/src/common/status.h:432: doris::vectorized::VectorizedFnCall::open(doris::RuntimeState*, doris::vectorized::VExprContext*, doris::FunctionContext::FunctionStateScope) @ 0x000000000f43a55e in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 13. /root/src/doris-2.0/be/src/common/status.h:348: doris::vectorized::VExprContext::open(doris::RuntimeState*) @ 0x000000000f45655c in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 14. /root/src/doris-2.0/be/src/common/status.h:432: doris::vectorized::VExpr::open(std::vector<std::shared_ptrdoris::vectorized::VExprContext, std::allocator<std::shared_ptrdoris::vectorized::VExprContext > > const&, doris::RuntimeState*) @ 0x000000000f4437aa in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 15. /root/src/doris-2.0/be/src/common/status.h:432: doris::vectorized::VUnionNode::alloc_resource(doris::RuntimeState*) @ 0x000000000f40e5a7 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 16. /root/src/doris-2.0/be/src/common/status.h:432: doris::pipeline::StreamingOperatordoris::pipeline::ConstValueOperatorBuilder::open(doris::RuntimeState*) @ 0x00000000126633f3 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 17. /root/src/doris-2.0/be/src/common/status.h:432: doris::pipeline::PipelineTask::_open() @ 0x00000000126a1f5a in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 18. /root/src/doris-2.0/be/src/common/status.h:427: doris::pipeline::PipelineTask::execute(bool*) @ 0x00000000126a236c in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 19. /root/src/doris-2.0/be/src/common/status.h:348: doris::pipeline::TaskScheduler::_do_work(unsigned long) @ 0x00000000126ab103 in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 20. /root/src/doris-2.0/be/src/util/threadpool.cpp:0: doris::ThreadPool::dispatch_thread() @ 0x000000000ba5bdaf in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 21. /var/local/ldb-toolchain/bin/../usr/include/pthread.h:562: doris::Thread::supervise_thread(void*) @ 0x000000000ba51d3c in /home/work/doris/apache-doris-2.0.2-bin-x64/be/lib/doris_be 22. start_thread @ 0x00007f17bfd06851 in ? 23. clone @ 0x00007f17c048b67d in ?`

当我在将类似语句应用于我所建的表：
SELECT
MIN(
(
app_bidder_monitor_stats_hourly.account_id = '121381342526'
)
) AS Calculation_4272438315924406285,
'\u5e94\u7528' AS Calculation_9109304311352020998,
app_bidder_monitor_stats_hourly.account_id AS account_id
FROM
app_bidder_monitor_stats_hourly
WHERE
(
(
ISNULL (
(
app_bidder_monitor_stats_hourly.account_id = '121381342526'
)
)
OR (
app_bidder_monitor_stats_hourly.account_id <> '121381342526'
)
)
AND (
NOT ISNULL (
(
CASE
WHEN IFNULL ( (app_bidder_monitor_stats_hourly.region IN ('US')), 0 )
THEN app_bidder_monitor_stats_hourly.region
ELSE 'other'
END
)
)
)
)
GROUP BY
3
不仅会出现该错误，还会导致所有BE宕机。（我的该条SQL来自tableau自动生成）

[LemonLiTree]

It's ok when I execute it on the master：

[DA1OOO]

Is this error possibly related to any configuration? I am using the default configuration of doris2.0.2.

[DA1OOO]

My Doris version is 2.0.2.

[LemonLiTree]

The same problem occurred when I tested 2.0.2. We plan to fix it in 2.0.3

[DA1OOO]

Which version is OK to run this SQL？ 2.0.1.1 ? Tableau will generate many SQL like this.

[LemonLiTree]

master is ok

[zhiqiang-hhhh]

Planner of 2.0.2 generates a wrong plan:

if(ifnull(CAST(TRUE AS TINYINT), 0), 'US', 'other') should be corrected to

if(CAST(ifnull(CAST(TRUE AS TINYINT), 0) AS BOOLEAN), 'US', 'other')

This problem has already been fixed in branch-2.0, release 2.0.3 will contain the fix.

[zhiqiang-hhhh]

@DA1OOO You can wait the release of 2.0.3 or just checkout branch-2.0 and compile frontend by youself.
btw, can you provide the logs mentioned in the case of be crash down?

[DA1OOO]

Thanks! I has used release of 2.0.1.1, it's ok in this case.
I only found related logs I mentioned above like Execution failed: Error Failed to execute sql: java.sql.SQLException: (conn=82) errCode = 2, detailMessage = (10.252.155.201)[CANCELLED][INVALID_ARGUMENT]Illegal column Int8 of first argument of function if,Must be ColumnUInt8 or ColumnConstUInt8. It seems that there are no other related crash down logs printed out.

[DA1OOO]

@zhiqiang-hhhh do you know when will doris 2.0.3 be released?

[zhiqiang-hhhh]

@zhiqiang-hhhh do you know when will doris 2.0.3 be released?

maybe next week

[DA1OOO]

@zhiqiang-hhhh I checkout to branch-2.0 and compile frontend by myself and meet new bad case, Tableau can’t connect to doris by mysql odbc driver 5.3.
Error like "wrong data type string. expected integer"

I find such SQL in query profile when I tried to connect. SELECT CONNECTION_ID()

btw, I can connect normally using other MySQL client.