[Bug]: cte recursive: multi recursive layers, insufficient memory error reported

heni02 commented 1 year ago

Is there an existing issue for the same bug?

[X] I have checked the existing issues.

Environment

- Version or commit-id (e.g. v0.1.0 or 8b23a93):6828ed04cefb5e2b306120f655ab34c16c56aa1a
- Hardware parameters:
- OS type:
- Others:

Actual Behavior

mo limit 100 iterations，mem 16GB mac execute sql reported :Invalid alloc size 1147486208，about 1GB

企业微信截图_0a65cf72-5009-4fb5-ac79-051c425becdc

mysql:

企业微信截图_a47a46d1-161d-43cd-a489-db7c67a0e74e

Expected Behavior

No response

Steps to Reproduce

with recursive  cte_ab_8(productID,price) as(select p_id,price from product  union all select c.productID,p.price from product p join cte_ab_8 c on p.p_id = c.productID)select * from cte_ab_8;

ddl:
create table product (id int primary key,p_id int,p_name varchar(25),price decimal(10,3));
insert into product values (3,2,"bed",3560.98),(2,null,"chair",1599.00),(4,1,"desk",2999.99),(5,3,"door",8123.09),(6,3,"mirrors",698.00),(7,4,"tv",5678);

Additional information

No response

heni02 commented 1 year ago

@iamlinjunhong plz confirm

heni02 commented 1 year ago

retest on 128 server， it is reported same error ：internal error: Invalid alloc size 1147486208

企业微信截图_cb2411c2-88ca-413c-baf5-519bd82a78e8

so it is two questions： 1.Invalid alloc size 1147486208, about 1GB is small？ 2.which priority is higher for memory limit and limit 100 limit errors? mysql returns limit errors quickly during execution

iamlinjunhong commented 1 year ago

无进度，在看其他问题

iamlinjunhong commented 1 year ago

在看其他问题

iamlinjunhong commented 1 year ago

join 的结果是个超过 1G 的batch，导致在 dup 的时候出错

iamlinjunhong commented 1 year ago

把join probe 出来的大 batch，切分成小 batch 后由于递归计算出的中结果太大（几十上百亿行数据），mo 跑一段时间会 oom，需要做 spill。mysql 在 129 上还未能跑出结果

iamlinjunhong commented 1 year ago

mysql 在 128上报错

iamlinjunhong commented 1 year ago

还需要做 spill

aunjgr commented 1 year ago

Not working on this today

aunjgr commented 1 year ago

不需要做spill。应该把大的batch按8192行切块。等pipeline重构吧

aunjgr commented 1 year ago

not working on this

aunjgr commented 12 months ago

not working on this today

aunjgr commented 11 months ago

Not started

aunjgr commented 11 months ago

Not started

aunjgr commented 11 months ago

Not started

aunjgr commented 10 months ago

pipeline重构之后可自然解决