| ==== |
| ---- 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 |
| ==== |