testdata/workloads/functional-query/queries/QueryTest/ranger_column_masking_complex_types.test

====
---- QUERY
# Test queries on complex types table.
select id from complextypestbl
---- RESULTS
100
200
300
400
500
600
700
800
---- TYPES
BIGINT
====
---- QUERY
# Test queries on complex types table.
select * from 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 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 expanding star expression.
select id, nested_struct.* from 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 expanding star expression.
select nested_struct.* from complextypestbl
---- RESULTS
1
NULL
NULL
NULL
NULL
NULL
7
-1
---- TYPES
INT
====
---- QUERY
# Test resolving nested columns in expanding star expression.
set EXPAND_COMPLEX_TYPES=1;
select nested_struct.* from complextypestbl
---- RESULTS
-1,'[-1]','{"d":[[{"e":-1,"f":"nonnullable"}]]}','{}'
1,'[1]','{"d":[[{"e":10,"f":"aaa"},{"e":-10,"f":"bbb"}],[{"e":11,"f":"c"}]]}','{"foo":{"h":{"i":[1.1]}}}'
NULL,'[null]','{"d":[[{"e":null,"f":null},{"e":10,"f":"aaa"},{"e":null,"f":null},{"e":-10,"f":"bbb"},{"e":null,"f":null}],[{"e":11,"f":"c"},null],[],null]}','{"g1":{"h":{"i":[2.2,null]}},"g2":{"h":{"i":[]}},"g3":null,"g4":{"h":{"i":null}},"g5":{"h":null}}'
NULL,'NULL','{"d":[]}','{}'
NULL,'NULL','{"d":null}','NULL'
NULL,'NULL','NULL','{"foo":{"h":{"i":[2.2,3.3]}}}'
NULL,'NULL','NULL','NULL'
7,'[2,3,null]','{"d":[[],[null],null]}','NULL'
---- TYPES
INT,STRING,STRING,STRING
====
---- QUERY
# Test resolving explicit STAR path on a nested struct column inside array
select id, nested_arr.item.*
from complextypestbl t,
  t.nested_struct.c.d arr,
  arr.item nested_arr;
---- RESULTS
100,10,'aaa'
100,-10,'bbb'
100,11,'c'
200,NULL,'NULL'
200,10,'aaa'
200,NULL,'NULL'
200,-10,'bbb'
200,NULL,'NULL'
200,11,'c'
200,NULL,'NULL'
700,NULL,'NULL'
800,-1,'nonnullable'
---- TYPES
BIGINT,INT,STRING
====
---- QUERY
# Test resolving explicit STAR path on a nested struct column inside array
select nested_arr.item.*
from complextypestbl t,
  t.nested_struct.c.d arr,
  arr.item nested_arr;
---- RESULTS
10,'aaa'
-10,'bbb'
11,'c'
NULL,'NULL'
10,'aaa'
NULL,'NULL'
-10,'bbb'
NULL,'NULL'
11,'c'
NULL,'NULL'
NULL,'NULL'
-1,'nonnullable'
---- TYPES
INT,STRING
====
---- QUERY
# Test resolving implicit STAR path on a nested struct column inside array
select id, nested_arr.*
from complextypestbl t,
  t.nested_struct.c.d arr,
  arr.item nested_arr;
---- RESULTS
100,10,'aaa'
100,-10,'bbb'
100,11,'c'
200,NULL,'NULL'
200,10,'aaa'
200,NULL,'NULL'
200,-10,'bbb'
200,NULL,'NULL'
200,11,'c'
200,NULL,'NULL'
700,NULL,'NULL'
800,-1,'nonnullable'
---- TYPES
BIGINT,INT,STRING
====
---- QUERY
# Test resolving explicit STAR path on a nested struct column inside array
select nested_arr.*
from complextypestbl t,
  t.nested_struct.c.d arr,
  arr.item nested_arr;
---- RESULTS
10,'aaa'
-10,'bbb'
11,'c'
NULL,'NULL'
10,'aaa'
NULL,'NULL'
-10,'bbb'
NULL,'NULL'
11,'c'
NULL,'NULL'
NULL,'NULL'
-1,'nonnullable'
---- TYPES
INT,STRING
====
---- QUERY
# Test resolving nested column in function.
select count(id), count(nested_struct.a) from complextypestbl
---- RESULTS
8,3
---- TYPES
BIGINT,BIGINT
====
---- QUERY
# Test predicates on masked columns and nested columns.
select id, nested_struct.a from 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 complextypestbl
---- RESULTS
8,3
---- TYPES
BIGINT,BIGINT
====
---- QUERY
select count(distinct id), count(distinct nested_struct.a) from 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 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 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 complextypestbl t0 on tiny.id = t0.id
  join complextypestbl t1 on tiny.id = t1.id
  join complextypestbl t2 on tiny.id = t2.id
  join 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 complextypestbl t0 on tiny.id = t0.id
  join complextypestbl t1 on tiny.id = t1.id
  join complextypestbl t2 on tiny.id = t2.id
  join 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
====
---- QUERY
select id, a.item from complextypes_arrays_only_view v, v.int_array a;
---- RESULTS
100,1
100,2
100,3
200,NULL
200,1
200,2
200,NULL
200,3
200,NULL
800,-1
---- TYPES
BIGINT,INT
====
---- QUERY
select id, a.key, a.value from complextypes_maps_view v, v.int_map a;
---- 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
====