| ==== |
| ---- QUERY |
| # join cols are not part of select list (and still get materialized) |
| select j.test_name, d.name |
| from JoinTbl j inner join DimTbl d on (j.test_id = d.id) |
| ---- RESULTS |
| 'Name1','Name1' |
| 'Name16','Name6' |
| 'Name16','Name6' |
| 'Name16','Name6' |
| 'Name16','Name6' |
| 'Name2','Name2' |
| 'Name3','Name3' |
| 'Name4','Name4' |
| 'Name5','Name5' |
| 'Name6','Name6' |
| 'Name6','Name6' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| # join on bigint |
| select j.*, d.* |
| from JoinTbl j inner join DimTbl d on (j.test_id = d.id) |
| ---- 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,1006,'Name6',94613 |
| 1006,'Name16',94612,5000,1006,'Name6',94613 |
| 1006,'Name16',94616,15000,1006,'Name6',94613 |
| 1006,'Name16',94616,5000,1006,'Name6',94613 |
| 1006,'Name6',94616,15000,1006,'Name6',94613 |
| 1006,'Name6',94616,5000,1006,'Name6',94613 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from JoinTbl j left outer join DimTbl d on (j.test_id = d.id) |
| ---- 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,1006,'Name6',94613 |
| 1006,'Name16',94612,5000,1006,'Name6',94613 |
| 1006,'Name16',94616,15000,1006,'Name6',94613 |
| 1006,'Name16',94616,5000,1006,'Name6',94613 |
| 1006,'Name6',94616,15000,1006,'Name6',94613 |
| 1006,'Name6',94616,5000,1006,'Name6',94613 |
| 1106,'Name16',94612,15000,NULL,'NULL',NULL |
| 1106,'Name16',94612,5000,NULL,'NULL',NULL |
| 1106,'Name16',94616,15000,NULL,'NULL',NULL |
| 1106,'Name16',94616,5000,NULL,'NULL',NULL |
| 1106,'Name6',94612,15000,NULL,'NULL',NULL |
| 1106,'Name6',94612,5000,NULL,'NULL',NULL |
| 1106,'Name6',94616,15000,NULL,'NULL',NULL |
| 1106,'Name6',94616,5000,NULL,'NULL',NULL |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from JoinTbl j right outer join DimTbl d on (j.test_id = d.id) |
| ---- 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,1006,'Name6',94613 |
| 1006,'Name16',94612,5000,1006,'Name6',94613 |
| 1006,'Name16',94616,15000,1006,'Name6',94613 |
| 1006,'Name16',94616,5000,1006,'Name6',94613 |
| 1006,'Name6',94616,15000,1006,'Name6',94613 |
| 1006,'Name6',94616,5000,1006,'Name6',94613 |
| NULL,'NULL',NULL,NULL,1007,'Name7',94614 |
| NULL,'NULL',NULL,NULL,1008,'Name8',94614 |
| NULL,'NULL',NULL,NULL,1009,'Name9',94615 |
| NULL,'NULL',NULL,NULL,1010,'Name10',94615 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from JoinTbl j full outer join DimTbl d on (j.test_id = d.id) |
| ---- 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,1006,'Name6',94613 |
| 1006,'Name16',94612,5000,1006,'Name6',94613 |
| 1006,'Name16',94616,15000,1006,'Name6',94613 |
| 1006,'Name16',94616,5000,1006,'Name6',94613 |
| 1006,'Name6',94616,15000,1006,'Name6',94613 |
| 1006,'Name6',94616,5000,1006,'Name6',94613 |
| 1106,'Name16',94612,15000,NULL,'NULL',NULL |
| 1106,'Name16',94612,5000,NULL,'NULL',NULL |
| 1106,'Name16',94616,15000,NULL,'NULL',NULL |
| 1106,'Name16',94616,5000,NULL,'NULL',NULL |
| 1106,'Name6',94612,15000,NULL,'NULL',NULL |
| 1106,'Name6',94612,5000,NULL,'NULL',NULL |
| 1106,'Name6',94616,15000,NULL,'NULL',NULL |
| 1106,'Name6',94616,5000,NULL,'NULL',NULL |
| NULL,'NULL',NULL,NULL,1007,'Name7',94614 |
| NULL,'NULL',NULL,NULL,1008,'Name8',94614 |
| NULL,'NULL',NULL,NULL,1009,'Name9',94615 |
| NULL,'NULL',NULL,NULL,1010,'Name10',94615 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| # join on string |
| select j.*, d.* |
| from JoinTbl j inner join DimTbl d on (j.test_name = d.name) |
| ---- 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,'Name6',94616,15000,1006,'Name6',94613 |
| 1006,'Name6',94616,5000,1006,'Name6',94613 |
| 1106,'Name6',94612,15000,1006,'Name6',94613 |
| 1106,'Name6',94612,5000,1006,'Name6',94613 |
| 1106,'Name6',94616,15000,1006,'Name6',94613 |
| 1106,'Name6',94616,5000,1006,'Name6',94613 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from JoinTbl j left outer join DimTbl d on (j.test_name = d.name) |
| ---- 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 |
| 1106,'Name16',94612,15000,NULL,'NULL',NULL |
| 1106,'Name16',94612,5000,NULL,'NULL',NULL |
| 1106,'Name16',94616,15000,NULL,'NULL',NULL |
| 1106,'Name16',94616,5000,NULL,'NULL',NULL |
| 1106,'Name6',94612,15000,1006,'Name6',94613 |
| 1106,'Name6',94612,5000,1006,'Name6',94613 |
| 1106,'Name6',94616,15000,1006,'Name6',94613 |
| 1106,'Name6',94616,5000,1006,'Name6',94613 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from JoinTbl j right outer join DimTbl d on (j.test_name = d.name) |
| ---- 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,'Name6',94616,15000,1006,'Name6',94613 |
| 1006,'Name6',94616,5000,1006,'Name6',94613 |
| 1106,'Name6',94612,15000,1006,'Name6',94613 |
| 1106,'Name6',94612,5000,1006,'Name6',94613 |
| 1106,'Name6',94616,15000,1006,'Name6',94613 |
| 1106,'Name6',94616,5000,1006,'Name6',94613 |
| NULL,'NULL',NULL,NULL,1007,'Name7',94614 |
| NULL,'NULL',NULL,NULL,1008,'Name8',94614 |
| NULL,'NULL',NULL,NULL,1009,'Name9',94615 |
| NULL,'NULL',NULL,NULL,1010,'Name10',94615 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from JoinTbl j full outer join DimTbl d on (j.test_name = d.name) |
| ---- 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 |
| 1106,'Name16',94612,15000,NULL,'NULL',NULL |
| 1106,'Name16',94612,5000,NULL,'NULL',NULL |
| 1106,'Name16',94616,15000,NULL,'NULL',NULL |
| 1106,'Name16',94616,5000,NULL,'NULL',NULL |
| 1106,'Name6',94612,15000,1006,'Name6',94613 |
| 1106,'Name6',94612,5000,1006,'Name6',94613 |
| 1106,'Name6',94616,15000,1006,'Name6',94613 |
| 1106,'Name6',94616,5000,1006,'Name6',94613 |
| NULL,'NULL',NULL,NULL,1007,'Name7',94614 |
| NULL,'NULL',NULL,NULL,1008,'Name8',94614 |
| NULL,'NULL',NULL,NULL,1009,'Name9',94615 |
| NULL,'NULL',NULL,NULL,1010,'Name10',94615 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| # join on int |
| select j.*, d.* |
| from JoinTbl j inner join DimTbl d on (j.test_zip = d.zip) |
| ---- RESULTS |
| 1001,'Name1',94611,5000,1001,'Name1',94611 |
| 1001,'Name1',94611,5000,1002,'Name2',94611 |
| 1002,'Name2',94611,5000,1001,'Name1',94611 |
| 1002,'Name2',94611,5000,1002,'Name2',94611 |
| 1003,'Name3',94611,5000,1001,'Name1',94611 |
| 1003,'Name3',94611,5000,1002,'Name2',94611 |
| 1004,'Name4',94611,5000,1001,'Name1',94611 |
| 1004,'Name4',94611,5000,1002,'Name2',94611 |
| 1005,'Name5',94611,5000,1001,'Name1',94611 |
| 1005,'Name5',94611,5000,1002,'Name2',94611 |
| 1006,'Name16',94612,15000,1003,'Name3',94612 |
| 1006,'Name16',94612,15000,1004,'Name4',94612 |
| 1006,'Name16',94612,5000,1003,'Name3',94612 |
| 1006,'Name16',94612,5000,1004,'Name4',94612 |
| 1106,'Name16',94612,15000,1003,'Name3',94612 |
| 1106,'Name16',94612,15000,1004,'Name4',94612 |
| 1106,'Name16',94612,5000,1003,'Name3',94612 |
| 1106,'Name16',94612,5000,1004,'Name4',94612 |
| 1106,'Name6',94612,15000,1003,'Name3',94612 |
| 1106,'Name6',94612,15000,1004,'Name4',94612 |
| 1106,'Name6',94612,5000,1003,'Name3',94612 |
| 1106,'Name6',94612,5000,1004,'Name4',94612 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from JoinTbl j left outer join DimTbl d on (j.test_zip = d.zip) |
| ---- RESULTS |
| 1001,'Name1',94611,5000,1001,'Name1',94611 |
| 1001,'Name1',94611,5000,1002,'Name2',94611 |
| 1002,'Name2',94611,5000,1001,'Name1',94611 |
| 1002,'Name2',94611,5000,1002,'Name2',94611 |
| 1003,'Name3',94611,5000,1001,'Name1',94611 |
| 1003,'Name3',94611,5000,1002,'Name2',94611 |
| 1004,'Name4',94611,5000,1001,'Name1',94611 |
| 1004,'Name4',94611,5000,1002,'Name2',94611 |
| 1005,'Name5',94611,5000,1001,'Name1',94611 |
| 1005,'Name5',94611,5000,1002,'Name2',94611 |
| 1006,'Name16',94612,15000,1003,'Name3',94612 |
| 1006,'Name16',94612,15000,1004,'Name4',94612 |
| 1006,'Name16',94612,5000,1003,'Name3',94612 |
| 1006,'Name16',94612,5000,1004,'Name4',94612 |
| 1006,'Name16',94616,15000,NULL,'NULL',NULL |
| 1006,'Name16',94616,5000,NULL,'NULL',NULL |
| 1006,'Name6',94616,15000,NULL,'NULL',NULL |
| 1006,'Name6',94616,5000,NULL,'NULL',NULL |
| 1106,'Name16',94612,15000,1003,'Name3',94612 |
| 1106,'Name16',94612,15000,1004,'Name4',94612 |
| 1106,'Name16',94612,5000,1003,'Name3',94612 |
| 1106,'Name16',94612,5000,1004,'Name4',94612 |
| 1106,'Name16',94616,15000,NULL,'NULL',NULL |
| 1106,'Name16',94616,5000,NULL,'NULL',NULL |
| 1106,'Name6',94612,15000,1003,'Name3',94612 |
| 1106,'Name6',94612,15000,1004,'Name4',94612 |
| 1106,'Name6',94612,5000,1003,'Name3',94612 |
| 1106,'Name6',94612,5000,1004,'Name4',94612 |
| 1106,'Name6',94616,15000,NULL,'NULL',NULL |
| 1106,'Name6',94616,5000,NULL,'NULL',NULL |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from JoinTbl j right outer join DimTbl d on (j.test_zip = d.zip) |
| ---- RESULTS |
| 1001,'Name1',94611,5000,1001,'Name1',94611 |
| 1001,'Name1',94611,5000,1002,'Name2',94611 |
| 1002,'Name2',94611,5000,1001,'Name1',94611 |
| 1002,'Name2',94611,5000,1002,'Name2',94611 |
| 1003,'Name3',94611,5000,1001,'Name1',94611 |
| 1003,'Name3',94611,5000,1002,'Name2',94611 |
| 1004,'Name4',94611,5000,1001,'Name1',94611 |
| 1004,'Name4',94611,5000,1002,'Name2',94611 |
| 1005,'Name5',94611,5000,1001,'Name1',94611 |
| 1005,'Name5',94611,5000,1002,'Name2',94611 |
| 1006,'Name16',94612,15000,1003,'Name3',94612 |
| 1006,'Name16',94612,15000,1004,'Name4',94612 |
| 1006,'Name16',94612,5000,1003,'Name3',94612 |
| 1006,'Name16',94612,5000,1004,'Name4',94612 |
| 1106,'Name16',94612,15000,1003,'Name3',94612 |
| 1106,'Name16',94612,15000,1004,'Name4',94612 |
| 1106,'Name16',94612,5000,1003,'Name3',94612 |
| 1106,'Name16',94612,5000,1004,'Name4',94612 |
| 1106,'Name6',94612,15000,1003,'Name3',94612 |
| 1106,'Name6',94612,15000,1004,'Name4',94612 |
| 1106,'Name6',94612,5000,1003,'Name3',94612 |
| 1106,'Name6',94612,5000,1004,'Name4',94612 |
| NULL,'NULL',NULL,NULL,1005,'Name5',94613 |
| NULL,'NULL',NULL,NULL,1006,'Name6',94613 |
| NULL,'NULL',NULL,NULL,1007,'Name7',94614 |
| NULL,'NULL',NULL,NULL,1008,'Name8',94614 |
| NULL,'NULL',NULL,NULL,1009,'Name9',94615 |
| NULL,'NULL',NULL,NULL,1010,'Name10',94615 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| # Right outer join with duplicates |
| SELECT straight_join count(*) |
| FROM functional.tinyinttable a RIGHT OUTER JOIN functional.tinyinttable b |
| ON a.int_col % 2 = b.int_col % 2 |
| ---- RESULTS |
| 50 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Right outer join with duplicates and other conjuncts |
| SELECT straight_join a.int_col, b.int_col |
| FROM functional.tinyinttable a RIGHT OUTER JOIN functional.tinyinttable b |
| ON a.int_col % 2 = b.int_col % 2 AND a.int_col < b.int_col |
| ---- RESULTS |
| 0,2 |
| 0,4 |
| 0,6 |
| 0,8 |
| 1,3 |
| 1,5 |
| 1,7 |
| 1,9 |
| 2,4 |
| 2,6 |
| 2,8 |
| 3,5 |
| 3,7 |
| 3,9 |
| 4,6 |
| 4,8 |
| 5,7 |
| 5,9 |
| 6,8 |
| 7,9 |
| NULL,0 |
| NULL,1 |
| ---- TYPES |
| int, int |
| ==== |
| ---- QUERY |
| select j.*, d.* |
| from JoinTbl j full outer join DimTbl d on (j.test_zip = d.zip) |
| ---- RESULTS |
| 1001,'Name1',94611,5000,1001,'Name1',94611 |
| 1001,'Name1',94611,5000,1002,'Name2',94611 |
| 1002,'Name2',94611,5000,1001,'Name1',94611 |
| 1002,'Name2',94611,5000,1002,'Name2',94611 |
| 1003,'Name3',94611,5000,1001,'Name1',94611 |
| 1003,'Name3',94611,5000,1002,'Name2',94611 |
| 1004,'Name4',94611,5000,1001,'Name1',94611 |
| 1004,'Name4',94611,5000,1002,'Name2',94611 |
| 1005,'Name5',94611,5000,1001,'Name1',94611 |
| 1005,'Name5',94611,5000,1002,'Name2',94611 |
| 1006,'Name16',94612,15000,1003,'Name3',94612 |
| 1006,'Name16',94612,15000,1004,'Name4',94612 |
| 1006,'Name16',94612,5000,1003,'Name3',94612 |
| 1006,'Name16',94612,5000,1004,'Name4',94612 |
| 1006,'Name16',94616,15000,NULL,'NULL',NULL |
| 1006,'Name16',94616,5000,NULL,'NULL',NULL |
| 1006,'Name6',94616,15000,NULL,'NULL',NULL |
| 1006,'Name6',94616,5000,NULL,'NULL',NULL |
| 1106,'Name16',94612,15000,1003,'Name3',94612 |
| 1106,'Name16',94612,15000,1004,'Name4',94612 |
| 1106,'Name16',94612,5000,1003,'Name3',94612 |
| 1106,'Name16',94612,5000,1004,'Name4',94612 |
| 1106,'Name16',94616,15000,NULL,'NULL',NULL |
| 1106,'Name16',94616,5000,NULL,'NULL',NULL |
| 1106,'Name6',94612,15000,1003,'Name3',94612 |
| 1106,'Name6',94612,15000,1004,'Name4',94612 |
| 1106,'Name6',94612,5000,1003,'Name3',94612 |
| 1106,'Name6',94612,5000,1004,'Name4',94612 |
| 1106,'Name6',94616,15000,NULL,'NULL',NULL |
| 1106,'Name6',94616,5000,NULL,'NULL',NULL |
| NULL,'NULL',NULL,NULL,1005,'Name5',94613 |
| NULL,'NULL',NULL,NULL,1006,'Name6',94613 |
| NULL,'NULL',NULL,NULL,1007,'Name7',94614 |
| NULL,'NULL',NULL,NULL,1008,'Name8',94614 |
| NULL,'NULL',NULL,NULL,1009,'Name9',94615 |
| NULL,'NULL',NULL,NULL,1010,'Name10',94615 |
| ---- TYPES |
| bigint, string, int, int, bigint, string, int |
| ==== |
| ---- QUERY |
| # joins on empty tables |
| select * from emptytable t1 full outer join emptytable t2 on (t1.field=t2.field) |
| ---- RESULTS |
| ---- TYPES |
| string, int, string, int |
| ==== |
| ---- QUERY |
| select count(*) from emptytable t1 right outer join greptiny t2 on (t1.field=t2.field) |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from greptiny t1 left outer join emptytable t2 on (t1.field=t2.field) |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # test nested joins |
| select a.id, b.id, c.id, a.day, b.day, c.month, a.month, b.month |
| from alltypesagg a join alltypesagg b on |
| (a.id = b.id and a.month = b.day and b.month = 1 and b.month = b.day and a.day = b.day) |
| left outer join alltypessmall c on |
| (c.id = a.id and a.month = c.id and a.month=c.month) |
| where a.id < 10 |
| ---- RESULTS |
| 0,0,NULL,1,1,NULL,1,1 |
| 1,1,1,1,1,1,1,1 |
| 2,2,NULL,1,1,NULL,1,1 |
| 3,3,NULL,1,1,NULL,1,1 |
| 4,4,NULL,1,1,NULL,1,1 |
| 5,5,NULL,1,1,NULL,1,1 |
| 6,6,NULL,1,1,NULL,1,1 |
| 7,7,NULL,1,1,NULL,1,1 |
| 8,8,NULL,1,1,NULL,1,1 |
| 9,9,NULL,1,1,NULL,1,1 |
| ---- TYPES |
| int, int, int, int, int, int, int, int |
| ==== |
| ---- QUERY |
| # test full outer join where the RHS is empty and the LHS has a very low selectivity |
| # test query filed in IMP-791 |
| SELECT a.string_col ts2 |
| FROM alltypes a |
| FULL OUTER JOIN ( |
| select * from alltypessmall where id < 0 |
| ) b |
| ON a.id=b.id AND a.id = 1 |
| ORDER BY ts2 DESC LIMIT 10; |
| ---- RESULTS |
| '9' |
| '9' |
| '9' |
| '9' |
| '9' |
| '9' |
| '9' |
| '9' |
| '9' |
| '9' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # test nullable expressions from inline views in left outer join |
| select x.id, y.a, y.b, y.id from alltypestiny x left outer join |
| (select 10 as a, case when id is null then 11 else 12 end as b, id from alltypestiny) |
| y on x.id = (y.id % 2) |
| order by x.id, y.id limit 100 |
| ---- RESULTS |
| 0,10,12,0 |
| 0,10,12,2 |
| 0,10,12,4 |
| 0,10,12,6 |
| 1,10,12,1 |
| 1,10,12,3 |
| 1,10,12,5 |
| 1,10,12,7 |
| 2,NULL,NULL,NULL |
| 3,NULL,NULL,NULL |
| 4,NULL,NULL,NULL |
| 5,NULL,NULL,NULL |
| 6,NULL,NULL,NULL |
| 7,NULL,NULL,NULL |
| ---- TYPES |
| int, tinyint, tinyint, int |
| ==== |
| ---- QUERY |
| # test nullable expressions from inline views in full outer join |
| select x.a, x.b, x.id, y.a, y.b, y.id from |
| (select 10 as a, case when id is null then 11 else 12 end as b, id from alltypestiny) x |
| full outer join |
| (select 20 as a, case when id is null then 21 else 22 end as b, id from alltypestiny) y on x.id = (y.id + 4) |
| order by x.id, y.id limit 100 |
| ---- RESULTS |
| 10,12,0,NULL,NULL,NULL |
| 10,12,1,NULL,NULL,NULL |
| 10,12,2,NULL,NULL,NULL |
| 10,12,3,NULL,NULL,NULL |
| 10,12,4,20,22,0 |
| 10,12,5,20,22,1 |
| 10,12,6,20,22,2 |
| 10,12,7,20,22,3 |
| NULL,NULL,NULL,20,22,4 |
| NULL,NULL,NULL,20,22,5 |
| NULL,NULL,NULL,20,22,6 |
| NULL,NULL,NULL,20,22,7 |
| ---- TYPES |
| tinyint, tinyint, int, tinyint, tinyint, int |
| ==== |
| ---- QUERY |
| # test nullable expressions wrapped multiple times |
| select z.xa, z.xid, z.ya, z.yid, k.a, k.id from |
| (select x.a as xa, x.id as xid, y.a as ya, y.id as yid from |
| (select 10 as a, id from alltypestiny where id = 1) x |
| full outer join |
| (select 20 as a, id from alltypestiny where id = 1) y on x.id = (y.id + 2) |
| ) z |
| full outer join |
| (select 30 as a, id from alltypestiny where id = 1) k on z.xid = (k.id + 3) |
| order by z.xid, z.yid, k.id limit 100 |
| ---- RESULTS |
| 10,1,NULL,NULL,NULL,NULL |
| NULL,NULL,20,1,NULL,NULL |
| NULL,NULL,NULL,NULL,30,1 |
| ---- TYPES |
| tinyint, int, tinyint, int, tinyint, int |
| ==== |
| ---- QUERY |
| # right outer join requires the join op to be partitioned, otherwise non-matches cause |
| # duplicates |
| select count(*) |
| from functional.alltypesagg a |
| right outer join functional.alltypestiny b on (a.tinyint_col = b.id) |
| where a.tinyint_col is null |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # same for full outer joins |
| select count(*) |
| from functional.alltypesagg a |
| full outer join functional.alltypestiny b on (a.tinyint_col = b.id) |
| where a.tinyint_col is null |
| ---- RESULTS |
| 2001 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # test multiple outer joins with an inline view to check that nullable tuples |
| # are properly sent over the network |
| select z.xid, z.yid, k.id from |
| (select x.id as xid, y.id as yid from |
| (select id from alltypestiny where id = 1) x |
| full outer join |
| (select id from alltypestiny where id = 1) y on x.id = (y.id + 2) |
| ) z |
| full outer join |
| (select id from alltypestiny where id = 1) k on z.xid = (k.id + 3) |
| order by z.xid, z.yid, k.id limit 100 |
| ---- RESULTS |
| 1,NULL,NULL |
| NULL,1,NULL |
| NULL,NULL,1 |
| ---- TYPES |
| int, int, int |
| ==== |
| ---- QUERY |
| # test that right outer join includes tuples from rhs |
| # even if one of its join columns is NULL |
| select count(*) from functional.alltypesagg a |
| right outer join functional.alltypesagg b on a.int_col = b.int_col |
| where b.int_col is null |
| ---- RESULTS |
| 20 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # test that full outer join includes tuples from rhs |
| # even if one of its join columns is NULL |
| select count(*) from functional.alltypesagg a |
| full outer join functional.alltypesagg b on a.int_col = b.int_col |
| where a.int_col is null or b.int_col is null |
| ---- RESULTS |
| 40 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # IMPALA-2440: Full outer join using non-partitioned HJ can incorrectly produce |
| # extra row of nulls |
| select a.id, b.id |
| from |
| (select id |
| from functional.alltypestiny where id = -1) a |
| full outer join |
| (select id |
| from functional.alltypestiny where id = -2) b |
| on (a.id = b.id) |
| ---- RESULTS |
| ---- TYPES |
| INT, INT |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-676, which exposed a bug in our codegen subexpresssion |
| # elimination logic. |
| select count(*) from alltypesagg a |
| left outer join alltypesagg b |
| on (a.id = b.id |
| and (a.int_col = b.int_col + 10000 or (a.int_col is null and b.int_col is null))) |
| where b.id is not null |
| ---- RESULTS |
| 40 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-735. |
| select distinct t1.tinyint_col from alltypesagg t1 |
| full outer join alltypesagg t2 |
| on t2.smallint_col = (t1.tinyint_col + 10000) |
| ---- RESULTS |
| NULL |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| ---- TYPES |
| tinyint |
| ==== |
| ---- QUERY |
| ## Test for IMPALA-2065. This query needs a join with >1GB build partitions. We currently |
| ## cannot allocate such big partitions and we fail with OOM. Once IMPALA-1619 is delivered |
| ## we should be able to run this query. |
| #SELECT |
| #COUNT(DISTINCT t1.double_col) AS int_col, |
| #(COALESCE((t1.month) - (t1.tinyint_col), |
| # (t1.month) - (t1.tinyint_col), |
| # (t1.month) - (t1.tinyint_col))) = (MIN((t1.month) - (t1.tinyint_col))) |
| #AS boolean_col, |
| #(t1.month) - (t1.tinyint_col) AS int_col_2 |
| #FROM alltypes t1 |
| #WHERE(t1.id) NOT IN (WITH with_1 AS (SELECT |
| #LEAD(COALESCE(COALESCE(300, 124, -158), 369, 365), 63) |
| #OVER (ORDER BY bigint_col DESC, string_col ASC) AS int_col |
| #FROM alltypes) |
| #SELECT |
| #(tt1.int_col) - (-822.1) AS decimal_col |
| #FROM with_1 tt1 |
| #INNER JOIN with_1 tt2 ON ((tt2.int_col) = (tt1.int_col)) AND ((tt2.int_col) = (tt1.int_col)) |
| #WHERE |
| #((tt1.int_col) <= (tt1.int_col)) AND |
| #(((t1.tinyint_col) = (tt2.int_col)) AND |
| #((t1.id) = (tt2.int_col)))) |
| #GROUP BY (t1.month) - (t1.tinyint_col) |
| #---- RESULTS |
| #'WARNINGS:' |
| #'Memory limit exceeded' |
| #'Cannot perform hash join at node with id 9. Repartitioning did not reduce the size of a spilled partition. Repartitioning level 1. Number of rows 52374169.' |
| #==== |
| #---- QUERY |
| # Regression test for IMPALA-904. Tests correct execution of exprs from an outer-joined |
| # inline view that can evaluate to a non-NULL value with NULL-valued slots. Use an |
| # explicit [shuffle] hint for extra coverage because the expr will also be evaluated in |
| # a hashing data sink. |
| select count(*) from functional.alltypestiny t1 left outer join [shuffle] |
| (select zeroifnull(int_col) as id from functional.alltypessmall) t2 on (t1.id = t2.id) |
| ---- RESULTS |
| 84 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # IMPALA-2950: Tests correct execution of exprs from an outer-joined inline view that can |
| # evaluate to a non-NULL value with NULL-valued slots. This test covers deeply nested |
| # inline views where exprs with the said property are present at various nesting levels. |
| SELECT t1.id, v3.id, v3.int_col, v3.bigint_col |
| FROM functional.alltypestiny t1 |
| LEFT OUTER JOIN |
| (SELECT id, int_col, COALESCE(bigint_col, 4000) AS bigint_col |
| FROM |
| (SELECT id, COALESCE(int_col, 3000) AS int_col, bigint_col |
| FROM |
| (SELECT COALESCE(id + 100, 2000) AS id, int_col, bigint_col |
| FROM functional.alltypestiny t2 |
| ) v1 |
| ) v2 |
| ) v3 |
| ON t1.id = v3.id |
| ---- RESULTS |
| 0,NULL,NULL,NULL |
| 1,NULL,NULL,NULL |
| 2,NULL,NULL,NULL |
| 3,NULL,NULL,NULL |
| 4,NULL,NULL,NULL |
| 5,NULL,NULL,NULL |
| 6,NULL,NULL,NULL |
| 7,NULL,NULL,NULL |
| ---- TYPES |
| int,bigint,int,bigint |
| ==== |
| ---- QUERY |
| # IMPALA-5504: Check that a TupleIsNullPredicate evaluates to false if one of tuples to |
| # check is non-nullable. This happens for exprs that are evaluated before the outer join |
| # that makes those tuples nullable, e.g., in the ON-clause of that join. |
| SELECT /* +straight_join */ COUNT(t1.id) |
| FROM functional.alltypessmall t1 |
| LEFT OUTER JOIN ( |
| SELECT /* +straight_join */ IFNULL(t2.int_col, 1) AS c |
| FROM functional.alltypessmall t2 |
| LEFT OUTER JOIN functional.alltypestiny t3 ON t2.id = t3.id + 1000 |
| ) v ON t1.int_col = v.c; |
| ---- RESULTS |
| 1040 |
| ---- TYPES |
| bigint |
| ==== |