testdata/workloads/functional-query/queries/QueryTest/with-clause.test

====
---- QUERY
# Basic test with a single with-clause view.
with t as (select int_col x, bigint_col y from functional.alltypestiny)
select count(x), count(y) from t
---- RESULTS
8,8
---- TYPES
BIGINT, BIGINT
====
---- QUERY
# Basic test with a single with-clause view that references a virtual view.
with t as (select abc x, xyz y from functional.complex_view)
select x, y from t order by y limit 10
---- RESULTS
2,'0'
2,'1'
---- TYPES
BIGINT, STRING
====
---- QUERY
# Basic tests with a single with-clause view with column labels.
with t(c1, c2) as (select int_col, bigint_col y from functional.alltypestiny)
select * from t limit 1
---- RESULTS
0,0
---- TYPES
INT, BIGINT
---- LABELS
C1, C2
====
---- QUERY
with t(c1) as (select int_col, bigint_col from functional.alltypestiny)
select * from t limit 1
---- RESULTS
0,0
---- TYPES
INT, BIGINT
---- LABELS
C1, BIGINT_COL
====
---- QUERY
with t(c1, c2) as (select int_col from functional.alltypestiny)
select * from t limit 1
---- CATCH
AnalysisException: WITH-clause view 't' returns 1 columns, but 2 labels were specified.
====
---- QUERY
# Multiple views in with-clause. Only one view is used.
with t1 as (select int_col x, bigint_col y from functional.alltypestiny),
t2 as (select 1 x, 10 y), t3 as (values(2 x, 20 y), (3, 30))
select x, y from t2
---- RESULTS
1,10
---- TYPES
TINYINT, TINYINT
====
---- QUERY
# Multiple views in with-clause. All views are used in a union.
with t1 as (select int_col x, bigint_col y from functional.alltypestiny),
t2 as (select 1 x, 10 y), t3 as (values(2 x, 20 y), (3, 30))
select * from t1 union all select * from t2 union all (select * from t3) order by x limit 20
---- RESULTS
0,0
0,0
0,0
0,0
1,10
1,10
1,10
1,10
1,10
2,20
3,30
---- TYPES
INT, BIGINT
====
---- QUERY
with t1(c1, c2) as (select int_col x, bigint_col y from functional.alltypestiny),
t2(c3, c4) as (select 1 x, 10 y)
select * from t1 limit 1 union all select * from t2 limit 1
---- RESULTS
0,0
1,10
---- TYPES
INT, BIGINT
---- LABELS
C1, C2
====
---- QUERY
# Multiple views in with-clause. All views are used in a join.
with t1 as (select int_col x, bigint_col y from functional.alltypes limit 2),
t2 as (select int_col x, bigint_col y from functional.alltypestiny limit 2),
t3 as (select int_col x, bigint_col y from functional.alltypessmall limit 2)
select * from t1, t2, t3 where t1.x = t2.x and t2.x = t3.x
---- RESULTS
0,0,0,0,0,0
1,10,1,10,1,10
---- TYPES
INT, BIGINT, INT, BIGINT, INT, BIGINT
====
---- QUERY
# Self-join of with-clause table to make sure the join op is properly set
# in the cloned inline-view instances.
with t as (select int_col x, bigint_col y from functional.alltypestiny order by id limit 2)
select * from t t1 left outer join t t2 on t1.y = t2.x full outer join t t3 on t2.y = t3.x
order by t1.x limit 10
---- RESULTS
0,0,0,0,0,0
1,10,NULL,NULL,NULL,NULL
NULL,NULL,NULL,NULL,1,10
---- TYPES
INT, BIGINT, INT, BIGINT, INT, BIGINT
====
---- QUERY
# Multiple with clauses. One for the UnionStmt and one for each union operand.
with t1 as (values('a', 'b'))
(with t2 as (values('c', 'd')) select * from t2) union all
(with t3 as (values('e', 'f')) select * from t3)
---- RESULTS
'e','f'
'c','d'
---- TYPES
STRING, STRING
====
---- QUERY
# With clause containing an order by element that should be cloned properly.
with t1 as (select tinyint_col, count(*) from alltypesagg group by 1
order by 1 desc nulls last limit 10) select * from t1;
---- RESULTS
9,1000
8,1000
7,1000
6,1000
5,1000
4,1000
3,1000
2,1000
1,1000
NULL,2000
---- TYPES
TINYINT, BIGINT
====
---- QUERY
# IMPALA-1357: Analysis of WithClause pollutes global state
select 1 from (
  with w as (
    select 1 from alltypestiny
    where exists (select 1 from alltypestiny))
  select 1 from w) tt
---- RESULTS
1
1
1
1
1
1
1
1
---- TYPES
TINYINT
====