databend
databend copied to clipboard
datafuselabs
feat: support recursive CTE
Summary
AWS Athena:
create table testing.nk_example_table as
SELECT * FROM (
VALUES
(1, 'new loan' ,10000, 0.1),
(2, 'new loan' ,500 , 0.2),
(3, 'add interest' ,0 , 0.15)
) AS t (id, action, amount, interest)
with recursive recur(id, action, amount, interest, total_bal) as
(select id,
action,
amount,
interest,
cast(amount as double) as total_bal
from testing.nk_example_table
where id = 1
union all
select b.id,
b.action,
b.amount,
b.interest,
cast((a.total_bal * (1+b.interest))+b.amount as double)
from recur a, testing.nk_example_table b where a.id+1 = b.id
)
select * from recur
How to do (or rewrite) the query in Databend? Databend does not support recursive CTEs.
Another requirement is the ability to insert multiple CTEs( can we rewrite it into merge into?):
WITH x as ({first_Select}), y as ({second_select_uses_x}), z as ({third_select_uses_x_and_y})
INSERT INTO table
SELECT things from x, y, z
How to do (or rewrite) the query in Databend? Databend does not support recursive CTEs.
I think there isn't a good way to rewrite it without recursive CTE. If the max recursive level is known, we can expand the query manually. But the query will be tedious.
Another requirement is the ability to insert multiple CTEs( can we rewrite it into merge into?):
Merge into isn't the best choice for the case.
The case will insert all results of x,y,z cross join to things table which doesn't contain any matched or unmatched logic.
The case is relatively easy to support in our current CTE framework. I'll give it a try.
@xudong963 thanks for picking this task up. Do you have an ETA or need any help with testing?
@inviscid We're currently in the middle of the May Day holiday and will need next week to make progress.