SET log_error_verbosity = verbose;
drop table if exists r, r2, s2;
create table r
(
	a char, 
	ts int, 
	te int
);
insert into r values
('a',1,8), 
('j',2,6), 
('a',8,12);
create table r2
(
	a char, 
	b char, 
	ts int, 
	te int
);
create table s2
(
	c int, 
	d char, 
	ts int, 
	te int
);
insert into r2 values
('a','B',1,7), 
('b','B',3,9), 
('c','G',8,10);
insert into s2 values
(1,'B',2,5), 
(2,'B',3,4), 
(3,'B',7,9);
-- 
-- Testing Sub-selects
--
select * from ( (select * from r where a='j') r align r r2 on true with (ts,te,ts,te))x;
 a | ts | te 
---+----+----
 j |  2 |  6
(1 row)

select * from ( (select * from r where ts <= 1) r align (select * from r where a='j') r2 on true with (ts,te,ts,te))x;
 a | ts | te 
---+----+----
 a |  1 |  2
 a |  2 |  6
 a |  6 |  8
(3 rows)

select * from ( (select * from r where ts <= 1) r normalize r r2 on true with (ts,te,ts,te))x;
 a | ts | te 
---+----+----
 a |  1 |  2
 a |  2 |  6
 a |  6 |  8
(3 rows)

--
-- Testing CTEs
--
with x as (select * from r) select * from (x normalize x z using() with(ts,te,ts,te)) c;
 a | ts | te 
---+----+----
 a |  1 |  2
 a |  2 |  6
 a |  6 |  8
 j |  2 |  6
 a |  8 | 12
(5 rows)

with x as (select a,te,ts from r) select ts,te from (x normalize x z using() with(ts,te,ts,te)) c;
 ts | te 
----+----
  1 |  2
  2 |  6
  6 |  8
  2 |  6
  8 | 12
(5 rows)

with x as (select a, b, te, ts from r2) select a, b, ts, te from (x normalize s2 using() with(ts,te,ts,te)) c;
 a | b | ts | te 
---+---+----+----
 a | B |  1 |  2
 a | B |  2 |  3
 a | B |  3 |  4
 a | B |  4 |  5
 a | B |  5 |  7
 b | B |  3 |  4
 b | B |  4 |  5
 b | B |  5 |  7
 b | B |  7 |  9
 c | G |  8 |  9
 c | G |  9 | 10
(11 rows)

with x as (select * from r) select * from (x align x z on true with(ts,te,ts,te)) c;
 a | ts | te 
---+----+----
 a |  1 |  8
 a |  2 |  6
 j |  2 |  6
 a |  8 | 12
(4 rows)

with x as (select a,te,ts from r) select ts,te from (x align x z on true with(ts,te,ts,te)) c;
 ts | te 
----+----
  1 |  8
  2 |  6
  2 |  6
  8 | 12
(4 rows)

with x as (select a, b, te, ts from r2) select a, b, ts, te from (x align s2 on true with(ts,te,ts,te)) c;
 a | b | ts | te 
---+---+----+----
 a | B |  1 |  2
 a | B |  2 |  5
 a | B |  3 |  4
 a | B |  5 |  7
 b | B |  3 |  4
 b | B |  3 |  5
 b | B |  5 |  7
 b | B |  7 |  9
 c | G |  8 |  9
 c | G |  9 | 10
(10 rows)

