testdata/workloads/functional-query/queries/QueryTest/joins-against-hbase.test

====
---- QUERY
# join between hdfs and hbase, extra join predicate, extra scan predicates, nulls in
# joins cols and non-equality join predicate
# (alltypesagg.tinyint_col contains nulls instead of 0s)
# Should be same result as the test below
select a.tinyint_col, b.id, a.string_col, a.tinyint_col + b.tinyint_col
from alltypesagg a join functional_hbase.alltypessmall b
     on (a.tinyint_col = b.id and a.tinyint_col + b.tinyint_col < 5)
where a.month=1
and a.day=1
and a.string_col > '88'
and b.bool_col = false
---- RESULTS
1,1,'881',2
1,1,'891',2
1,1,'901',2
1,1,'91',2
1,1,'911',2
1,1,'921',2
1,1,'931',2
1,1,'941',2
1,1,'951',2
1,1,'961',2
1,1,'971',2
1,1,'981',2
1,1,'991',2
---- TYPES
tinyint, int, string, smallint
====
---- QUERY
# join between hdfs and hbase, extra join predicate, extra scan predicates, nulls in joins cols
# (alltypesagg.tinyint_col contains nulls instead of 0s)
# Should be same result as the test below
select a.tinyint_col, b.id, a.string_col
from alltypesagg a join functional_hbase.alltypessmall b on (a.tinyint_col = b.id)
where a.month=1
and a.day=1
and a.tinyint_col + b.tinyint_col < 5
and a.string_col > '88'
and b.bool_col = false
---- RESULTS
1,1,'881'
1,1,'891'
1,1,'901'
1,1,'91'
1,1,'911'
1,1,'921'
1,1,'931'
1,1,'941'
1,1,'951'
1,1,'961'
1,1,'971'
1,1,'981'
1,1,'991'
---- TYPES
tinyint, int, string
====
---- QUERY
# reversing the order of the tables produces the same result
select a.tinyint_col, b.id, a.string_col
from alltypessmall b join functional_hbase.alltypesagg a on (a.tinyint_col = b.id)
where a.month=1
and a.day=1
and a.tinyint_col + b.tinyint_col < 5
and a.string_col > '88'
and b.bool_col = false
---- RESULTS
1,1,'881'
1,1,'891'
1,1,'901'
1,1,'91'
1,1,'911'
1,1,'921'
1,1,'931'
1,1,'941'
1,1,'951'
1,1,'961'
1,1,'971'
1,1,'981'
1,1,'991'
---- TYPES
tinyint, int, string
====
---- QUERY
# cross join between hdfs and hbase, where predicate, extra scan predicates, nulls
# in joins cols and non-equality join predicate
# (alltypesagg.tinyint_col contains nulls instead of 0s)
select a.tinyint_col, b.id, a.string_col, a.tinyint_col + b.tinyint_col
from alltypesagg a cross join functional_hbase.alltypessmall b
where a.tinyint_col = b.id
and a.tinyint_col + b.tinyint_col < 5
and a.month=1
and a.day=1
and a.string_col > '88'
and b.bool_col = false
---- RESULTS
1,1,'91',2
1,1,'881',2
1,1,'891',2
1,1,'901',2
1,1,'911',2
1,1,'921',2
1,1,'931',2
1,1,'941',2
1,1,'951',2
1,1,'961',2
1,1,'971',2
1,1,'981',2
1,1,'991',2
---- TYPES
tinyint, int, string, smallint