testdata/workloads/functional-query/queries/QueryTest/inline-view-limit.test

====
---- QUERY
# limit in subquery
select count(*) from (select * from alltypessmall limit 10) a
---- TYPES
BIGINT
---- RESULTS
10
====
---- QUERY
# order by/limit in subquery, followed by addititional selection predicate;
# predicate is not applied in subquery
select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month
from (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypessmall order by id limit 10) a
where bool_col = true
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000000,2009,1
4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000000,2009,1
6,true,6,6,6,60,6.599999904632568,60.6,'01/01/09','6',2009-01-01 00:06:00.150000000,2009,1
8,true,8,8,8,80,8.800000190734863,80.8,'01/01/09','8',2009-01-01 00:08:00.280000000,2009,1
---- DBAPI_RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000,2009,1
4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000,2009,1
6,true,6,6,6,60,6.599999904632568,60.6,'01/01/09','6',2009-01-01 00:06:00.150000,2009,1
8,true,8,8,8,80,8.800000190734863,80.8,'01/01/09','8',2009-01-01 00:08:00.280000,2009,1
====
---- QUERY
# order by/limit in subquery, followed by addititional selection predicate;
# variant w/ join
select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month
from (
    select a.* from alltypessmall a join alltypessmall using (id)
    order by a.id limit 10
  ) a
where bool_col = true
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000000,2009,1
4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000000,2009,1
6,true,6,6,6,60,6.599999904632568,60.6,'01/01/09','6',2009-01-01 00:06:00.150000000,2009,1
8,true,8,8,8,80,8.800000190734863,80.8,'01/01/09','8',2009-01-01 00:08:00.280000000,2009,1
---- DBAPI_RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000,2009,1
4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000,2009,1
6,true,6,6,6,60,6.599999904632568,60.6,'01/01/09','6',2009-01-01 00:06:00.150000,2009,1
8,true,8,8,8,80,8.800000190734863,80.8,'01/01/09','8',2009-01-01 00:08:00.280000,2009,1
====
---- QUERY
# join against subquery with limit creates a merge fragment that applies the limit
select alltypes.id, a.id
from alltypes
join (select id from alltypessmall order by id limit 10) a using (id)
---- TYPES
INT, INT
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,0
1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9
====
---- QUERY
# join against subquery with limit;
# predicate pushdown is prevented in presence of order by/limit clause; variant w/ join
select alltypes.id, a.id
from alltypes
  join (
    select a.id, a.bool_col
    from alltypessmall a join alltypessmall using (id)
    order by a.id limit 10
  ) a using (id)
where a.bool_col = true order by a.id limit 5
---- TYPES
INT, INT
---- RESULTS
0,0
2,2
4,4
6,6
8,8
====
---- QUERY
# IMPALA-2926: Test query plan with a select node that processes multiple row batches
# but filters no rows.
select count(*) from (
  select id from
    (select id from functional.alltypes limit 7300) v1
  where id >= 0) v2
---- RESULTS
7300
---- TYPES
bigint
====
---- QUERY
# Test that limit is enforced by select node.
select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col,
double_col, date_string_col, string_col, timestamp_col, year, month from
(select * from alltypes order by id limit 20) t1 where int_col < 100 limit 5
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000000,2009,1
3,false,3,3,3,30,3.299999952316284,30.3,'01/01/09','3',2009-01-01 00:03:00.300000000,2009,1
4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000000,2009,1
---- DBAPI_RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000,2009,1
3,false,3,3,3,30,3.299999952316284,30.3,'01/01/09','3',2009-01-01 00:03:00.300000,2009,1
4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000,2009,1
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT
====