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