use test;

select * from a1 a join (select today + 1d + 2s as ts, f, g, 'a' from b1) b on a.ts = b.ts;
select * from a1 a join (select timestamp '__today__ 00:00:00.000' + 1d + 2s as ts, f, g, 'a' from b1) b on a.ts = b.ts;
select * from a1 a join (select timestamp '__today__ 00:00:00.000' + 1d + 2s as ts, f, g, 'a' from b1) b on a.ts = timetruncate(b.ts, 1d);
select * from a1 a join (select timestamp '__today__ 00:00:00.000' + 1d + 2s as ts, f, g, 'a' from b1) b on a.ts = timetruncate(b.ts + 1s, 1d);
select * from (select ts + 2s as ts,f from a1) a join (select timestamp '__today__ 00:00:00.000' + 1d + 2s as ts, f, g, 'a' from b1) b on a.ts = b.ts;
select * from a1 a join (select case when 1 > 0 then timestamp '__today__ 00:00:00.000' else timestamp '__tomorrow__ 00:00:00.000' end as ts, f, g, 'a' from b1) b on a.ts = b.ts;
select * from a1 a join (select timestamp '__today__ 00:00:00.000' + 1s as ts, f, g, 'a' from b1) b on a.ts = b.ts + 1d + 1s;
select * from a1 a join (select timestamp '__today__ 00:00:00.000' + 1s as ts, f, g, 'a' from b1) b on a.ts = timetruncate(b.ts + 1d + 1s, 1d) + 2s;

select * from a1 a, (select today + 1d + 2s as ts, f, g, 'a' from b1) b where a.ts = b.ts;
select * from a1 a, (select timestamp '__today__ 00:00:00.000' + 1d + 2s as ts, f, g, 'a' from b1) b where a.ts = b.ts;
select * from a1 a, (select timestamp '__today__ 00:00:00.000' + 1d + 2s as ts, f, g, 'a' from b1) b where a.ts = timetruncate(b.ts, 1d);
select * from a1 a, (select timestamp '__today__ 00:00:00.000' + 1d + 2s as ts, f, g, 'a' from b1) b where a.ts = timetruncate(b.ts + 1s, 1d);
select * from (select ts + 2s as ts,f from a1) a, (select timestamp '__today__ 00:00:00.000' + 1d + 2s as ts, f, g, 'a' from b1) b where a.ts = b.ts;
select * from a1 a, (select case when 1 > 0 then timestamp '__today__ 00:00:00.000' else timestamp '__tomorrow__ 00:00:00.000' end as ts, f, g, 'a' from b1) b where a.ts = b.ts; 
select * from a1 a, (select timestamp '__today__ 00:00:00.000' + 1s as ts, f, g, 'a' from b1) b where a.ts = b.ts + 1d + 1s;
select * from a1 a, (select timestamp '__today__ 00:00:00.000' + 1s as ts, f, g, 'a' from b1) b where a.ts = timetruncate(b.ts + 1d + 1s, 1d) + 2s;

select * from a1 a, (select today as ts, f, g, 'a' from b1) b where a.ts = b.ts order by b.ts, b.f desc;
select count(*) from a1 a, (select today as ts, f, g, 'a' from b1) b where a.ts = b.ts group by b.ts order by b.ts;
select b.ts, count(*) from a1 a left join (select today as ts, f, g, 'a' from b1) b on a.ts = b.ts group by b.ts order by b.ts;
select * from (select * from a1 order by ts desc) a join (select today as ts1, * from b1 order by ts1 desc) b on a.ts = b.ts1;

/*table+subq*/
select * from a1 a join (select * from b1 order by ts) b on a.ts = b.ts;
select * from a1 a join (select * from b1 order by ts desc) b on a.ts = b.ts;

/*table+const subq*/
select * from a1 a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts;
select * from a1 a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts;

select * from a1 a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts1;
select * from a1 a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts1;
select * from a1 a join (select today() as ts1, * from b1 order by f) b on a.ts = b.ts1;


/*subq+subq*/
select * from (select * from a1 order by ts) a join (select * from b1 order by ts) b on a.ts = b.ts;
select * from (select * from a1 order by ts) a join (select * from b1 order by ts desc) b on a.ts = b.ts;

select * from (select * from a1 order by ts desc) a join (select * from b1 order by ts desc) b on a.ts = b.ts;

