laxfight commented on issue #33007:
URL: https://github.com/apache/doris/issues/33007#issuecomment-2051373380
Thank you for your reply.
Our production environment experienced a crash when executing the following
statement:
```SQL
SELECT t_a_0.`device_id` AS f_ax_0
FROM
(
SELECT b.device_type
,A.device_id
,b.engine_id
,b.secondary_engine_id
, LEFT(b.sale_date, 10)
,COUNT(1)
FROM A
LEFT JOIN B
ON get_json_string(a.json_data, '$."[0]"') = b.id AND b.id = 'DL11'
LEFT JOIN C
ON a.device_id = c.id
LEFT JOIN D
ON get_json_string(a.json_data, '$."[0]"') = e.id
LEFT JOIN F
ON get_json_string(a.json_data, '$."[0]"') = f.id
LEFT JOIN G
ON get_json_string(a.json_data, '$."[0]"') = g.id
WHERE a.device_id IS NOT NULL
AND a.`status` = 'COMPLETED'
AND b.device_type IS NOT NULL
GROUP BY b.device_type
,a.device_id
,b.engine_id
,b.secondary_engine_id
, LEFT(b.sale_date, 10)
) t_a_0
GROUP BY t_a_0.`device_id`
```
The statement was modified to the following, which restored normalcy:
```sql
SELECT t_a_0.`device_id` AS f_ax_0
FROM
(
SELECT b.device_type
,A.device_id
,b.engine_id
,b.secondary_engine_id
-- modified
--, LEFT(b.sale_date, 10)
,COUNT(1)
FROM A
LEFT JOIN B
ON get_json_string(a.json_data, '$."[0]"') = b.id AND b.id = 'DL11'
LEFT JOIN C
ON a.device_id = c.id
LEFT JOIN D
ON get_json_string(a.json_data, '$."[0]"') = e.id
LEFT JOIN F
ON get_json_string(a.json_data, '$."[0]"') = f.id
LEFT JOIN G
ON get_json_string(a.json_data, '$."[0]"') = g.id
WHERE a.device_id IS NOT NULL
AND a.`status` = 'COMPLETED'
AND b.device_type IS NOT NULL
GROUP BY b.device_type
,a.device_id
,b.engine_id
,b.secondary_engine_id
-- modified
--, LEFT(b.sale_date, 10)
) t_a_0
GROUP BY t_a_0.`device_id`
```
Alternatively, changing it to the following code also restored normal
operation:
```sql
SELECT t_a_0.`device_id` AS f_ax_0
FROM
(
SELECT t.device_type
,t.device_id
,t.engine_id
,t.secondary_engine_id
,sd
,count(1)
FROM
(
SELECT b.device_type
,A.device_id
,b.engine_id
,b.secondary_engine_id
,LEFT(b.sale_date,10) AS sd
FROM A
LEFT JOIN B
ON get_json_string(a.json_data, '$."[0]"') = b.id AND b.id =
'DL11'
LEFT JOIN C
ON a.device_id = c.id
LEFT JOIN D
ON get_json_string(a.json_data, '$."[0]"') = e.id
LEFT JOIN F
ON get_json_string(a.json_data, '$."[0]"') = f.id
LEFT JOIN G
ON get_json_string(a.json_data, '$."[0]"') = g.id
WHERE a.device_id IS NOT NULL
AND a.`status` = 'COMPLETED'
AND b.device_type IS NOT NULL
)t
GROUP BY t.device_type
,t.device_id
,t.engine_id
,t.sd
) t_a_0
GROUP BY t_a_0.`device_id`
```
The same approach was applied to another SQL statement involving Distinct.
As a result, we made the described modifications to all SQL aggregation
operations, ultimately restoring normal operation.
Based on these results, we speculate that certain function operations cannot
be performed on columns used in GROUP BY or DISTINCT when executing
aggregations, such as `GROUP BY xxx, LEFT(b.sale_date, 10)` or `DISTINCT xxx,
LEFT(b.sale_date, 10)`. These statements, for some reason, lead to crashes in
the library.
Below are some error logs. Although the directory name in the logs is
`apache-doris-be-1.1.2-bin-x86_64`, we actually replaced the files in the
directory, and the actual version is 2.1.0.
```
*** Query id: dad927596e224b47-9d2d83f1f7e04afe ***
*** tablet id: 0 ***
*** Aborted at 1711951667 (unix time) try "date -d @1711951667" if you are
using GNU date ***
*** Current BE git commitID: 91efb6a43d ***
*** SIGSEGV unknown detail explain (@0x0) received by PID 30330 (TID 30602
OR 0x7f382d9a8700) from PID 0; stack trace: ***
0# doris::signal::(anonymous namespace)::FailureSignalHandler(int,
siginfo_t*, void*) at
/home/zcp/repo_center/doris_release/doris/be/src/common/signal_handler.h:417
1# os::Linux::chained_handler(int, siginfo*, void*) in
/usr/java/jdk1.8.0_151/jre/lib/amd64/server/libjvm.so
2# JVM_handle_linux_signal in
/usr/java/jdk1.8.0_151/jre/lib/amd64/server/libjvm.so
3# signalHandler(int, siginfo*, void*) in
/usr/java/jdk1.8.0_151/jre/lib/amd64/server/libjvm.so
4# 0x00007F38D6904400 in /lib64/libc.so.6
5# doris::vectorized::VExprContext::execute(doris::vectorized::Block*,
int*) at
/home/zcp/repo_center/doris_release/doris/be/src/vec/exprs/vexpr_context.cpp:50
6#
doris::pipeline::JoinProbeLocalState<doris::pipeline::HashJoinSharedState,
doris::pipeline::HashJoinProbeLocalState>::_build_output_block(doris::vectorized::Block*,
doris::vectorized::Block*, bool) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/exec/join_probe_operator.cpp:127
7#
doris::pipeline::HashJoinProbeLocalState::filter_data_and_build_output(doris::RuntimeState*,
doris::vectorized::Block*, bool*, doris::vectorized::Block*, bool) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/exec/hashjoin_probe_operator.cpp:433
8# doris::pipeline::HashJoinProbeOperatorX::pull(doris::RuntimeState*,
doris::vectorized::Block*, bool*) const at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/exec/hashjoin_probe_operator.cpp:364
9#
doris::pipeline::StatefulOperatorX<doris::pipeline::HashJoinProbeLocalState>::get_block(doris::RuntimeState*,
doris::vectorized::Block*, bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/operator.cpp:459
10#
doris::pipeline::OperatorXBase::get_block_after_projects(doris::RuntimeState*,
doris::vectorized::Block*, bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/operator.cpp:210
11#
doris::pipeline::StatefulOperatorX<doris::pipeline::DistinctStreamingAggLocalState>::get_block(doris::RuntimeState*,
doris::vectorized::Block*, bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/operator.cpp:444
12#
doris::pipeline::OperatorXBase::get_block_after_projects(doris::RuntimeState*,
doris::vectorized::Block*, bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/operator.cpp:210
13# doris::pipeline::PipelineXTask::execute(bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/pipeline_x_task.cpp:274
14# doris::pipeline::TaskScheduler::_do_work(unsigned long) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/task_scheduler.cpp:334
15# doris::ThreadPool::dispatch_thread() in
/opt/soft/apache-doris-be-1.1.2-bin-x86_64/be/lib/doris_be
16# doris::Thread::supervise_thread(void*) at
/home/zcp/repo_center/doris_release/doris/be/src/util/thread.cpp:499
17# start_thread in /lib64/libpthread.so.0
18# clone in /lib64/libc.so.6w
```
```
*** Query id: ef491c0a7c2e4447-97d2f477293425fc ***
*** tablet id: 0 ***
*** Aborted at 1711950862 (unix time) try "date -d @1711950862" if you are
using GNU date ***
*** Current BE git commitID: 91efb6a43d ***
*** SIGSEGV unknown detail explain (@0x0) received by PID 26242 (TID 26641
OR 0x7f8cb70a2700) from PID 0; stack trace: ***
0# doris::signal::(anonymous namespace)::FailureSignalHandler(int,
siginfo_t*, void*) at
/home/zcp/repo_center/doris_release/doris/be/src/common/signal_handler.h:417
1# os::Linux::chained_handler(int, siginfo*, void*) in
/usr/java/jdk1.8.0_151/jre/lib/amd64/server/libjvm.so
2# JVM_handle_linux_signal in
/usr/java/jdk1.8.0_151/jre/lib/amd64/server/libjvm.so
3# signalHandler(int, siginfo*, void*) in
/usr/java/jdk1.8.0_151/jre/lib/amd64/server/libjvm.so
4# 0x00007F8D62F6D400 in /lib64/libc.so.6
5# doris::vectorized::VExprContext::execute(doris::vectorized::Block*,
int*) at
/home/zcp/repo_center/doris_release/doris/be/src/vec/exprs/vexpr_context.cpp:50
6#
doris::pipeline::JoinProbeLocalState<doris::pipeline::HashJoinSharedState,
doris::pipeline::HashJoinProbeLocalState>::_build_output_block(doris::vectorized::Block*,
doris::vectorized::Block*, bool) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/exec/join_probe_operator.cpp:127
7#
doris::pipeline::HashJoinProbeLocalState::filter_data_and_build_output(doris::RuntimeState*,
doris::vectorized::Block*, bool*, doris::vectorized::Block*, bool) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/exec/hashjoin_probe_operator.cpp:433
8# doris::pipeline::HashJoinProbeOperatorX::pull(doris::RuntimeState*,
doris::vectorized::Block*, bool*) const at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/exec/hashjoin_probe_operator.cpp:364
9#
doris::pipeline::StatefulOperatorX<doris::pipeline::HashJoinProbeLocalState>::get_block(doris::RuntimeState*,
doris::vectorized::Block*, bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/operator.cpp:459
10#
doris::pipeline::OperatorXBase::get_block_after_projects(doris::RuntimeState*,
doris::vectorized::Block*, bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/operator.cpp:210
11#
doris::pipeline::StatefulOperatorX<doris::pipeline::DistinctStreamingAggLocalState>::get_block(doris::RuntimeState*,
doris::vectorized::Block*, bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/operator.cpp:444
12#
doris::pipeline::OperatorXBase::get_block_after_projects(doris::RuntimeState*,
doris::vectorized::Block*, bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/operator.cpp:210
13# doris::pipeline::PipelineXTask::execute(bool*) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/pipeline_x/pipeline_x_task.cpp:274
14# doris::pipeline::TaskScheduler::_do_work(unsigned long) at
/home/zcp/repo_center/doris_release/doris/be/src/pipeline/task_scheduler.cpp:334
15# doris::ThreadPool::dispatch_thread() in
/opt/soft/apache-doris-be-1.1.2-bin-x86_64/be/lib/doris_be
16# doris::Thread::supervise_thread(void*) at
/home/zcp/repo_center/doris_release/doris/be/src/util/thread.cpp:499
17# start_thread in /lib64/libpthread.so.0
18# clone in /lib64/libc.so.6
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
