PolarDB:Transitive predicate generation (enhanced condition pushdown)

Simple transitive predicate generation

Applies to both non-equivalence and equivalence relations:

-- Original
SELECT * FROM t1, v1 WHERE v1.a > t1.a AND t1.a > 1;
-- Derived: v1.a > 1 is added as a new single-table condition
SELECT * FROM t1, v1 WHERE v1.a > t1.a AND t1.a > 1 AND v1.a > 1;

-- Original
SELECT * FROM t1, v1 WHERE v1.b < t1.c AND t1.c < t2.c AND t2.c = 1;
-- Derived: propagates through the chain to add v1.b < 1 and t1.c < 1
SELECT * FROM t1, v1 WHERE v1.b < t1.c AND t1.c < t2.c AND t2.c = 1 AND v1.b < 1 AND t1.c < 1;

Commutative expressions

Expressions that satisfy the commutative law are also eligible:

-- Original
SELECT * FROM t1, v2 WHERE v2.c1 < t1.c2 + t1.c1 AND t1.c1 + t1.c2 < 2;
-- t1.c2 + t1.c1 and t1.c1 + t1.c2 are recognized as equivalent; v2.c1 < 2 is derived
SELECT * FROM t1, v2 WHERE v2.c1 < t1.c2 + t1.c1 AND t1.c1 + t1.c2 < 2 AND v2.c1 < 2;

Complex transitive predicate generation based on equivalence relations

Equality chains allow more complex predicates to propagate:

-- Original
SELECT * FROM t3, v3 WHERE v3.c2 = t3.c2 AND t3.c2 LIKE "%00%";
-- The LIKE condition propagates through the equality to v3.c2
SELECT * FROM t3, v3 WHERE v3.c2 = t3.c2 AND t3.c2 LIKE "%00%" AND v3.c2 LIKE "%00%";

-- Original
SELECT * FROM t1, v2 WHERE v2.c1 = t1.c2 AND t1.c2 IN (1,5,7);
-- The IN list propagates through the equality to v2.c1
SELECT * FROM t1, v2 WHERE v2.c1 = t1.c2 AND t1.c2 IN (1,5,7) AND v2.c1 IN (1,5,7);

MIN/MAX derivation from HAVING clauses

MIN() and MAX() aggregate conditions in a HAVING clause are converted into WHERE conditions on the base table:

-- Original
SELECT * FROM t1 GROUP BY a, b, c HAVING MAX(b) > 20;
-- Derived: filters rows at scan time instead of after aggregation
SELECT * FROM t1 WHERE b > 20 GROUP BY a, b, c;

-- Original
SELECT * FROM t1 GROUP BY a, b, c HAVING MIN(b) < 20;
-- Derived
SELECT * FROM t1 WHERE b < 20 GROUP BY a, b, c;

Example 1: Transitive predicate generation combined with condition pushdown

This example shows how a non-equivalence and an equivalence relation combine to derive a single-table condition, which is then pushed into a derived table.

View definition:

CREATE VIEW v1 AS
SELECT c1, c2, MAX(c3) AS max_c3, AVG(c4) AS avg_c4
FROM t2
GROUP BY c1, c2
HAVING max_c3 > 10;

Query:

EXPLAIN FORMAT = TREE SELECT * FROM v1, t1 WHERE v1.c1 >= t1.c2 AND t1.c2 = v1.c2;

Derivation steps:

1. Transitive predicate generation — the conditions v1.c1 >= t1.c2 and t1.c2 = v1.c2 together imply v1.c1 > v1.c2, a new single-table condition on v1. After this step, the effective query becomes:

   SELECT * FROM v1, t1 WHERE v1.c1 >= t1.c2 AND t1.c2 = v1.c2 AND v1.c1 > v1.c2;

2. Condition pushdown — the derived condition v1.c1 > v1.c2 is pushed into the derived table v1 and translated to the base-table column t2.c1 >= t2.c2.

3. Early filtering — the filter runs before aggregation, reducing the rows that GROUP BY and HAVING need to process.

Expected execution plan (the filter on t2 appears inside the aggregation step):

-> Inner hash join (t1.c2 = v1.c2)
    -> Table scan on t1  (cost=0.18 rows=10)
    -> Hash
        -> Table scan on v1
            -> Materialize
                -> Filter: (max_c3 > 10)
                    -> Table scan on <temporary>
                        -> Aggregate using temporary table
                            -> Filter: (t2.c1 >= t2.c2)  (cost=0.75 rows=2)
                                -> Table scan on t2  (cost=0.75 rows=5)

Example 2: Chained MIN/MAX derivation across nested derived tables

This example shows MIN/MAX derivation applied at two levels.

Original query:

SELECT a, b, ee
FROM (
    SELECT b, a, MIN(c) AS ee
    FROM t1
    GROUP BY a, b
) dt
GROUP BY a, b
HAVING MIN(ee) < 20;

Derivation steps:

1. Outer MIN/MAX derivation — the outer HAVING MIN(ee) < 20 derives dt.ee < 20. After this step:

   SELECT a, b, ee
   FROM (
       SELECT b, a, MIN(c) AS ee
       FROM t1
       GROUP BY a, b
       HAVING MIN(c) < 20
   ) dt
   GROUP BY a, b;

2. Inner MIN/MAX derivation — inside dt, HAVING MIN(c) < 20 derives t1.c < 20.

3. Base table filtering — the filter t1.c < 20 runs at the table scan level, eliminating rows before either aggregation step.

Equivalent SQL after both derivations:

SELECT a, b, ee
FROM (
    SELECT b, a, MIN(c) AS ee
    FROM t1
    WHERE c < 20
    GROUP BY a, b
) dt
GROUP BY a, b;

Expected execution plan (the filter on t1 appears at the innermost scan):

-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Table scan on dt
            -> Materialize
                -> Table scan on <temporary>
                    -> Aggregate using temporary table
                        -> Filter: (t1.c < 20)  (cost=*** rows=***)
                            -> Table scan on t1  (cost=*** rows=***)