bug: large query causes stack overflow in sqlsmith

[kwannoel]

Describe the bug

Update: This is expected after switching to local execution mode that we may overflow on large queries. See comment below for more details: https://github.com/singularity-data/risingwave/issues/4807#issuecomment-1223608299.

We can fix it by configuring distributed execution mode in sqlsmith.

frontend.log

thread 'risingwave-main' has overflowed its stack
fatal runtime error: stack overflow
Tue Aug 23 04:20:58 AM UTC 2022 [risedev]: Program exited with 134

To Reproduce

---- START
-- Setup
CREATE TABLE supplier (s_suppkey INT, s_name CHARACTER VARYING, s_address CHARACTER VARYING, s_nationkey INT, s_phone CHARACTER VARYING, s_acctbal NUMERIC, s_comment CHARACTER VARYING);CREATE TABLE part (p_partkey INT, p_name CHARACTER VARYING, p_mfgr CHARACTER VARYING, p_brand CHARACTER VARYING, p_type CHARACTER VARYING, p_size INT, p_container CHARACTER VARYING, p_retailprice NUMERIC, p_comment CHARACTER VARYING);CREATE TABLE partsupp (ps_partkey INT, ps_suppkey INT, ps_availqty INT, ps_supplycost NUMERIC, ps_comment CHARACTER VARYING);CREATE TABLE customer (c_custkey INT, c_name CHARACTER VARYING, c_address CHARACTER VARYING, c_nationkey INT, c_phone CHARACTER VARYING, c_acctbal NUMERIC, c_mktsegment CHARACTER VARYING, c_comment CHARACTER VARYING);CREATE TABLE orders (o_orderkey BIGINT, o_custkey INT, o_orderstatus CHARACTER VARYING, o_totalprice NUMERIC, o_orderdate DATE, o_orderpriority CHARACTER VARYING, o_clerk CHARACTER VARYING, o_shippriority INT, o_comment CHARACTER VARYING);CREATE TABLE lineitem (l_orderkey BIGINT, l_partkey INT, l_suppkey INT, l_linenumber INT, l_quantity NUMERIC, l_extendedprice NUMERIC, l_discount NUMERIC, l_tax NUMERIC, l_returnflag CHARACTER VARYING, l_linestatus CHARACTER VARYING, l_shipdate DATE, l_commitdate DATE, l_receiptdate DATE, l_shipinstruct CHARACTER VARYING, l_shipmode CHARACTER VARYING, l_comment CHARACTER VARYING);CREATE TABLE nation (n_nationkey INT, n_name CHARACTER VARYING, n_regionkey INT, n_comment CHARACTER VARYING);CREATE TABLE region (r_regionkey INT, r_name CHARACTER VARYING, r_comment CHARACTER VARYING);CREATE TABLE person (id BIGINT, name CHARACTER VARYING, email_address CHARACTER VARYING, credit_card CHARACTER VARYING, city CHARACTER VARYING, state CHARACTER VARYING, date_time TIMESTAMP);CREATE TABLE auction (id BIGINT, item_name CHARACTER VARYING, description CHARACTER VARYING, initial_bid BIGINT, reserve BIGINT, date_time TIMESTAMP, expires TIMESTAMP, seller BIGINT, category BIGINT);CREATE TABLE bid (auction BIGINT, bidder BIGINT, price BIGINT, date_time TIMESTAMP);CREATE MATERIALIZED VIEW m0 AS SELECT 0 AS col_0, t_0.l_partkey AS col_1, FLOAT '842388994.8183842' AS col_2 FROM lineitem AS t_0 WHERE true GROUP BY t_0.l_linenumber, t_0.l_shipmode, t_0.l_partkey, t_0.l_orderkey, t_0.l_shipinstruct, t_0.l_receiptdate, t_0.l_tax, t_0.l_returnflag, t_0.l_commitdate, t_0.l_quantity, t_0.l_discount, t_0.l_suppkey;CREATE MATERIALIZED VIEW m1 AS SELECT TIME '23:44:29' - INTERVAL '60' AS col_0, coalesce(NULL, NULL, NULL, CASE WHEN INT '1' < INT '0' THEN INT '464455733' * FLOAT '2147483647' WHEN false THEN FLOAT '750670743.8978382' WHEN false THEN FLOAT '0' / (REAL '1401141166.367301' + t_0.s_acctbal) WHEN true THEN FLOAT '354229493.8069413' ELSE FLOAT '82260209.06570174' END / FLOAT '1', NULL, NULL, NULL, NULL, NULL, NULL) + FLOAT '1475506649.1166892' AS col_1 FROM supplier AS t_0 GROUP BY t_0.s_name, t_0.s_nationkey, t_0.s_suppkey, t_0.s_acctbal, t_0.s_phone HAVING true;CREATE MATERIALIZED VIEW m2 AS SELECT DATE '2022-08-23' AS col_0, true AS col_1, 2714735607835072250 AS col_2, true AS col_3 FROM (WITH with_0 AS (SELECT TIME '03:50:04' AS col_0, INT '698472181' AS col_1, TIMESTAMP '2022-08-16 01:55:33' AS col_2 FROM auction AS t_1 GROUP BY t_1.expires, t_1.initial_bid, t_1.category, t_1.item_name) SELECT INTERVAL '740836' AS col_0, TIMESTAMP '2022-08-23 03:50:03' - TIMESTAMP '2022-08-22 03:50:04' AS col_1, INT '1858661004' AS col_2 FROM with_0) AS sq_2 GROUP BY sq_2.col_1, sq_2.col_2, sq_2.col_0 HAVING true;CREATE MATERIALIZED VIEW m3 AS WITH with_0 AS (SELECT t_2.initial_bid AS col_0, t_2.reserve - SMALLINT '25826' AS col_1 FROM region AS t_1 JOIN auction AS t_2 ON t_1.r_comment = t_2.item_name WHERE (t_1.r_regionkey | t_2.id) <> SMALLINT '0' GROUP BY t_2.reserve, t_2.expires, t_2.date_time, t_1.r_name, t_2.id, t_2.initial_bid, t_2.seller) SELECT max(REAL '848226044.5371134') AS col_0, REAL '375701123.43741214' + INT '2147483647' AS col_1 FROM with_0 WHERE true;CREATE MATERIALIZED VIEW m4 AS WITH with_0 AS (SELECT REAL '448707391.60230464' AS col_0, repeat('CuERbOCiJC', CAST(t_1.c_acctbal <> INT '2147483647' AS INT)) AS col_1 FROM customer AS t_1 GROUP BY t_1.c_address, t_1.c_nationkey, t_1.c_custkey, t_1.c_acctbal, t_1.c_name) SELECT 8129139254694872675 AS col_0, TIMESTAMP '2022-08-23 03:49:04' AS col_1, FLOAT '1252946376.4456582' AS col_2, OVERLAY(TRIM('CY8bwljA4B') PLACING TRIM('UuIqxoFIha') FROM DATE '2022-08-23' - ((INT '859787575' + DATE '2022-08-23') - INT '993744265')) AS col_3 FROM with_0 WHERE true;CREATE MATERIALIZED VIEW m5 AS WITH with_0 AS (SELECT DATE '2022-08-18' AS col_0 FROM auction AS t_1 WHERE true GROUP BY t_1.date_time, t_1.category, t_1.id, t_1.description, t_1.reserve, t_1.item_name HAVING false) SELECT REAL '1' AS col_0, FLOAT '1' AS col_1, SMALLINT '32767' AS col_2, 0 AS col_3 FROM with_0 WHERE true;CREATE MATERIALIZED VIEW m6 AS SELECT 0 AS col_0, TIME '14:36:09' AS col_1 FROM (SELECT SMALLINT '24833' AS col_0 FROM m5 AS t_0 WHERE false GROUP BY t_0.col_3, t_0.col_2, t_0.col_1, t_0.col_0 HAVING false) AS sq_1 WHERE (5978344472026226029 * 954855141.2104132) >= (coalesce(NULL, NULL, SMALLINT '1039', NULL, NULL, NULL, NULL, NULL, NULL, NULL) & SMALLINT '30859') GROUP BY sq_1.col_0 HAVING true;CREATE MATERIALIZED VIEW m7 AS SELECT (t_0.s_suppkey + CASE WHEN true THEN 0 WHEN true THEN 5047002324958852000 << t_0.s_suppkey WHEN CAST(t_0.s_nationkey % char_length('JpQGI0HDvD') AS BOOLEAN) THEN 0 WHEN false THEN 6375312481384151923 WHEN false THEN ~ (1 >> SMALLINT '32767') WHEN false THEN (SMALLINT '26148' * 1) # SMALLINT '1' WHEN true THEN 3790644562719062375 ELSE coalesce(NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL) END) % t_0.s_acctbal AS col_0, INT '2147483647' AS col_1, INTERVAL '86400' AS col_2 FROM supplier AS t_0 WHERE false GROUP BY t_0.s_address, t_0.s_acctbal, t_0.s_comment, t_0.s_name, t_0.s_phone, t_0.s_nationkey, t_0.s_suppkey HAVING false;CREATE MATERIALIZED VIEW m8 AS WITH with_0 AS (SELECT 925177686.751 AS col_0, 0 - t_1.l_discount AS col_1, TIMESTAMP '2022-08-16 03:50:05' AS col_2 FROM lineitem AS t_1 WHERE false GROUP BY t_1.l_shipinstruct, t_1.l_shipmode, t_1.l_comment, t_1.l_partkey, t_1.l_tax, t_1.l_discount, t_1.l_returnflag, t_1.l_shipdate, t_1.l_quantity, t_1.l_linenumber) SELECT 1 << (SMALLINT '32767' & INT '0') AS col_0 FROM with_0;CREATE MATERIALIZED VIEW m9 AS SELECT INTERVAL '3600' AS col_0 FROM m6 AS t_0 GROUP BY t_0.col_1, t_0.col_0;
-- Query
SELECT sq_64.col_1 AS col_0, DATE '2022-08-12' AS col_1, t_1.c_acctbal AS col_2, INTERVAL '604800' AS col_3 FROM supplier AS t_0 JOIN customer AS t_1 ON t_0.s_phone = t_1.c_name, bid AS t_2, partsupp AS t_5, m7 AS t_6, (SELECT (SMALLINT '32767' # SMALLINT '14940') | t_60.ps_partkey AS col_0, SMALLINT '32767' AS col_1, TIMESTAMP '2022-08-19 19:04:40' AS col_2 FROM orders AS t_7, (SELECT coalesce(NULL, NULL, NULL, NULL, NULL, SMALLINT '28972', NULL, NULL, NULL, NULL) AS col_0 FROM m6 AS t_8, tumble(bid, bid.date_time, INTERVAL '967361') AS tumble_9, m2 AS t_10, tumble(person, person.date_time, INTERVAL '3600') AS tumble_11, (WITH with_12 AS (WITH with_13 AS (SELECT TIMESTAMP '2022-08-16 03:51:41' AS col_0, TRIM(TRAILING 'ZGXNJGhPoX' FROM min(t_26.item_name)) AS col_1, TIME '02:51:41' AS col_2, 'ZWuOBkjPOM' AS col_3 FROM m3 AS t_14, m3 AS t_15, region AS t_16, m5 AS t_17, m9 AS t_18, supplier AS t_19, m3 AS t_20, nation AS t_21 JOIN auction AS t_22 ON t_21.n_comment = t_22.description, customer AS t_23, partsupp AS t_24, part AS t_25, auction AS t_26 GROUP BY t_22.item_name, t_25.p_retailprice, t_22.expires, t_25.p_partkey, t_23.c_phone, t_26.id, t_22.category, t_20.col_0, t_17.col_3, t_23.c_mktsegment, t_25.p_mfgr, t_17.col_1, t_24.ps_suppkey, t_19.s_suppkey, t_17.col_2, t_22.seller, t_24.ps_partkey, t_26.item_name, t_15.col_0, t_26.reserve, t_21.n_name, t_22.date_time, t_21.n_comment, t_25.p_size, t_26.category, t_16.r_regionkey, t_18.col_0, t_16.r_name, t_26.seller, t_22.description, t_22.reserve, t_19.s_phone, t_19.s_nationkey, t_19.s_address, t_14.col_1, t_19.s_name, t_25.p_name, t_26.date_time, t_15.col_1, t_21.n_nationkey) SELECT TIME '19:54:23' AS col_0, t_33.col_2 + t_34.col_0 AS col_1 FROM with_13, partsupp AS t_27, m7 AS t_28, auction AS t_29, m4 AS t_30, m9 AS t_31, m8 AS t_32 JOIN m4 AS t_33 ON t_32.col_0 = t_33.col_0, m0 AS t_34, customer AS t_35 WHERE false GROUP BY t_29.category, t_33.col_1, t_32.col_0, t_29.description, t_27.ps_suppkey, t_28.col_2, t_29.expires, t_33.col_2, t_30.col_3, t_33.col_0, t_27.ps_availqty, t_35.c_name, t_30.col_1, t_29.item_name, t_30.col_2, t_33.col_3, t_29.id, t_28.col_1, t_34.col_2, t_27.ps_partkey, t_29.date_time, t_35.c_nationkey, t_30.col_0, t_35.c_mktsegment, t_29.initial_bid, t_34.col_0, t_29.seller, t_27.ps_comment, t_31.col_0, t_35.c_acctbal, t_28.col_0, t_35.c_custkey) SELECT DATE '2022-08-15' AS col_0, t_48.col_0 * INTERVAL '60' AS col_1, false AS col_2, FLOAT '2147483647' AS col_3 FROM with_12, partsupp AS t_36, auction AS t_37, auction AS t_40, m9 AS t_41, customer AS t_42, auction AS t_43, m0 AS t_44, m9 AS t_45, lineitem AS t_46, m9 AS t_47, m5 AS t_48 GROUP BY t_46.l_tax, t_46.l_shipmode, t_46.l_partkey, t_37.reserve, t_48.col_0, t_37.id, t_41.col_0, t_43.reserve, t_42.c_nationkey, t_40.date_time, t_40.id, t_42.c_phone, t_36.ps_supplycost, t_44.col_2, t_46.l_shipdate, t_37.item_name, t_40.seller, t_42.c_acctbal, t_48.col_3, t_36.ps_comment, t_47.col_0, t_43.initial_bid, t_46.l_orderkey, t_43.seller, t_40.item_name, t_40.reserve, t_36.ps_suppkey, t_42.c_custkey, t_46.l_suppkey, t_46.l_discount, t_43.date_time, t_42.c_comment) AS sq_49, part AS t_50, nation AS t_51, region AS t_52, bid AS t_53, m9 AS t_54, part AS t_55 WHERE t_10.col_3 GROUP BY t_50.p_container, tumble_11.credit_card, t_52.r_regionkey, t_55.p_brand, t_55.p_type, tumble_9.date_time, t_51.n_name, t_50.p_type, t_8.col_0, t_10.col_1, t_51.n_nationkey, t_52.r_comment, t_55.p_size, sq_49.col_2, t_10.col_0, t_55.p_partkey, t_53.date_time, t_55.p_retailprice, t_50.p_partkey, t_55.p_comment, t_55.p_container, t_8.col_1, t_55.p_mfgr, tumble_9.bidder, t_54.col_0 HAVING false) AS sq_56, m5 AS t_57, bid AS t_58, region AS t_59, partsupp AS t_60, tumble(m4, m4.col_1, INTERVAL '57413') AS tumble_61, customer AS t_62, tumble(auction, auction.date_time, INTERVAL '1') AS tumble_63 WHERE CASE WHEN t_59.r_regionkey > ((t_62.c_acctbal * t_60.ps_suppkey) % SMALLINT '32767') THEN false WHEN t_7.o_shippriority <= tumble_63.reserve THEN true WHEN TIMESTAMP '2022-08-22 03:51:41' <> (t_62.c_nationkey + coalesce(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, INT '2147483647' + DATE '2022-08-23')) THEN false WHEN false THEN false WHEN true THEN true WHEN false THEN false WHEN true THEN true WHEN INT '0' < FLOAT '0' THEN coalesce(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9004259106038590178 <> FLOAT '327918452.9950868') ELSE false END GROUP BY t_60.ps_partkey, tumble_63.expires HAVING SMALLINT '32767' >= FLOAT '2147483647') AS sq_64, lineitem AS t_65, m7 AS t_66, m8 AS t_67, customer AS t_68, nation AS t_69, tumble(person, person.date_time, INTERVAL '1') AS tumble_70, part AS t_71, tumble(person, person.date_time, INTERVAL '86400') AS tumble_72, partsupp AS t_73 WHERE false GROUP BY t_65.l_linestatus, t_68.c_comment, t_69.n_regionkey, tumble_70.name, tumble_72.city, t_65.l_returnflag, t_71.p_partkey, sq_64.col_0, t_65.l_orderkey, t_1.c_acctbal, t_1.c_name, t_68.c_phone, t_71.p_retailprice, t_73.ps_availqty, t_68.c_custkey, t_65.l_extendedprice, t_73.ps_supplycost, t_65.l_shipdate, tumble_70.email_address, t_0.s_nationkey, sq_64.col_1;
---- END

Expected behavior A clear and concise description of what you expected to happen.

Additional context Add any other context about the problem here.

[kwannoel]

Working on shrinking the query.

[kwannoel]

Observed from main cron job: https://buildkite.com/singularity-data/main-cron. originates somewhere between recent commits: [730a8a3, c1ec8e5cbba07b34edf82fc1cfde9142e64f2a49)

• Latest commit has this issue locally as well: 730a8a3
• Lower bound commit does not have this issue: c1ec8e5cbba07b34edf82fc1cfde9142e64f2a49

[BowenXiao1999]

How you know this is a stack overflow? Is there a log?

Sounds like parser recursive too much while processing the query...

[kwannoel]

How you know this is a stack overflow? Is there a log?

Updated in description, thanks

[kwannoel]

Seems like this is the commit: c275f7e0fc8de90f41de6361a7a7703f8f7cf05d after running git bisect.

[kwannoel]

Seems like this is the commit: c275f7e after running git bisect.

Could I get your help to take a look too @liurenjie1024 ?

[kwannoel]

I guess in the future the system should figure out during optimization phase whether to use distributed / local execution mode. For now I will set sqlsmith to use distributed mode when executing. That should fix this bug.

[liurenjie1024]

Seems like this is the commit: c275f7e after running git bisect.

Could I get your help to take a look too @liurenjie1024 ?

Yes, after this pr we changed default mode from distributed to local, so it's possible to incur overflow. Also agree that we should automatically determine execution mode by optimizer, but it's low priority.

[kwannoel]

cc @xxchan

[kwannoel]

Not fixed yet, only worked around in sqlsmith. Keep it open to track. Fix is to automatically use distributed execution mode when query is too large.

[yuhao-su]

Found another stack overflow https://buildkite.com/risingwavelabs/pull-request/builds/52814#0190560f-6359-4d81-8d96-b02ed46c00cd