Error: Incorrect results when using CTEs and UNION ALL with GPORCA

Products

• VMware Tanzu Data
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

When executing queries in Greenplum Database that utilize Common Table Expressions (CTEs) combined with `UNION ALL` (or multiple references) and GPORCA enabled, you may observe the following:

• Queries return an unexpectedly inflated number of rows.
• Specific branches of a UNION ALL erroneously return tuples that should have been filtered out by a LIKE or NOT LIKE condition.
• The EXPLAIN plan shows a Shared Scan setup where the Result node for the affected consumer branch is completely missing its Filter condition.
• The issue does not occur when the Postgres-based Legacy Planner is used.

Environment

• Product: Greenplum Database (version below 7.8.2)
• Optimizer: GPORCA is enabled (default behavior, or explicitly set via `SET optimizer = on;`)

Cause

This issue is caused by a defect in how GPORCA handles predicate pushdown for pattern-matching operators (such as LIKE). During optimization, GPORCA incorrectly pushes these predicates through CTEs when the CTE is referenced multiple times (Shared Scan), causing the filter to be dropped or ignored in specific branches of the query.

 

Resolution

Workaround

Until an official patch is released, the most effective and safest workaround is to disable GPORCA and fall back to the PostgreSQL Legacy Planner for the affected queries or sessions. The Legacy Planner correctly processes this logic and does not trigger the Shared Scan predicate drop.

Execute the following command before running the affected SQL:

SET optimizer = off;

 

Permanent Fix:

This issue is targeted to be fixed in a future release of Greenplum Database. Please subscribe to this article (see How to subscribe to a Knowledge Article) to be notified of the fix version.

Additional Information

Below is the minimal reproduction script provided by the Greenplum Engineering team. You can run this in your test environment to verify if you are hitting this specific defect.

-- 1. Create minimal tables 
CREATE TABLE t_fact (
    id int, 
    location_id varchar(50), 
    transport_type varchar(50), 
    flag int
) DISTRIBUTED BY (id);

CREATE TABLE t_dim (
    dim_location_id varchar(50), 
    location_name varchar(50)
) DISTRIBUTED BY (dim_location_id);

-- 2. Insert test data
INSERT INTO t_dim VALUES ('LOC1', 'Division_A');

-- Row 1: Intended for Upper_Half (contains 'air')
INSERT INTO t_fact VALUES (1, 'LOC1', 'airplane', 1); 
-- Row 2: Intended for Lower_Half (no 'air', flag=1)
INSERT INTO t_fact VALUES (2, 'LOC1', 'truck', 1); 

-- 3. Gather statistics (Crucial for optimizer routing)
ANALYZE t_fact;
ANALYZE t_dim;

-- 4. Enable GPORCA (Triggers the bug)
SET optimizer = on;

WITH cte AS (
    SELECT f.id, f.transport_type, f.flag, f.location_id
    FROM t_fact f
    -- Forces a more complex CTE, prompting a Shared Scan
    LEFT JOIN t_dim d ON f.location_id = d.dim_location_id 
)
SELECT id, 'Upper_Half' AS source_part FROM cte 
-- Consumer 1: Mixes common equality filter with a LIKE predicate
WHERE location_id = 'LOC1'          
  AND transport_type LIKE '%air%' 
UNION ALL
SELECT id, 'Lower_Half' AS source_part FROM cte 
-- Consumer 2: Mixes common equality filter with NOT LIKE
WHERE location_id = 'LOC1'          
  AND flag = 1 
  AND transport_type NOT LIKE '%air%';

/*
Unexpected/Incorrect Output (3 rows):
 id | source_part
----+-------------
  1 | Upper_Half
  2 | Upper_Half   <-- BUG: The LIKE filter was pruned. Row 2 incorrectly bypasses the filter.
  2 | Lower_Half
*/

-- 5. Disable GPORCA (Verifies correct behavior)
SET optimizer = off;

-- Re-run the exact same query from Step 4
/*
Expected/Correct Output (2 rows):
 id | source_part
----+-------------
  1 | Upper_Half
  2 | Lower_Half
*/