testdata/workloads/functional-query/queries/QueryTest/nested-types-tpch-mem-limit.test - impala - Git at Google

 ====
 ---- QUERY
 # IMPALA-2473: Scan query with large row size leading to oversized batches.
 # Should not OOM given single scanner thread and sufficient memory limit.
 # mem_limit is tuned for a 3-node HDFS minicluster.
 set num_scanner_threads=1;
 set mem_limit=1g;
 select *
 from 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.
 # mem_limit is tuned for a 3-node HDFS minicluster.
 set num_scanner_threads=1;
 set mem_limit=500m;
 select *
 from 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
 # 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=180m;
 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
 ====
	====
	---- QUERY
	# IMPALA-2473: Scan query with large row size leading to oversized batches.
	# Should not OOM given single scanner thread and sufficient memory limit.
	# mem_limit is tuned for a 3-node HDFS minicluster.
	set num_scanner_threads=1;
	set mem_limit=1g;
	select *
	from 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.
	# mem_limit is tuned for a 3-node HDFS minicluster.
	set num_scanner_threads=1;
	set mem_limit=500m;
	select *
	from 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
	# 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=180m;
	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
	====