insert overwirte select子语句中包含递归cte时会立即拖垮doris be

Question

实例sql：
WITH RECURSIVE
table_lineage AS (
SELECT DISTINCT
SUBSTRING_INDEX(field_id, '.', 2) AS downstream_tbl,
SUBSTRING_INDEX(depend_field_id, '.', 2) AS upstream_tbl
FROM dgp_mysql_redan.redan.dgp_dataset_field_lineage
),

    upstream_closure AS (
        SELECT downstream_tbl AS table_name,
               upstream_tbl   AS depend_table,
               CAST(1 AS BIGINT) AS hop
        FROM table_lineage
     
        UNION ALL
     
        SELECT u.table_name,
               tl.upstream_tbl,
               u.hop + 1
        FROM upstream_closure u
        JOIN table_lineage tl ON u.depend_table = tl.downstream_tbl
    ),
     
    downstream_closure AS (
        SELECT upstream_tbl     AS table_name,
               downstream_tbl   AS depend_table,
               CAST(1 AS BIGINT) AS hop
        FROM table_lineage
     
        UNION ALL
     
        SELECT d.table_name,
               tl.downstream_tbl,
               d.hop + 1
        FROM downstream_closure d
        JOIN table_lineage tl ON d.depend_table = tl.upstream_tbl
    )
     
    SELECT table_name, depend_table, hop, 'up' AS direction
    FROM upstream_closure
     
    UNION ALL
     
    SELECT table_name, depend_table, hop, 'down' AS direction
    FROM downstream_closure
     
    ORDER BY table_name, direction, hop, depend_table;

阿渊@SelectDB (没回帖直接加我主页微信) · Answer

show variables like "%cte_max_recursion_depth%";

看下这个session var的值是多少，拖垮BE主要的现象是啥，是内存耗尽？

xycheng · Answer

cte_max_recursion_depth= 20