/*subq+const subq*/
select * from (select * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts;
select * from (select * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts;

select * from (select * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts;
select * from (select * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts;

select * from (select * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts1;
select * from (select * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts1;
select * from (select * from a1 order by ts) a join (select today() as ts1, * from b1 order by f) b on a.ts = b.ts1;

select * from (select * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts1;
select * from (select * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts1;
select * from (select * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by f) b on a.ts = b.ts1;

/*const subq+const subq*/
select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts;
select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts;

select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts;
select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts;


select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts;
select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts;

select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts;
select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts;

select * from (select today() as ts1, * from a1 order by f desc) a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts;
select * from (select today() as ts1, * from a1 order by f desc) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts;


select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts1;
select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts1;
select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by f) b on a.ts1 = b.ts1;

select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts1;
select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts1;
select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by f) b on a.ts1 = b.ts1;

select * from (select today() as ts1, * from a1 order by f desc) a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts1;
select * from (select today() as ts1, * from a1 order by f desc) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts1;
select * from (select today() as ts1, * from a1 order by f desc) a join (select today() as ts1, * from b1 order by f) b on a.ts1 = b.ts1;


select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts1 and a.ts = b.ts;
select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts1 and a.ts = b.ts;
select * from (select today() as ts1, * from a1 order by ts) a join (select today() as ts1, * from b1 order by f) b on a.ts1 = b.ts1 and a.ts = b.ts;

select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts1 and a.ts = b.ts;
select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts1 and a.ts = b.ts;
select * from (select today() as ts1, * from a1 order by ts desc) a join (select today() as ts1, * from b1 order by f) b on a.ts1 = b.ts1 and a.ts = b.ts;

select * from (select today() as ts1, * from a1 order by f desc) a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts1 and a.ts = b.ts;
select * from (select today() as ts1, * from a1 order by f desc) a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts1 and a.ts = b.ts;
select * from (select today() as ts1, * from a1 order by f desc) a join (select today() as ts1, * from b1 order by f) b on a.ts1 = b.ts1 and a.ts = b.ts;

/*functions*/
select * from a1 a join (select to_timestamp('__today__', 'yyyy-mm-dd') as ts1, * from b1) b on a.ts = b.ts1;
select * from a1 a join (select to_timestamp('__today__', 'yyyy-mm-dd') + 1s as ts1, * from b1) b on a.ts = b.ts1;
select * from a1 a join (select to_unixtimestamp('__today__ 00:00:00.000', 1) as ts1, * from b1) b on a.ts = b.ts1;
select * from a1 a join (select to_unixtimestamp('__today__ 00:00:00.000', 1) + 1s as ts1, * from b1) b on a.ts = b.ts1;

/*view*/
select * from view1 a join (select * from b1 order by ts) b on a.ts = b.ts;
select * from view1 a join (select * from b1 order by ts desc) b on a.ts = b.ts;
select * from view1 a join (select * from b1 order by ts) b on a.ts1 = b.ts;
select * from view1 a join (select * from b1 order by ts desc) b on a.ts1 = b.ts;

select * from view1 a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts;
select * from view1 a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts;
select * from view1 a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts;
select * from view1 a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts;

select * from view1 a join (select today() as ts1, * from b1 order by ts) b on a.ts = b.ts1;
select * from view1 a join (select today() as ts1, * from b1 order by ts desc) b on a.ts = b.ts1;
select * from view1 a join (select today() as ts1, * from b1 order by f) b on a.ts = b.ts1;
select * from view1 a join (select today() as ts1, * from b1 order by ts) b on a.ts1 = b.ts1;
select * from view1 a join (select today() as ts1, * from b1 order by ts desc) b on a.ts1 = b.ts1;
select * from view1 a join (select today() as ts1, * from b1 order by f) b on a.ts1 = b.ts1;

select * from view1 a join (select today() as ts1, * from b1 order by f) b on a.ts1 = b.ts1 where a.ts1 is null;
select * from view1 a join (select today() as ts1, * from b1 order by f) b on a.ts1 = b.ts1 where a.ts1 is not null;
select * from view1 a, (select today() as ts1, * from b1 order by f) b where a.ts1 = b.ts1;
