| ==== |
| ---- QUERY |
| # join between three tables, extra join predicates, extra scan predicates, nulls in joins |
| # cols |
| # (alltypesagg.tinyint_col contains nulls instead of 0s) |
| select x.smallint_col, x.id, x.tinyint_col, c.id, x.int_col, x.float_col, c.string_col |
| from ( |
| select a.smallint_col smallint_col, a.tinyint_col tinyint_col, a.day day, |
| a.int_col int_col, a.month month, |
| b.float_col float_col, b.id id |
| from ( |
| select * |
| from alltypesagg a |
| where month=1 |
| ) a |
| join alltypessmall b |
| on (a.smallint_col = b.id) |
| ) x |
| join alltypessmall c on (x.tinyint_col = c.id) |
| where x.day=1 |
| and x.int_col > 899 |
| and x.float_col > 4.5 |
| and c.string_col < '7' |
| and x.int_col + x.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 |
| # Same join as above, but subquery on the RHS |
| select x.smallint_col, x.id, x.tinyint_col, c.id, x.int_col, x.float_col, c.string_col |
| from alltypessmall c |
| join |
| ( |
| select a.smallint_col smallint_col, a.tinyint_col tinyint_col, a.day day, |
| a.int_col int_col, a.month month, |
| b.float_col float_col, b.id id |
| from alltypessmall b |
| join |
| ( |
| select * |
| from alltypesagg a |
| where month=1 |
| ) a |
| on (a.smallint_col = b.id) |
| ) x |
| on (x.tinyint_col = c.id) |
| where x.day=1 |
| and x.int_col > 899 |
| and x.float_col > 4.5 |
| and c.string_col < '7' |
| and x.int_col + x.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 |
| # aggregate without group by |
| select * |
| from ( |
| select count(*), count(tinyint_col), min(tinyint_col), max(tinyint_col), |
| sum(tinyint_col), avg(tinyint_col) |
| from ( |
| select * from alltypesagg |
| ) a |
| ) b |
| ---- RESULTS |
| 11000,9000,1,9,45000,5 |
| ---- TYPES |
| bigint, bigint, tinyint, tinyint, bigint, double |
| ==== |
| ---- QUERY |
| # aggregate with group-by, having |
| select * |
| from ( |
| select int_col % 7 c1, count(*) c2, avg(int_col) c3 |
| from ( |
| select * from alltypesagg where day is not null |
| ) a |
| group by 1 |
| having avg(int_col) > 500 or count(*) = 10 |
| ) b |
| where c1 is not null |
| and c2 > 10 |
| ---- RESULTS |
| 4,1430,501 |
| 5,1430,502 |
| 0,1420,500.5 |
| ---- TYPES |
| int, bigint, double |
| ==== |
| ---- QUERY |
| # subquery with aggregation and order by/limit, as left-hand side of join; |
| # having clause in subquery is transfered to merge agg step in distrib plan |
| select * |
| from ( |
| select int_col, count(*) |
| from alltypessmall |
| where month = 1 |
| group by int_col |
| having count(*) > 2 |
| order by count(*) desc, int_col limit 5 |
| ) t1 |
| join alltypes t2 on (t1.int_col = t2.id) |
| where month = 1 |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 0,3,0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,3,1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,3,2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000000,2009,1 |
| 3,3,3,false,3,3,3,30,3.299999952316284,30.3,'01/01/09','3',2009-01-01 00:03:00.300000000,2009,1 |
| 4,3,4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000000,2009,1 |
| ---- DBAPI_RESULTS: VERIFY_IS_EQUAL_SORTED |
| 0,3,0,True,0,0,0,0,0.0,0.0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,3,1,False,1,1,1,10,1.10000002384,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,3,2,True,2,2,2,20,2.20000004768,20.2,'01/01/09','2',2009-01-01 00:02:00.100000,2009,1 |
| 3,3,3,False,3,3,3,30,3.29999995232,30.3,'01/01/09','3',2009-01-01 00:03:00.300000,2009,1 |
| 4,3,4,True,4,4,4,40,4.40000009537,40.4,'01/01/09','4',2009-01-01 00:04:00.600000,2009,1 |
| ---- TYPES |
| int, bigint, int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| # subquery with aggregation and order by/limit, as left-hand side of join; |
| # having clause in subquery is transfered to merge agg step in distrib plan |
| # Disable the estimation of cardinality for an hdfs table withot stats. |
| set DISABLE_HDFS_NUM_ROWS_ESTIMATE=1; |
| select * |
| from ( |
| select int_col, count(*) |
| from alltypessmall |
| where month = 1 |
| group by int_col |
| having count(*) > 2 |
| order by count(*) desc, int_col limit 5 |
| ) t1 |
| join alltypes t2 on (t1.int_col = t2.id) |
| where month = 1 |
| ---- RESULTS |
| 0,3,0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,3,1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,3,2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000000,2009,1 |
| 3,3,3,false,3,3,3,30,3.299999952316284,30.3,'01/01/09','3',2009-01-01 00:03:00.300000000,2009,1 |
| 4,3,4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000000,2009,1 |
| ---- DBAPI_RESULTS |
| 0,3,0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,3,1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,3,2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000,2009,1 |
| 3,3,3,false,3,3,3,30,3.299999952316284,30.3,'01/01/09','3',2009-01-01 00:03:00.300000,2009,1 |
| 4,3,4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000,2009,1 |
| ---- TYPES |
| int, bigint, int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| select distinct * |
| from ( |
| select bool_col, tinyint_col, count(*) |
| from alltypesagg |
| group by bool_col, tinyint_col |
| having bool_col = true |
| ) x |
| where tinyint_col < 6 |
| ---- RESULTS |
| true,2,1000 |
| true,4,1000 |
| ---- TYPES |
| boolean, tinyint, bigint |
| ==== |
| ---- QUERY |
| # distinct w/ explicit select list |
| select * |
| from ( |
| select distinct bool_col, tinyint_col |
| from ( |
| select * from alltypesagg where tinyint_col < 7 |
| ) y |
| ) x |
| where bool_col = true |
| ---- RESULTS |
| true,2 |
| true,4 |
| true,6 |
| ---- TYPES |
| boolean, tinyint |
| ==== |
| ---- QUERY |
| # semi-join on string |
| select * |
| from ( |
| select d.* |
| from DimTbl d left semi join JoinTbl j on (j.test_name = d.name) |
| ) x |
| where x.name > 'Name1' |
| ---- RESULTS |
| 1002,'Name2',94611 |
| 1003,'Name3',94612 |
| 1004,'Name4',94612 |
| 1005,'Name5',94613 |
| 1006,'Name6',94613 |
| ---- TYPES |
| bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from ( |
| select * |
| from JoinTbl a |
| ) j |
| left outer join |
| ( |
| select * |
| from DimTbl b |
| ) d |
| on (j.test_name = d.name) |
| where j.test_id <= 1006 |
| ---- RESULTS |
| 1001,'Name1',94611,5000,1001,'Name1',94611 |
| 1002,'Name2',94611,5000,1002,'Name2',94611 |
| 1003,'Name3',94611,5000,1003,'Name3',94612 |
| 1004,'Name4',94611,5000,1004,'Name4',94612 |
| 1005,'Name5',94611,5000,1005,'Name5',94613 |
| 1006,'Name16',94612,15000,NULL,'NULL',NULL |
| 1006,'Name16',94612,5000,NULL,'NULL',NULL |
| 1006,'Name16',94616,15000,NULL,'NULL',NULL |
| 1006,'Name16',94616,5000,NULL,'NULL',NULL |
| 1006,'Name6',94616,15000,1006,'Name6',94613 |
| 1006,'Name6',94616,5000,1006,'Name6',94613 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| # TODO: If we apply predicate on d, the result will be incorrect. This is a general |
| # predicate evaluation issue. |
| # |
| select j.*, d.* |
| from ( |
| select * |
| from JoinTbl a |
| ) j |
| left outer join |
| ( |
| select * |
| from DimTbl b |
| ) d |
| on (j.test_name = d.name) |
| where j.test_id <= 1006 |
| ---- RESULTS |
| 1001,'Name1',94611,5000,1001,'Name1',94611 |
| 1002,'Name2',94611,5000,1002,'Name2',94611 |
| 1003,'Name3',94611,5000,1003,'Name3',94612 |
| 1004,'Name4',94611,5000,1004,'Name4',94612 |
| 1005,'Name5',94611,5000,1005,'Name5',94613 |
| 1006,'Name16',94612,15000,NULL,'NULL',NULL |
| 1006,'Name16',94612,5000,NULL,'NULL',NULL |
| 1006,'Name16',94616,15000,NULL,'NULL',NULL |
| 1006,'Name16',94616,5000,NULL,'NULL',NULL |
| 1006,'Name6',94616,15000,1006,'Name6',94613 |
| 1006,'Name6',94616,5000,1006,'Name6',94613 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| # Constant selects in subqueries |
| select * from (select 1, 2) x |
| ---- RESULTS |
| 1,2 |
| ---- TYPES |
| tinyint, tinyint |
| ==== |
| ---- QUERY |
| # Constant selects in subqueries |
| select * from (select y from (select 1 y) a where y < 10) b |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| tinyint |
| ==== |
| ---- QUERY |
| # Constant selects in subqueries |
| select * from (select 1 union all select 2 union all select * from (select 3) y) x |
| ---- RESULTS |
| 1 |
| 2 |
| 3 |
| ---- TYPES |
| tinyint |
| ==== |
| ---- QUERY |
| # Join on inline views made up of unions of constant selects |
| select * from |
| (select 1 a, 2 b union all select 1 a, 2 b) x |
| inner join |
| (select 1 a, 3 b union all select 1 a, 2 b) y on x.a = y.a |
| inner join |
| (select 1 a, 3 b union all select 1 a, 3 b) z on z.b = y.b |
| ---- RESULTS |
| 1,2,1,3,1,3 |
| 1,2,1,3,1,3 |
| 1,2,1,3,1,3 |
| 1,2,1,3,1,3 |
| ---- TYPES |
| tinyint, tinyint, tinyint, tinyint, tinyint, tinyint |
| ==== |
| ---- QUERY |
| # Semi and inner join on a table and on inline views made up of constant selects |
| select x.date_string_col, z.* from functional.alltypessmall x |
| left semi join |
| (select 1 a, 3 b union all select 1 a, 3 b) y on y.a = x.id |
| inner join |
| (select 1 a, 3 b union all select 1 a, 3 b) z on z.b = x.id + 2 |
| ---- RESULTS |
| '01/01/09',1,3 |
| '01/01/09',1,3 |
| ---- TYPES |
| string, tinyint, tinyint |
| ==== |
| ---- QUERY |
| # Values statement in subqueries |
| select * from (values(1, 2), (3, 4)) x |
| ---- RESULTS |
| 1,2 |
| 3,4 |
| ---- TYPES |
| TINYINT, TINYINT |
| ==== |
| ---- QUERY |
| # Values statement in subqueries |
| select * from (select y from (values(1 as y), (11)) a where y < 10) b |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| TINYINT |
| ==== |
| ---- QUERY |
| # Values statement in subqueries with union |
| select * from (values(1), (2) union all select * from (values(3)) y) x |
| ---- RESULTS |
| 1 |
| 2 |
| 3 |
| ---- TYPES |
| TINYINT |
| ==== |
| ---- QUERY |
| # Join on inline views made up of values statements |
| select * from |
| (values(1 a, 2 b), (1, 2)) x |
| inner join |
| (values(1 a, 3 b), (1, 2)) y on x.a = y.a |
| inner join |
| (values(1 a, 3 b), (1, 3)) z on z.b = y.b |
| ---- RESULTS |
| 1,2,1,3,1,3 |
| 1,2,1,3,1,3 |
| 1,2,1,3,1,3 |
| 1,2,1,3,1,3 |
| ---- TYPES |
| TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT |
| ==== |
| ---- QUERY |
| # Semi and inner join on a table and on inline views made up of values statements |
| select x.date_string_col, z.* from functional.alltypessmall x |
| left semi join |
| (values(1 a, 3 b), (1, 3)) y on y.a = x.id |
| inner join |
| (values(1 a, 3 b), (1, 3)) z on z.b = x.id + 2 |
| ---- RESULTS |
| '01/01/09',1,3 |
| '01/01/09',1,3 |
| ---- TYPES |
| STRING, TINYINT, TINYINT |
| ==== |
| ---- QUERY |
| # Select constant with unreferenced aggregate in subquery |
| select 1 from (select count(*) from functional.alltypessmall) x |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| tinyint |
| ==== |
| ---- QUERY |
| # Select constant with unreferenced distinct aggregate in subquery |
| select 1 from (select count(distinct tinyint_col) from functional.alltypessmall) x |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| tinyint |
| ==== |
| ---- QUERY |
| # Select aggregate from unreferenced aggregate in subquery |
| select count(*) from (select count(*) from functional.alltypessmall) x |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Select * from aggregate in subquery |
| select * from (select count(*) from functional.alltypessmall) x |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Select from aliased aggregate in subquery |
| select c from (select count(*) c from functional.alltypessmall) x |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Select aggregate from aliased aggregate in subquery |
| select count(c) from (select count(*) c from functional.alltypessmall) x |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Select aggregate from aggregate of basetable column in subquery |
| select count(1) from (select count(tinyint_col) from functional.alltypessmall) x |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Select aggregate from aggregate in subquery with group by |
| select count(1) from |
| (select count(*) from functional.alltypessmall group by tinyint_col) x |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-812 |
| select bool_col, count(*) from |
| (select bool_col FROM ( SELECT bool_col FROM alltypessmall t ) t WHERE bool_col ) t |
| group by 1 |
| ---- RESULTS |
| true,50 |
| ---- TYPES |
| boolean, bigint |
| ==== |
| ---- QUERY |
| # Test that tuple ids from semi-joined table refs are not added in an |
| # IsNullPredicate expr; inline view with left semi join between a base table |
| # and an inline view (IMPALA-1526) |
| select t1.int_col |
| from alltypestiny t1 left join |
| (select coalesce(t1.year, 384) as int_col |
| from alltypesagg t1 |
| where t1.bigint_col in |
| (select day as int_col from alltypesagg where t1.id = day)) t2 |
| on t2.int_col = t1.month |
| where t1.month is not null |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 0 |
| 1 |
| 0 |
| 1 |
| 0 |
| 1 |
| 0 |
| 1 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Test that tuple ids from semi-joined table refs are not added in |
| # an IsNullPredicate expr; inline view with right semi join between |
| # two inline views (IMPALA-1526) |
| select distinct t1.int_col |
| from alltypestiny t1 left join |
| (select coalesce(t3.int_col, 384) as int_col |
| from (select int_col from alltypes) t1 |
| right semi join (select int_col from alltypesagg) t3 on t1.int_col = t3.int_col) t2 |
| on t2.int_col = t1.month |
| where t1.month is not null |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 0 |
| 1 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1528 |
| SELECT t2.int_col is NULL FROM alltypesagg t1 |
| LEFT JOIN (SELECT COALESCE(76, -937, -981) AS int_col FROM alltypestiny) t2 |
| ON (t2.int_col) = (t1.year) limit 1 |
| ---- RESULTS |
| true |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| # IMPALA-1987: Equi-join predicates of outer joins contain TupleIsNullPredicate exprs. |
| select t1.int_col, t2.int_col, t3.id |
| from alltypestiny t1 left outer join |
| (select coalesce(int_col, 384) as int_col from alltypestiny) t2 |
| on t1.int_col = t2.int_col |
| left outer join |
| (select 0 as id from alltypestiny) t3 |
| on t1.int_col = t3.id |
| order by 1 limit 5 |
| ---- RESULTS |
| 0,0,0 |
| 0,0,0 |
| 0,0,0 |
| 0,0,0 |
| 0,0,0 |
| ---- TYPES |
| INT,INT,TINYINT |
| ==== |
