SET OPERATIONS: Rewrite set-operation-tree
Created by: Piiit
A set operation tree contains connected SelectStmt
- nodes. A node can be a setop when stmt->op
is set to something different from SETOP_NONE
, or a select. We go through this tree and when we find a periodClause
attached to stmt
then we transform the nodes below.
Example:
Input query:
select * from e
union period with (ts,te,ts,te)
select * from f
Rewritten query (if we had two leaf nodes below a temporal set operation):
with
x as ( select * from e),
y as ( select * from f)
select * from (x normalize y using(a,b) with (ts,te,ts,te)) x
union
select * from (y normalize x using(a,b) with (ts,te,ts,te)) y
...where a,b
are all non-temporal attribute columns.
NB: We cannot use using
here, because the CTEs x
and y
could have different column names, and set operations which take the top-most relations column names are not transformed yet. Hence, we must use on x.xc1 = y.yc1 and x.xc2 = y.yc2 ...
instead. Where xc1
stands for column 1 of relation x
, and yc2
is then column 2 of relation y
, etc.
Input query2:
select * from e
union period with (ts,te,ts,te)
select * from f
union period with (ts,te,ts,te)
select * from g
Rewritten query2 (if we had another set operation on the left argument, and the upper transformation below. NB: right arguments never have set-op-nodes):
with
y1 as (
with
x1 as ( select * from e),
x2 as ( select * from f)
select * from (x1 normalize x2 using(a,b) with (ts,te,ts,te)) x1
union
select * from (x2 normalize x1 using(a,b) with (ts,te,ts,te)) x2
),
y2 as ( select * from g)
select * from (y1 normalize y2 using(a,b) with (ts,te,ts,te)) y1
union
select * from (y2 normalize y1 using(a,b) with (ts,te,ts,te)) y2