| ==== |
| ---- QUERY |
| use tpch_nested_parquet |
| ==== |
| ---- QUERY |
| select count(*) |
| from tpch_nested_parquet.customer c, c.c_orders o, o.o_lineitems i |
| where c_custkey in (1, 2) and o_orderkey in (4808192, 1374019) |
| ---- RESULTS |
| 7 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) |
| from tpch_nested_parquet.customer c, c.c_orders o, o.o_lineitems i |
| ---- RESULTS |
| 6001215 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(l_linenumber) |
| from tpch_nested_parquet.customer.c_orders.o_lineitems i |
| ---- RESULTS |
| 6001215 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) |
| from tpch_nested_parquet.customer.c_orders.o_lineitems i |
| ---- RESULTS |
| 6001215 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Relational version of above queries (same results) |
| select count(l_linenumber) from tpch_parquet.lineitem |
| ---- RESULTS |
| 6001215 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Relational version (almost) of above query |
| select c_custkey, count(*) |
| from tpch_parquet.customer join tpch_parquet.orders on c_custkey = o_custkey |
| where c_custkey < 10 |
| group by c_custkey |
| order by c_custkey |
| ---- RESULTS |
| 1,6 |
| 2,7 |
| 4,20 |
| 5,4 |
| 7,16 |
| 8,13 |
| ---- TYPES |
| bigint,bigint |
| ==== |
| ---- QUERY |
| select c_custkey from tpch_nested_parquet.customer c left anti join c.c_orders |
| where c_custkey < 10 order by c_custkey |
| ---- RESULTS |
| 3 |
| 6 |
| 9 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Exercise TOP-N node with array data |
| select c_custkey, c_name, o_orderkey, o_orderpriority |
| from tpch_nested_parquet.customer c, c.c_orders o |
| where c_custkey < 100 |
| order by o_orderpriority, o_orderkey |
| limit 10; |
| ---- RESULTS |
| 29,'Customer#000000029',24322,'1-URGENT' |
| 46,'Customer#000000046',74055,'1-URGENT' |
| 76,'Customer#000000076',309891,'1-URGENT' |
| 53,'Customer#000000053',322501,'1-URGENT' |
| 25,'Customer#000000025',330404,'1-URGENT' |
| 22,'Customer#000000022',346693,'1-URGENT' |
| 20,'Customer#000000020',360067,'1-URGENT' |
| 79,'Customer#000000079',400101,'1-URGENT' |
| 79,'Customer#000000079',410595,'1-URGENT' |
| 65,'Customer#000000065',438881,'1-URGENT' |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # IMPALA-2335: non-nullable zero-width tuples can be NULL. This query hit a DCHECK. |
| # In this query no slots of the supplier.s_partssupps array are materialized, so there |
| # are NULL tuples once the array is unnested. |
| SELECT t2.s_name |
| FROM tpch_nested_parquet.customer t1 |
| INNER JOIN tpch_nested_parquet.supplier t2 |
| ON t2.s_phone = t1.c_phone AND t2.s_acctbal = t1.c_acctbal, |
| t2.s_partsupps t3 |
| WHERE t2.s_suppkey > t2.s_nationkey; |
| ---- RESULTS |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # IMPALA-2229: deduping of zero-length tuples during exchange hits DCHECK. This query has |
| # an exchange that uses full dedup (> 3 tuples, including an array) and includes a tuple |
| # that is zero bytes in size. |
| SELECT STRAIGHT_JOIN r_regionkey, p_retailprice |
| FROM (SELECT p1.p_partkey, p2.p_retailprice, p2.p_mfgr |
| FROM part p1 INNER JOIN part p2 ON p1.p_partkey = p2.p_partkey |
| WHERE p1.p_partkey < 100 AND p2.p_partkey < 100) t2 |
| RIGHT JOIN (SELECT r_name, r_regionkey |
| FROM region r, r.r_nations |
| WHERE r_name = 'AFRICA') t1 |
| ON t1.r_regionkey = t2.p_partkey |
| ---- RESULTS |
| 0,NULL |
| 0,NULL |
| 0,NULL |
| 0,NULL |
| 0,NULL |
| ---- TYPES |
| SMALLINT,DECIMAL |
| ==== |
| ---- QUERY |
| # IMPALA-2338: serialization of arrays in exchange. |
| SELECT COALESCE(t2.p_size, t4.pos, o_orderkey) AS int_col, COUNT(*) |
| FROM customer t1 |
| LEFT JOIN part t2 ON t1.c_custkey = t2.p_partkey, |
| t1.c_orders t3, |
| t3.item.o_lineitems t4 |
| WHERE t1.c_custkey < 100 AND t2.p_partkey < 100 |
| GROUP BY 1 |
| HAVING int_col <= 5 |
| ORDER BY 1 |
| ---- RESULTS |
| 1,177 |
| 2,113 |
| 3,119 |
| 5,71 |
| ---- TYPES |
| BIGINT,BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-2357: Test analytic function with order by on top of subplans that |
| # generate many rows. This test stresses the order by and should not crash. |
| SELECT o_orderkey, r FROM |
| (SELECT o2.o_orderkey, COUNT(o1.pos) OVER (ORDER BY o2.o_orderkey DESC) r |
| FROM tpch_nested_parquet.customer c |
| JOIN c.c_orders o1 |
| JOIN c.c_orders o2 ON (o1.pos = o2.pos)) v |
| ORDER BY o_orderkey LIMIT 1 |
| ---- RESULTS |
| 1,1500000 |
| ---- TYPES |
| BIGINT,BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-2376 |
| # Set memory limit low enough to get the below query to consistently fail. |
| # TODO: change query and/or table so we can consistently check for the following error: |
| # Failed to allocate buffer for collection '...'. |
| set mem_limit=4m; |
| select max(cnt) from customer c, |
| (select count(l_returnflag) cnt from c.c_orders.o_lineitems) v; |
| ---- TYPES |
| BIGINT |
| ---- CATCH |
| Memory limit exceeded |
| ==== |
| ---- QUERY |
| # IMPALA-2473: Scan query with large row size leading to oversized batches. |
| # Should not OOM given single scanner thread and sufficient memory limit. |
| set num_scanner_threads=1; |
| set mem_limit=1g; |
| select * |
| from tpch_nested_parquet.customer c, c.c_orders o, o.o_lineitems |
| order by l_partkey desc, l_suppkey desc, l_linenumber desc, c_custkey |
| limit 5 |
| ---- RESULTS |
| 29888,'Customer#000029888','b8rX5B9YSZSnyro2o',6,'16-601-969-5769',8896.68,'FURNITURE','requests unwind carefully. slyly regular deposits across the fluffily final pac',3386277,'F',196816.57,'1993-11-29','2-HIGH','Clerk#000000176',0,'ular foxes cajole blithely slyly b',200000,7558,2,20.00,22000.00,0.03,0.07,'A','F','1994-01-13','1994-01-12','1994-02-02','COLLECT COD','REG AIR','ly express sauternes breach slyly' |
| 51016,'Customer#000051016','DkC0qs6omn',23,'33-214-457-4366',5249.55,'MACHINERY','ual deposits according to the permanently regular instructions d',1500291,'F',252068.24,'1993-08-05','2-HIGH','Clerk#000000569',0,' are along the furiously even asymptotes. carefully ironic foxes accordin',200000,7558,2,44.00,48400.00,0.04,0.04,'R','F','1993-11-21','1993-10-26','1993-11-25','DELIVER IN PERSON','AIR','xpress packages. slyly regular theodoli' |
| 90343,'Customer#000090343','KaC1qsGLktDWrWRuK4 Nrgbpvhs2rcg',4,'14-925-398-1742',4718.83,'MACHINERY',' even orbits. daringly pending accounts poach above the furiously even frays. final packages run. carefully stealthy',4315909,'O',221276.50,'1997-02-18','4-NOT SPECIFIED','Clerk#000000517',0,'lites: slyly final instructions integrate slyly pending, fina',200000,7558,1,32.00,35200.00,0.00,0.06,'N','O','1997-06-03','1997-04-11','1997-06-17','COLLECT COD','AIR','ns: special ' |
| 111239,'Customer#000111239','xXeQMLtOCiS76XiMsRGzNdWCgZwsK S1l',6,'16-890-971-8054',3103.05,'FURNITURE','ites wake slyly according to the e',4186720,'F',277201.14,'1993-01-26','3-MEDIUM','Clerk#000000743',0,' regular ideas. stealthily bold accounts boost fu',200000,5039,3,47.00,51700.00,0.08,0.04,'A','F','1993-02-01','1993-03-26','1993-02-17','TAKE BACK RETURN','AIR','ias. slyly regular excuses are aft' |
| 116908,'Customer#000116908','kCt00BJVwUSKxuOySsLlLCS',21,'31-857-393-5714',8079.23,'FURNITURE','ending platelets. final dependencies haggle about the ironic, express courts. s',3911265,'O',166014.13,'1997-07-27','3-MEDIUM','Clerk#000000161',0,'ily along the blithely ironic foxes. ',200000,5039,3,36.00,39600.00,0.10,0.02,'N','O','1997-10-31','1997-10-12','1997-11-12','TAKE BACK RETURN','REG AIR','y slyly special deposits. bli' |
| ---- TYPES |
| bigint,string,string,smallint,string,decimal,string,string,bigint,string,decimal,string,string,string,int,string,bigint,bigint,int,decimal,decimal,decimal,decimal,string,string,string,string,string,string,string,string |
| ==== |
| ---- QUERY |
| # IMPALA-2473: Highly selective scan query with large row size. |
| # Should not OOM given single scanner thread and sufficient memory limit. |
| set num_scanner_threads=1; |
| set mem_limit=500m; |
| select * |
| from tpch_nested_parquet.customer c, c.c_orders o, o.o_lineitems |
| where c_phone='20-968-632-1388' and l_partkey = 127499 |
| ---- RESULTS |
| 53722,'Customer#000053722','p2hQ7009dQSEuRdE31HtjVv9idN2rXBHdTx4JRk',10,'20-968-632-1388',4153.75,'HOUSEHOLD','l foxes are ideas. final deposits boost blithely. carefully bo',10277,'F',101242.81,'1994-04-08','3-MEDIUM','Clerk#000000655',0,'counts sleep around the special',127499,2524,1,12.00,18317.88,0.07,0.07,'A','F','1994-07-27','1994-05-22','1994-08-11','NONE','RAIL',' according to the furiously silent th' |
| ---- TYPES |
| bigint,string,string,smallint,string,decimal,string,string,bigint,string,decimal,string,string,string,int,string,bigint,bigint,int,decimal,decimal,decimal,decimal,string,string,string,string,string,string,string,string |
| ==== |
| ---- QUERY |
| # IMPALA-4049: non-grouping aggregation with selective predicate in subplan feeding into |
| # build side of a nested loop join. Reproduces a memory transfer bug triggered by empty |
| # row batches in the build side of the join. |
| select straight_join c_custkey, cnt1 |
| from tpch_nested_parquet.customer c, |
| (select count(*) cnt1 from c.c_orders) v |
| where cnt1 = 1 |
| order by c_custkey |
| ---- RESULTS |
| 1910,1 |
| 2855,1 |
| 9938,1 |
| 14996,1 |
| 17480,1 |
| 25622,1 |
| 42239,1 |
| 43360,1 |
| 48365,1 |
| 52973,1 |
| 67328,1 |
| 86840,1 |
| 87212,1 |
| 131732,1 |
| 138173,1 |
| 140732,1 |
| 148949,1 |
| ---- TYPES |
| bigint, bigint |
| ==== |
| ---- QUERY |
| # This was originally a regression test for IMPALA-5446: dropped status from |
| # Sorter::Reset() when sort cannot get reserved buffer. However with the |
| # IMPALA-3200 changes it now succeeds. |
| set buffer_pool_limit=100m; |
| select c_custkey, c_name, c_address, c_phone, c_acctbal, c_mktsegment, c_comment, |
| o_orderdate, sum(o_totalprice), min(rnum) |
| from customer c, |
| (select o_orderkey, o_totalprice, o_orderdate, row_number() over (order by o_orderdate desc) rnum |
| from c.c_orders) v |
| group by 1, 2, 3, 4, 5, 6, 7, 8 |
| order by 9, 10 desc |
| limit 10 |
| ---- RESULTS |
| 3115,'Customer#000003115','oB 75yHls7ptt5zCheWJLQ','22-291-864-7521',8889.56,'BUILDING','ts are quickly across the bold deposits. carefully spe','1998-04-23',857.71,3 |
| 53551,'Customer#000053551','e,fT3URuJDH,tE6a6Z3Pjg0DZMFSqWbtYgd','15-429-275-5686',1137.38,'FURNITURE',' detect evenly along the blithely pending asymptotes. furiously even notornis detect carefu','1992-04-18',866.90,25 |
| 64043,'Customer#000064043','Snyi GOB00','22-446-332-2750',4627.24,'FURNITURE','the quickly express asymptotes are around the pe','1992-01-31',870.88,11 |
| 107698,'Customer#000107698','stUoykCwpTBAO3OC3lw','33-686-199-1188',698.89,'AUTOMOBILE',' accounts eat carefully express packages. slyly even id','1993-11-21',875.52,15 |
| 1351,'Customer#000001351','NYMFfkNlCGoTeaDrNO9nn','11-916-210-6616',3106.00,'FURNITURE',' accounts after the final deposits sleep fluffily ironic accoun','1994-01-14',877.30,13 |
| 85468,'Customer#000085468','EuFCX4qk4k0O4bV3UHoNVBTP','23-876-106-3120',8926.31,'AUTOMOBILE','kages. slyly even requests according to the ironic, ironic accounts cajole furiou','1997-04-12',884.52,4 |
| 148522,'Customer#000148522','PIDMm8ulW4oam3VsoZL4f ,dpAf3LEV','16-597-824-4946',-133.27,'BUILDING','ly quickly express deposits. regularly regular requests cajole carefully slyly even noto','1995-03-20',885.75,12 |
| 83222,'Customer#000083222','vI3tUuqtUYGPfrXAYeonVD9','27-599-263-5978',289.66,'BUILDING','ost quietly idle foxes. packages at the slyly pending pa','1993-05-02',891.74,5 |
| 25090,'Customer#000025090','92GyVjZZiCBUmn','23-396-651-8663',8497.56,'BUILDING','osits. slyly final pinto beans sleep carefully fluffily express deposits. packages affix. carefully spe','1995-08-12',895.39,15 |
| 27490,'Customer#000027490','jRzZQ1z7T,nrX5F58P,ZH','26-121-240-6744',7512.30,'AUTOMOBILE','slyly quickly even pinto beans: pend','1995-07-25',896.59,14 |
| ---- TYPES |
| bigint,string,string,string,decimal,string,string,string,decimal,bigint |
| ==== |