保持日志：
9de3d22bb77d5771|fragment_id=1
2026-07-07T14:12:49.836118616Z I20260707 22:12:49.835949 509 query_context.cpp:256] Query f16aedf7fa4340d0-9de3d22bb77d5771 deconstructed, mem_tracker: deregister query/load memory tracker, queryId=f16aedf7fa4340d0-9de3d22bb77d5771, Limit=4.00 GB, CurrUsed=0, PeakUsed=33.55 MB
I20260707 22:12:49.844324 509 task_worker_pool.cpp:428] successfully submit task|type=PUBLISH_VERSION|signature=35790759
I20260707 22:12:49.845001 509 engine_publish_version_task.cpp:492] publish version successfully on tablet, table_id=1776129874593, tablet=1783312548918, transaction_id=35790759, version=2, num_rows=12228, res=[OK], cost: 441(us)
2026-07-07T14:12:49.845181710Z I20260707 22:12:49.845077 509 engine_publish_version_task.cpp:319] finish to publish version on transaction.transaction_id=35790759, cost(us): 657, error_tablet_size=0, res=[OK]
2026-07-07T14:12:49.845241042Z I20260707 22:12:49.845149 509 task_worker_pool.cpp:2232] successfully publish version|signature=35790759|transaction_id=35790759|tablets_num=1|cost(s)=0
I20260707 22:12:49.864475 509 task_worker_pool.cpp:428] successfully submit task|type=UPDATE_VISIBLE_VERSION|signature=-1
I20260707 22:12:50.063609 509 backend_service.cpp:1304] query for dictionary status, return 0 rows
I20260707 22:12:50.450639 509 load_channel_mgr.cpp:257] cleaning timed out load channels
I20260707 22:12:50.977213 509 load_stream_writer.cpp:174] file 46 path /opt/apache-doris/be/storage/data/70/1783312548522/667861511/020000000000015631479da2948cac63d49c6b710a3974a6_46.dat closed, written 35074114 bytes, file type is 1
I20260707 22:12:52.121069 509 fragment_mgr.cpp:482] query_id: 65d54596e9e948d6-94970d85e68109e9, coord_addr: TNetworkAddress(hostname=doriscluster-fe-1.doriscluster-fe-internal.doris.svc.cluster.local, port=9020), total fragment num on current host: 96, fe process uuid: 1783068970318, query type: LOAD, report audit fe:TNetworkAddress(hostname=doriscluster-fe-0.doriscluster-fe-internal.doris.svc.cluster.local, port=9020), use wg:1766733138325,normal
I20260707 22:12:52.127384 509 fragment_mgr.cpp:482] query_id: 8ce6d3e28d5c4c68-a098a959adf040c3, coord_addr: TNetworkAddress(hostname=doriscluster-fe-1.doriscluster-fe-internal.doris.svc.cluster.local, port=9020), total fragment num on current host: 209, fe process uuid: 1783068970318, query type: LOAD, report audit fe:TNetworkAddress(hostname=doriscluster-fe-0.doriscluster-fe-internal.doris.svc.cluster.local, port=9020), use wg:1766733138325,normal
I20260707 22:12:52.626933 509 daemon.cpp:231] sys physical memory 36.00 GB. process memory used 2.57 GB(= 2.57 GB[vm/rss] + 0[reserved] + 0B[waiting_refresh]), limit 28.80 GB, soft limit 25.92 GB. sys available memory 33.89 GB(= 33.89 GB[proc/available] - 0[reserved] - 0B[waiting_refresh]), low water mark 5.40 GB, warning water mark 10.80 GB
0# doris::signal::(anonymous namespace)::FailureSignalHandler(int, siginfo_t*, void*) at /home/zcp/repo_center/doris_release/doris/be/src/common/signal_handler.h:420
1# PosixSignals::chained_handler(int, siginfo*, void*) [clone .part.0] in /usr/lib/jvm/java/lib/server/libjvm.so
2026-07-07T14:12:53.234740453Z 2# JVM_handle_linux_signal in /usr/lib/jvm/java/lib/server/libjvm.so
2026-07-07T14:12:53.234746329Z 3# 0x00007F1594211520 in /lib/x86_64-linux-gnu/libc.so.6
2026-07-07T14:12:53.234752035Z 4# doris::VExpr::create_expr_trees(std::vector const&, std::vector >&) at /home/zcp/repo_center/doris_release/doris/be/src/exprs/vexpr.cpp:639
5# doris::RecCTEAnchorSinkOperatorX::init(doris::TPlanNode const&, doris::RuntimeState*) at /home/zcp/repo_center/doris_release/doris/be/src/exec/operator/rec_cte_anchor_sink_operator.cpp:41
2026-07-07T14:12:53.234766980Z 6# doris::PipelineFragmentContext::_create_operator(doris::ObjectPool*, doris::TPlanNode const&, doris::DescriptorTbl const&, std::shared_ptr&, std::shared_ptr&, int, int, bool, bool, std::shared_ptr&) at /home/zcp/repo_center/doris_release/doris/be/src/exec/pipeline/pipeline_fragment_context.cpp:1742
2026-07-07T14:12:53.234774461Z 7# doris::PipelineFragmentContext::_create_tree_helper(doris::ObjectPool*, std::vector const&, doris::DescriptorTbl const&, std::shared_ptr, int*, std::shared_ptr, std::shared_ptr&, int, bool, bool) at /home/zcp/repo_center/doris_release/doris/be/src/exec/pipeline/pipeline_fragment_context.cpp:712
2026-07-07T14:12:53.234782316Z 8# doris::PipelineFragmentContext::_build_pipelines(doris::ObjectPool, doris::DescriptorTbl const&, std::shared_ptr, std::shared_ptr) at /home/zcp/repo_center/doris_release/doris/be/src/exec/pipeline/pipeline_fragment_context.cpp:684
2026-07-07T14:12:53.234790423Z 9# doris::PipelineFragmentContext::_build_and_prepare_full_pipeline(doris::ThreadPool) at /home/zcp/repo_center/doris_release/doris/be/src/exec/pipeline/pipeline_fragment_context.cpp:291
2026-07-07T14:12:53.234796436Z 10# doris::PipelineFragmentContext::prepare(doris::ThreadPool*) at /home/zcp/repo_center/doris_release/doris/be/src/exec/pipeline/pipeline_fragment_context.cpp:403
2026-07-07T14:12:53.234802381Z 11# doris::FragmentMgr::exec_plan_fragment(doris::TPipelineFragmentParams const&, doris::QuerySource, std::function const&, doris::TPipelineFragmentParamsList const&) in /opt/apache-dor
is/be/lib/doris_be
2026-07-07T14:12:53.234808938Z 12# doris::FragmentMgr::exec_plan_fragment(doris::TPipelineFragmentParams const&, doris::QuerySource, doris::TPipelineFragmentParamsList const&) at /home/zcp/repo_center/doris_release/doris/be/src/runtime/fragment_mgr.cpp:386
2026-07-07T14:12:53.234815248Z 13# doris::PInternalService::_exec_plan_fragment_impl(std::__cxx11::basic_string const&, doris::PFragmentRequestVersion, bool, std::function const&) in /opt/apache-doris/be/lib/doris_be
2026-07-07T14:12:53.234830051Z 14# doris::PInternalService::_exec_plan_fragment_in_pthread(google::protobuf::RpcController*, doris::PExecPlanFragmentRequest const*, doris::PExecPlanFragmentResult*, google::protobuf::Closure*) in /opt/apache-doris/be/lib/doris_be
2026-07-07T14:12:53.234836280Z 15# doris::WorkThreadPool::work_thread(int) at /home/zcp/repo_center/doris_release/doris/be/src/util/work_thread_pool.hpp:159
2026-07-07T14:12:53.234842316Z 16# execute_native_thread_routine in /opt/apache-doris/be/lib/doris_be
2026-07-07T14:12:53.234847872Z 17# 0x00007F1594263AC3 in /lib/x86_64-linux-gnu/libc.so.6
2026-07-07T14:12:53.234853462Z 18# 0x00007F15942F58C0 in /lib/x86_64-linux-gnu/libc.so.6
2026-07-07T14:12:53.234858886Z
/opt/apache-doris/be/bin/start_be.sh: line 608: 509 Segmentation fault (core dumped) ${LIMIT:+${LIMIT}} "${DORIS_HOME}/lib/doris_be" "$@" 2>> "${LOG_DIR}/be.out" < /dev/null
[Tue Jul 7 22:12:53 CST 2026] run post_exit
[Tue Jul 7 22:12:53 CST 2026] [info] read 'be.conf' config [ LOG_DIR: /opt/apache-doris/be/log]

insert overwirte select子语句中包含递归cte时会立即拖垮doris be

2 Answers