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