支持包含union子查询的谓词下推

【需求背景】

【需求描述】

【其他说明】

用例构造：
-- 创建 PROMOTIONS 表
CREATE TABLE PROMOTIONS (
    PROMO_ID INT PRIMARY KEY,
    PROD_ID VARCHAR(50),
    PROMO_NAME VARCHAR(100)
);

-- 创建 SALES 表
CREATE TABLE SALES (
    PROD_ID VARCHAR(50),
    AMOUNT_SOLD INT,
    PROMO_ID INT
);

-- 创建 COSTS 表
CREATE TABLE COSTS (
    PROD_ID VARCHAR(50),
    UNIT_PRICE DECIMAL(10, 2),
    PROMO_ID INT
);
-- 创建 PRODUCTS 表
CREATE TABLE PRODUCTS (
    PROD_ID VARCHAR(50) PRIMARY KEY,
    PROD_NAME VARCHAR(100),
    CATEGORY VARCHAR(50),
    DESCRIPTION varchar(50)
);

create index idx1 on PROMOTIONS(PROD_ID);
create index idx2 on SALES(PROD_ID);
create index idx3 on SALES(PROMO_ID);
create index idx4 on COSTS(PROD_ID);
create index idx5 on COSTS(PROMO_ID);
CREATE OR REPLACE PROCEDURE GenerateData(IN numRows INT)
LANGUAGE plpgsql
AS $$
DECLARE
    i INT := 0;
BEGIN
    -- 生成 PRODUCTS 数据
    WHILE i < numRows LOOP
        INSERT INTO PRODUCTS (PROD_ID, PROD_NAME, CATEGORY, DESCRIPTION) 
        VALUES (CONCAT('P', i), CONCAT('Product ', i), 'Category ' || (i % 5), 'Description for product ' || (i % 10));
        i := i + 1;
    END LOOP;

i := 0;

-- 生成 PROMOTIONS 数据
    WHILE i < numRows LOOP
        INSERT INTO PROMOTIONS (PROMO_ID, PROD_ID, PROMO_NAME) 
        VALUES (i, CONCAT('P', FLOOR(RANDOM() * numRows)), CONCAT('Promotion ', i));
        i := i + 1;
    END LOOP;

i := 0;

-- 生成 SALES 数据
    WHILE i < numRows LOOP
        INSERT INTO SALES (PROD_ID, AMOUNT_SOLD, PROMO_ID) 
        VALUES (CONCAT('P', FLOOR(RANDOM() * numRows)), FLOOR(RANDOM() * 1000), FLOOR(RANDOM() * numRows));
        i := i + 1;
    END LOOP;

i := 0;

-- 生成 COSTS 数据
    WHILE i < numRows LOOP
        INSERT INTO COSTS (PROD_ID, UNIT_PRICE, PROMO_ID) 
        VALUES (CONCAT('P', FLOOR(RANDOM() * numRows)), ROUND(RANDOM() * 100, 2), FLOOR(RANDOM() * numRows));
        i := i + 1;
    END LOOP;

END;
$$;

-- 使用存储过程
CALL GenerateData(10000);  -- 生成 10000 行数据

执行用例无法下推：
select prod.* from PROMOTIONS prod join (
select AMOUNT_SOLD,s.PROMO_ID from sales s 
union 
select UNIT_PRICE,c.PROMO_ID from COSTS c ) V on prod.PROMO_ID=v.PROMO_ID and prod.prod_id = 'P8289';
需要手动下推，修改SQL为：
select prod.* from PROMOTIONS prod join (
select AMOUNT_SOLD,s.PROMO_ID from sales s where s.PROMO_ID in (select PROMO_ID from PROMOTIONS where prod_id = 'P8289')
union 
select UNIT_PRICE,c.PROMO_ID from COSTS c where c.PROMO_ID in (select PROMO_ID from PROMOTIONS where prod_id = 'P8289')) V on prod.PROMO_ID=v.PROMO_ID and prod.prod_id = 'P8289';

openGauss/openGauss-server

内容风险标识

评论 (2)

openGauss/openGauss-server .gitee-modal { width: 500px !important; }

内容风险标识