| ==== |
| ---- QUERY |
| # Test join on timestamp, hashing was not working properly |
| select a.timestamp_col from alltypessmall a inner join alltypessmall b on |
| (a.timestamp_col = b.timestamp_col) |
| where a.year=2009 and a.month=1 and b.year=2009 and b.month=1 |
| ---- RESULTS |
| 2009-01-01 00:00:00 |
| 2009-01-01 00:01:00 |
| 2009-01-01 00:02:00.100000000 |
| 2009-01-01 00:03:00.300000000 |
| 2009-01-01 00:04:00.600000000 |
| 2009-01-01 00:05:00.100000000 |
| 2009-01-01 00:06:00.150000000 |
| 2009-01-01 00:07:00.210000000 |
| 2009-01-01 00:08:00.280000000 |
| 2009-01-01 00:09:00.360000000 |
| 2009-01-02 00:10:00.450000000 |
| 2009-01-02 00:11:00.450000000 |
| 2009-01-02 00:12:00.460000000 |
| 2009-01-02 00:13:00.480000000 |
| 2009-01-02 00:14:00.510000000 |
| 2009-01-02 00:15:00.550000000 |
| 2009-01-02 00:16:00.600000000 |
| 2009-01-02 00:17:00.660000000 |
| 2009-01-02 00:18:00.730000000 |
| 2009-01-02 00:19:00.810000000 |
| 2009-01-03 00:20:00.900000000 |
| 2009-01-03 00:21:00.900000000 |
| 2009-01-03 00:22:00.910000000 |
| 2009-01-03 00:23:00.930000000 |
| 2009-01-03 00:24:00.960000000 |
| ---- TYPES |
| timestamp |
| ==== |
| ---- QUERY |
| # Joins with multiple exprs |
| select j.*, d.* from JoinTbl j inner join DimTbl d on |
| (j.test_name = d.name AND j.test_zip = d.zip) |
| ---- RESULTS |
| 1001,'Name1',94611,5000,1001,'Name1',94611 |
| 1002,'Name2',94611,5000,1002,'Name2',94611 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* from JoinTbl j inner join DimTbl d on |
| (j.test_zip = d.zip AND j.test_name = d.name) |
| ---- RESULTS |
| 1001,'Name1',94611,5000,1001,'Name1',94611 |
| 1002,'Name2',94611,5000,1002,'Name2',94611 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| # join between three tables, extra join predicates, extra scan predicates, nulls in joins cols |
| # (alltypesagg.tinyint_col contains nulls instead of 0s) |
| select a.smallint_col, b.id, a.tinyint_col, c.id, a.int_col, b.float_col, c.string_col |
| from alltypesagg a |
| join alltypessmall b on (a.smallint_col = b.id) |
| join alltypessmall c on (a.tinyint_col = c.id) |
| where a.month=1 |
| and a.day=1 |
| and a.int_col > 899 |
| and b.float_col > 4.5 |
| and c.string_col < '7' |
| and a.int_col + b.float_col + cast(c.string_col as float) < 1000 |
| ---- RESULTS |
| 15,15,5,5,915,5.5,'5' |
| 16,16,6,6,916,6.599999904632568,'6' |
| 31,31,1,1,931,6.599999904632568,'1' |
| 32,32,2,2,932,7.699999809265137,'2' |
| 33,33,3,3,933,8.800000190734863,'3' |
| 34,34,4,4,934,9.899999618530273,'4' |
| 41,41,1,1,941,6.599999904632568,'1' |
| 42,42,2,2,942,7.699999809265137,'2' |
| 43,43,3,3,943,8.800000190734863,'3' |
| 44,44,4,4,944,9.899999618530273,'4' |
| 5,5,5,5,905,5.5,'5' |
| 55,55,5,5,955,5.5,'5' |
| 56,56,6,6,956,6.599999904632568,'6' |
| 6,6,6,6,906,6.599999904632568,'6' |
| 65,65,5,5,965,5.5,'5' |
| 66,66,6,6,966,6.599999904632568,'6' |
| 81,81,1,1,981,6.599999904632568,'1' |
| 82,82,2,2,982,7.699999809265137,'2' |
| 83,83,3,3,983,8.800000190734863,'3' |
| 84,84,4,4,984,9.899999618530273,'4' |
| 91,91,1,1,991,6.599999904632568,'1' |
| ---- TYPES |
| smallint, int, tinyint, int, int, float, string |
| ==== |
| ---- QUERY |
| # reversing the order produces the same results |
| select a.smallint_col, b.id, a.tinyint_col, c.id, a.int_col, b.float_col, c.string_col |
| from alltypessmall b |
| join alltypesagg a on (a.smallint_col = b.id) |
| join alltypessmall c on (a.tinyint_col = c.id) |
| where a.month=1 |
| and a.day=1 |
| and a.int_col > 899 |
| and b.float_col > 4.5 |
| and c.string_col < '7' |
| and a.int_col + b.float_col + cast(c.string_col as float) < 1000 |
| ---- RESULTS |
| 15,15,5,5,915,5.5,'5' |
| 16,16,6,6,916,6.599999904632568,'6' |
| 31,31,1,1,931,6.599999904632568,'1' |
| 32,32,2,2,932,7.699999809265137,'2' |
| 33,33,3,3,933,8.800000190734863,'3' |
| 34,34,4,4,934,9.899999618530273,'4' |
| 41,41,1,1,941,6.599999904632568,'1' |
| 42,42,2,2,942,7.699999809265137,'2' |
| 43,43,3,3,943,8.800000190734863,'3' |
| 44,44,4,4,944,9.899999618530273,'4' |
| 5,5,5,5,905,5.5,'5' |
| 55,55,5,5,955,5.5,'5' |
| 56,56,6,6,956,6.599999904632568,'6' |
| 6,6,6,6,906,6.599999904632568,'6' |
| 65,65,5,5,965,5.5,'5' |
| 66,66,6,6,966,6.599999904632568,'6' |
| 81,81,1,1,981,6.599999904632568,'1' |
| 82,82,2,2,982,7.699999809265137,'2' |
| 83,83,3,3,983,8.800000190734863,'3' |
| 84,84,4,4,984,9.899999618530273,'4' |
| 91,91,1,1,991,6.599999904632568,'1' |
| ---- TYPES |
| smallint, int, tinyint, int, int, float, string |
| ==== |
| ---- QUERY |
| # joins on empty tables |
| select * from emptytable t1 join emptytable t2 on (t1.field=t2.field) |
| ---- RESULTS |
| ---- TYPES |
| string, int, string, int |
| ==== |
| ---- QUERY |
| select * from emptytable t1 join greptiny t2 on (t1.field=t2.field) |
| ---- RESULTS |
| ---- TYPES |
| string, int, string |
| ==== |
| ---- QUERY |
| select * from greptiny t1 join emptytable t2 on (t1.field=t2.field) |
| ---- RESULTS |
| ---- TYPES |
| string, string, int |
| ==== |
| ---- QUERY |
| # cross join |
| select t1.id, t2.id from alltypestiny t1 cross join alltypestiny t2 |
| where (t1.id < 3 and t2.id < 3) |
| order by t1.id, t2.id limit 100 |
| ---- RESULTS |
| 0,0 |
| 0,1 |
| 0,2 |
| 1,0 |
| 1,1 |
| 1,2 |
| 2,0 |
| 2,1 |
| 2,2 |
| ---- TYPES |
| int, int |
| ==== |
| ---- QUERY |
| # cross join with an empty table |
| select t1.id, e.field from alltypestiny t1 cross join emptytable e |
| ---- RESULTS |
| ---- TYPES |
| int, string |
| ==== |
| ---- QUERY |
| # check a larger cross join produces the expected number of rows |
| select count(*) from functional.AllTypesSmall t1 cross join functional.AllTypesSmall t2 |
| ---- RESULTS |
| 10000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # cross join with nulls and constant table |
| select id, tinyint_col, t1.c from functional.alltypesagg |
| cross join (values(NULL c, 1, 2)) as t1 |
| order by tinyint_col nulls first, id, t1.c |
| limit 6 |
| ---- RESULTS |
| 0,NULL,NULL |
| 0,NULL,NULL |
| 10,NULL,NULL |
| 10,NULL,NULL |
| 20,NULL,NULL |
| 20,NULL,NULL |
| ---- TYPES |
| int, tinyint, null |
| ==== |
| ---- QUERY |
| # check cross joins within a subquery |
| select t1.id as t1_id, t2_id, t3_id from alltypestiny t1 |
| cross join (select t2.id as t2_id, t3.id as t3_id from alltypestiny t2 |
| cross join alltypestiny t3) t4 |
| where t1.id < 2 and t2_id < 2 and t3_id < 2 |
| order by t1.id, t2_id, t3_id |
| limit 10 |
| ---- RESULTS |
| 0,0,0 |
| 0,0,1 |
| 0,1,0 |
| 0,1,1 |
| 1,0,0 |
| 1,0,1 |
| 1,1,0 |
| 1,1,1 |
| ---- TYPES |
| int, int, int |
| ==== |
| ---- QUERY |
| # cross join between two tables, extra where predicates, extra scan predicates, nulls in |
| # joins cols (alltypesagg.tinyint_col contains nulls instead of 0s) |
| select a.tinyint_col, b.id, a.string_col |
| from alltypesagg a cross join alltypessmall b |
| where a.tinyint_col = b.id |
| and 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,'91' |
| 1,1,'881' |
| 1,1,'891' |
| 1,1,'901' |
| 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 |
| # join with three tables and then a cross join, extra where predicates, extra scan |
| # predicates, nulls in joins cols (alltypesagg.tinyint_col contains nulls instead of |
| # 0s) |
| select a.smallint_col, b.id, a.tinyint_col, c.id, a.int_col, b.float_col, c.string_col, d.id |
| from alltypesagg a |
| join alltypessmall b on (a.smallint_col = b.id) |
| join alltypessmall c on (a.tinyint_col = c.id) |
| cross join alltypestiny d |
| where a.month=1 |
| and a.day=1 |
| and a.int_col > 899 |
| and b.float_col > 4.5 |
| and c.string_col < '4' |
| and a.int_col + b.float_col + cast(c.string_col as float) < 1000 |
| and d.id < 2 |
| order by a.id, b.id, c.id, d.id |
| limit 100 |
| ---- RESULTS |
| 31,31,1,1,931,6.599999904632568,'1',0 |
| 31,31,1,1,931,6.599999904632568,'1',1 |
| 32,32,2,2,932,7.699999809265137,'2',0 |
| 32,32,2,2,932,7.699999809265137,'2',1 |
| 33,33,3,3,933,8.800000190734863,'3',0 |
| 33,33,3,3,933,8.800000190734863,'3',1 |
| 41,41,1,1,941,6.599999904632568,'1',0 |
| 41,41,1,1,941,6.599999904632568,'1',1 |
| 42,42,2,2,942,7.699999809265137,'2',0 |
| 42,42,2,2,942,7.699999809265137,'2',1 |
| 43,43,3,3,943,8.800000190734863,'3',0 |
| 43,43,3,3,943,8.800000190734863,'3',1 |
| 81,81,1,1,981,6.599999904632568,'1',0 |
| 81,81,1,1,981,6.599999904632568,'1',1 |
| 82,82,2,2,982,7.699999809265137,'2',0 |
| 82,82,2,2,982,7.699999809265137,'2',1 |
| 83,83,3,3,983,8.800000190734863,'3',0 |
| 83,83,3,3,983,8.800000190734863,'3',1 |
| 91,91,1,1,991,6.599999904632568,'1',0 |
| 91,91,1,1,991,6.599999904632568,'1',1 |
| ---- TYPES |
| SMALLINT, INT, TINYINT, INT, INT, FLOAT, STRING, INT |
| ==== |
| ---- QUERY |
| # FULL OUTER JOIN between two inline views followed by a GROUP BY (IMPALA-964) |
| select a.x FROM (VALUES(1 x, 1 y)) a FULL OUTER JOIN (VALUES(1 x, 1 y)) b |
| ON (a.x = b.y) GROUP BY a.x |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| TINYINT |
| ==== |
| ---- QUERY |
| select 1 FROM (VALUES(1 x, 1 y)) a RIGHT OUTER JOIN (VALUES(1 x, 1 y)) b |
| ON (a.x = b.y) GROUP BY a.x |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| TINYINT |
| ==== |
| ---- QUERY |
| # Test joins with union inputs. One input is a union. |
| select a.id, b.id, a.string_col, b.string_col |
| from |
| (select id, string_col from functional.alltypessmall |
| where year = 2009 and month = 1 |
| union all |
| select id, string_col from functional.alltypessmall |
| where year = 2009 and month = 2 |
| union all |
| select 0, '1234') a |
| inner join |
| functional.alltypestiny b |
| on a.id = b.id |
| where b.id < 5 |
| ---- RESULTS |
| 0,0,'0','0' |
| 0,0,'1234','0' |
| 1,1,'1','1' |
| 2,2,'2','0' |
| 3,3,'3','1' |
| 4,4,'4','0' |
| ---- TYPES |
| INT, INT, STRING, STRING |
| ==== |
| ---- QUERY |
| # Test joins with union inputs. One input is a union. |
| select a.id, b.id, a.string_col, b.string_col |
| from |
| functional.alltypestiny b |
| left outer join |
| (select id, string_col from functional.alltypessmall |
| where year = 2009 and month = 1 |
| union all |
| select id, string_col from functional.alltypessmall |
| where year = 2009 and month = 2 |
| union all |
| select 0, '1234') a |
| on a.id = b.id |
| where b.id < 5 |
| ---- RESULTS |
| 0,0,'0','0' |
| 0,0,'1234','0' |
| 1,1,'1','1' |
| 2,2,'2','0' |
| 3,3,'3','1' |
| 4,4,'4','0' |
| ---- TYPES |
| INT, INT, STRING, STRING |
| ==== |
| ---- QUERY |
| # Test joins with union inputs. Both inputs are a union. |
| select a.id, b.id, a.string_col, b.string_col |
| from |
| (select id, string_col from functional.alltypessmall |
| where year = 2009 and month = 1 |
| union all |
| select id, string_col from functional.alltypessmall |
| where year = 2009 and month = 2 |
| union all |
| select 0, '1234') a |
| full outer join |
| (select id, string_col from functional.alltypessmall |
| where year = 2009 and month = 1 |
| union all |
| select id, string_col from functional.alltypessmall |
| where year = 2009 and month = 2 |
| union all |
| select 0, '5678') b |
| on a.id = b.id |
| where b.id < 5 |
| ---- RESULTS |
| 0,0,'0','0' |
| 0,0,'0','5678' |
| 0,0,'1234','0' |
| 0,0,'1234','5678' |
| 1,1,'1','1' |
| 2,2,'2','2' |
| 3,3,'3','3' |
| 4,4,'4','4' |
| ---- TYPES |
| INT, INT, STRING, STRING |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1123. Tests that hash exchanges feeding the same |
| # hash-partitioned fragment use type-identical partition exprs. |
| select straight_join count(*) from |
| (select tinyint_col from functional.alltypessmall |
| union distinct |
| select tinyint_col from functional.alltypessmall) a |
| inner join [shuffle] |
| (select smallint_col from functional.alltypessmall |
| union distinct |
| select smallint_col from functional.alltypessmall) b |
| on a.tinyint_col = b.smallint_col |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1123. The plan below has one big hash-partitioned |
| # fragment with two joins and a merge agg since their partition exprs are all |
| # compatible. The single fragment is fed by multiple hash-partitioning senders, |
| # and we need to ensure that the partition exprs used by the senders are cast |
| # to identical types because the same value will have different hashes for |
| # different types. Minimal explain plan: |
| # |
| # 15:AGGREGATE [MERGE FINALIZE] |
| # 14:EXCHANGE [UNPARTITIONED] |
| # 08:AGGREGATE |
| # 07:HASH JOIN [INNER JOIN, PARTITIONED] |
| # |--13:AGGREGATE [MERGE FINALIZE] |
| # | 12:EXCHANGE [HASH(tinyint_col)] |
| # | 05:AGGREGATE |
| # | 04:SCAN HDFS [functional.alltypessmall] |
| # 06:HASH JOIN [INNER JOIN, PARTITIONED] |
| # |--03:HASH JOIN [INNER JOIN, PARTITIONED] |
| # | |--10:EXCHANGE [HASH(t2.smallint_col)] |
| # | | 02:SCAN HDFS [functional.alltypessmall t2] |
| # | 09:EXCHANGE [HASH(t1.tinyint_col)] |
| # | 01:SCAN HDFS [functional.alltypessmall t1] |
| # 11:EXCHANGE [HASH(a.int_col)] |
| # 00:SCAN HDFS [functional.alltypessmall a] |
| # |
| select straight_join count(*) from |
| functional.alltypessmall a |
| inner join [shuffle] |
| (select straight_join t2.* from |
| functional.alltypessmall t1 |
| inner join [shuffle] |
| functional.alltypessmall t2 |
| on t1.tinyint_col = t2.smallint_col) b |
| on a.int_col = b.smallint_col |
| inner join [shuffle] |
| (select distinct tinyint_col |
| from functional.alltypessmall) c |
| on a.int_col = c.tinyint_col |
| ---- RESULTS |
| 11200 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Same query as above with broadcast joins. Results should be identical. |
| select straight_join count(*) from |
| functional.alltypessmall a |
| inner join [broadcast] |
| (select straight_join t2.* from |
| functional.alltypessmall t1 |
| inner join [broadcast] |
| functional.alltypessmall t2 |
| on t1.tinyint_col = t2.smallint_col) b |
| on a.int_col = b.smallint_col |
| inner join [broadcast] |
| (select distinct tinyint_col |
| from functional.alltypessmall) c |
| on a.int_col = c.tinyint_col |
| ---- RESULTS |
| 11200 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1329 |
| select COUNT(t1.string_col) AS int_col_1 FROM alltypestiny t1 |
| LEFT JOIN alltypestiny t2 ON t2.date_string_col = t1.string_col |
| LEFT JOIN alltypesagg t3 ON t3.id = t2.tinyint_col |
| ---- RESULTS |
| 8 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1332 |
| select a.int_col from alltypesagg a |
| RIGHT OUTER JOIN alltypesagg b |
| using(int_col) |
| where a.int_col is NULL limit 1 |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Left outer join with non-equi join predicates |
| select straight_join count(*) from |
| alltypestiny a left outer join alltypes b on a.id < b.id |
| where a.id < 5 |
| ---- RESULTS |
| 36485 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Left semi join with non-equi join predicates |
| select straight_join count(*) from |
| alltypesagg a left semi join alltypestiny b on a.id >= b.id or a.int_col != b.int_col |
| where a.tinyint_col < 10 |
| ---- RESULTS |
| 9000 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Left anti join with non-equi join predicates |
| select straight_join count(*) from |
| alltypesagg a left anti join alltypestiny b on a.id < b.id and a.int_col > b.int_col |
| ---- RESULTS |
| 10994 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Inner and left outer joins with non-equi join predicates |
| select straight_join count(*) from |
| alltypesagg a inner join alltypes b on a.id < b.id |
| left outer join alltypestiny c |
| on a.int_col != c.int_col or a.tinyint_col > c.tinyint_col |
| where a.smallint_col < 2 |
| ---- RESULTS |
| 2038096 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Inner, left-semi and left-anti joins with non-equi join predicates |
| select straight_join count(*) from |
| alltypestiny a left semi join alltypesagg b on a.id != b.id |
| left anti join alltypes c on a.int_col < c.int_col |
| inner join alltypestiny d on a.int_col = d.int_col |
| where a.tinyint_col < 10 |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Joins with non-equi join predicates between inline views |
| select straight_join count(*) from |
| (select * from alltypestiny where id < 10) v1 |
| inner join |
| (select * from alltypesagg a left semi join |
| alltypes b on a.id < b.id) v2 |
| on v1.int_col != v2.tinyint_col |
| ---- RESULTS |
| 49632 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-2319: NLJ with limit |
| select straight_join t1.id, t2.bool_col |
| from functional.alltypes t1 |
| cross join functional.alltypes t2 where t1.id = 0 limit 1; |
| ---- RESULTS |
| 0,true |
| ---- TYPES |
| INT,BOOLEAN |
| ==== |
| ---- QUERY |
| # IMPALA-2147: IS [NOT] DISTINCT FROM and "<=>" |
| select count(*) > 0 |
| from alltypesagg as a, alltypesagg as b |
| where (a.tinyint_col IS DISTINCT FROM b.tinyint_col) |
| and a.tinyint_col is null |
| ---- RESULTS |
| true |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| select count(*) > 0 |
| from alltypesagg as a, alltypesagg as b |
| where (a.tinyint_col IS NOT DISTINCT FROM b.tinyint_col) |
| and a.tinyint_col is null |
| ---- RESULTS |
| true |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| select count(*) > 0 |
| from alltypesagg as a, alltypesagg as b |
| where (a.tinyint_col <=> b.tinyint_col) |
| and a.tinyint_col is null |
| ---- RESULTS |
| true |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| # left joins with <=> are different from left joins with = |
| select P.d, Q.d, Q.b, Q.b is null |
| from nulltable P left join nulltable Q |
| on P.d = Q.d |
| ---- RESULTS |
| NULL,NULL,'NULL',true |
| ---- TYPES |
| INT,INT,STRING,BOOLEAN |
| ==== |
| ---- QUERY |
| select P.d, Q.d, Q.b |
| from nulltable P left join nulltable Q |
| on P.d <=> Q.d |
| ---- RESULTS |
| NULL,NULL,'' |
| ---- TYPES |
| INT,INT,STRING |
| ==== |
| ---- QUERY |
| select count(*) |
| from nulltable P left anti join nulltable Q |
| on P.d = Q.d |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select count(*) |
| from nulltable P left anti join nulltable Q |
| on P.d <=> Q.d |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Test that 'IS DISTINCT FROM' works in nested loop joins |
| select count(*) from nulltable t1, nulltable t2 where not(t1.d != t2.d) |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select t1.d, t2.d from nulltable t1, nulltable t2 where not(t1.d IS DISTINCT FROM t2.d) |
| ---- RESULTS |
| NULL,NULL |
| ---- TYPES |
| INT,INT |
| ==== |
| ---- QUERY |
| select count(*) from nulltable t1, nulltable t2 |
| where t1.d != length(t2.a) |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select t1.d, t2.a from nulltable t1, nulltable t2 |
| where t1.d IS DISTINCT FROM length(t2.a) |
| ---- RESULTS |
| NULL,'a' |
| ---- TYPES |
| INT,STRING |
| ==== |
| ---- QUERY |
| select t1.a, t1.b, t2.a, t2.b from |
| (values |
| (NULL a, NULL b), (NULL, 0), (NULL, 1), |
| (0, NULL), (0, 0), (0, 1), |
| (1, NULL), (1, 0), (1, 1)) as t1, |
| (values |
| (NULL a, NULL b), (NULL, 0), (NULL, 1), |
| (0, NULL), (0, 0), (0, 1), |
| (1, NULL), (1, 0), (1, 1)) as t2 |
| where t1.a <=> t2.a |
| and t1.b <=> t2.b |
| order by t1.a, t1.b, t2.a, t2.b |
| ---- RESULTS |
| 0,0,0,0 |
| 0,1,0,1 |
| 0,NULL,0,NULL |
| 1,0,1,0 |
| 1,1,1,1 |
| 1,NULL,1,NULL |
| NULL,0,NULL,0 |
| NULL,1,NULL,1 |
| NULL,NULL,NULL,NULL |
| ---- TYPES |
| TINYINT,TINYINT,TINYINT,TINYINT |
| ==== |
| ---- QUERY |
| select t1.a, t1.b, t2.a, t2.b from |
| (values |
| (NULL a, NULL b), (NULL, 0), (NULL, 1), |
| (0, NULL), (0, 0), (0, 1), |
| (1, NULL), (1, 0), (1, 1)) as t1, |
| (values |
| (NULL a, NULL b), (NULL, 0), (NULL, 1), |
| (0, NULL), (0, 0), (0, 1), |
| (1, NULL), (1, 0), (1, 1)) as t2 |
| where t1.a <=> t2.a |
| and t1.b = t2.b |
| order by t1.a, t1.b, t2.a, t2.b |
| ---- RESULTS |
| 0,0,0,0 |
| 0,1,0,1 |
| 1,0,1,0 |
| 1,1,1,1 |
| NULL,0,NULL,0 |
| NULL,1,NULL,1 |
| ---- TYPES |
| TINYINT,TINYINT,TINYINT,TINYINT |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-3645. Certain UDFs may do local allocations in Expr::Open(). |
| # Make sure the DCHECK in PartitionedHashJoinNode::ProcessBuildInput() doesn't fire. |
| select count(*) from functional.alltypesagg t1 join functional.alltypesagg t2 |
| on extract(minute from t1.timestamp_col) = extract(hour from t2.timestamp_col); |
| ---- RESULTS |
| 2042200 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Regression for IMPALA-3884. Exercise HashTableCtx::AssignNullValue() for |
| # 128-bit TimestampValue. |
| select count(*) from functional.alltypes t1 right outer join functional.decimal_tbl t2 on |
| t1.timestamp_col = cast(t2.d4 as TIMESTAMP); |
| ---- RESULTS |
| 5 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Exercise HashTableCtx::AssignNullValue() for DateValue. |
| select count(*) from functional.date_tbl t1 right outer join functional.alltypestiny t2 on |
| t1.date_col >= DATE '1400-01-01' and t1.date_col = t2.timestamp_col; |
| ---- RESULTS |
| 8 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-5850: Tests that the sender partition exprs of a series of partitioned hash |
| # joins are cast to compatible types even when the containing fragment is RANDOM |
| # partitioned due a union. This test borrows ideas from existing tests for IMPALA-1123. |
| # The test has a union with three branches where each branch contains independent and |
| # incompatible hash-partitioning exchanges. Two of the union branches contain |
| # partitioned joins which require the sender partition exprs to be cast to produce |
| # correct results. |
| # Breakdown of result count: |
| # The first union branch is expected to produce 2 rows |
| # The second union branch is expected to produce 11200 rows (see IMPALA-1123 test) |
| # The third union branch is expected to produce 10 rows |
| select count(*) from ( |
| select distinct tinyint_col, smallint_col, int_col |
| from functional.alltypestiny |
| union all |
| select /* +straight_join */ b.id, c.tinyint_col, null |
| from functional.alltypessmall a |
| inner join /* +shuffle */ |
| (select /* +straight_join */ t2.id, t2.smallint_col |
| from functional.alltypessmall t1 |
| inner join /* +shuffle */ functional.alltypessmall t2 |
| on t1.tinyint_col = t2.smallint_col) b |
| on a.int_col = b.smallint_col |
| inner join /* +shuffle */ |
| (select distinct tinyint_col |
| from functional.alltypessmall) c |
| on a.int_col = c.tinyint_col |
| union all |
| select /* +straight_join */ tinyint_col, bigint_col, null from |
| (select distinct tinyint_col, bigint_col div 10 as bigint_col |
| from functional.alltypessmall) a |
| inner join /* +shuffle */ |
| (select distinct int_col, smallint_col |
| from functional.alltypessmall) b |
| on a.tinyint_col = b.int_col and a.bigint_col = b.smallint_col) v |
| ---- RESULTS |
| 11212 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Test that 'nan' != 'nan' when joining. |
| with x as (select cast('nan' as double) n) |
| select * from x a, x b where a.n = b.n |
| ---- RESULTS |
| ---- TYPES |
| DOUBLE,DOUBLE |
| ==== |
| ---- QUERY |
| # Test that 'nan' != 'nan' when joining. |
| select a.id from functional.alltypestiny a, functional.alltypestiny b |
| where 1/a.double_col + -1/a.double_col = log10(-b.id) |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Test that 'nan' != 'nan' when joining. |
| select a.id from functional.alltypestiny a, functional.alltypestiny b |
| where sqrt(-a.id) = b.float_col / b.double_col |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Test than 'nan' != 'nan' when joining. |
| with t1 as (select cast(sqrt(0.5-x) as FLOAT) c1, y c2 from |
| (VALUES((1.6 x, 0 y), (3.2, 1), (5.4,2), (0.5, 3), (0.5, 4), (-0.5, 5))) XX), |
| t2 as (select * from t1) |
| select t1.c1,t2.c1 from t1 right outer join t2 on t1.c1 = t2.c1; |
| ---- RESULTS |
| 0,0 |
| 0,0 |
| 0,0 |
| 0,0 |
| 1,1 |
| NULL,NaN |
| NULL,NaN |
| NULL,NaN |
| ---- TYPES |
| FLOAT,FLOAT |
| ==== |
| ---- QUERY |
| # Test that NaN <=> NaN in joins |
| select t1.float_col as v |
| from functional.alltypessmall t1, functional.alltypessmall t2 |
| where sqrt(0.5-t1.float_col) <=> sqrt(0.5-t2.float_col) and t1.float_col > 0.5 |
| ---- RESULTS |
| ---- TYPES |
| FLOAT |
| ==== |
| ---- QUERY |
| # IMPALA-6660: Test that +0 equals -0 when joining |
| select * from |
| (select cast("-0" as float) c1) v1, |
| (select cast("+0" as float) c2) v2 |
| where v1.c1 = v2.c2; |
| ---- RESULTS |
| -0, 0 |
| ---- TYPES |
| FLOAT,FLOAT |
| ==== |