| ==== |
| ---- QUERY |
| # IMPALA-9983 |
| # Base case. Limit pushdown into analytic sort should be applied |
| select * from ( |
| select l_partkey, l_quantity, l_orderkey, |
| rank() over (partition by l_partkey order by l_orderkey desc) rk |
| from lineitem) dt |
| where rk <= 10 |
| order by l_partkey, l_quantity, l_orderkey, rk |
| limit 10 |
| ---- RESULTS |
| 1,16.00,4464900,9 |
| 1,20.00,4450401,10 |
| 1,21.00,5618244,3 |
| 1,30.00,4912929,7 |
| 1,32.00,5926723,1 |
| 1,33.00,5362629,4 |
| 1,37.00,4810753,8 |
| 1,38.00,5862498,2 |
| 1,40.00,5352710,5 |
| 1,42.00,5120486,6 |
| ---- TYPES |
| BIGINT, DECIMAL, BIGINT, BIGINT |
| ==== |
| ---- QUERY |
| # Multi column partition-by which is prefix of sort exprs. |
| # Limit pushdown into analytic sort should be applied |
| select * from ( |
| select l_partkey, l_quantity, l_orderkey, |
| rank() over (partition by l_partkey, l_quantity |
| order by l_orderkey desc) rk |
| from lineitem) dt |
| where rk <= 10 |
| order by l_partkey, l_quantity, l_orderkey, rk |
| limit 10 |
| ---- RESULTS |
| 1,1.00,599361,1 |
| 1,8.00,3438019,1 |
| 1,11.00,4422049,1 |
| 1,14.00,2535332,1 |
| 1,15.00,545153,1 |
| 1,16.00,1139363,3 |
| 1,16.00,1695463,2 |
| 1,16.00,4464900,1 |
| 1,19.00,1876199,1 |
| 1,20.00,4450401,1 |
| ---- TYPES |
| BIGINT, DECIMAL, BIGINT, BIGINT |
| ==== |
| ---- QUERY |
| # No predicate after the analytic function. |
| # Limit pushdown should be applied |
| select * from ( |
| select l_partkey, l_quantity, l_orderkey, |
| rank() over (partition by l_partkey, l_quantity |
| order by l_orderkey desc) rk |
| from lineitem) dt |
| order by l_partkey, l_quantity, l_orderkey, rk |
| limit 10 |
| ---- RESULTS |
| 1,1.00,599361,1 |
| 1,8.00,3438019,1 |
| 1,11.00,4422049,1 |
| 1,14.00,2535332,1 |
| 1,15.00,545153,1 |
| 1,16.00,1139363,3 |
| 1,16.00,1695463,2 |
| 1,16.00,4464900,1 |
| 1,19.00,1876199,1 |
| 1,20.00,4450401,1 |
| ---- TYPES |
| BIGINT, DECIMAL, BIGINT, BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-10299: the limit pushdown optimization should not be applied because |
| # the sort orders do not match exactly. |
| select * |
| from ( |
| select month, id, rank() over (partition by month order by id desc) rnk |
| from functional_parquet.alltypes |
| WHERE month >= 11) v |
| order by month, id |
| limit 3 |
| ---- RESULTS |
| 11,3040,600 |
| 11,3041,599 |
| 11,3042,598 |
| ---- TYPES |
| INT, INT, BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-10296: the limit pushdown through rank() requires tie handling in |
| # the top-n. This query is designed to exercise that by using an analytic |
| # order by with low cardinality and therefore many ties. The query returns |
| # values from two analytic partitions. |
| select l_orderkey, l_partkey, l_suppkey, l_linenumber, l_shipmode, rnk from ( |
| select *, rank() over (partition by l_partkey order by l_shipmode) rnk |
| from lineitem) v |
| where rnk <= 50 |
| order by l_partkey, l_orderkey, l_suppkey, l_linenumber, l_shipmode |
| limit 50 |
| ---- RESULTS |
| 504065,1,2502,6,'RAIL',15 |
| 545153,1,2,4,'SHIP',24 |
| 599361,1,5002,7,'AIR',1 |
| 1139363,1,2,1,'FOB',8 |
| 1225698,1,2,5,'FOB',8 |
| 1695463,1,5002,5,'FOB',8 |
| 1829732,1,2,3,'RAIL',15 |
| 1876199,1,2502,3,'AIR',1 |
| 2265089,1,2502,4,'RAIL',15 |
| 2301222,1,2,5,'SHIP',24 |
| 2485411,1,2502,6,'REG AIR',21 |
| 2535332,1,5002,4,'MAIL',12 |
| 2603011,1,5002,2,'MAIL',12 |
| 2698307,1,2,3,'RAIL',15 |
| 3438019,1,2,6,'RAIL',15 |
| 3700610,1,5002,2,'AIR',1 |
| 3743746,1,5002,6,'SHIP',24 |
| 4076803,1,2,4,'SHIP',24 |
| 4168578,1,5002,3,'TRUCK',30 |
| 4343170,1,7502,2,'SHIP',24 |
| 4422049,1,7502,5,'AIR',1 |
| 4450401,1,7502,1,'MAIL',12 |
| 4464900,1,2502,1,'AIR',1 |
| 4810753,1,5002,5,'REG AIR',21 |
| 4912929,1,5002,5,'TRUCK',30 |
| 5120486,1,2,1,'SHIP',24 |
| 5352710,1,7502,2,'AIR',1 |
| 5362629,1,5002,3,'AIR',1 |
| 5618244,1,7502,5,'REG AIR',21 |
| 5862498,1,2,6,'RAIL',15 |
| 5926723,1,2,2,'FOB',8 |
| 16135,2,5003,1,'RAIL',11 |
| 269286,2,3,2,'RAIL',11 |
| 437670,2,3,5,'MAIL',6 |
| 698981,2,3,1,'TRUCK',25 |
| 864452,2,7503,6,'TRUCK',25 |
| 957444,2,7503,2,'TRUCK',25 |
| 976196,2,7503,2,'RAIL',11 |
| 1140614,2,5003,2,'SHIP',22 |
| 1204321,2,2503,3,'MAIL',6 |
| 1339139,2,5003,5,'REG AIR',19 |
| 1394727,2,2503,3,'AIR',1 |
| 2189347,2,7503,3,'TRUCK',25 |
| 2664550,2,7503,4,'AIR',1 |
| 2774018,2,2503,1,'RAIL',11 |
| 2838083,2,5003,5,'SHIP',22 |
| 2859844,2,5003,2,'AIR',1 |
| 2974054,2,2503,5,'REG AIR',19 |
| 3204707,2,2503,1,'MAIL',6 |
| 3815428,2,7503,3,'TRUCK',25 |
| ---- TYPES |
| BIGINT, BIGINT, BIGINT, INT, STRING, BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-10296: the same query as above with a higher limit. The limit pushdown |
| # optimization should not be applied and the results should be the same as above, |
| # but with additional rows. |
| select l_orderkey, l_partkey, l_suppkey, l_linenumber, l_shipmode, rnk from ( |
| select *, rank() over (partition by l_partkey order by l_shipmode) rnk |
| from lineitem) v |
| where rnk <= 50 |
| order by l_partkey, l_orderkey, l_suppkey, l_linenumber, l_shipmode |
| limit 70 |
| ---- RESULTS |
| 504065,1,2502,6,'RAIL',15 |
| 545153,1,2,4,'SHIP',24 |
| 599361,1,5002,7,'AIR',1 |
| 1139363,1,2,1,'FOB',8 |
| 1225698,1,2,5,'FOB',8 |
| 1695463,1,5002,5,'FOB',8 |
| 1829732,1,2,3,'RAIL',15 |
| 1876199,1,2502,3,'AIR',1 |
| 2265089,1,2502,4,'RAIL',15 |
| 2301222,1,2,5,'SHIP',24 |
| 2485411,1,2502,6,'REG AIR',21 |
| 2535332,1,5002,4,'MAIL',12 |
| 2603011,1,5002,2,'MAIL',12 |
| 2698307,1,2,3,'RAIL',15 |
| 3438019,1,2,6,'RAIL',15 |
| 3700610,1,5002,2,'AIR',1 |
| 3743746,1,5002,6,'SHIP',24 |
| 4076803,1,2,4,'SHIP',24 |
| 4168578,1,5002,3,'TRUCK',30 |
| 4343170,1,7502,2,'SHIP',24 |
| 4422049,1,7502,5,'AIR',1 |
| 4450401,1,7502,1,'MAIL',12 |
| 4464900,1,2502,1,'AIR',1 |
| 4810753,1,5002,5,'REG AIR',21 |
| 4912929,1,5002,5,'TRUCK',30 |
| 5120486,1,2,1,'SHIP',24 |
| 5352710,1,7502,2,'AIR',1 |
| 5362629,1,5002,3,'AIR',1 |
| 5618244,1,7502,5,'REG AIR',21 |
| 5862498,1,2,6,'RAIL',15 |
| 5926723,1,2,2,'FOB',8 |
| 16135,2,5003,1,'RAIL',11 |
| 269286,2,3,2,'RAIL',11 |
| 437670,2,3,5,'MAIL',6 |
| 698981,2,3,1,'TRUCK',25 |
| 864452,2,7503,6,'TRUCK',25 |
| 957444,2,7503,2,'TRUCK',25 |
| 976196,2,7503,2,'RAIL',11 |
| 1140614,2,5003,2,'SHIP',22 |
| 1204321,2,2503,3,'MAIL',6 |
| 1339139,2,5003,5,'REG AIR',19 |
| 1394727,2,2503,3,'AIR',1 |
| 2189347,2,7503,3,'TRUCK',25 |
| 2664550,2,7503,4,'AIR',1 |
| 2774018,2,2503,1,'RAIL',11 |
| 2838083,2,5003,5,'SHIP',22 |
| 2859844,2,5003,2,'AIR',1 |
| 2974054,2,2503,5,'REG AIR',19 |
| 3204707,2,2503,1,'MAIL',6 |
| 3815428,2,7503,3,'TRUCK',25 |
| 3978178,2,2503,4,'REG AIR',19 |
| 4052995,2,3,4,'MAIL',6 |
| 4212039,2,3,2,'FOB',4 |
| 4291943,2,5003,5,'MAIL',6 |
| 4325446,2,2503,6,'SHIP',22 |
| 4563589,2,2503,2,'TRUCK',25 |
| 4701030,2,7503,1,'RAIL',11 |
| 4849414,2,2503,4,'TRUCK',25 |
| 5000327,2,5003,3,'RAIL',11 |
| 5240129,2,7503,4,'RAIL',11 |
| 5399651,2,3,1,'TRUCK',25 |
| 5435686,2,7503,4,'FOB',4 |
| 5879491,2,3,4,'RAIL',11 |
| 367685,3,7504,1,'RAIL',20 |
| 480000,3,5004,5,'MAIL',15 |
| 601123,3,2504,3,'AIR',1 |
| 789220,3,7504,5,'TRUCK',34 |
| 810723,3,4,1,'AIR',1 |
| 951425,3,2504,6,'RAIL',20 |
| 993152,3,4,1,'SHIP',31 |
| ---- TYPES |
| BIGINT, BIGINT, BIGINT, INT, STRING, BIGINT |
| ==== |
