| ==== |
| ---- QUERY |
| # Basic subplan with an unnest + join. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c, c.c_orders o |
| where c_custkey in (1, 2) |
| ---- RESULTS |
| 1,'BUILDING',454791,'1992-04-19' |
| 1,'BUILDING',579908,'1996-12-09' |
| 1,'BUILDING',3868359,'1992-08-22' |
| 1,'BUILDING',4273923,'1997-03-23' |
| 1,'BUILDING',4808192,'1996-06-29' |
| 1,'BUILDING',5133509,'1996-07-01' |
| 2,'AUTOMOBILE',430243,'1994-12-24' |
| 2,'AUTOMOBILE',1071617,'1995-03-10' |
| 2,'AUTOMOBILE',1374019,'1992-04-05' |
| 2,'AUTOMOBILE',1763205,'1994-08-28' |
| 2,'AUTOMOBILE',1842406,'1996-08-05' |
| 2,'AUTOMOBILE',2992930,'1994-05-21' |
| 2,'AUTOMOBILE',3986496,'1997-02-22' |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Basic subplan with a predicate on parent and nested collection. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c, c.c_orders o |
| where c_custkey in (1, 2) and o_orderkey % 2 = 0 |
| ---- RESULTS |
| 1,'BUILDING',579908,'1996-12-09' |
| 1,'BUILDING',4808192,'1996-06-29' |
| 2,'AUTOMOBILE',1842406,'1996-08-05' |
| 2,'AUTOMOBILE',2992930,'1994-05-21' |
| 2,'AUTOMOBILE',3986496,'1997-02-22' |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Test subplan with limit. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c, c.c_orders o |
| where c_custkey = 1 |
| limit 3 |
| ---- RESULTS |
| 1,regex:.*,regex:.*,regex:.* |
| 1,regex:.*,regex:.*,regex:.* |
| 1,regex:.*,regex:.*,regex:.* |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Subplan with a limit applied by the unnest inside the subplan. |
| # Use regex as expected values because this test depends on the insertion |
| # order of the orders in data loading which is non-deterministic. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c, (select * from c.c_orders limit 3) v |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,regex:.+,regex:.+,regex:.+ |
| 1,regex:.+,regex:.+,regex:.+ |
| 1,regex:.+,regex:.+,regex:.+ |
| 2,regex:.+,regex:.+,regex:.+ |
| 2,regex:.+,regex:.+,regex:.+ |
| 2,regex:.+,regex:.+,regex:.+ |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Subplan with an unnest that evaluates a conjunct. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate, o_shippriority |
| from customer c, (select *, coalesce(o_orderkey, o_shippriority) x from c.c_orders) v |
| where c_custkey < 4 and x % 2 = 0 |
| ---- RESULTS |
| 1,'BUILDING',579908,'1996-12-09',0 |
| 1,'BUILDING',4808192,'1996-06-29',0 |
| 2,'AUTOMOBILE',1842406,'1996-08-05',0 |
| 2,'AUTOMOBILE',2992930,'1994-05-21',0 |
| 2,'AUTOMOBILE',3986496,'1997-02-22',0 |
| ---- TYPES |
| bigint,string,bigint,string,int |
| ==== |
| ---- QUERY |
| # Subplan with select node on top of the unnest+limit. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c, (select * from c.c_orders limit 3) v |
| where c_custkey < 4 and o_orderdate != "" |
| ---- RESULTS |
| 1,regex:.+,regex:.+,regex:.+ |
| 1,regex:.+,regex:.+,regex:.+ |
| 1,regex:.+,regex:.+,regex:.+ |
| 2,regex:.+,regex:.+,regex:.+ |
| 2,regex:.+,regex:.+,regex:.+ |
| 2,regex:.+,regex:.+,regex:.+ |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Top-n inside a subplan. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c, (select * from c.c_orders order by o_totalprice limit 2) v |
| where c_custkey < 5 |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 1,'BUILDING',579908,'1996-12-09' |
| 1,'BUILDING',4808192,'1996-06-29' |
| 2,'AUTOMOBILE',430243,'1994-12-24' |
| 2,'AUTOMOBILE',1763205,'1994-08-28' |
| 4,'MACHINERY',1755398,'1997-06-12' |
| 4,'MACHINERY',2765152,'1996-08-11' |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Union inside a subplan. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c, |
| (select * from c.c_orders where o_orderkey % 2 = 0 |
| union all |
| select * from c.c_orders where o_orderkey % 2 = 1) v |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,'BUILDING',454791,'1992-04-19' |
| 1,'BUILDING',579908,'1996-12-09' |
| 1,'BUILDING',3868359,'1992-08-22' |
| 1,'BUILDING',4273923,'1997-03-23' |
| 1,'BUILDING',4808192,'1996-06-29' |
| 1,'BUILDING',5133509,'1996-07-01' |
| 2,'AUTOMOBILE',430243,'1994-12-24' |
| 2,'AUTOMOBILE',1071617,'1995-03-10' |
| 2,'AUTOMOBILE',1374019,'1992-04-05' |
| 2,'AUTOMOBILE',1763205,'1994-08-28' |
| 2,'AUTOMOBILE',1842406,'1996-08-05' |
| 2,'AUTOMOBILE',2992930,'1994-05-21' |
| 2,'AUTOMOBILE',3986496,'1997-02-22' |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Non-grouping aggregation inside subplan. |
| select c_custkey, v.* from customer c, |
| (select count(o_orderkey) c, sum(o_totalprice) s, avg(o_totalprice) a, |
| max(o_orderstatus) mx, min(o_orderdate) mn |
| from c.c_orders) v |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,6,587762.91,97960.485000,'O','1992-04-19' |
| 3,0,NULL,NULL,'NULL','NULL' |
| 2,7,1028273.43,146896.204286,'P','1992-04-05' |
| ---- TYPES |
| bigint,bigint,decimal,decimal,string,string |
| ==== |
| ---- QUERY |
| # Non-grouping count(*) aggregation inside subplan. |
| # Test count(*) in isolation to test a collection table ref with an item tuple |
| # that has no materialized slots. |
| select c_custkey, v.* from customer c, |
| (select count(*) cnt from c.c_orders) v |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,6 |
| 2,7 |
| 3,0 |
| ---- TYPES |
| bigint,bigint |
| ==== |
| ---- QUERY |
| # Non-grouping count(*) aggregation inside subplan. |
| # Test count(*) in isolation to test a collection table ref with an item tuple |
| # that has no materialized slots. |
| select c_custkey, v.* from customer c, |
| (select count(*) cnt from c.c_orders.o_lineitems) v |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,15 |
| 2,26 |
| 3,0 |
| ---- TYPES |
| bigint,bigint |
| ==== |
| ---- QUERY |
| # Test grouping aggregation inside a subplan. |
| select c_custkey, v.* from customer c, |
| (select o_orderpriority, count(o_orderkey) c, sum(o_totalprice) s, |
| avg(o_totalprice) a, max(o_orderstatus) mx, |
| min(o_orderdate) mn |
| from c.c_orders |
| group by o_orderpriority) v |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,'2-HIGH',1,65478.05,65478.050000,'O','1996-06-29' |
| 1,'5-LOW',2,177125.10,88562.550000,'O','1992-08-22' |
| 1,'1-URGENT',2,249248.75,124624.375000,'O','1992-04-19' |
| 1,'3-MEDIUM',1,95911.01,95911.010000,'O','1997-03-23' |
| 2,'2-HIGH',1,221397.35,221397.350000,'P','1995-03-10' |
| 2,'4-NOT SPECIFIED',2,136380.51,68190.255000,'F','1994-05-21' |
| 2,'1-URGENT',4,670495.57,167623.892500,'O','1992-04-05' |
| ---- TYPES |
| bigint,string,bigint,decimal,decimal,string,string |
| ==== |
| ---- QUERY |
| # Test analytic functions without partition by and order by inside a subplan. |
| select c_custkey, v.* from customer c, |
| (select count(o_orderkey) over() c, sum(o_totalprice) over() s, |
| avg(o_totalprice) over() a, max(o_orderstatus) over() mx, |
| min(o_orderdate) over() mn |
| from c.c_orders) v |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,6,587762.91,97960.485000,'O','1992-04-19' |
| 1,6,587762.91,97960.485000,'O','1992-04-19' |
| 1,6,587762.91,97960.485000,'O','1992-04-19' |
| 1,6,587762.91,97960.485000,'O','1992-04-19' |
| 1,6,587762.91,97960.485000,'O','1992-04-19' |
| 1,6,587762.91,97960.485000,'O','1992-04-19' |
| 2,7,1028273.43,146896.204286,'P','1992-04-05' |
| 2,7,1028273.43,146896.204286,'P','1992-04-05' |
| 2,7,1028273.43,146896.204286,'P','1992-04-05' |
| 2,7,1028273.43,146896.204286,'P','1992-04-05' |
| 2,7,1028273.43,146896.204286,'P','1992-04-05' |
| 2,7,1028273.43,146896.204286,'P','1992-04-05' |
| 2,7,1028273.43,146896.204286,'P','1992-04-05' |
| ---- TYPES |
| bigint,bigint,decimal,decimal,string,string |
| ==== |
| ---- QUERY |
| # Test analytic functions with partition by inside a subplan. |
| select c_custkey, v.* from customer c, |
| (select o_orderstatus, |
| count(o_orderkey) over(partition by o_orderstatus) c, |
| sum(o_totalprice) over(partition by o_orderstatus) s, |
| avg(o_totalprice) over(partition by o_orderstatus) a, |
| max(o_orderstatus) over(partition by o_orderstatus) mx, |
| min(o_orderdate) over(partition by o_orderstatus) mn |
| from c.c_orders) v |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,'F',2,197679.65,98839.825000,'F','1992-04-19' |
| 1,'F',2,197679.65,98839.825000,'F','1992-04-19' |
| 1,'O',4,390083.26,97520.815000,'O','1996-06-29' |
| 1,'O',4,390083.26,97520.815000,'O','1996-06-29' |
| 1,'O',4,390083.26,97520.815000,'O','1996-06-29' |
| 1,'O',4,390083.26,97520.815000,'O','1996-06-29' |
| 2,'F',4,319892.45,79973.112500,'F','1992-04-05' |
| 2,'F',4,319892.45,79973.112500,'F','1992-04-05' |
| 2,'F',4,319892.45,79973.112500,'F','1992-04-05' |
| 2,'F',4,319892.45,79973.112500,'F','1992-04-05' |
| 2,'O',2,486983.63,243491.815000,'O','1996-08-05' |
| 2,'O',2,486983.63,243491.815000,'O','1996-08-05' |
| 2,'P',1,221397.35,221397.350000,'P','1995-03-10' |
| ---- TYPES |
| bigint,string,bigint,decimal,decimal,string,string |
| ==== |
| ---- QUERY |
| # Test a left outer join inside a subplan. |
| select count(okey), opriority |
| from customer c, |
| (select ca.o_orderkey okey, ca.o_orderpriority opriority |
| from c.c_orders ca left outer join c.c_orders cb |
| on ca.o_orderkey = cb.o_orderkey) v |
| group by opriority |
| ---- TYPES |
| bigint, string |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 300091,'2-HIGH' |
| 300254,'4-NOT SPECIFIED' |
| 300589,'5-LOW' |
| 300343,'1-URGENT' |
| 298723,'3-MEDIUM' |
| ==== |
| ---- QUERY |
| # Test left outer join of a relative table ref. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c left outer join c.c_orders |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,'BUILDING',454791,'1992-04-19' |
| 1,'BUILDING',579908,'1996-12-09' |
| 1,'BUILDING',3868359,'1992-08-22' |
| 1,'BUILDING',4273923,'1997-03-23' |
| 1,'BUILDING',4808192,'1996-06-29' |
| 1,'BUILDING',5133509,'1996-07-01' |
| 2,'AUTOMOBILE',430243,'1994-12-24' |
| 2,'AUTOMOBILE',1071617,'1995-03-10' |
| 2,'AUTOMOBILE',1374019,'1992-04-05' |
| 2,'AUTOMOBILE',1763205,'1994-08-28' |
| 2,'AUTOMOBILE',1842406,'1996-08-05' |
| 2,'AUTOMOBILE',2992930,'1994-05-21' |
| 2,'AUTOMOBILE',3986496,'1997-02-22' |
| 3,'AUTOMOBILE',NULL,'NULL' |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Test left outer join of a relative table ref. |
| # Use straight_join to enforce a left outer join inside the subplan. |
| select straight_join c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c left outer join c.c_orders |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,'BUILDING',454791,'1992-04-19' |
| 1,'BUILDING',579908,'1996-12-09' |
| 1,'BUILDING',3868359,'1992-08-22' |
| 1,'BUILDING',4273923,'1997-03-23' |
| 1,'BUILDING',4808192,'1996-06-29' |
| 1,'BUILDING',5133509,'1996-07-01' |
| 2,'AUTOMOBILE',430243,'1994-12-24' |
| 2,'AUTOMOBILE',1071617,'1995-03-10' |
| 2,'AUTOMOBILE',1374019,'1992-04-05' |
| 2,'AUTOMOBILE',1763205,'1994-08-28' |
| 2,'AUTOMOBILE',1842406,'1996-08-05' |
| 2,'AUTOMOBILE',2992930,'1994-05-21' |
| 2,'AUTOMOBILE',3986496,'1997-02-22' |
| 3,'AUTOMOBILE',NULL,'NULL' |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Test a right outer join inside a subplan. |
| select count(okey), opriority |
| from customer c, |
| (select ca.o_orderkey okey, ca.o_orderpriority opriority |
| from c.c_orders ca right outer join c.c_orders cb |
| on ca.o_orderkey = cb.o_orderkey |
| where ca.o_totalprice + cb.o_totalprice < 2 |
| and cb.o_orderpriority = '5-LOW') v |
| group by opriority |
| ---- TYPES |
| bigint, string |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test left semi join of a relative table ref. |
| select c_custkey, c_mktsegment |
| from customer c left semi join c.c_orders |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,'BUILDING' |
| 2,'AUTOMOBILE' |
| ---- TYPES |
| bigint,string |
| ==== |
| ---- QUERY |
| # Test left semi join of a relative table ref. |
| # Use straight_join to enforce a left semi join inside the subplan. |
| select straight_join c_custkey, c_mktsegment |
| from customer c left semi join c.c_orders |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,'BUILDING' |
| 2,'AUTOMOBILE' |
| ---- TYPES |
| bigint,string |
| ==== |
| ---- QUERY |
| # Test left anti join on a relative table ref. |
| select c_custkey, c_mktsegment |
| from customer c left anti join c.c_orders |
| where c_custkey < 4 |
| ---- RESULTS |
| 3,'AUTOMOBILE' |
| ---- TYPES |
| bigint,string |
| ==== |
| ---- QUERY |
| # Test left anti join on a relative table ref. |
| # Use straight_join to enforce a left anti join inside the subplan. |
| select straight_join c_custkey, c_mktsegment |
| from customer c left anti join c.c_orders |
| where c_custkey < 4 |
| ---- RESULTS |
| 3,'AUTOMOBILE' |
| ---- TYPES |
| bigint,string |
| ==== |
| ---- QUERY |
| # Test hash join inside a subplan. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c, |
| (select o1.o_orderkey, o2.o_orderdate |
| from c.c_orders o1, c.c_orders o2 |
| where o1.o_orderkey = o2.o_orderkey) v |
| where c_custkey < 4 |
| ---- RESULTS |
| 1,'BUILDING',454791,'1992-04-19' |
| 1,'BUILDING',579908,'1996-12-09' |
| 1,'BUILDING',3868359,'1992-08-22' |
| 1,'BUILDING',4273923,'1997-03-23' |
| 1,'BUILDING',4808192,'1996-06-29' |
| 1,'BUILDING',5133509,'1996-07-01' |
| 2,'AUTOMOBILE',430243,'1994-12-24' |
| 2,'AUTOMOBILE',1071617,'1995-03-10' |
| 2,'AUTOMOBILE',1374019,'1992-04-05' |
| 2,'AUTOMOBILE',1763205,'1994-08-28' |
| 2,'AUTOMOBILE',1842406,'1996-08-05' |
| 2,'AUTOMOBILE',2992930,'1994-05-21' |
| 2,'AUTOMOBILE',3986496,'1997-02-22' |
| ---- TYPES |
| bigint,string,bigint,string |
| ==== |
| ---- QUERY |
| # Test uncorrelated scalar subquery with a relative table ref. |
| select c_custkey, c_mktsegment, c_acctbal |
| from customer c |
| where c_custkey < 10 |
| and c_acctbal < (select avg(o_totalprice) from c.c_orders) |
| ---- RESULTS |
| 1,'BUILDING',711.56 |
| 2,'AUTOMOBILE',121.65 |
| 4,'MACHINERY',2866.83 |
| 5,'HOUSEHOLD',794.47 |
| 7,'AUTOMOBILE',9561.95 |
| 8,'BUILDING',6819.74 |
| ---- TYPES |
| bigint,string,decimal |
| ==== |
| ---- QUERY |
| # Test correlated scalar subquery with a relative table ref. |
| select c_custkey, c_mktsegment, c_acctbal |
| from customer c |
| where c_custkey < 10 |
| and c_acctbal < (select avg(o_totalprice) from c.c_orders o |
| where c.c_custkey != o.o_orderkey) |
| ---- RESULTS |
| 1,'BUILDING',711.56 |
| 2,'AUTOMOBILE',121.65 |
| 4,'MACHINERY',2866.83 |
| 5,'HOUSEHOLD',794.47 |
| 7,'AUTOMOBILE',9561.95 |
| 8,'BUILDING',6819.74 |
| ---- TYPES |
| bigint,string,decimal |
| ==== |
| ---- QUERY |
| # Test uncorrelated EXISTS subquery with a relative table ref. |
| select c_custkey, c_mktsegment, c_acctbal |
| from customer c |
| where c_custkey < 10 |
| and exists (select o_orderkey from c.c_orders where o_orderkey % 2 = 0) |
| ---- RESULTS |
| 1,'BUILDING',711.56 |
| 2,'AUTOMOBILE',121.65 |
| 4,'MACHINERY',2866.83 |
| 5,'HOUSEHOLD',794.47 |
| 7,'AUTOMOBILE',9561.95 |
| 8,'BUILDING',6819.74 |
| ---- TYPES |
| bigint,string,decimal |
| ==== |
| ---- QUERY |
| # Test uncorrelated NOT EXISTS subquery with a relative table ref. |
| select c_custkey, c_mktsegment, c_acctbal |
| from customer c |
| where c_custkey < 10 |
| and not exists (select o_orderkey from c.c_orders where o_orderkey % 2 = 0) |
| ---- RESULTS |
| 3,'AUTOMOBILE',7498.12 |
| 6,'AUTOMOBILE',7638.57 |
| 9,'FURNITURE',8324.07 |
| ---- TYPES |
| bigint,string,decimal |
| ==== |
| ---- QUERY |
| # Test correlated EXISTS subquery with a relative table ref. |
| select c_custkey, c_mktsegment, c_acctbal |
| from customer c |
| where c_custkey < 10 |
| and exists (select o_orderkey from c.c_orders o where c.c_acctbal * 10 > o.o_totalprice) |
| ---- RESULTS |
| 4,'MACHINERY',2866.83 |
| 7,'AUTOMOBILE',9561.95 |
| 8,'BUILDING',6819.74 |
| ---- TYPES |
| bigint,string,decimal |
| ==== |
| ---- QUERY |
| # Test correlated NOT EXISTS subquery with a relative table ref. |
| select c_custkey, c_mktsegment, c_acctbal |
| from customer c |
| where c_custkey < 10 |
| and not exists (select o_orderkey from c.c_orders o where c.c_acctbal * 10 > o.o_totalprice) |
| ---- RESULTS |
| 1,'BUILDING',711.56 |
| 2,'AUTOMOBILE',121.65 |
| 3,'AUTOMOBILE',7498.12 |
| 5,'HOUSEHOLD',794.47 |
| 6,'AUTOMOBILE',7638.57 |
| 9,'FURNITURE',8324.07 |
| ---- TYPES |
| bigint,string,decimal |
| ==== |
| ---- QUERY |
| # Test uncorrelated IN subquery with a relative table ref. |
| select c_custkey, c_mktsegment, o_orderkey, o_totalprice, o_orderdate |
| from customer c, c.c_orders o |
| where c_custkey < 10 |
| and cast(o_orderdate as timestamp) + interval 13 days in |
| (select cast(l_shipdate as timestamp) from o.o_lineitems) |
| ---- RESULTS |
| 1,'BUILDING',454791,74602.81,'1992-04-19' |
| 4,'MACHINERY',164711,311722.87,'1992-04-26' |
| 7,'AUTOMOBILE',2014848,181378.37,'1997-01-19' |
| 8,'BUILDING',1859778,263567.37,'1998-02-04' |
| ---- TYPES |
| bigint,string,bigint,decimal,string |
| ==== |
| ---- QUERY |
| # Test correlated IN subquery with a relative table ref. |
| select c_custkey, c_mktsegment, o_orderkey, o_totalprice, o_orderdate |
| from customer c, c.c_orders o |
| where c_custkey < 10 |
| and cast(o_orderdate as timestamp) + interval 13 days in |
| (select cast(l_shipdate as timestamp) from o.o_lineitems |
| where c_acctbal > l_extendedprice * l_tax) |
| ---- RESULTS |
| 1,'BUILDING',454791,74602.81,'1992-04-19' |
| 7,'AUTOMOBILE',2014848,181378.37,'1997-01-19' |
| 8,'BUILDING',1859778,263567.37,'1998-02-04' |
| ---- TYPES |
| bigint,string,bigint,decimal,string |
| ==== |
| ---- QUERY |
| # Test uncorrelated NOT IN subquery with a relative table ref. |
| select c_custkey, c_mktsegment, o_orderkey, o_totalprice, o_orderdate |
| from customer c, c.c_orders o |
| where c_custkey < 10 |
| and o_orderdate like "1992%" |
| and cast(o_orderdate as timestamp) + interval 13 days not in |
| (select cast(l_shipdate as timestamp) |
| from o.o_lineitems) |
| ---- RESULTS |
| 1,'BUILDING',3868359,123076.84,'1992-08-22' |
| 2,'AUTOMOBILE',1374019,167016.61,'1992-04-05' |
| 4,'MACHINERY',4320612,182956.01,'1992-09-20' |
| 7,'AUTOMOBILE',2096544,185203.14,'1992-04-17' |
| 7,'AUTOMOBILE',3318789,249621.82,'1992-04-30' |
| 7,'AUTOMOBILE',4328998,322431.85,'1992-03-25' |
| 8,'BUILDING',4659813,7459.52,'1992-07-26' |
| 8,'BUILDING',5987111,97765.58,'1992-09-03' |
| ---- TYPES |
| bigint,string,bigint,decimal,string |
| ==== |
| ---- QUERY |
| # Nested subplans with predicates on every table ref. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate, l_linenumber, l_shipdate |
| from customer c, c.c_orders o, o.o_lineitems i |
| where c_custkey in (1, 2) and o_orderkey in (4808192, 1374019) and l_linenumber % 2 = 0 |
| ---- RESULTS |
| 1,'BUILDING',4808192,'1996-06-29',2,'1996-09-15' |
| 2,'AUTOMOBILE',1374019,'1992-04-05',2,'1992-06-21' |
| 2,'AUTOMOBILE',1374019,'1992-04-05',4,'1992-07-06' |
| ---- TYPES |
| bigint,string,bigint,string,int,string |
| ==== |
| ---- QUERY |
| # IMPALA-2289: Test nested-loop join with right anti join mode inside a subplan. |
| select count(*) from customer c left anti join c.c_orders |
| where c_custkey between 8000 and 10000 |
| ---- RESULTS |
| 667 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # IMPALA-2289: Test nested-loop join with left anti join mode inside a subplan. |
| select straight_join count(*) from customer c left anti join c.c_orders |
| where c_custkey between 8000 and 10000 |
| ---- RESULTS |
| 667 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # IMPALA-2539: Test empty union operands containing relative table refs. |
| select c_custkey, o_orderkey |
| from customer c, |
| (select o_orderkey from c.c_orders o1 |
| union distinct |
| select o_orderkey from c.c_orders o2 |
| where false |
| union all |
| select o_orderkey from c.c_orders o3 |
| where false |
| ) v1 |
| where c_custkey = 1 |
| ---- RESULTS |
| 1,454791 |
| 1,579908 |
| 1,3868359 |
| 1,4273923 |
| 1,4808192 |
| 1,5133509 |
| ---- TYPES |
| bigint, bigint |
| ==== |
| ---- QUERY |
| # IMPALA-3678: Union in a subplan - passthrough should be disabled. |
| select count(c.c_custkey), count(v.tot_price) |
| from customer c, ( |
| select sum(o_totalprice) tot_price from c.c_orders |
| union |
| select sum(o_totalprice) tot_price from c.c_orders |
| ) v; |
| ---- RESULTS |
| 150000,99996 |
| ---- TYPES |
| bigint,bigint |
| ==== |
| ---- QUERY |
| # IMPALA-5363: Reset probe_batch_ after reaching limit. |
| # Query with a partitioned hash join inside a subplan |
| select count(*) FROM customer c, (SELECT ca.o_orderkey okey, |
| ca.o_orderpriority opriority FROM c.c_orders ca, c.c_orders cb |
| WHERE ca.o_orderkey = cb.o_orderkey limit 2) v limit 51 |
| ---- RESULTS |
| 199975 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # IMPALA-5363: Reset probe_batch_ after reaching limit. |
| # Query with a Nested loop join inside a subplan |
| select count(*) FROM customer c, (SELECT ca.o_orderkey okey, |
| ca.o_orderpriority opriority FROM c.c_orders ca, c.c_orders cb |
| WHERE ca.o_orderkey < cb.o_orderkey limit 2) v limit 51 |
| ---- RESULTS |
| 199835 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # IMPALA-5438: Union with constant exprs in a subplan. The 'c_custkey % 100' was chosen |
| # to have all impalads produce results to make sure the constant exprs in the union are |
| # evaluated regardless of which fragment instance they are in. |
| select c_custkey, order_cnt, union_cnt from customer c, |
| (select count(o_orderkey) order_cnt from c.c_orders) v, |
| (select count(o_orderkey) union_cnt from ( |
| select o_orderkey from c.c_orders |
| union all |
| values(11),(22),(33)) v) v2 |
| where c_custkey % 100 = 0 and order_cnt != union_cnt - 3; |
| ---- RESULTS |
| ---- TYPES |
| bigint,bigint,bigint |
| ==== |
| ---- QUERY |
| # IMPALA-2368: Resetting nested subplans works correctly with an analytic sort. |
| select count(o_orderkey) |
| from customer c |
| inner join c.c_orders o |
| where c_custkey < 10 and c_custkey in |
| (select lead(l.l_linenumber) over (partition by l.l_shipdate order by l.l_linenumber) |
| from o.o_lineitems l) |
| ---- RESULTS |
| 3 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Create CardinalityCheckNode inside a subplan |
| select c_custkey |
| from customer c |
| where c_custkey < (select o_orderkey |
| from c.c_orders |
| where o_orderkey = 6000000) |
| ---- RESULTS |
| 110063 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Create CardinalityCheckNode inside a subplan |
| # o_orderkey 6000000 and 5000000 belong to different customers |
| select c_custkey |
| from customer c |
| where c_custkey < (select o_orderkey |
| from c.c_orders |
| where o_orderkey = 6000000 or o_orderkey = 5000000) |
| order by c_custkey |
| ---- RESULTS |
| 24325 |
| 110063 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Create CardinalityCheckNode inside a subplan. |
| # o_orderkey 6000000 and 4285920 belong to the same customer |
| select c_custkey |
| from customer c |
| where c_custkey < (select o_orderkey |
| from c.c_orders |
| where o_orderkey = 6000000 or o_orderkey = 4285920) |
| ---- CATCH |
| Subquery must not return more than one row: SELECT o_orderkey FROM c.c_orders WHERE o_orderkey = 6000000 OR o_orderkey = 4285920 |
| ==== |
| ---- QUERY |
| # IMPALA-7573: SELECT DISTINCT in a subplan. |
| select c_custkey, c_mktsegment, o_orderkey, o_orderdate |
| from customer c, |
| (select distinct o1.o_orderkey, o2.o_orderdate |
| from c.c_orders o1, c.c_orders o2 |
| where o1.o_orderkey = o2.o_orderkey limit 1) v where c_custkey = 1; |
| ---- RESULTS |
| 1,'BUILDING',4808192,'1996-06-29' |
| ---- TYPES |
| BIGINT,STRING,BIGINT,STRING |
| ==== |
