[Bug]: [tpcds] mo crashed by oom for query4/query11/query74 of tpcds with 1G scale data on standlone mode

[aressu1985]

Is there an existing issue for the same bug?

• [X] I have checked the existing issues.

Branch Name

main

Commit ID

81eb633e5f489f172e0fd9e434a437e99e82c40c

Other Environment Information

- Hardware parameters: 64C 256G
- OS type:
- Others:

Actual Behavior

after running all the queries for tpcds 1G data, thtere were 3 queries caused mo crahed by oom. [q4] with year_total as ( select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,c_preferred_cust_flag customer_preferred_cust_flag ,c_birth_country customer_birth_country ,c_login customer_login ,c_email_address customer_email_address ,d_year dyear ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total ,'s' sale_type from customer ,store_sales ,date_dim where c_customer_sk = ss_customer_sk and ss_sold_date_sk = d_date_sk group by c_customer_id ,c_first_name ,c_last_name ,c_preferred_cust_flag ,c_birth_country ,c_login ,c_email_address ,d_year union all select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,c_preferred_cust_flag customer_preferred_cust_flag ,c_birth_country customer_birth_country ,c_login customer_login ,c_email_address customer_email_address ,d_year dyear ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total ,'c' sale_type from customer ,catalog_sales ,date_dim where c_customer_sk = cs_bill_customer_sk and cs_sold_date_sk = d_date_sk group by c_customer_id ,c_first_name ,c_last_name ,c_preferred_cust_flag ,c_birth_country ,c_login ,c_email_address ,d_year union all select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,c_preferred_cust_flag customer_preferred_cust_flag ,c_birth_country customer_birth_country ,c_login customer_login ,c_email_address customer_email_address ,d_year dyear ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total ,'w' sale_type from customer ,web_sales ,date_dim where c_customer_sk = ws_bill_customer_sk and ws_sold_date_sk = d_date_sk group by c_customer_id ,c_first_name ,c_last_name ,c_preferred_cust_flag ,c_birth_country ,c_login ,c_email_address ,d_year ) select
t_s_secyear.customer_id ,t_s_secyear.customer_first_name ,t_s_secyear.customer_last_name ,t_s_secyear.customer_birth_country from year_total t_s_firstyear ,year_total t_s_secyear ,year_total t_c_firstyear ,year_total t_c_secyear ,year_total t_w_firstyear ,year_total t_w_secyear where t_s_secyear.customer_id = t_s_firstyear.customer_id and t_s_firstyear.customer_id = t_c_secyear.customer_id and t_s_firstyear.customer_id = t_c_firstyear.customer_id and t_s_firstyear.customer_id = t_w_firstyear.customer_id and t_s_firstyear.customer_id = t_w_secyear.customer_id and t_s_firstyear.sale_type = 's' and t_c_firstyear.sale_type = 'c' and t_w_firstyear.sale_type = 'w' and t_s_secyear.sale_type = 's' and t_c_secyear.sale_type = 'c' and t_w_secyear.sale_type = 'w' and t_s_firstyear.dyear = 1999 and t_s_secyear.dyear = 1999+1 and t_c_firstyear.dyear = 1999 and t_c_secyear.dyear = 1999+1 and t_w_firstyear.dyear = 1999 and t_w_secyear.dyear = 1999+1 and t_s_firstyear.year_total > 0 and t_c_firstyear.year_total > 0 and t_w_firstyear.year_total > 0 and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end

case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end order by t_s_secyear.customer_id ,t_s_secyear.customer_first_name ,t_s_secyear.customer_last_name ,t_s_secyear.customer_birth_country limit 100;

[q11] with year_total as ( select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,c_preferred_cust_flag customer_preferred_cust_flag ,c_birth_country customer_birth_country ,c_login customer_login ,c_email_address customer_email_address ,d_year dyear ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total ,'s' sale_type from customer ,store_sales ,date_dim where c_customer_sk = ss_customer_sk and ss_sold_date_sk = d_date_sk group by c_customer_id ,c_first_name ,c_last_name ,c_preferred_cust_flag ,c_birth_country ,c_login ,c_email_address ,d_year union all select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,c_preferred_cust_flag customer_preferred_cust_flag ,c_birth_country customer_birth_country ,c_login customer_login ,c_email_address customer_email_address ,d_year dyear ,sum(ws_ext_list_price-ws_ext_discount_amt) year_total ,'w' sale_type from customer ,web_sales ,date_dim where c_customer_sk = ws_bill_customer_sk and ws_sold_date_sk = d_date_sk group by c_customer_id ,c_first_name ,c_last_name ,c_preferred_cust_flag ,c_birth_country ,c_login ,c_email_address ,d_year ) select
t_s_secyear.customer_id ,t_s_secyear.customer_first_name ,t_s_secyear.customer_last_name ,t_s_secyear.customer_email_address from year_total t_s_firstyear ,year_total t_s_secyear ,year_total t_w_firstyear ,year_total t_w_secyear where t_s_secyear.customer_id = t_s_firstyear.customer_id and t_s_firstyear.customer_id = t_w_secyear.customer_id and t_s_firstyear.customer_id = t_w_firstyear.customer_id and t_s_firstyear.sale_type = 's' and t_w_firstyear.sale_type = 'w' and t_s_secyear.sale_type = 's' and t_w_secyear.sale_type = 'w' and t_s_firstyear.dyear = 1998 and t_s_secyear.dyear = 1998+1 and t_w_firstyear.dyear = 1998 and t_w_secyear.dyear = 1998+1 and t_s_firstyear.year_total > 0 and t_w_firstyear.year_total > 0 and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else 0.0 end

