[Bug] [ORCA] Wrong plan for index nestloop join

[gfphoenix78]

Cloudberry Database version

main

What happened

orca produce wrong plan: the inner plan should be redistributed before join.

What you think should happen instead

orca produce wrong plan: the inner plan should be redistributed before join.

How to reproduce

CREATE TABLE t_clientinstrumentind2 (
    tradingday text,
    client_id INT,
    instrumentid text,
    PRIMARY KEY (tradingday, client_id, instrumentid)
)
DISTRIBUTED BY (tradingday, client_id, instrumentid)
PARTITION BY RANGE (tradingday)
(
     PARTITION p2019 START ('20190101'::character varying(8)) END ('20200101'::character varying(8)) WITH (tablename='t_clientinstrumentind_2_prt_p2019', appendonly=false),
    PARTITION p2020 START ('20200101'::character varying(8)) END ('20210101'::character varying(8)) WITH (tablename='t_clientinstrumentind_2_prt_p2020', appendonly=false)
);

CREATE TABLE t_clientproductind2 (
    tradingday character varying(8),
    productid TEXT,
    clientid INT,
    exchangegroup TEXT,
    customertype INT ,
    PRIMARY KEY (tradingday, productid, clientid, exchangegroup, customertype)
)
DISTRIBUTED BY (tradingday, productid, clientid, exchangegroup, customertype)
PARTITION BY RANGE (tradingday)
(
     PARTITION p2019 START ('20190101'::character varying(8)) END ('20200101'::character varying(8)) WITH (tablename='t_clientproductind_2_prt_p2019', appendonly=false),
    PARTITION p2020 START ('20200101'::character varying(8)) END ('20210101'::character varying(8)) WITH (tablename='t_clientproductind_2_prt_p2020', appendonly=false)
);




INSERT INTO t_clientinstrumentind2 (tradingday, client_id, instrumentid) VALUES
('20190715', 54982370, 'al1908'),
('20190715', 54982370, 'rb2001'),
('20190715', 54982370, 'cu1909'),
('20190715', 54982370, 'cu1908'),
('20190715', 54982370, 'zn1908'),
('20190715', 54982370, 'pb1908');


INSERT INTO t_clientproductind2 (tradingday, productid, clientid, exchangegroup, customertype) VALUES
('20190715', 'cu_f', 54982370, 'SHFE', 1),
('20190715', 'rb_f', 54982370, 'SHFE', 1),
('20190715', 'al_f', 54982370, 'SHFE', 1),
('20190715', 'zn_f', 54982370, 'SHFE', 1),
('20190715', 'pb_f', 54982370, 'SHFE', 1);



SELECT
*
FROM(
SELECT
tradingday,
1 AS ins_SpanInsArbitrageRatio FROM
t_clientinstrumentind2 t WHERE
t.tradingday BETWEEN '20190715'AND'20190715' GROUP BY
t.tradingday
)t1
INNER JOIN
(
SELECT
t.tradingday,
0.9233716475 AS prod_SpanInsArbitrageRatio FROM
t_clientproductind2 t WHERE
t.tradingday BETWEEN'20190715'AND '20190715' GROUP BY
t.tradingday
)t2 ON t1.tradingday = t2.tradingday;

Operating System

any

Anything else

No response

Are you willing to submit PR?

• [X] Yes, I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.