TEMPORAL JOINS: Create a WITH clause first to materialize subqueries
Created by: Piiit
We need the original, i.e., values before alignment, temporal bounds for the "absorb" where clause after the joins.
The temporal join query
SELECT * FROM r
JOIN PERIOD WITH (ts,te,ts,te) s ON b=d;
gets rewritten into
WITH
x1 AS (SELECT *, ts Us, te Ue FROM s),
x2 AS (SELECT *, ts Vs, te Ve FROM r)
SELECT * FROM
(x1 ALIGN x2 ON b=d WITH (ts,te,ts,te))
right JOIN
(x2 ALIGN x1 ON b=d WITH (ts,te,ts,te))
ON b = d AND x1.ts = x2.ts AND x1.te = x2.te
-- This is the absorb where clause:
WHERE (x1.ts = Us OR x1.ts = Vs)
AND (x1.te = Ue OR x1.te = Ve)
OR Us IS NULL
OR Vs IS NULL;
The absorb clause should maybe add coalesce
, because it is not possible to add coalesce to the target list of the join.
WHERE (coalesce(x1.ts, x2.ts) = Us OR coalesce(x1.ts, x2.ts) = Vs)
AND (coalesce(x1.te, x2.te) = Ue OR coalesce(x1.te, x2.te) = Ve)
OR Us IS NULL
OR Vs IS NULL;
Belongs to #55.