【测试类型：SQL功能】【测试版本：5.1.1】【资源池化】开启spq,spqplugin.spq_enable_adaptive_scan=on,query_dop=8,TPCDS Q14 第二条语句执行时查询节点coredump

【标题描述】:开启spq,spqplugin.spq_enable_adaptive_scan=on,query_dop=8,TPCDS Q14 第二条语句执行时查询节点coredump
【测试类型：SQL功能】【测试版本：5.1.1】【资源池化】开启spq,spqplugin.spq_enable_adaptive_scan=on,query_dop=8,TPCDS Q14 第二条语句执行时查询节点coredump
【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):
openEuler release 20.03 (LTS-SP3)
Linux openGauss117 4.19.90-2303.4.0.0193.oe1.aarch64 #1 SMP Wed Mar 22 03:04:16 UTC 2023 aarch64 aarch64 aarch64 GNU/Linux
【测试环境】(单机/1主x备x级联备):
一主两备
【被测功能】:spq多机并行
【测试类型】:功能测试
【数据库版本】(查询命令: gaussdb -V):
gaussdb (openGauss 5.1.1 build 5099d94c) compiled at 2023-11-08 14:52:29 commit 0 last mr
【预置条件】:TPCDS 100GB数据
gs_guc set -N all -I all -c"shared_preload_libraries='security_plugin,spqplugin'"
spqplugin.enable_spq = on
spqplugin.cluster_map = 'xxx|xxx|xxx|xxx|xxx|xxx,...,...'
shared_buffers=300GB
【操作步骤】(请填写详细的操作步骤):
```
--1.查询Q14语句
set query_dop = 8;
set spqplugin.spq_enable_adaptive_scan to on;

with  cross_items as
 (select i_item_sk ss_item_sk
 from item,
 (select iss.i_brand_id brand_id
     ,iss.i_class_id class_id
     ,iss.i_category_id category_id
 from store_sales
     ,item iss
     ,date_dim d1
 where ss_item_sk = iss.i_item_sk
   and ss_sold_date_sk = d1.d_date_sk
   and d1.d_year between 1998 AND 1998 + 2
 intersect
 select ics.i_brand_id
     ,ics.i_class_id
     ,ics.i_category_id
 from catalog_sales
     ,item ics
     ,date_dim d2
 where cs_item_sk = ics.i_item_sk
   and cs_sold_date_sk = d2.d_date_sk
   and d2.d_year between 1998 AND 1998 + 2
 intersect
 select iws.i_brand_id
     ,iws.i_class_id
     ,iws.i_category_id
 from web_sales
     ,item iws
     ,date_dim d3
 where ws_item_sk = iws.i_item_sk
   and ws_sold_date_sk = d3.d_date_sk
   and d3.d_year between 1998 AND 1998 + 2) x
 where i_brand_id = brand_id
      and i_class_id = class_id
      and i_category_id = category_id
),
 avg_sales as
(select avg(quantity*list_price) average_sales
  from (select ss_quantity quantity
             ,ss_list_price list_price
       from store_sales
           ,date_dim
       where ss_sold_date_sk = d_date_sk
         and d_year between 1998 and 1998 + 2
       union all
       select cs_quantity quantity
             ,cs_list_price list_price
       from catalog_sales
           ,date_dim
       where cs_sold_date_sk = d_date_sk
         and d_year between 1998 and 1998 + 2
       union all
       select ws_quantity quantity
             ,ws_list_price list_price
       from web_sales
           ,date_dim
       where ws_sold_date_sk = d_date_sk
         and d_year between 1998 and 1998 + 2) x)
 select * from ( select  this_year.channel ty_channel
                           ,this_year.i_brand_id ty_brand
                           ,this_year.i_class_id ty_class
                           ,this_year.i_category_id ty_category
                           ,this_year.sales ty_sales
                           ,this_year.number_sales ty_number_sales
                           ,last_year.channel ly_channel
                           ,last_year.i_brand_id ly_brand
                           ,last_year.i_class_id ly_class
                           ,last_year.i_category_id ly_category
                           ,last_year.sales ly_sales
                           ,last_year.number_sales ly_number_sales 
 from
 (select 'store' channel, i_brand_id,i_class_id,i_category_id
        ,sum(ss_quantity*ss_list_price) sales, count(*) number_sales
 from store_sales 
     ,item
     ,date_dim
 where ss_item_sk in (select ss_item_sk from cross_items)
   and ss_item_sk = i_item_sk
   and ss_sold_date_sk = d_date_sk
   and d_week_seq = (select d_week_seq
                     from date_dim
                     where d_year = 1998 + 1
                       and d_moy = 12
                       and d_dom = 16)
 group by i_brand_id,i_class_id,i_category_id
 having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)) this_year,
 (select 'store' channel, i_brand_id,i_class_id
        ,i_category_id, sum(ss_quantity*ss_list_price) sales, count(*) number_sales
 from store_sales
     ,item
     ,date_dim
 where ss_item_sk in (select ss_item_sk from cross_items)
   and ss_item_sk = i_item_sk
   and ss_sold_date_sk = d_date_sk
   and d_week_seq = (select d_week_seq
                     from date_dim
                     where d_year = 1998
                       and d_moy = 12
                       and d_dom = 16)
 group by i_brand_id,i_class_id,i_category_id
 having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)) last_year
 where this_year.i_brand_id= last_year.i_brand_id
   and this_year.i_class_id = last_year.i_class_id
   and this_year.i_category_id = last_year.i_category_id
 order by this_year.channel, this_year.i_brand_id, this_year.i_class_id, this_year.i_category_id
  ) limit 100;
```
【预期输出】:查询成功
![输入图片说明](https://foruda.gitee.com/images/1700103357717605120/ff09583c_10840237.png "屏幕截图")
【实际输出】:查询节点core
![输入图片说明](https://foruda.gitee.com/images/1700105434715981564/04179693_10840237.png "屏幕截图")
【原因分析】:
1. 这个问题的根因
2. 问题推断过程
3. 还有哪些原因可能造成类似现象
4. 该问题是否有临时规避措施
5. 问题解决方案
6. 预计修复问题时间

【日志信息】(请附上日志文件、截图、coredump信息):
```
#0  0x0000aaad58ef23ec in sys_wait4 (ru=0x0, options=options@entry=1073741824, stat_addr=stat_addr@entry=0xff9f696470cc, upid=upid@entry=2330057) at bbox_syscall_support.cpp:36
#1  sys_waitpid (pid=pid@entry=2330057, status=status@entry=0xff9f696470cc, options=options@entry=1073741824) at bbox_syscall_support.cpp:516
#2  0x0000aaad58ef37e8 in BBOX_GetClonePidResult (iClonePid=iClonePid@entry=2330057, pstArgs=pstArgs@entry=0xff9f69647150, iCloneErrno=iCloneErrno@entry=9) at bbox_threads.cpp:572
#3  0x0000aaad58ef3b28 in BBOX_GetAllThreads (enType=<optimized out>, pDone=pDone@entry=0xaaad58eea6d0 <BBOX_FinishDumpFile(void*)>, pDoneArgs=pDoneArgs@entry=0xff9f69647298,
    pCallback=<optimized out>) at bbox_threads.cpp:700
#4  0x0000aaad58eea98c in BBOX_CreateCoredump (file_name=0xff9f696473c8 "/home/core//core-gaussdb-1067650-2023_11_15_19_46_50-bbox.lz4", file_name@entry=0x0) at bbox_create.cpp:440
#5  0x0000aaad58ef5494 in bbox_handler (sig=11, si=0xff9f69647900, uc=<optimized out>) at gs_bbox.cpp:112
#6  bbox_handler (sig=11, si=0xff9f69647900, uc=<optimized out>) at gs_bbox.cpp:102
#7  <signal handler called>
#8  check_match_and_update_state (has_finished=<synthetic pointer>, seqReq=0xff9f69648c58, p_state=0x0) at execRemote.cpp:721
#9  adps_get_response_block (seqReq=seqReq@entry=0xff9f69648c58, node_idx=0) at execRemote.cpp:721
#10 0x0000aaad58d888b0 in spq_adps_consumer () at execRemote.cpp:1146
#11 0x0000aaad58d88a58 in spq_adps_coordinator_thread_main () at execRemote.cpp:1164
#12 0x0000aaad591d1104 in GaussDbThreadMain<(knl_thread_role)68> (arg=<optimized out>) at postmaster.cpp:11764
#13 0x0000aaad591b0644 in InternalThreadFunc (args=<optimized out>) at postmaster.cpp:14563
#14 0x0000fffee118878c in start_thread (arg=0xff9f8b4396d0) at pthread_create.c:486
#15 0x0000fffee10d511c in thread_start () at ../sysdeps/unix/sysv/linux/aarch64/clone.S:80
```
【测试代码】:无

openGauss/openGauss-server

内容风险标识

评论 (3)

openGauss/openGauss-server .gitee-modal { width: 500px !important; }

内容风险标识