| ==== |
| ---- QUERY |
| select id, bool_col, string_col from functional.alltypestiny |
| ---- RESULTS |
| 0,NULL,'0aaa' |
| 100,NULL,'1aaa' |
| 200,NULL,'0aaa' |
| 300,NULL,'1aaa' |
| 400,NULL,'0aaa' |
| 500,NULL,'1aaa' |
| 600,NULL,'0aaa' |
| 700,NULL,'1aaa' |
| ---- TYPES |
| INT,BOOLEAN,STRING |
| ==== |
| ---- QUERY |
| # Test on table alias |
| select t.id, bool_col, string_col from functional.alltypestiny t |
| ---- RESULTS |
| 0,NULL,'0aaa' |
| 100,NULL,'1aaa' |
| 200,NULL,'0aaa' |
| 300,NULL,'1aaa' |
| 400,NULL,'0aaa' |
| 500,NULL,'1aaa' |
| 600,NULL,'0aaa' |
| 700,NULL,'1aaa' |
| ---- TYPES |
| INT,BOOLEAN,STRING |
| ==== |
| ---- QUERY |
| # Test on slot path resolution |
| select functional.alltypestiny.id, alltypestiny.bool_col, string_col |
| from functional.alltypestiny |
| ---- RESULTS |
| 0,NULL,'0aaa' |
| 100,NULL,'1aaa' |
| 200,NULL,'0aaa' |
| 300,NULL,'1aaa' |
| 400,NULL,'0aaa' |
| 500,NULL,'1aaa' |
| 600,NULL,'0aaa' |
| 700,NULL,'1aaa' |
| ---- TYPES |
| INT,BOOLEAN,STRING |
| ==== |
| ---- QUERY |
| # Test on star select item |
| select * from functional.alltypestiny |
| ---- RESULTS |
| 0,NULL,0,0,0,0,0,0,'01/01/09','0aaa',2009-01-01 00:00:00,2009,1 |
| 100,NULL,1,1,1,10,1.100000023841858,10.1,'01/01/09','1aaa',2009-01-01 00:01:00,2009,1 |
| 200,NULL,0,0,0,0,0,0,'02/01/09','0aaa',2009-02-01 00:00:00,2009,2 |
| 300,NULL,1,1,1,10,1.100000023841858,10.1,'02/01/09','1aaa',2009-02-01 00:01:00,2009,2 |
| 400,NULL,0,0,0,0,0,0,'03/01/09','0aaa',2009-03-01 00:00:00,2009,3 |
| 500,NULL,1,1,1,10,1.100000023841858,10.1,'03/01/09','1aaa',2009-03-01 00:01:00,2009,3 |
| 600,NULL,0,0,0,0,0,0,'04/01/09','0aaa',2009-04-01 00:00:00,2009,4 |
| 700,NULL,1,1,1,10,1.100000023841858,10.1,'04/01/09','1aaa',2009-04-01 00:01:00,2009,4 |
| ---- TYPES |
| INT,BOOLEAN,TINYINT,SMALLINT,INT,BIGINT,FLOAT,DOUBLE,STRING,STRING,TIMESTAMP,INT,INT |
| ==== |
| ---- QUERY |
| # Test on star select item with table alias |
| select * from functional.alltypestiny t |
| ---- RESULTS |
| 0,NULL,0,0,0,0,0,0,'01/01/09','0aaa',2009-01-01 00:00:00,2009,1 |
| 100,NULL,1,1,1,10,1.100000023841858,10.1,'01/01/09','1aaa',2009-01-01 00:01:00,2009,1 |
| 200,NULL,0,0,0,0,0,0,'02/01/09','0aaa',2009-02-01 00:00:00,2009,2 |
| 300,NULL,1,1,1,10,1.100000023841858,10.1,'02/01/09','1aaa',2009-02-01 00:01:00,2009,2 |
| 400,NULL,0,0,0,0,0,0,'03/01/09','0aaa',2009-03-01 00:00:00,2009,3 |
| 500,NULL,1,1,1,10,1.100000023841858,10.1,'03/01/09','1aaa',2009-03-01 00:01:00,2009,3 |
| 600,NULL,0,0,0,0,0,0,'04/01/09','0aaa',2009-04-01 00:00:00,2009,4 |
| 700,NULL,1,1,1,10,1.100000023841858,10.1,'04/01/09','1aaa',2009-04-01 00:01:00,2009,4 |
| ---- TYPES |
| INT,BOOLEAN,TINYINT,SMALLINT,INT,BIGINT,FLOAT,DOUBLE,STRING,STRING,TIMESTAMP,INT,INT |
| ==== |
| ---- QUERY |
| # Test on predicate. Should evaluate on masked values. |
| select * from functional.alltypestiny where id = 1 |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test on predicate. Should evaluate on masked values. |
| select * from functional.alltypestiny where id = 100 |
| ---- RESULTS |
| 100,NULL,1,1,1,10,1.100000023841858,10.1,'01/01/09','1aaa',2009-01-01 00:01:00,2009,1 |
| ---- TYPES |
| INT,BOOLEAN,TINYINT,SMALLINT,INT,BIGINT,FLOAT,DOUBLE,STRING,STRING,TIMESTAMP,INT,INT |
| ==== |
| ---- QUERY |
| # Test on predicate. Should evaluate on masked values. |
| select concat(string_col, date_string_col) from ( |
| select string_col, date_string_col from functional.alltypestiny where string_col = "1" |
| ) t |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test on predicate. Should evaluate on masked values. |
| select concat(string_col, date_string_col) from ( |
| select string_col, date_string_col from functional.alltypestiny where string_col = "1aaa" |
| ) t |
| ---- RESULTS |
| '1aaa01/01/09' |
| '1aaa02/01/09' |
| '1aaa03/01/09' |
| '1aaa04/01/09' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| # Test on slot path resolution for multiple tables |
| select t.id, s.bool_col, t.bool_col, s.string_col, t.string_col |
| from functional.alltypessmall s join functional.alltypestiny t |
| on s.id = t.id |
| ---- RESULTS |
| 0,true,NULL,'0','0aaa' |
| ---- TYPES |
| INT,BOOLEAN,BOOLEAN,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test on slot path resolution for multiple tables |
| select t.id, s.bool_col, t.bool_col, s.string_col, t.string_col |
| from functional.alltypessmall s join functional.alltypestiny t using (id) |
| ---- RESULTS |
| 0,true,NULL,'0','0aaa' |
| ---- TYPES |
| INT,BOOLEAN,BOOLEAN,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test on slot path resolution for multiple tables |
| select a.id, s.bool_col, t.bool_col, s.string_col, t.string_col |
| from functional.alltypes a |
| join functional.alltypestiny t using (id) |
| join functional.alltypessmall s on t.id = s.id |
| ---- RESULTS |
| 0,true,NULL,'0','0aaa' |
| ---- TYPES |
| INT,BOOLEAN,BOOLEAN,STRING,STRING |
| ==== |
| ---- QUERY |
| select t.* from functional.alltypessmall s join functional.alltypestiny t on s.id = t.id |
| ---- RESULTS |
| 0,NULL,0,0,0,0,0,0,'01/01/09','0aaa',2009-01-01 00:00:00,2009,1 |
| ---- TYPES |
| INT,BOOLEAN,TINYINT,SMALLINT,INT,BIGINT,FLOAT,DOUBLE,STRING,STRING,TIMESTAMP,INT,INT |
| ==== |
| ---- QUERY |
| select |
| alltypessmall.id, alltypestiny.id + 1, |
| alltypessmall.bool_col, functional.alltypestiny.bool_col is null, |
| alltypessmall.string_col, concat('xxx', alltypestiny.string_col) |
| from functional.alltypessmall join functional.alltypestiny |
| on alltypessmall.id = functional.alltypestiny.id |
| where alltypestiny.id % 200 = 0 |
| ---- RESULTS |
| 0,1,true,true,'0','xxx0aaa' |
| ---- TYPES |
| INT,BIGINT,BOOLEAN,BOOLEAN,STRING,STRING |
| ==== |
| ---- QUERY |
| # Test on subqueries |
| select id, bool_col, int_col, string_col from ( |
| select id, bool_col, int_col, string_col from ( |
| select id, bool_col, int_col, upper(string_col) as string_col |
| from functional.alltypestiny |
| ) t1 where id % 200 = 0 |
| ) t0 where id = 400 |
| ---- RESULTS |
| 400,NULL,0,'0AAA' |
| ---- TYPES |
| INT,BOOLEAN,INT,STRING |
| ==== |
| ---- QUERY |
| # Test on union |
| select id, bool_col, string_col from functional.alltypestiny |
| union all |
| select id, bool_col, string_col from functional.alltypestiny |
| ---- RESULTS |
| 0,NULL,'0aaa' |
| 100,NULL,'1aaa' |
| 200,NULL,'0aaa' |
| 300,NULL,'1aaa' |
| 400,NULL,'0aaa' |
| 500,NULL,'1aaa' |
| 600,NULL,'0aaa' |
| 700,NULL,'1aaa' |
| 0,NULL,'0aaa' |
| 100,NULL,'1aaa' |
| 200,NULL,'0aaa' |
| 300,NULL,'1aaa' |
| 400,NULL,'0aaa' |
| 500,NULL,'1aaa' |
| 600,NULL,'0aaa' |
| 700,NULL,'1aaa' |
| ---- TYPES |
| INT,BOOLEAN,STRING |
| ==== |
| ---- QUERY |
| # Test on view and it's underlying table. alltypes_view is a view on table alltypes. |
| # They both have column masking policies and are applied. |
| select id, bool_col, int_col, string_col from functional.alltypes_view |
| order by id limit 10 |
| ---- RESULTS |
| 0,true,0,'vvv0ttt' |
| 100,false,1,'vvv1ttt' |
| 200,true,2,'vvv2ttt' |
| 300,false,3,'vvv3ttt' |
| 400,true,4,'vvv4ttt' |
| 500,false,5,'vvv5ttt' |
| 600,true,6,'vvv6ttt' |
| 700,false,7,'vvv7ttt' |
| 800,true,8,'vvv8ttt' |
| 900,false,9,'vvv9ttt' |
| ---- TYPES |
| INT,BOOLEAN,INT,STRING |
| ==== |
| ---- QUERY |
| # Test on local view (CTE). Correctly ignore masking on local view names so the result |
| # won't be 100 (affected by policy id => id * 100). |
| use functional; |
| with alltypestiny as (select 1 as id) |
| select * from alltypestiny |
| ---- RESULTS |
| 1 |
| ==== |
| ---- QUERY |
| # Test on local view (CTE). Correctly ignore masking on local view names so the value |
| # of local view 'alltypes' won't be 10000 (affected by policy id => id * 100). |
| use functional; |
| with alltypes as (select 100 as id) |
| select alltypes.id from alltypestiny join alltypes using (id) |
| ---- RESULTS |
| 100 |
| ==== |
| ---- QUERY |
| # Test on local view (CTE). Correctly mask table used in local view. |
| use functional; |
| with iv as (select id, bool_col, string_col from alltypestiny) |
| select * from iv |
| ---- RESULTS |
| 0,NULL,'0aaa' |
| 100,NULL,'1aaa' |
| 200,NULL,'0aaa' |
| 300,NULL,'1aaa' |
| 400,NULL,'0aaa' |
| 500,NULL,'1aaa' |
| 600,NULL,'0aaa' |
| 700,NULL,'1aaa' |
| ---- TYPES |
| INT,BOOLEAN,STRING |
| ==== |
| ---- QUERY |
| # Test on local view (CTE). Correctly mask view used in local view. |
| use functional; |
| with iv as (select id, bool_col, string_col from alltypes_view order by id limit 10) |
| select * from iv |
| ---- RESULTS |
| 0,true,'vvv0ttt' |
| 100,false,'vvv1ttt' |
| 200,true,'vvv2ttt' |
| 300,false,'vvv3ttt' |
| 400,true,'vvv4ttt' |
| 500,false,'vvv5ttt' |
| 600,true,'vvv6ttt' |
| 700,false,'vvv7ttt' |
| 800,true,'vvv8ttt' |
| 900,false,'vvv9ttt' |
| ---- TYPES |
| INT,BOOLEAN,STRING |
| ==== |
| ---- QUERY |
| # Test on local view (CTE). |
| use functional; |
| with iv1 as (select id, bool_col, string_col from alltypestiny), |
| iv2 as (select int_col, count(int_col) as cnt from alltypestiny group by int_col) |
| select iv1.*, iv2.*, v.string_col from iv1, iv2, alltypes_view v |
| where iv1.id = iv2.int_col and iv1.id = v.id |
| ---- RESULTS |
| 0,NULL,'0aaa',0,4,'vvv0ttt' |
| ---- TYPES |
| INT,BOOLEAN,STRING,INT,BIGINT,STRING |
| ==== |
| ---- QUERY |
| # Test on local view (CTE). The join properties are associated at TableRef of |
| # alltypes_view. Test those properties are migrated correctly in masking and |
| # unmasking. See more in FromClause#reset(). |
| with iv as (select v.string_col |
| from functional.alltypestiny t |
| join functional.alltypes_view v |
| on t.id = v.id) |
| select * from iv; |
| ---- RESULTS |
| 'vvv0ttt' |
| 'vvv1ttt' |
| 'vvv2ttt' |
| 'vvv3ttt' |
| 'vvv4ttt' |
| 'vvv5ttt' |
| 'vvv6ttt' |
| 'vvv7ttt' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| # Test on local view (CTE). The join properties are associated at TableRef of |
| # alltypestiny. Test those properties are migrated correctly in masking and |
| # unmasking. See more in FromClause#reset(). |
| with iv as (select v.string_col |
| from functional.alltypes_view v |
| join functional.alltypestiny t |
| on t.id = v.id) |
| select * from iv; |
| ---- RESULTS |
| 'vvv0ttt' |
| 'vvv1ttt' |
| 'vvv2ttt' |
| 'vvv3ttt' |
| 'vvv4ttt' |
| 'vvv5ttt' |
| 'vvv6ttt' |
| 'vvv7ttt' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| # Test on masking table inside correlated subquery. |
| use functional; |
| select id, string_col from alltypes a |
| where exists (select id from alltypestiny where id = a.id) |
| order by id |
| ---- RESULTS |
| 0,'0ttt' |
| 100,'1ttt' |
| 200,'2ttt' |
| 300,'3ttt' |
| 400,'4ttt' |
| 500,'5ttt' |
| 600,'6ttt' |
| 700,'7ttt' |
| ---- TYPES |
| INT,STRING |
| ==== |
| ---- QUERY |
| # Test on masking table inside correlated subquery. |
| use functional; |
| select id from alltypessmall |
| where id * 10 in (select id from alltypestiny) |
| order by id |
| ---- RESULTS |
| 0 |
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Test on masking table inside uncorrelated subquery. |
| use functional; |
| select id from alltypessmall |
| where id = (select count(1) from alltypestiny where id < 500) |
| ---- RESULTS |
| 5 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Test on CTAS |
| create table $UNIQUE_DB.masked_tbl as select * from alltypestiny; |
| select * from $UNIQUE_DB.masked_tbl; |
| ---- RESULTS |
| 0,NULL,0,0,0,0,0,0,'01/01/09','0aaa',2009-01-01 00:00:00,2009,1 |
| 100,NULL,1,1,1,10,1.100000023841858,10.1,'01/01/09','1aaa',2009-01-01 00:01:00,2009,1 |
| 200,NULL,0,0,0,0,0,0,'02/01/09','0aaa',2009-02-01 00:00:00,2009,2 |
| 300,NULL,1,1,1,10,1.100000023841858,10.1,'02/01/09','1aaa',2009-02-01 00:01:00,2009,2 |
| 400,NULL,0,0,0,0,0,0,'03/01/09','0aaa',2009-03-01 00:00:00,2009,3 |
| 500,NULL,1,1,1,10,1.100000023841858,10.1,'03/01/09','1aaa',2009-03-01 00:01:00,2009,3 |
| 600,NULL,0,0,0,0,0,0,'04/01/09','0aaa',2009-04-01 00:00:00,2009,4 |
| 700,NULL,1,1,1,10,1.100000023841858,10.1,'04/01/09','1aaa',2009-04-01 00:01:00,2009,4 |
| ---- TYPES |
| INT,BOOLEAN,TINYINT,SMALLINT,INT,BIGINT,FLOAT,DOUBLE,STRING,STRING,TIMESTAMP,INT,INT |
| ==== |
| ---- QUERY |
| # Test on CreateView. Should not mask the columns when used in sql generations. |
| create view $UNIQUE_DB.masked_view as select * from alltypestiny; |
| show create view $UNIQUE_DB.masked_view; |
| ---- RESULTS |
| 'CREATE VIEW $UNIQUE_DB.masked_view AS\nSELECT * FROM functional.alltypestiny' |
| ==== |
| ---- QUERY |
| # Test on AlterView. Should not mask the columns when used in sql generations. |
| alter view $UNIQUE_DB.masked_view as select id from alltypestiny; |
| show create view $UNIQUE_DB.masked_view; |
| ---- RESULTS |
| 'CREATE VIEW $UNIQUE_DB.masked_view AS\nSELECT id FROM functional.alltypestiny' |
| ==== |
| ---- QUERY |
| # Test queries on complex types table. |
| select id from functional_parquet.complextypestbl |
| ---- RESULTS |
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
| 600 |
| 700 |
| 800 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Test queries on complex types table. |
| select * from functional_parquet.complextypestbl |
| ---- RESULTS |
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
| 600 |
| 700 |
| 800 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Test resolving nested column of the masked table. |
| select id, nested_struct.a from functional_parquet.complextypestbl |
| ---- RESULTS |
| 100,1 |
| 200,NULL |
| 300,NULL |
| 400,NULL |
| 500,NULL |
| 600,NULL |
| 700,7 |
| 800,-1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test resolving nested columns in expending star expression. |
| select id, nested_struct.* from functional_parquet.complextypestbl |
| ---- RESULTS |
| 100,1 |
| 200,NULL |
| 300,NULL |
| 400,NULL |
| 500,NULL |
| 600,NULL |
| 700,7 |
| 800,-1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test resolving nested column in function. |
| select count(id), count(nested_struct.a) from functional_parquet.complextypestbl |
| ---- RESULTS |
| 8,3 |
| ---- TYPES |
| BIGINT,BIGINT |
| ==== |
| ---- QUERY |
| # Test predicates on masked columns and nested columns. |
| select id, nested_struct.a from functional_parquet.complextypestbl t |
| where id = 100 and nested_struct.a = 1; |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test resolving nested collection of the nested table. Should resolve 't.int_array' |
| # correctly though 'complextypestbl' will be masked into a table masking view. |
| select pos, item from functional_parquet.complextypestbl t, t.int_array |
| ---- RESULTS |
| 0,-1 |
| 0,1 |
| 0,NULL |
| 1,1 |
| 1,2 |
| 2,2 |
| 2,3 |
| 3,NULL |
| 4,3 |
| 5,NULL |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # Regression test when 'complextypestbl' is not used as TableRef. |
| select pos, item from functional_parquet.complextypestbl.int_array |
| ---- RESULTS |
| 0,-1 |
| 0,1 |
| 0,NULL |
| 1,1 |
| 1,2 |
| 2,2 |
| 2,3 |
| 3,NULL |
| 4,3 |
| 5,NULL |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test resolving nested columns and nested collections. |
| select id, nested_struct.a, a.pos, a.item |
| from functional_parquet.complextypestbl t, t.int_array a |
| ---- RESULTS |
| 100,1,0,1 |
| 100,1,1,2 |
| 100,1,2,3 |
| 200,NULL,0,NULL |
| 200,NULL,1,1 |
| 200,NULL,2,2 |
| 200,NULL,3,NULL |
| 200,NULL,4,3 |
| 200,NULL,5,NULL |
| 800,-1,0,-1 |
| ---- TYPES |
| BIGINT,INT,BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test different JOINs comparing to the above test. |
| select id, nested_struct.a, a.pos, a.item |
| from functional_parquet.complextypestbl t join t.int_array a |
| ---- RESULTS |
| 100,1,0,1 |
| 100,1,1,2 |
| 100,1,2,3 |
| 200,NULL,0,NULL |
| 200,NULL,1,1 |
| 200,NULL,2,2 |
| 200,NULL,3,NULL |
| 200,NULL,4,3 |
| 200,NULL,5,NULL |
| 800,-1,0,-1 |
| ---- TYPES |
| BIGINT,INT,BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test different JOINs. |
| select id, nested_struct.a, a.pos, a.item |
| from functional_parquet.complextypestbl t left join t.int_array a |
| ---- RESULTS |
| 100,1,0,1 |
| 100,1,1,2 |
| 100,1,2,3 |
| 200,NULL,0,NULL |
| 200,NULL,1,1 |
| 200,NULL,2,2 |
| 200,NULL,3,NULL |
| 200,NULL,4,3 |
| 200,NULL,5,NULL |
| 300,NULL,NULL,NULL |
| 400,NULL,NULL,NULL |
| 500,NULL,NULL,NULL |
| 600,NULL,NULL,NULL |
| 700,7,NULL,NULL |
| 800,-1,0,-1 |
| ---- TYPES |
| BIGINT,INT,BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test different JOINs. |
| select id, nested_struct.a, a.pos, a.item |
| from functional_parquet.complextypestbl t right join t.int_array a |
| ---- RESULTS |
| 100,1,0,1 |
| 100,1,1,2 |
| 100,1,2,3 |
| 200,NULL,0,NULL |
| 200,NULL,1,1 |
| 200,NULL,2,2 |
| 200,NULL,3,NULL |
| 200,NULL,4,3 |
| 200,NULL,5,NULL |
| 800,-1,0,-1 |
| ---- TYPES |
| BIGINT,INT,BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test different JOINs. |
| select id, nested_struct.a, a.pos, a.item |
| from functional_parquet.complextypestbl t full outer join t.int_array a |
| ---- RESULTS |
| 100,1,0,1 |
| 100,1,1,2 |
| 100,1,2,3 |
| 200,NULL,0,NULL |
| 200,NULL,1,1 |
| 200,NULL,2,2 |
| 200,NULL,3,NULL |
| 200,NULL,4,3 |
| 200,NULL,5,NULL |
| 300,NULL,NULL,NULL |
| 400,NULL,NULL,NULL |
| 500,NULL,NULL,NULL |
| 600,NULL,NULL,NULL |
| 700,7,NULL,NULL |
| 800,-1,0,-1 |
| ---- TYPES |
| BIGINT,INT,BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test function and predicates on nested columns of the masked table. |
| select count(nested_struct.a) from functional_parquet.complextypestbl t, t.int_array a |
| where id = 100 and nested_struct.a = 1 |
| ---- RESULTS |
| 3 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Test on a deeper nested collection 'int_array_array'. |
| select id, nested_struct.a, aa.item |
| from functional_parquet.complextypestbl t, t.int_array_array.item aa |
| ---- RESULTS |
| 100,1,1 |
| 100,1,3 |
| 100,1,2 |
| 100,1,4 |
| 200,NULL,NULL |
| 200,NULL,3 |
| 200,NULL,1 |
| 200,NULL,NULL |
| 200,NULL,2 |
| 200,NULL,4 |
| 200,NULL,NULL |
| 700,7,5 |
| 700,7,6 |
| 800,-1,-1 |
| 800,-1,-2 |
| ---- TYPES |
| BIGINT,INT,INT |
| ==== |
| ---- QUERY |
| # Test on several nested collections. |
| select id, nested_struct.a as field, a.item, aa.item |
| from functional_parquet.complextypestbl t, t.int_array a, t.int_array_array.item aa |
| where nested_struct.a = -1 |
| ---- RESULTS |
| 800,-1,-1,-1 |
| 800,-1,-1,-2 |
| ---- TYPES |
| BIGINT,INT,INT,INT |
| ==== |
| ---- QUERY |
| # Test on map type. |
| select id, key, value from functional_parquet.complextypestbl t, t.int_map |
| ---- RESULTS |
| 100,'k1',1 |
| 100,'k2',100 |
| 200,'k1',2 |
| 200,'k2',NULL |
| 700,'k1',NULL |
| 700,'k3',NULL |
| 800,'k1',-1 |
| ---- TYPES |
| BIGINT,STRING,INT |
| ==== |
| ---- QUERY |
| # Test on deep nested column 'nested_struct.b'. |
| select id, item from functional_parquet.complextypestbl t, t.nested_struct.b |
| ---- RESULTS |
| 100,1 |
| 200,NULL |
| 700,2 |
| 700,3 |
| 700,NULL |
| 800,-1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test on correlated CollectionTableRefs. This query is copied from nested-types-scanner-multiple-materialization.test. |
| select id, item from functional_parquet.complextypestbl t, |
| (select item from t.int_array where item = 2 |
| union all |
| select item from t.int_array where item != 2 |
| union all |
| select item from t.int_array where item is null) v |
| ---- RESULTS |
| 100,1 |
| 100,2 |
| 100,3 |
| 200,1 |
| 200,2 |
| 200,3 |
| 200,NULL |
| 200,NULL |
| 200,NULL |
| 800,-1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # Test on correlated CollectionTableRefs. This query is copied from nested-types-scanner-multiple-materialization.test. |
| select id, e, f from functional_parquet.complextypestbl t, |
| (select e, f from t.nested_struct.c.d.item where e = 10 |
| union all |
| select e, f from t.nested_struct.c.d.item where e != 10 |
| union all |
| select e, f from t.nested_struct.c.d.item where e is null) v |
| ---- RESULTS |
| 100,-10,'bbb' |
| 100,10,'aaa' |
| 100,11,'c' |
| 200,-10,'bbb' |
| 200,10,'aaa' |
| 200,11,'c' |
| 200,NULL,'NULL' |
| 200,NULL,'NULL' |
| 200,NULL,'NULL' |
| 200,NULL,'NULL' |
| 700,NULL,'NULL' |
| 800,-1,'nonnullable' |
| ---- TYPES |
| BIGINT,INT,STRING |
| ==== |
| ---- QUERY |
| # Test on relative CollectionTableRefs. This query is copied from nested-types-scanner-multiple-materialization.test. |
| select id, int_array.item, a2.item, a3.item, |
| nested_struct.a, b.item, d2.e, d2.f, d3.e, d3.f |
| from functional_parquet.complextypestbl t, |
| t.int_array, |
| t.int_array_array a1, a1.item a2, |
| t.int_array_array.item a3, |
| t.nested_struct.b, |
| t.nested_struct.c.d, d.item d2, |
| t.nested_struct.c.d.item d3 |
| where a2.item = 1 and a3.item = 2 and d2.e = 10 and d3.e = -10 |
| ---- RESULTS |
| 100,1,1,2,1,1,10,'aaa',-10,'bbb' |
| 100,2,1,2,1,1,10,'aaa',-10,'bbb' |
| 100,3,1,2,1,1,10,'aaa',-10,'bbb' |
| 200,1,1,2,NULL,NULL,10,'aaa',-10,'bbb' |
| 200,2,1,2,NULL,NULL,10,'aaa',-10,'bbb' |
| 200,3,1,2,NULL,NULL,10,'aaa',-10,'bbb' |
| 200,NULL,1,2,NULL,NULL,10,'aaa',-10,'bbb' |
| 200,NULL,1,2,NULL,NULL,10,'aaa',-10,'bbb' |
| 200,NULL,1,2,NULL,NULL,10,'aaa',-10,'bbb' |
| ---- TYPES |
| BIGINT,INT,INT,INT,INT,INT,INT,STRING,INT,STRING |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates that can be resolved to have different tuple ids. |
| select id, nested_struct.a from functional_parquet.complextypestbl t |
| where id = 100 or nested_struct.a = 1; |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates that can be resolved to have different tuple ids. |
| select id, nested_struct.a from functional_parquet.complextypestbl t |
| where id + nested_struct.a = 101; |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates that can be resolved to have different tuple ids. |
| select id, id2 from ( |
| select id, id as id2 from functional.alltypestiny |
| union all |
| select id, nested_struct.a as id2 from functional_parquet.complextypestbl |
| ) t |
| where id + id2 = 101; |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates that can be resolved to have different tuple ids. |
| with v as ( |
| select id, nested_struct.a as id2 from functional_parquet.complextypestbl |
| ) |
| select id, id2 from v |
| where id + id2 = 101 or (id = 200 and id2 is null); |
| ---- RESULTS |
| 100,1 |
| 200,NULL |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids. |
| select t.id, t.nested_struct.a |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| where tiny.id + t.id + t.nested_struct.a = 201; |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids. |
| select t.id, t.nested_struct.a |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id + t.id + t.nested_struct.a = 201; |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids. Make sure nested columns inside |
| # the column masking view are materialized. |
| select count(1) |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| where (tiny.id + t.id + t.nested_struct.a) is null; |
| ---- RESULTS |
| 5 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids in GroupBy clause. |
| select count(1) |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| group by tiny.id + t.id + t.nested_struct.a; |
| ---- RESULTS |
| 1 |
| 5 |
| 1 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids in Having clause. |
| select count(tiny.id + t.id + t.nested_struct.a) |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| group by t.id |
| having count(tiny.id + t.id + t.nested_struct.a) = 1 |
| ---- RESULTS |
| 1 |
| 1 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids in OrderBy clause. |
| select t.id |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| order by tiny.id + t.id + t.nested_struct.a, t.id; |
| ---- RESULTS: VERIFY_IS_EQUAL |
| 100 |
| 700 |
| 200 |
| 300 |
| 400 |
| 500 |
| 600 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids in analytic query. |
| select t.id, rank() over(order by t.id) |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| where tiny.id + t.id + t.nested_struct.a is null; |
| ---- RESULTS |
| 200,1 |
| 300,2 |
| 400,3 |
| 500,4 |
| 600,5 |
| ---- TYPES |
| BIGINT,BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids. |
| select t.id, t.nested_struct.a |
| from functional.alltypestiny tiny |
| left join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| where tiny.id + t.id + t.nested_struct.a = 201; |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids. |
| select t.id, t.nested_struct.a |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| join functional.alltypestiny tiny2 |
| on t.id = tiny2.id |
| where tiny.id + t.id + t.nested_struct.a + tiny2.id = 301; |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids. |
| select t.id, t.nested_struct.a |
| from functional.alltypestiny tiny |
| left join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| join functional.alltypestiny tiny2 |
| on t.id = tiny2.id |
| where tiny.id + t.id + t.nested_struct.a + tiny2.id = 301; |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-9529: Test predicates with multiple tuple ids. |
| with v as ( |
| select t.id, t.nested_struct.a |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| where tiny.id + t.id + t.nested_struct.a = 201 |
| ) |
| select t.id, t.nested_struct.a from v |
| join functional_parquet.complextypestbl t |
| on v.id = t.id |
| where v.id + t.id + t.nested_struct.a = 201 |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| BIGINT,INT |
| ==== |
| ---- QUERY |
| select count(distinct id), count(distinct nested_struct.a) from functional_parquet.complextypestbl |
| ---- RESULTS |
| 8,3 |
| ---- TYPES |
| BIGINT,BIGINT |
| ==== |
| ---- QUERY |
| select count(distinct id), count(distinct nested_struct.a) from functional_parquet.complextypestbl |
| where id + nested_struct.a = 101; |
| ---- RESULTS |
| 1,1 |
| ---- TYPES |
| BIGINT,BIGINT |
| ==== |
| ---- QUERY |
| select count(distinct tiny.int_col), count(distinct t.id), count(distinct t.nested_struct.a) |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| ---- RESULTS |
| 2,7,2 |
| ---- TYPES |
| BIGINT,BIGINT,BIGINT |
| ==== |
| ---- QUERY |
| select count(distinct tiny.int_col), count(distinct t.id), count(distinct t.nested_struct.a) |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t |
| on tiny.id = t.id |
| where tiny.int_col + t.id + t.nested_struct.a = 102 |
| ---- RESULTS |
| 1,1,1 |
| ---- TYPES |
| BIGINT,BIGINT,BIGINT |
| ==== |
| ---- QUERY |
| select tiny.id, t0.nested_struct.a |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t0 on tiny.id = t0.id |
| join functional_parquet.complextypestbl t1 on tiny.id = t1.id |
| join functional_parquet.complextypestbl t2 on tiny.id = t2.id |
| join functional_parquet.complextypestbl t3 on tiny.id = t3.id |
| where tiny.id + t2.id + t3.nested_struct.a >= 201 |
| ---- RESULTS |
| 100,1 |
| 700,7 |
| ---- TYPES |
| INT,INT |
| ==== |
| ---- QUERY |
| select tiny.id, t0.nested_struct.a |
| from functional.alltypestiny tiny |
| join functional_parquet.complextypestbl t0 on tiny.id = t0.id |
| join functional_parquet.complextypestbl t1 on tiny.id = t1.id |
| join functional_parquet.complextypestbl t2 on tiny.id = t2.id |
| join functional_parquet.complextypestbl t3 on tiny.id = t3.id |
| where (t2.id + t3.id + t3.nested_struct.a = 201 or t2.nested_struct.a is null) |
| and tiny.id + t0.id + t3.nested_struct.a >= 201 |
| ---- RESULTS |
| 100,1 |
| ---- TYPES |
| INT,INT |
| ==== |
