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