====
---- QUERY
# Right outer join with non-equi join predicates
select straight_join a.id, b.id, a.int_col, b.int_col
from alltypestiny a right outer join (values(1 as id, 1 as int_col)) b
  on a.id != b.id or a.int_col < b.int_col
---- RESULTS
6,1,0,1
7,1,1,1
0,1,0,1
2,1,0,1
3,1,1,1
4,1,0,1
5,1,1,1
---- TYPES
INT, TINYINT, INT, TINYINT
====
---- QUERY
# Right semi join with non-equi join predicates
select straight_join *
from alltypesagg a right semi join (values((1 as id, 1 as int_col), (2,2))) b
  on a.id != b.id
---- RESULTS
1,1
2,2
---- TYPES
TINYINT, TINYINT
====
---- QUERY
# Right anti join with non-equi join predicates
select straight_join *
from alltypestiny a right anti join (values((1 as id, 1 as int_col),(2,2),(3,3))) b
  on a.id < b.id
---- RESULTS
---- TYPES
TINYINT, TINYINT
====
---- QUERY
# Full outer join with non-equi join predicates
select straight_join a.id, b.id
from alltypestiny a full outer join (values((1 as id), (100))) b
  on a.id != b.id
---- RESULTS
6,1
6,100
7,1
7,100
4,1
4,100
5,1
5,100
2,1
2,100
3,1
3,100
0,1
0,100
1,100
---- TYPES
INT, TINYINT
====
---- QUERY
# Multi-way join query with right semi, right outer and full outer joins with
# non-equi join predicates
select straight_join *
from alltypestiny a right semi join (values(1 as tinyint_col, 1 as id)) c
 on a.tinyint_col < c.tinyint_col
right outer join (values(1 as bigint_col)) d
 on c.tinyint_col >= d.bigint_col
full outer join (values((1 as id, 1 as int_col),(2,2),(3,3))) e
 on c.tinyint_col != e.id
---- RESULTS
1,1,1,2,2
1,1,1,3,3
NULL,NULL,NULL,1,1
---- TYPES
TINYINT, TINYINT, TINYINT, TINYINT, TINYINT
====
---- QUERY
# Right semi join where the probe input is significantly smaller than the build input.
select straight_join a.id, b.id
from (values(1 as tinyint_col, 1 as id)) a right outer join alltypessmall b
  on a.tinyint_col != b.tinyint_col
order by a.id, b.id
limit 2
---- RESULTS
1,0
1,2
---- TYPES
TINYINT,INT
====
---- QUERY
# Right outer join where none of the build rows matches a probe row.
select straight_join count(*)
from (values(10000 as id)) a right outer join alltypesagg b
  on a.id < b.id
where a.id is null
---- RESULTS
11000
---- TYPES
BIGINT
====
---- QUERY
# Full outer join where the probe input is smaller than the build input and
# it can fit in a single row batch.
select straight_join a.id, b.id
from (values(1 as id)) a full outer join alltypestiny b
  on a.id < b.id
order by a.id, b.id
---- RESULTS
1,2
1,3
1,4
1,5
1,6
1,7
NULL,0
NULL,1
---- TYPES
TINYINT, INT
====
---- QUERY
# IMPALA-2319: the NLJ node returned more rows than limit in certain cases. This resulted
# in extra rows being returned to the client if the NLJ was the topmost node in the plan,
# which is the case when this query is executed on a single node. If this same query is
# executed on multiple nodes, the exchange enforces the limit and masks the bug.
select straight_join a, cnt from (values(1 a)) v1
cross join
(select id, count(*) cnt
from alltypes
group by id) v2 limit 5;
---- RESULTS
1,1
1,1
1,1
1,1
1,1
---- TYPES
TINYINT,BIGINT
====
---- QUERY
# IMPALA-2207: Aggregation feeding into build side of nested loop join.
# We want to test that this query doesn't crash or hit a DCHECK.
select straight_join * from (values(1 a)) v1
cross join
(select id, count(*) cnt
from alltypes
group by id) v2;
---- TYPES
TINYINT,INT,BIGINT
====
---- QUERY
# Regression test for IMPALA-561: Multiple scan nodes in a plan fragment.
select count(*)
from functional.alltypes a1
left join functional.alltypes a2 on a2.tinyint_col >= 1
---- RESULTS
47961000
---- TYPES
BIGINT
====
---- QUERY
# Regression test for IMPALA-4180: a single node plan with blocking join node
# and multiple top-n + scan nodes to trigger concurrent Close() on scan nodes.
with t as (select int_col x from functional.alltypestiny order by id limit 2)
select * from t t1 left join t t2 on t1.x > 0
---- RESULTS
0,NULL
1,0
1,1
---- TYPES
INT,INT
====
---- QUERY
# Right non-equi-join with empty build.
select straight_join atp.id
from alltypes atp
  right join functional.alltypestiny att on atp.id < att.id
where att.int_col = 999
---- RESULTS
---- TYPES
INT
====
---- QUERY
# Full outer non-equi-join with empty build.
select straight_join atp.id
from alltypes atp
  full outer join (
    select * from functional.alltypestiny where int_col = 999) att on atp.id < att.id
order by atp.id desc
limit 5
---- RESULTS
7299
7298
7297
7296
7295
---- TYPES
INT
====
---- QUERY
# Right semi non-equi-join with empty build.
select straight_join atp.id
from (select * from functional.alltypestiny att where int_col = 999) att
  right semi join alltypes atp on atp.id < att.id
---- RESULTS
---- TYPES
INT
====
---- QUERY
# Right anti non-equi-join with empty build.
select straight_join atp.id
from (select * from functional.alltypestiny att where int_col = 999) att
  right anti join alltypes atp on atp.id < att.id
order by atp.id desc
limit 5
---- RESULTS
7299
7298
7297
7296
7295
---- TYPES
INT
====
---- QUERY
# IMPALA-13991: Disable CROSS_JOIN rewrite if there is a scalar subquery inside
# disjunctive expression. right_tc is '2009-04-01 01:01:00'.
select id, timestamp_col from functional.alltypes
where (id in (1000, 1001))
  or ((id in (1, 2))
      and timestamp_col < (select hours_add(timestamp_col, 1) right_tc
                           from functional.alltypestiny
                           order by timestamp_col DESC limit 1));
---- RESULTS: VERIFY_IS_EQUAL_SORTED
1,2009-01-01 00:01:00
2,2009-01-01 00:02:00.100000000
1000,2009-04-11 01:40:04.500000000
1001,2009-04-11 01:41:04.500000000
---- TYPES
INT,TIMESTAMP
====