3.3.6 be crash: starrocks::pipeline::SpillProcessOperator::pull_chunk

colagy · 2025年03月11日 02:06

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述
be crash

【背景】做过哪些操作？
【业务影响】
【是否存算分离】
【StarRocks版本】例如：3.3.6
【集群规模】例如：3fe（3 follower+0observer）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：16C/64G/万兆
【联系方式】为了在解决问题过程中能及时联系到您获取一些日志信息，请补充下您的联系方式，例如：社区群16-colagy或者邮箱，谢谢
【附件】

fe.log/beINFO/相应截图

W20250311 09:40:50.794377 139986770675264 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:40:50.895560 139986770675264 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:40:50.997820 139986770675264 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:41:02.174420 139984684504640 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:41:23.488083 139986737104448 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:41:33.100276 139986560857664 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:41:39.649083 139986644784704 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:43:15.371214 139986728711744 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:43:31.808899 139984684504640 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:43:52.354570 139985869080128 query_context.cpp:675] Retrying ReportExecStatus: No more data to read.
W20250311 09:43:54.942871 139984684504640 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:43:55.059100 139984684504640 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:44:10.840282 139984709682752 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:44:10.956387 139984709682752 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:44:23.797423 139986611213888 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:44:23.898188 139986611213888 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:44:23.999268 139986611213888 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:44:52.366995 139985869080128 query_context.cpp:675] Retrying ReportExecStatus: No more data to read.
W20250311 09:45:01.784686 139984709682752 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:45:22.387358 139985869080128 query_context.cpp:675] Retrying ReportExecStatus: No more data to read.
W20250311 09:45:52.583482 139985869080128 query_context.cpp:675] Retrying ReportExecStatus: No more data to read.
W20250311 09:46:02.863743 139986636392000 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:46:09.690354 139984692897344 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:46:09.806950 139984692897344 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:47:01.423710 139984684504640 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:47:01.540110 139984684504640 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:47:08.505173 139985885865536 runtime_filter_worker.cpp:333] brpc failed, error=RPC call is timed out, error_text=[E1008]Reached timeout=400ms @192.168.18.222:8060
W20250311 09:47:08.505367 139985885865536 runtime_filter_worker.cpp:333] brpc failed, error=RPC call is timed out, error_text=[E1008]Reached timeout=400ms @192.168.18.224:8060
W20250311 09:47:32.242575 139986602821184 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:47:52.461264 139986485323328 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:48:03.148128 139986527286848 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:48:03.249068 139986527286848 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:49:12.468167 139816349894208 data_dir.cpp:383] could not find tablet id: 13493761 for rowset: 020000000220c136e746f725ea0ddadbf6da3519f0637c95, skip loading this rowset
W20250311 09:49:17.430282 139811735868992 heartbeat_server.cpp:205] pengze-node00 not equal to to backend localhost 192.168.18.220
W20250311 09:49:59.125195 139811613533760 engine_clone_task.cpp:403] Fail to make snapshot from pengze-node02: Not found: get_rowsets_for_snapshot: no version to clone tablet:13442909 #version:97 [514 577@96 577] #pending:0 request_version:578, tablet:13442909
W20250311 09:49:59.125363 139811596748352 engine_clone_task.cpp:403] Fail to make snapshot from pengze-node01: Not found: get_rowsets_for_snapshot: no version to clone tablet:13442933 #version:74 [530 577@73 577] #pending:0 request_version:578, tablet:13442933
W20250311 09:49:59.125407 139811672282688 engine_clone_task.cpp:403] Fail to make snapshot from pengze-node01: Not found: get_rowsets_for_snapshot: no version to clone tablet:13442925 #version:73 [530 577@72 577] #pending:0 request_version:578, tablet:13442925
W20250311 09:52:13.777864 139812514285120 storage_engine.cpp:1124] tablet not found, remove rowset meta, rowset_id=020000000220c136e746f725ea0ddadbf6da3519f0637c95 tablet: 13493761
W20250311 09:52:13.780432 139812514285120 storage_engine.cpp:1144] traverse_rowset_meta and remove 81/163 invalid rowset metas, path:/dbdata1 duration:2ms
W20250311 09:54:00.180397 139813891724864 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:55:20.195493 139813765834304 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node02, port=9020), reason=No more data to read.
W20250311 09:55:20.296716 139813765834304 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node02, port=9020), reason=No more data to read.
W20250311 09:56:56.733431 139813376099904 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.
W20250311 09:56:57.068602 139813334136384 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 09:56:57.511903 139813325743680 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node02, port=9020), reason=No more data to read.
W20250311 09:57:34.357294 139813975651904 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node02, port=9020), reason=No more data to read.
W20250311 09:57:34.457910 139813975651904 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node02, port=9020), reason=No more data to read.
W20250311 09:58:44.679638 139811799860800 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node01, port=9020), reason=No more data to read.
W20250311 10:00:02.838293 139813858154048 thrift_rpc_helper.cpp:129] Rpc error: FE RPC failure, address=TNetworkAddress(hostname=pengze-node00, port=9020), reason=No more data to read.

