matthewgapp commented on code in PR #8840: URL: https://github.com/apache/arrow-datafusion/pull/8840#discussion_r1451591961
########## datafusion/sqllogictest/test_files/cte.slt: ########## @@ -19,3 +19,221 @@ query II select * from (WITH source AS (select 1 as e) SELECT * FROM source) t1, (WITH source AS (select 1 as e) SELECT * FROM source) t2 ---- 1 1 + +# trivial recursive CTE works +query I rowsort +WITH RECURSIVE nodes AS ( + SELECT 1 as id + UNION ALL + SELECT id + 1 as id + FROM nodes + WHERE id < 10 +) +SELECT * FROM nodes +---- +1 +10 +2 +3 +4 +5 +6 +7 +8 +9 + +# setup +statement ok +CREATE EXTERNAL TABLE beg_account_balance STORED as CSV WITH HEADER ROW LOCATION '../../testing/data/csv/recursive_query_account_beg_2.csv' + +# setup +statement ok +CREATE EXTERNAL TABLE account_balance_growth STORED as CSV WITH HEADER ROW LOCATION '../../testing/data/csv/recursive_query_account_growth_3.csv' + +# recursive CTE with static term derived from table works +query ITI rowsort +WITH RECURSIVE balances AS ( + SELECT * from beg_account_balance + UNION ALL + SELECT time + 1 as time, name, account_balance + 10 as account_balance + FROM balances + WHERE time < 10 +) +SELECT * FROM balances +---- +1 John 100 +1 Tim 200 +10 John 190 +10 Tim 290 +2 John 110 +2 Tim 210 +3 John 120 +3 Tim 220 +4 John 130 +4 Tim 230 +5 John 140 +5 Tim 240 +6 John 150 +6 Tim 250 +7 John 160 +7 Tim 260 +8 John 170 +8 Tim 270 +9 John 180 +9 Tim 280 + + +# recursive CTE with recursive join works +query ITI +WITH RECURSIVE balances AS ( + SELECT time as time, name as name, account_balance as account_balance + FROM beg_account_balance + UNION ALL + SELECT time + 1 as time, balances.name, account_balance + account_balance_growth.account_growth as account_balance + FROM balances + JOIN account_balance_growth + ON balances.name = account_balance_growth.name + WHERE time < 10 +) +SELECT * FROM balances +ORDER BY time, name +---- +1 John 100 +1 Tim 200 +2 John 103 +2 Tim 220 +3 John 106 +3 Tim 240 +4 John 109 +4 Tim 260 +5 John 112 +5 Tim 280 +6 John 115 +6 Tim 300 +7 John 118 +7 Tim 320 +8 John 121 +8 Tim 340 +9 John 124 +9 Tim 360 +10 John 127 +10 Tim 380 + +# recursive CTE with aggregations works +query I rowsort +WITH RECURSIVE nodes AS ( + SELECT 1 as id + UNION ALL + SELECT id + 1 as id + FROM nodes + WHERE id < 10 +) +SELECT sum(id) FROM nodes +---- +55 + +# setup +statement ok +CREATE TABLE t(a BIGINT) AS VALUES(1),(2),(3); + +# referencing CTE multiple times does not error +query II rowsort +WITH RECURSIVE my_cte AS ( + SELECT a from t + UNION ALL + SELECT a+2 as a + FROM my_cte + WHERE a<5 +) +SELECT * FROM my_cte t1, my_cte +---- +1 1 +1 2 +1 3 +1 3 +1 4 +1 5 +1 5 +1 6 +2 1 +2 2 +2 3 +2 3 +2 4 +2 5 +2 5 +2 6 +3 1 +3 1 +3 2 +3 2 +3 3 +3 3 +3 3 +3 3 +3 4 +3 4 +3 5 +3 5 +3 5 +3 5 +3 6 +3 6 +4 1 +4 2 +4 3 +4 3 +4 4 +4 5 +4 5 +4 6 +5 1 +5 1 +5 2 +5 2 +5 3 +5 3 +5 3 +5 3 +5 4 +5 4 +5 5 +5 5 +5 5 +5 5 +5 6 +5 6 +6 1 +6 2 +6 3 +6 3 +6 4 +6 5 +6 5 +6 6 + +# CTE within recursive CTE works and does not result in 'index out of bounds: the len is 0 but the index is 0' +query +WITH RECURSIVE "recursive_cte" AS ( + SELECT 1 as "val" + UNION ALL ( + WITH "sub_cte" AS ( + SELECT + time, + 1 as "val" + FROM + (SELECT DISTINCT "time" FROM "beg_account_balance") Review Comment: I was able to increase the partition size to 2 within the memory exec setting the batch size to 50 but still unable to reproduce in my test crate outside of our larger project. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