case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end order by t_s_secyear.customer_id ,t_s_secyear.customer_first_name ,t_s_secyear.customer_last_name ,t_s_secyear.customer_email_address limit 100;

[q74] with year_total as ( select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,d_year as year ,max(ss_net_paid) year_total ,'s' sale_type from customer ,store_sales ,date_dim where c_customer_sk = ss_customer_sk and ss_sold_date_sk = d_date_sk and d_year in (1999,1999+1) group by c_customer_id ,c_first_name ,c_last_name ,d_year union all select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,d_year as year ,max(ws_net_paid) year_total ,'w' sale_type from customer ,web_sales ,date_dim where c_customer_sk = ws_bill_customer_sk and ws_sold_date_sk = d_date_sk and d_year in (1999,1999+1) group by c_customer_id ,c_first_name ,c_last_name ,d_year ) select t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name from year_total t_s_firstyear ,year_total t_s_secyear ,year_total t_w_firstyear ,year_total t_w_secyear where t_s_secyear.customer_id = t_s_firstyear.customer_id and t_s_firstyear.customer_id = t_w_secyear.customer_id and t_s_firstyear.customer_id = t_w_firstyear.customer_id and t_s_firstyear.sale_type = 's' and t_w_firstyear.sale_type = 'w' and t_s_secyear.sale_type = 's' and t_w_secyear.sale_type = 'w' and t_s_firstyear.year = 1999 and t_s_secyear.year = 1999+1 and t_w_firstyear.year = 1999 and t_w_secyear.year = 1999+1 and t_s_firstyear.year_total > 0 and t_w_firstyear.year_total > 0 and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end

case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end order by 1,3,2 limit 100;

DDL: tpcds.sql.zip

PROFILE HEAP: q4_oom.tar.gz

q11_oom.tar.gz

q74_oom.tar.gz

Expected Behavior

No response

Steps to Reproduce

1、login to 129 srv
2、cd /data1/tpcds/matrixone, 启动MO
3、执行DDL，并LOAD 1G 数据，数据在/data1/tpcds/data/1G目录
4、执行queries

第3步可执行执行如下：
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/call_center.dat' into table tpcds.call_center  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/catalog_page.dat' into table tpcds.catalog_page  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/catalog_returns.dat' into table tpcds.catalog_returns  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/catalog_sales.dat' into table tpcds.catalog_sales  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/customer_address.dat' into table tpcds.customer_address  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/customer.dat' into table tpcds.customer  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/customer_demographics.dat' into table tpcds.customer_demographics  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/date_dim.dat' into table tpcds.date_dim  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/dbgen_version.dat' into table tpcds.dbgen_version  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/household_demographics.dat' into table tpcds.household_demographics  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/income_band.dat' into table tpcds.income_band  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/inventory.dat' into table tpcds.inventory  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/item.dat' into table tpcds.item  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/promotion.dat' into table tpcds.promotion  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/reason.dat' into table tpcds.reason  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/ship_mode.dat' into table tpcds.ship_mode  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/store.dat' into table tpcds.store  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/store_returns.dat' into table tpcds.store_returns  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/store_sales.dat' into table tpcds.store_sales  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/time_dim.dat' into table tpcds.time_dim  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/warehouse.dat' into table tpcds.warehouse  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/web_page.dat' into table tpcds.web_page  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/web_returns.dat' into table tpcds.web_returns  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/web_sales.dat' into table tpcds.web_sales  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '/data1/sudong/tpcds/DSGen-software-code-3.2.0rc1/data/1/web_site.dat' into table tpcds.web_site  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';

Additional information

No response

[sukki37]

[triump2020]

Working on sharding.

[triump2020]

Working on other issue.

[triump2020]

Working on other issue.

[triump2020]

Ref to #18364

[triump2020]

No repo yet , keep observing!

[triump2020]

No repo yet , keep observing!