be crash
- be.out

3.3.6 RELEASE (build 8f01cfa)
query_id:90705366-fe1a-11ef-ae94-024234d8287d, fragment_instance:90705366-fe1a-11ef-ae94-024234d828eb
tracker:process consumption: 19217561592
tracker:jemalloc_metadata consumption: 601174560
tracker:jemalloc_fragmentation consumption: 336564736
tracker:query_pool consumption: 11133394080
tracker:query_pool/connector_scan consumption: 0
tracker:load consumption: 763392
tracker:metadata consumption: 661226991
tracker:tablet_metadata consumption: 162826852
tracker:rowset_metadata consumption: 41567547
tracker:segment_metadata consumption: 21636041
tracker:column_metadata consumption: 435196551
tracker:tablet_schema consumption: 1169644
tracker:segment_zonemap consumption: 10098957
tracker:short_key_index consumption: 245579
tracker:column_zonemap_index consumption: 24667199
tracker:ordinal_index consumption: 211970168
tracker:bitmap_index consumption: 0
tracker:bloom_filter_index consumption: 0
tracker:compaction consumption: 0
tracker:schema_change consumption: 0
tracker:column_pool consumption: 0
tracker:page_cache consumption: 7222224
tracker:jit_cache consumption: 9584
tracker:update consumption: 35321512
tracker:chunk_allocator consumption: 0
tracker:passthrough consumption: 0
tracker:clone consumption: 0
tracker:consistency consumption: 0
tracker:datacache consumption: 0
tracker:replication consumption: 0
*** Aborted at 1741657717 (unix time) try "date -d @1741657717" if you are using GNU date ***
PC: @     0x7f51ec288aca (/usr/lib/x86_64-linux-gnu/libc.so.6+0x1a0ac9)
*** SIGSEGV (@0x7f51e57ff000) received by PID 25 (TID 0x7f5145bf4640) from PID 18446744073264951296; stack trace: ***
    @     0x7f51ec181ee8 (/usr/lib/x86_64-linux-gnu/libc.so.6+0x99ee7)
    @          0xa16e1c9 google::(anonymous namespace)::FailureSignalHandler(int, siginfo_t*, void*)
    @     0x7f51ec12a520 (/usr/lib/x86_64-linux-gnu/libc.so.6+0x4251f)
    @     0x7f51ec288aca (/usr/lib/x86_64-linux-gnu/libc.so.6+0x1a0ac9)
    @          0x5472aed starrocks::FixedLengthColumnBase<signed char>::append(starrocks::Column const&, unsigned long, unsigned long)
    @          0x53b194a starrocks::Chunk::append(starrocks::Chunk const&, unsigned long, unsigned long)
    @          0x58b5989 starrocks::spill::OrderedMemTable::append(std::shared_ptr<starrocks::Chunk>)
    @          0x5827e8c starrocks::Status starrocks::spill::RawSpillerWriter::spill<starrocks::spill::IOTaskExecutor, starrocks::spill::ResourceMemTrackerGuard<std::weak_ptr<starrocks::pipeline::QueryContext>, std::weak_ptr<starrocks::spill::Spiller> >&>(starrocks::RuntimeState*,¹^Q
    @          0x582a4ad starrocks::Status starrocks::spill::Spiller::spill<starrocks::spill::IOTaskExecutor, starrocks::spill::ResourceMemTrackerGuard<std::weak_ptr<starrocks::pipeline::QueryContext>, std::weak_ptr<starrocks::spill::Spiller> > >(starrocks::RuntimeState*, std::sha¹^Q
    @          0x75cfa3c starrocks::pipeline::SpillProcessOperator::pull_chunk(starrocks::RuntimeState*)
    @          0x5396c5f starrocks::pipeline::PipelineDriver::process(starrocks::RuntimeState*, int)
    @          0x7d94683 starrocks::pipeline::GlobalDriverExecutor::_worker_thread()
    @          0x8aa57d2 starrocks::ThreadPool::dispatch_thread()
    @          0x8a9db09 starrocks::Thread::supervise_thread(void*)
    @     0x7f51ec17cac3 (/usr/lib/x86_64-linux-gnu/libc.so.6+0x94ac2)
    @     0x7f51ec20da04 clone

coredump，如何获取coredump
外表查询报错
- be.out和fe.warn.log

Star_Cao · 2025年03月11日 03:08

提供一下query_id为 90705366-fe1a-11ef-ae94-024234d8287d 的sql的具体信息

colagy · 2025年03月11日 03:25

/*"sqlid" = '1741657551044-qyd0',"tag" = 'http-nio-29090-exec-3',"fabric-jobId" = '18306a30-3082-bebe-5c7e-688041d8d100'*/ SELECT COUNT(*) AS `_count` FROM ((SELECT `sub_amount_rate` FROM (SELECT CASE WHEN CASE WHEN (COUNT(`sub_paid_amount`) OVER (PARTITION BY `sale_order_id`)) > 0 THEN CAST(SUM(`sub_paid_amount`) OVER (PARTITION BY `sale_order_id`) AS DECIMAL(38, 8)) ELSE CAST(NULL AS DECIMAL(38, 8)) END = 0 THEN CAST((1 / (COUNT(1) OVER (PARTITION BY `sale_order_id`))) AS DECIMAL(38, 6)) ELSE (`sub_paid_amount` / CASE WHEN (COUNT(`sub_paid_amount`) OVER (PARTITION BY `sale_order_id`)) > 0 THEN CAST(SUM(`sub_paid_amount`) OVER (PARTITION BY `sale_order_id`) AS DECIMAL(38, 8)) ELSE CAST(NULL AS DECIMAL(38, 8)) END) END AS `sub_amount_rate` FROM (SELECT `t21`.`sale_order_id`, `t21`.`sub_paid_amount` FROM ((SELECT `t20`.`sale_order_id`, `t20`.`so_sale_order_id`, `t20`.`sub_paid_amount` FROM ((SELECT `t19`.`trade_no` AS `sale_order_id`, `t19`.`so_sale_order_id`, `t19`.`sub_paid_amount` FROM ((SELECT `t18`.`trade_no`, `t18`.`logistics_code`, `t18`.`so_sale_order_id`, `t18`.`sub_paid_amount` FROM ((SELECT `t17`.`trade_no`, `t17`.`warehouse_no`, `t17`.`logistics_code`, `t17`.`so_sale_order_id`, `t17`.`sub_paid_amount` FROM ((SELECT `trade_no`, `shop_no`, `warehouse_no`, `logistics_code`, `so_sale_order_id`, `sub_paid_amount` FROM (SELECT `trade_no`, `shop_no`, `warehouse_no`, `logistics_code`, CAST(CAST(`order_detail` AS JSON) -> 'rec_id' AS VARCHAR) AS `sale_order_detail_id`, CAST(CAST(`order_detail` AS JSON) -> 'src_tid' AS VARCHAR) AS `so_sale_order_id`, CAST(CAST(`order_detail` AS JSON) -> 'src_oid' AS VARCHAR) AS `so_sale_order_detail_id`, CAST(CAST(`order_detail` AS JSON) -> 'api_goods_id' AS VARCHAR) AS `product_id`, CAST(CAST(`order_detail` AS JSON) -> 'api_spec_id' AS VARCHAR) AS `spec_id`, CAST(CAST(`order_detail` AS JSON) -> 'suite_no' AS VARCHAR) AS `suite_code`, CAST(CAST(`order_detail` AS JSON) -> 'suite_name' AS VARCHAR) AS `suite_name`, CAST(CAST(`order_detail` AS JSON) -> 'goods_no' AS VARCHAR) AS `product_code`, CAST(CAST(`order_detail` AS JSON) -> 'goods_name' AS VARCHAR) AS `product_name`, CAST(CAST(`order_detail` AS JSON) -> 'spec_no' AS VARCHAR) AS `spec_code`, CAST(CAST(`order_detail` AS JSON) -> 'spec_name' AS VARCHAR) AS `spec_name`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'num' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_num`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'price' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_price`, (CAST(CAST(CAST(`order_detail` AS JSON) -> 'num' AS VARCHAR) AS DECIMAL(38, 8)) * CAST(CAST(CAST(`order_detail` AS JSON) -> 'price' AS VARCHAR) AS DECIMAL(38, 8))) AS `sale_amount`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'discount' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_discount`, CASE WHEN CAST(CAST(`order_detail` AS JSON) -> 'gift_type' AS VARCHAR) = '0' THEN '?' ELSE '?' END AS `is_gift`, CAST(CAST(`order_detail` AS JSON) -> 'remark' AS VARCHAR) AS `remark`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'paid' AS VARCHAR) AS DECIMAL(38, 8)) AS `sub_paid_amount` FROM (SELECT `trade_id`, `trade_no`, `modified`, `shop_no`, `warehouse_no`, `logistics_code`, `order_detail`, `$99`, ROW_NUMBER() OVER (PARTITION BY `trade_id`, `$99` ORDER BY `modified` IS NULL DESC, `modified` DESC) AS `w0$o0` FROM ((SELECT `trade_id` AS `trade_id`, `trade_no` AS `trade_no`, `modified` AS `modified`, `shop_no` AS `shop_no`, `warehouse_no` AS `warehouse_no`, `logistics_code` AS `logistics_code`, `order_detail` AS `order_detail`, `$99` FROM (SELECT `trade_id`, `trade_no`, `modified`, `shop_no`, `warehouse_no`, `logistics_code`, `order_detail`, CAST(CAST(`order_detail` AS JSON) -> 'rec_id' AS VARCHAR) AS `$99` FROM (SELECT `t4`.`trade_id`, `t4`.`trade_no`, `t4`.`modified`, `t4`.`shop_no`, `t4`.`warehouse_no`, `t4`.`logistics_code`, CASE WHEN `t5`.`value` IS NULL THEN NULL ELSE `t5`.`value` END AS `order_detail` FROM ((SELECT `t0`.`trade_id`, `t0`.`trade_no`, `t0`.`modified`, `t0`.`shop_no`, `t0`.`warehouse_no`, `t0`.`logistics_code`, `t0`.`detail_list` FROM ((SELECT `ods_api_wdtqjqm_sale_order_info_f`.`trade_id`, `ods_api_wdtqjqm_sale_order_info_f`.`trade_no`, `ods_api_wdtqjqm_sale_order_info_f`.`pay_time`, `ods_api_wdtqjqm_sale_order_info_f`.`modified`, `ods_api_wdtqjqm_sale_order_info_f`.`shop_no`, `ods_api_wdtqjqm_sale_order_info_f`.`warehouse_no`, `ods_api_wdtqjqm_sale_order_info_f`.`logistics_code`, `ods_api_wdtqjqm_sale_order_info_f`.`detail_list`, `ods_api_wdtqjqm_sale_order_history_info_f`.`$f0`, `ods_api_wdtqjqm_sale_order_history_info_f`.`$f1`, `ods_api_wdtqjqm_sale_order_info_f`.`trade_id` AS `trade_id0` FROM ((SELECT `trade_id` AS `trade_id`, `trade_no` AS `trade_no`, `pay_time` AS `pay_time`, `modified` AS `modified`, `shop_no` AS `shop_no`, `warehouse_no` AS `warehouse_no`, `logistics_code` AS `logistics_code`, `detail_list` AS `detail_list` FROM (SELECT `ods_api_wdtqjqm_sale_order_info_f`.`trade_id`, `ods_api_wdtqjqm_sale_order_info_f`.`trade_no`, `ods_api_wdtqjqm_sale_order_info_f`.`pay_time`, `ods_api_wdtqjqm_sale_order_info_f`.`modified`, `ods_api_wdtqjqm_sale_order_info_f`.`shop_no`, `ods_api_wdtqjqm_sale_order_info_f`.`warehouse_no`, `ods_api_wdtqjqm_sale_order_info_f`.`logistics_code`, `ods_api_wdtqjqm_sale_order_info_f`.`detail_list` FROM `cubeappdata`.`ods_api_wdtqjqm_sale_order_info_f`) AS `ods_api_wdtqjqm_sale_order_info_f` WHERE `pay_time` >= '2023-01-01') AS `ods_api_wdtqjqm_sale_order_info_f` INNER JOIN (SELECT COUNT(*) AS `$f0`, COUNT(`ods_api_wdtqjqm_sale_order_history_info_f`.`trade_id`) AS `$f1` FROM `cubeappdata`.`ods_api_wdtqjqm_sale_order_history_info_f` HAVING (COUNT(*) = 0 OR COUNT(`ods_api_wdtqjqm_sale_order_history_info_f`.`trade_id`) >= COUNT(*))) AS `ods_api_wdtqjqm_sale_order_history_info_f` ON TRUE) WHERE `ods_api_wdtqjqm_sale_order_history_info_f`.`$f0` = 0 OR `ods_api_wdtqjqm_sale_order_info_f`.`trade_id` IS NOT NULL) AS `t0` LEFT JOIN (SELECT `ods_api_wdtqjqm_sale_order_history_info_f`.`trade_id`, MIN(1) AS `$f1` FROM `cubeappdata`.`ods_api_wdtqjqm_sale_order_history_info_f` GROUP BY `ods_api_wdtqjqm_sale_order_history_info_f`.`trade_id`) AS `ods_api_wdtqjqm_sale_order_history_info_f0` ON `t0`.`trade_id0` = `ods_api_wdtqjqm_sale_order_history_info_f0`.`trade_id`) WHERE `t0`.`$f0` = 0 OR `ods_api_wdtqjqm_sale_order_history_info_f0`.`$f1` IS NULL) AS `t4` INNER JOIN JSON_EACH(CASE WHEN CAST(CASE WHEN `detail_list` = '[]' THEN NULL ELSE `detail_list` END AS JSON) IS NULL THEN '{"mock":null}' ELSE CAST(CASE WHEN `detail_list` = '[]' THEN NULL ELSE `detail_list` END AS JSON) END) AS `t5` ON TRUE)) AS `t6`) AS `t9`) UNION ALL (SELECT `trade_id` AS `trade_id`, `trade_no` AS `trade_no`, `modified` AS `modified`, `shop_no` AS `shop_no`, `warehouse_no` AS `warehouse_no`, `logistics_code` AS `logistics_code`, `order_detail` AS `order_detail`, `$99` FROM (SELECT `trade_id`, `trade_no`, `modified`, `shop_no`, `warehouse_no`, `logistics_code`, `order_detail`, CAST(CAST(`order_detail` AS JSON) -> 'rec_id' AS VARCHAR) AS `$99` FROM (SELECT `ods_api_wdtqjqm_sale_order_history_info_f1`.`trade_id`, `ods_api_wdtqjqm_sale_order_history_info_f1`.`trade_no`, `ods_api_wdtqjqm_sale_order_history_info_f1`.`modified`, `ods_api_wdtqjqm_sale_order_history_info_f1`.`shop_no`, `ods_api_wdtqjqm_sale_order_history_info_f1`.`warehouse_no`, `ods_api_wdtqjqm_sale_order_history_info_f1`.`logistics_code`, CASE WHEN `t11`.`value` IS NULL THEN NULL ELSE `t11`.`value` END AS `order_detail` FROM ((SELECT `trade_id`, `trade_no`, `modified`, `shop_no`, `warehouse_no`, `logistics_code`, `detail_list` FROM (SELECT `ods_api_wdtqjqm_sale_order_history_info_f`.`trade_id`, `ods_api_wdtqjqm_sale_order_history_info_f`.`trade_no`, `ods_api_wdtqjqm_sale_order_history_info_f`.`pay_time`, `ods_api_wdtqjqm_sale_order_history_info_f`.`modified`, `ods_api_wdtqjqm_sale_order_history_info_f`.`shop_no`, `ods_api_wdtqjqm_sale_order_history_info_f`.`warehouse_no`, `ods_api_wdtqjqm_sale_order_history_info_f`.`logistics_code`, `ods_api_wdtqjqm_sale_order_history_info_f`.`detail_list` FROM `cubeappdata`.`ods_api_wdtqjqm_sale_order_history_info_f`) AS `ods_api_wdtqjqm_sale_order_history_info_f1` WHERE `pay_time` >= '2023-01-01') AS `ods_api_wdtqjqm_sale_order_history_info_f1` INNER JOIN JSON_EACH(CASE WHEN CAST(CASE WHEN `detail_list` = '[]' THEN NULL ELSE `detail_list` END AS JSON) IS NULL THEN '{"mock":null}' ELSE CAST(CASE WHEN `detail_list` = '[]' THEN NULL ELSE `detail_list` END AS JSON) END) AS `t11` ON TRUE)) AS `t12`) AS `t15`)) AS `t17`) AS `t17` WHERE `w0$o0` = 1) AS `t17`) AS `t17` LEFT JOIN (SELECT `ods_rpa_store_mapping_info_f`.`wdt_shop_code` FROM `cubeappdata`.`ods_rpa_store_mapping_info_f` GROUP BY `ods_rpa_store_mapping_info_f`.`wdt_shop_code`) AS `ods_rpa_store_mapping_info_f` ON `t17`.`shop_no` = `ods_rpa_store_mapping_info_f`.`wdt_shop_code`)) AS `t18` LEFT JOIN (SELECT `ods_api_wdtqjqm_warehouse_info_f`.`warehouse_no` FROM `cubeappdata`.`ods_api_wdtqjqm_warehouse_info_f`) AS `ods_api_wdtqjqm_warehouse_info_f` ON `t18`.`warehouse_no` = `ods_api_wdtqjqm_warehouse_info_f`.`warehouse_no`)) AS `t19` LEFT JOIN (SELECT `ods_api_wdtqjqm_logistics_info_f`.`logistics_no` FROM `cubeappdata`.`ods_api_wdtqjqm_logistics_info_f` GROUP BY `ods_api_wdtqjqm_logistics_info_f`.`logistics_no`) AS `ods_api_wdtqjqm_logistics_info_f` ON `t19`.`logistics_code` = `ods_api_wdtqjqm_logistics_info_f`.`logistics_no`)) AS `t20` LEFT JOIN (SELECT `df_remark_match_spec_message_info_f`.`sale_order_id` FROM `cubeappdata`.`df_remark_match_spec_message_info_f` GROUP BY `df_remark_match_spec_message_info_f`.`sale_order_id`) AS `df_remark_match_spec_message_info_f` ON `t20`.`sale_order_id` = `df_remark_match_spec_message_info_f`.`sale_order_id`)) AS `t21` LEFT JOIN (SELECT `zdysdh` FROM ((SELECT `df_sg_jd_in_click_order_list`.`zdysdh` FROM `cubeappdata`.`df_sg_jd_in_click_order_list`) UNION ALL (SELECT `df_sg_jd_out_click_order_list`.`zdysdh` FROM `cubeappdata`.`df_sg_jd_out_click_order_list`)) AS `t22` GROUP BY `zdysdh`) AS `t22` ON `t21`.`so_sale_order_id` = `t22`.`zdysdh`)) AS `t23`) AS `t25`) UNION ALL (SELECT `sub_amount_rate` FROM (SELECT CASE WHEN CASE WHEN (COUNT(`sub_paid_amount`) OVER (PARTITION BY `sale_order_id`)) > 0 THEN CAST(SUM(`sub_paid_amount`) OVER (PARTITION BY `sale_order_id`) AS DECIMAL(38, 8)) ELSE CAST(NULL AS DECIMAL(38, 8)) END = 0 THEN CAST((1 / (COUNT(1) OVER (PARTITION BY `sale_order_id`))) AS DECIMAL(38, 6)) ELSE (`sub_paid_amount` / CASE WHEN (COUNT(`sub_paid_amount`) OVER (PARTITION BY `sale_order_id`)) > 0 THEN CAST(SUM(`sub_paid_amount`) OVER (PARTITION BY `sale_order_id`) AS DECIMAL(38, 8)) ELSE CAST(NULL AS DECIMAL(38, 8)) END) END AS `sub_amount_rate` FROM (SELECT `t56`.`tid` AS `sale_order_id`, `t56`.`sub_paid_amount` FROM ((SELECT `tid` AS `tid`, `warehouse_no` AS `warehouse_no`, `sale_order_detail_id` AS `sale_order_detail_id`, `product_id` AS `product_id`, `spec_id` AS `spec_id`, `product_code` AS `product_code`, `product_name` AS `product_name`, `spec_code` AS `spec_code`, `spec_name` AS `spec_name`, `sale_num` AS `sale_num`, `sale_price` AS `sale_price`, `sale_amount` AS `sale_amount`, `sale_discount` AS `sale_discount`, `sale_weight` AS `sale_weight`, `sub_paid_amount` AS `sub_paid_amount` FROM (SELECT `t54`.`tid`, `t54`.`warehouse_no`, `t54`.`sale_order_detail_id`, `t54`.`product_id`, `t54`.`spec_id`, `t54`.`product_code`, `t54`.`product_name`, `t54`.`spec_code`, `t54`.`spec_name`, `t54`.`sale_num`, `t54`.`sale_price`, `t54`.`sale_amount`, `t54`.`sale_discount`, `t54`.`sale_weight`, `t54`.`sub_paid_amount` FROM (((SELECT `tid` AS `tid`, `shop_no` AS `shop_no`, `warehouse_no` AS `warehouse_no`, `sale_order_detail_id`, `product_id`, `spec_id`, `product_code`, `product_name`, `spec_code`, `spec_name`, `sale_num`, `sale_price`, `sale_amount`, `sale_discount`, `sale_weight`, `sub_paid_amount` FROM (SELECT `tid`, `shop_no`, `warehouse_no`, CAST(CAST(`order_detail` AS JSON) -> 'oid' AS VARCHAR) AS `sale_order_detail_id`, CAST(CAST(`order_detail` AS JSON) -> 'goods_id' AS VARCHAR) AS `product_id`, CAST(CAST(`order_detail` AS JSON) -> 'spec_id' AS VARCHAR) AS `spec_id`, CAST(CAST(`order_detail` AS JSON) -> 'goods_no' AS VARCHAR) AS `product_code`, CAST(CAST(`order_detail` AS JSON) -> 'goods_name' AS VARCHAR) AS `product_name`, CAST(CAST(`order_detail` AS JSON) -> 'spec_no' AS VARCHAR) AS `spec_code`, CAST(CAST(`order_detail` AS JSON) -> 'spec_name' AS VARCHAR) AS `spec_name`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'num' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_num`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'price' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_price`, (CAST(CAST(CAST(`order_detail` AS JSON) -> 'num' AS VARCHAR) AS DECIMAL(38, 8)) * CAST(CAST(CAST(`order_detail` AS JSON) -> 'price' AS VARCHAR) AS DECIMAL(38, 8))) AS `sale_amount`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'discount' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_discount`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'weight' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_weight`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'share_amount' AS VARCHAR) AS DECIMAL(38, 8)) AS `sub_paid_amount` FROM (SELECT `t38`.`tid`, `t38`.`shop_no`, `t38`.`warehouse_no`, CASE WHEN `t39`.`value` IS NULL THEN NULL ELSE `t39`.`value` END AS `order_detail` FROM ((SELECT `t32`.`tid`, `t32`.`shop_no`, `t32`.`warehouse_no`, `t32`.`trade_orders` FROM ((SELECT `ods_api_wdtqjqm_origin_sale_order_info_f`.`tid`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`shop_no`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`pay_time`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`warehouse_no`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`trade_orders`, `t30`.`$f0`, `t30`.`$f1`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`rec_id` AS `rec_id0` FROM ((SELECT `rec_id` AS `rec_id`, `tid` AS `tid`, `shop_no` AS `shop_no`, `pay_time` AS `pay_time`, `warehouse_no` AS `warehouse_no`, `trade_orders` AS `trade_orders` FROM (SELECT `ods_api_wdtqjqm_origin_sale_order_info_f`.`rec_id`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`tid`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`shop_no`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`pay_time`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`warehouse_no`, `ods_api_wdtqjqm_origin_sale_order_info_f`.`trade_orders` FROM `cubeappdata`.`ods_api_wdtqjqm_origin_sale_order_info_f`) AS `ods_api_wdtqjqm_origin_sale_order_info_f` WHERE `pay_time` >= '2023-01-01') AS `ods_api_wdtqjqm_origin_sale_order_info_f` INNER JOIN (SELECT `wdt_shop_code` FROM (SELECT `ods_rpa_store_mapping_info_f`.`shop_channel`, `ods_rpa_store_mapping_info_f`.`wdt_shop_code` FROM `cubeappdata`.`ods_rpa_store_mapping_info_f`) AS `ods_rpa_store_mapping_info_f0` WHERE `shop_channel` = '???' GROUP BY `wdt_shop_code`) AS `ods_rpa_store_mapping_info_f0` ON `ods_api_wdtqjqm_origin_sale_order_info_f`.`shop_no` = `ods_rpa_store_mapping_info_f0`.`wdt_shop_code` INNER JOIN (SELECT COUNT(*) AS `$f0`, COUNT(`ods_api_wdtqjqm_origin_sale_order_history_info_f`.`rec_id`) AS `$f1` FROM ((SELECT `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`rec_id`, `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`shop_no` FROM `cubeappdata`.`ods_api_wdtqjqm_origin_sale_order_history_info_f`) AS `ods_api_wdtqjqm_origin_sale_order_history_info_f` INNER JOIN (SELECT `wdt_shop_code` FROM (SELECT `ods_rpa_store_mapping_info_f`.`shop_channel`, `ods_rpa_store_mapping_info_f`.`wdt_shop_code` FROM `cubeappdata`.`ods_rpa_store_mapping_info_f`) AS `ods_rpa_store_mapping_info_f1` WHERE `shop_channel` = '???' GROUP BY `wdt_shop_code`) AS `ods_rpa_store_mapping_info_f1` ON `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`shop_no` = `ods_rpa_store_mapping_info_f1`.`wdt_shop_code`) HAVING (COUNT(*) = 0 OR COUNT(`ods_api_wdtqjqm_origin_sale_order_history_info_f`.`rec_id`) >= COUNT(*))) AS `t30` ON TRUE) WHERE `t30`.`$f0` = 0 OR `ods_api_wdtqjqm_origin_sale_order_info_f`.`rec_id` IS NOT NULL) AS `t32` LEFT JOIN (SELECT `ods_api_wdtqjqm_origin_sale_order_history_info_f0`.`rec_id`, MIN(1) AS `$f1` FROM ((SELECT `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`rec_id`, `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`shop_no` FROM `cubeappdata`.`ods_api_wdtqjqm_origin_sale_order_history_info_f`) AS `ods_api_wdtqjqm_origin_sale_order_history_info_f0` INNER JOIN (SELECT `wdt_shop_code` FROM (SELECT `ods_rpa_store_mapping_info_f`.`shop_channel`, `ods_rpa_store_mapping_info_f`.`wdt_shop_code` FROM `cubeappdata`.`ods_rpa_store_mapping_info_f`) AS `ods_rpa_store_mapping_info_f2` WHERE `shop_channel` = '???' GROUP BY `wdt_shop_code`) AS `ods_rpa_store_mapping_info_f2` ON `ods_api_wdtqjqm_origin_sale_order_history_info_f0`.`shop_no` = `ods_rpa_store_mapping_info_f2`.`wdt_shop_code`) GROUP BY `ods_api_wdtqjqm_origin_sale_order_history_info_f0`.`rec_id`) AS `t34` ON `t32`.`rec_id0` = `t34`.`rec_id`) WHERE `t32`.`$f0` = 0 OR `t34`.`$f1` IS NULL) AS `t38` INNER JOIN JSON_EACH(CASE WHEN CAST(CASE WHEN `trade_orders` = '[]' THEN NULL ELSE `trade_orders` END AS JSON) IS NULL THEN '{"mock":null}' ELSE CAST(CASE WHEN `trade_orders` = '[]' THEN NULL ELSE `trade_orders` END AS JSON) END) AS `t39` ON TRUE)) AS `t40`) AS `t43`) UNION ALL (SELECT `tid` AS `tid`, `shop_no` AS `shop_no`, `warehouse_no` AS `warehouse_no`, `sale_order_detail_id`, `product_id`, `spec_id`, `product_code`, `product_name`, `spec_code`, `spec_name`, `sale_num`, `sale_price`, `sale_amount`, `sale_discount`, `sale_weight`, `sub_paid_amount` FROM (SELECT `tid`, `shop_no`, `warehouse_no`, CAST(CAST(`order_detail` AS JSON) -> 'oid' AS VARCHAR) AS `sale_order_detail_id`, CAST(CAST(`order_detail` AS JSON) -> 'goods_id' AS VARCHAR) AS `product_id`, CAST(CAST(`order_detail` AS JSON) -> 'spec_id' AS VARCHAR) AS `spec_id`, CAST(CAST(`order_detail` AS JSON) -> 'goods_no' AS VARCHAR) AS `product_code`, CAST(CAST(`order_detail` AS JSON) -> 'goods_name' AS VARCHAR) AS `product_name`, CAST(CAST(`order_detail` AS JSON) -> 'spec_no' AS VARCHAR) AS `spec_code`, CAST(CAST(`order_detail` AS JSON) -> 'spec_name' AS VARCHAR) AS `spec_name`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'num' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_num`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'price' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_price`, (CAST(CAST(CAST(`order_detail` AS JSON) -> 'num' AS VARCHAR) AS DECIMAL(38, 8)) * CAST(CAST(CAST(`order_detail` AS JSON) -> 'price' AS VARCHAR) AS DECIMAL(38, 8))) AS `sale_amount`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'discount' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_discount`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'weight' AS VARCHAR) AS DECIMAL(38, 8)) AS `sale_weight`, CAST(CAST(CAST(`order_detail` AS JSON) -> 'share_amount' AS VARCHAR) AS DECIMAL(38, 8)) AS `sub_paid_amount` FROM (SELECT `t47`.`tid`, `t47`.`shop_no`, `t47`.`warehouse_no`, CASE WHEN `t48`.`value` IS NULL THEN NULL ELSE `t48`.`value` END AS `order_detail` FROM ((SELECT `ods_api_wdtqjqm_origin_sale_order_history_info_f1`.`tid`, `ods_api_wdtqjqm_origin_sale_order_history_info_f1`.`shop_no`, `ods_api_wdtqjqm_origin_sale_order_history_info_f1`.`warehouse_no`, `ods_api_wdtqjqm_origin_sale_order_history_info_f1`.`trade_orders` FROM ((SELECT `tid`, `shop_no`, `warehouse_no`, `trade_orders` FROM (SELECT `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`tid`, `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`shop_no`, `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`pay_time`, `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`warehouse_no`, `ods_api_wdtqjqm_origin_sale_order_history_info_f`.`trade_orders` FROM `cubeappdata`.`ods_api_wdtqjqm_origin_sale_order_history_info_f`) AS `ods_api_wdtqjqm_origin_sale_order_history_info_f1` WHERE `pay_time` >= '2023-01-01') AS `ods_api_wdtqjqm_origin_sale_order_history_info_f1` INNER JOIN (SELECT `wdt_shop_code` FROM (SELECT `ods_rpa_store_mapping_info_f`.`shop_channel`, `ods_rpa_store_mapping_info_f`.`wdt_shop_code` FROM `cubeappdata`.`ods_rpa_store_mapping_info_f`) AS `ods_rpa_store_mapping_info_f3` WHERE `shop_channel` = '???' GROUP BY `wdt_shop_code`) AS `ods_rpa_store_mapping_info_f3` ON `ods_api_wdtqjqm_origin_sale_order_history_info_f1`.`shop_no` = `ods_rpa_store_mapping_info_f3`.`wdt_shop_code`)) AS `t47` INNER JOIN JSON_EACH(CASE WHEN CAST(CASE WHEN `trade_orders` = '[]' THEN NULL ELSE `trade_orders` END AS JSON) IS NULL THEN '{"mock":null}' ELSE CAST(CASE WHEN `trade_orders` = '[]' THEN NULL ELSE `trade_orders` END AS JSON) END) AS `t48` ON TRUE)) AS `t49`) AS `t52`)) AS `t54` INNER JOIN (SELECT `wdt_shop_code` FROM (SELECT `ods_rpa_store_mapping_info_f`.`shop_channel`, `ods_rpa_store_mapping_info_f`.`platform_shop_code`, `ods_rpa_store_mapping_info_f`.`platform_shop_name`, `ods_rpa_store_mapping_info_f`.`wdt_shop_code` FROM `cubeappdata`.`ods_rpa_store_mapping_info_f`) AS `ods_rpa_store_mapping_info_f4` WHERE `shop_channel` = '???' AND `wdt_shop_code` IS NOT NULL GROUP BY `wdt_shop_code`) AS `ods_rpa_store_mapping_info_f4` ON `t54`.`shop_no` = `ods_rpa_store_mapping_info_f4`.`wdt_shop_code`)) AS `t55` WHERE `product_name` LIKE '%??????%' OR `product_name` LIKE '%????%' OR `product_name` LIKE '%??%' OR `product_name` LIKE '%??%') AS `t56` LEFT JOIN (SELECT `ods_api_wdtqjqm_warehouse_info_f`.`warehouse_no` FROM `cubeappdata`.`ods_api_wdtqjqm_warehouse_info_f`) AS `ods_api_wdtqjqm_warehouse_info_f0` ON `t56`.`warehouse_no` = `ods_api_wdtqjqm_warehouse_info_f0`.`warehouse_no`)) AS `t57`) AS `t60`)) AS `t63`

DoraeMeng · 2025年03月11日 05:49

方便拿一下explain verbose的返回结果么？

colagy · 2025年03月11日 09:15

explain_verbose.txt (46.3 KB)