Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
3.1.0, 3.1.1
-
None
-
None
Description
Repro Test :
0: jdbc:hive2://c1162-node2.squadron-labs.com> drop schema if exists bug_test cascade;
INFO : Compiling command(queryId=hive_20190507080927_7785e20c-edf0-42e7-85a8-e355a7f570fa): drop schema if exists bug_test cascade
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling command(queryId=hive_20190507080927_7785e20c-edf0-42e7-85a8-e355a7f570fa); Time taken: 0.239 seconds
INFO : Executing command(queryId=hive_20190507080927_7785e20c-edf0-42e7-85a8-e355a7f570fa): drop schema if exists bug_test cascade
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20190507080927_7785e20c-edf0-42e7-85a8-e355a7f570fa); Time taken: 0.725 seconds
INFO : OK
No rows affected (1.27 seconds)
0: jdbc:hive2://c1162-node2.squadron-labs.com> create schema bug_test;
INFO : Compiling command(queryId=hive_20190507080946_282992b9-cca8-4d9c-a69e-3d4591f69d71): create schema bug_test
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling command(queryId=hive_20190507080946_282992b9-cca8-4d9c-a69e-3d4591f69d71); Time taken: 0.023 seconds
INFO : Executing command(queryId=hive_20190507080946_282992b9-cca8-4d9c-a69e-3d4591f69d71): create schema bug_test
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20190507080946_282992b9-cca8-4d9c-a69e-3d4591f69d71); Time taken: 0.054 seconds
INFO : OK
No rows affected (0.1 seconds)
0: jdbc:hive2://c1162-node2.squadron-labs.com>
0: jdbc:hive2://c1162-node2.squadron-labs.com> create table if not exists bug_test.fct_z_tree
. . . . . . . . . . . . . . . . . . . . . . .> ( wh_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_valid_from timestamp
. . . . . . . . . . . . . . . . . . . . . . .> , wh_valid_to timestamp
. . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a1_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a1_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a2_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a2_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a3_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a3_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , root_parent boolean
. . . . . . . . . . . . . . . . . . . . . . .> , is_child boolean
. . . . . . . . . . . . . . . . . . . . . . .> , is_parent boolean
. . . . . . . . . . . . . . . . . . . . . . .> , structure_name string
. . . . . . . . . . . . . . . . . . . . . . .> , wh_data_source_entity_name string
. . . . . . . . . . . . . . . . . . . . . . .> , wh_data_source_table_name string
. . . . . . . . . . . . . . . . . . . . . . .> , wh_data_source_file_date timestamp
. . . . . . . . . . . . . . . . . . . . . . .> , wh_data_source_provenance string
. . . . . . . . . . . . . . . . . . . . . . .> , wh_hk string
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> partitioned by (wh_partition_date date)
. . . . . . . . . . . . . . . . . . . . . . .> clustered by (wh_sk) into 1 buckets
. . . . . . . . . . . . . . . . . . . . . . .> stored as orc
. . . . . . . . . . . . . . . . . . . . . . .> tblproperties ('orc.bloom.filter.columns'='wh_sk, wh_hk', 'transactional'='true');
INFO : Compiling command(queryId=hive_20190507080948_1220b4ff-d5f5-4317-950f-8e659699dc23): create table if not exists bug_test.fct_z_tree ( wh_sk bigint , wh_valid_from timestamp , wh_valid_to timestamp , wh_child_dim_a1_sk bigint , wh_parent_dim_a1_sk bigint , wh_child_dim_a2_sk bigint , wh_parent_dim_a2_sk bigint , wh_child_dim_a3_sk bigint , wh_parent_dim_a3_sk bigint , root_parent boolean , is_child boolean , is_parent boolean , structure_name string , wh_data_source_entity_name string , wh_data_source_table_name string , wh_data_source_file_date timestamp , wh_data_source_provenance string , wh_hk string ) partitioned by (wh_partition_date date) clustered by (wh_sk) into 1 buckets stored as orc tblproperties ('orc.bloom.filter.columns'='wh_sk, wh_hk', 'transactional'='true')
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling command(queryId=hive_20190507080948_1220b4ff-d5f5-4317-950f-8e659699dc23); Time taken: 0.132 seconds
INFO : Executing command(queryId=hive_20190507080948_1220b4ff-d5f5-4317-950f-8e659699dc23): create table if not exists bug_test.fct_z_tree
( wh_sk bigint
, wh_valid_from timestamp
, wh_valid_to timestamp
, wh_child_dim_a1_sk bigint
, wh_parent_dim_a1_sk bigint
, wh_child_dim_a2_sk bigint
, wh_parent_dim_a2_sk bigint
, wh_child_dim_a3_sk bigint
, wh_parent_dim_a3_sk bigint
, root_parent boolean
, is_child boolean
, is_parent boolean
, structure_name string
, wh_data_source_entity_name string
, wh_data_source_table_name string
, wh_data_source_file_date timestamp
, wh_data_source_provenance string
, wh_hk string
)
partitioned by (wh_partition_date date)
clustered by (wh_sk) into 1 buckets
stored as orc
tblproperties ('orc.bloom.filter.columns'='wh_sk, wh_hk', 'transactional'='true')
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20190507080948_1220b4ff-d5f5-4317-950f-8e659699dc23); Time taken: 0.129 seconds
INFO : OK
No rows affected (0.324 seconds)
0: jdbc:hive2://c1162-node2.squadron-labs.com> create or replace view bug_test.vir_z_tree_compo as
. . . . . . . . . . . . . . . . . . . . . . .> with tree as
. . . . . . . . . . . . . . . . . . . . . . .> ( select case when structure_name = 'S1' then wh_parent_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> when structure_name = 'S2' then wh_parent_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> when structure_name = 'S3' then wh_parent_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> end as parent_sk
. . . . . . . . . . . . . . . . . . . . . . .> , case when structure_name = 'S1' then wh_child_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> when structure_name = 'S2' then wh_child_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> when structure_name = 'S3' then wh_child_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> end as child_sk
. . . . . . . . . . . . . . . . . . . . . . .> , structure_name
. . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> from bug_test.fct_z_tree
. . . . . . . . . . . . . . . . . . . . . . .> where wh_partition_date = '2099-12-31'
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> , root as
. . . . . . . . . . . . . . . . . . . . . . .> ( select parent_sk
. . . . . . . . . . . . . . . . . . . . . . .> , child_sk
. . . . . . . . . . . . . . . . . . . . . . .> , structure_name
. . . . . . . . . . . . . . . . . . . . . . .> , true include_children
. . . . . . . . . . . . . . . . . . . . . . .> from tree
. . . . . . . . . . . . . . . . . . . . . . .> where parent_sk = child_sk
. . . . . . . . . . . . . . . . . . . . . . .> union all
. . . . . . . . . . . . . . . . . . . . . . .> select parent_sk
. . . . . . . . . . . . . . . . . . . . . . .> , child_sk
. . . . . . . . . . . . . . . . . . . . . . .> , structure_name
. . . . . . . . . . . . . . . . . . . . . . .> , false include_children
. . . . . . . . . . . . . . . . . . . . . . .> from tree
. . . . . . . . . . . . . . . . . . . . . . .> where parent_sk = child_sk
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> , node as
. . . . . . . . . . . . . . . . . . . . . . .> ( select parent_sk
. . . . . . . . . . . . . . . . . . . . . . .> , child_sk
. . . . . . . . . . . . . . . . . . . . . . .> , structure_name
. . . . . . . . . . . . . . . . . . . . . . .> , true include_children
. . . . . . . . . . . . . . . . . . . . . . .> from tree
. . . . . . . . . . . . . . . . . . . . . . .> where parent_sk != child_sk
. . . . . . . . . . . . . . . . . . . . . . .> union all
. . . . . . . . . . . . . . . . . . . . . . .> select parent_sk
. . . . . . . . . . . . . . . . . . . . . . .> , child_sk
. . . . . . . . . . . . . . . . . . . . . . .> , structure_name
. . . . . . . . . . . . . . . . . . . . . . .> , false include_children
. . . . . . . . . . . . . . . . . . . . . . .> from tree
. . . . . . . . . . . . . . . . . . . . . . .> where parent_sk != child_sk
. . . . . . . . . . . . . . . . . . . . . . .> and concat(child_sk,structure_name) in (select concat(x.parent_sk,x.structure_name) from tree x where x.parent_sk != x.child_sk)
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> , tree_path as
. . . . . . . . . . . . . . . . . . . . . . .> ( select coalesce
. . . . . . . . . . . . . . . . . . . . . . .> ( case when l15.child_sk is not null then 15 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l14.child_sk is not null then 14 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l13.child_sk is not null then 13 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l12.child_sk is not null then 12 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l11.child_sk is not null then 11 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l10.child_sk is not null then 10 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l9.child_sk is not null then 9 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l8.child_sk is not null then 8 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l7.child_sk is not null then 7 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l6.child_sk is not null then 6 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l5.child_sk is not null then 5 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l4.child_sk is not null then 4 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l3.child_sk is not null then 3 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l2.child_sk is not null then 2 end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l1.child_sk is not null then 1 end
. . . . . . . . . . . . . . . . . . . . . . .> ) as lvl
. . . . . . . . . . . . . . . . . . . . . . .> , coalesce( l15.parent_sk, l14.parent_sk, l13.parent_sk, l12.parent_sk, l11.parent_sk, l10.parent_sk, l9.parent_sk, l8.parent_sk, l7.parent_sk, l6.parent_sk, l5.parent_sk, l4.parent_sk, l3.parent_sk, l2.parent_sk, l1.parent_sk) as parent_sk
. . . . . . . . . . . . . . . . . . . . . . .> , coalesce( l15.child_sk, l14.child_sk, l13.child_sk, l12.child_sk, l11.child_sk, l10.child_sk, l9.child_sk, l8.child_sk, l7.child_sk, l6.child_sk, l5.child_sk, l4.child_sk, l3.child_sk, l2.child_sk, l1.child_sk) as child_sk
. . . . . . . . . . . . . . . . . . . . . . .> , split(concat
. . . . . . . . . . . . . . . . . . . . . . .> ( l1.child_sk
. . . . . . . . . . . . . . . . . . . . . . .> , case when l2.child_sk is not null then concat(',',l2.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l3.child_sk is not null then concat(',',l3.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l4.child_sk is not null then concat(',',l4.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l5.child_sk is not null then concat(',',l5.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l6.child_sk is not null then concat(',',l6.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l7.child_sk is not null then concat(',',l7.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l8.child_sk is not null then concat(',',l8.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l9.child_sk is not null then concat(',',l9.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l10.child_sk is not null then concat(',',l10.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l11.child_sk is not null then concat(',',l11.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l12.child_sk is not null then concat(',',l12.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l13.child_sk is not null then concat(',',l13.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l14.child_sk is not null then concat(',',l14.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> , case when l15.child_sk is not null then concat(',',l15.child_sk) else '' end
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> ,',') the_path
. . . . . . . . . . . . . . . . . . . . . . .> , l1.structure_name
. . . . . . . . . . . . . . . . . . . . . . .> from root l1
. . . . . . . . . . . . . . . . . . . . . . .> left join node l2 on l2.parent_sk = l1.child_sk and l2.structure_name = l1.structure_name and l1.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l3 on l3.parent_sk = l2.child_sk and l3.structure_name = l2.structure_name and l2.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l4 on l4.parent_sk = l3.child_sk and l4.structure_name = l3.structure_name and l3.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l5 on l5.parent_sk = l4.child_sk and l5.structure_name = l4.structure_name and l4.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l6 on l6.parent_sk = l5.child_sk and l6.structure_name = l5.structure_name and l5.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l7 on l7.parent_sk = l6.child_sk and l7.structure_name = l6.structure_name and l6.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l8 on l8.parent_sk = l7.child_sk and l8.structure_name = l7.structure_name and l7.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l9 on l9.parent_sk = l8.child_sk and l9.structure_name = l8.structure_name and l8.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l10 on l10.parent_sk = l9.child_sk and l10.structure_name = l9.structure_name and l9.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l11 on l11.parent_sk = l10.child_sk and l11.structure_name = l10.structure_name and l10.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l12 on l12.parent_sk = l11.child_sk and l12.structure_name = l11.structure_name and l11.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l13 on l13.parent_sk = l12.child_sk and l13.structure_name = l12.structure_name and l12.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l14 on l14.parent_sk = l13.child_sk and l14.structure_name = l13.structure_name and l13.include_children
. . . . . . . . . . . . . . . . . . . . . . .> left join node l15 on l15.parent_sk = l14.child_sk and l15.structure_name = l14.structure_name and l14.include_children
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> , expanded_tree as
. . . . . . . . . . . . . . . . . . . . . . .> ( select a.lvl
. . . . . . . . . . . . . . . . . . . . . . .> , b.root_sk
. . . . . . . . . . . . . . . . . . . . . . .> , a.structure_name
. . . . . . . . . . . . . . . . . . . . . . .> , a.wh_child_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> , a.wh_parent_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> , a.wh_child_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> , a.wh_parent_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> , a.wh_child_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> , a.wh_parent_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> from ( select t.wh_child_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> , t.wh_parent_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> , t.wh_child_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> , t.wh_parent_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> , t.wh_child_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> , t.wh_parent_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> , t.structure_name
. . . . . . . . . . . . . . . . . . . . . . .> , p.lvl
. . . . . . . . . . . . . . . . . . . . . . .> , p.the_path
. . . . . . . . . . . . . . . . . . . . . . .> from tree t
. . . . . . . . . . . . . . . . . . . . . . .> join tree_path p
. . . . . . . . . . . . . . . . . . . . . . .> on t.parent_sk = p.parent_sk
. . . . . . . . . . . . . . . . . . . . . . .> and t.child_sk = p.child_sk
. . . . . . . . . . . . . . . . . . . . . . .> and t.structure_name = p.structure_name
. . . . . . . . . . . . . . . . . . . . . . .> ) a
. . . . . . . . . . . . . . . . . . . . . . .> lateral view explode(the_path) b as root_sk
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> select et.structure_name
. . . . . . . . . . . . . . . . . . . . . . .> , et.lvl
. . . . . . . . . . . . . . . . . . . . . . .> , t.wh_child_dim_a1_sk as wh_root_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> , et.wh_parent_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> , et.wh_child_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> , t.wh_child_dim_a2_sk as wh_root_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> , et.wh_parent_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> , et.wh_child_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> , t.wh_child_dim_a3_sk as wh_root_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> , et.wh_parent_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> , et.wh_child_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> from expanded_tree et
. . . . . . . . . . . . . . . . . . . . . . .> join tree t
. . . . . . . . . . . . . . . . . . . . . . .> on et.root_sk = t.child_sk
. . . . . . . . . . . . . . . . . . . . . . .> and et.structure_name = t.structure_name
. . . . . . . . . . . . . . . . . . . . . . .> order by et.structure_name
. . . . . . . . . . . . . . . . . . . . . . .> , case when structure_name = 'S1' then wh_root_dim_a1_sk
. . . . . . . . . . . . . . . . . . . . . . .> when structure_name = 'S2' then wh_root_dim_a2_sk
. . . . . . . . . . . . . . . . . . . . . . .> when structure_name = 'S3' then wh_root_dim_a3_sk
. . . . . . . . . . . . . . . . . . . . . . .> end;
INFO : Compiling command(queryId=hive_20190507081013_87842815-d223-4f44-aeb4-97c7774b28ef): create or replace view bug_test.vir_z_tree_compo as with tree as ( select case when structure_name = 'S1' then wh_parent_dim_a1_sk when structure_name = 'S2' then wh_parent_dim_a2_sk when structure_name = 'S3' then wh_parent_dim_a3_sk end as parent_sk , case when structure_name = 'S1' then wh_child_dim_a1_sk when structure_name = 'S2' then wh_child_dim_a2_sk when structure_name = 'S3' then wh_child_dim_a3_sk end as child_sk , structure_name , wh_child_dim_a1_sk , wh_parent_dim_a1_sk , wh_child_dim_a2_sk , wh_parent_dim_a2_sk , wh_child_dim_a3_sk , wh_parent_dim_a3_sk from bug_test.fct_z_tree where wh_partition_date = '2099-12-31' ) , root as ( select parent_sk , child_sk , structure_name , true include_children from tree where parent_sk = child_sk union all select parent_sk , child_sk , structure_name , false include_children from tree where parent_sk = child_sk ) , node as ( select parent_sk , child_sk , structure_name , true include_children from tree where parent_sk != child_sk union all select parent_sk , child_sk , structure_name , false include_children from tree where parent_sk != child_sk and concat(child_sk,structure_name) in (select concat(x.parent_sk,x.structure_name) from tree x where x.parent_sk != x.child_sk) ) , tree_path as ( select coalesce ( case when l15.child_sk is not null then 15 end , case when l14.child_sk is not null then 14 end , case when l13.child_sk is not null then 13 end , case when l12.child_sk is not null then 12 end , case when l11.child_sk is not null then 11 end , case when l10.child_sk is not null then 10 end , case when l9.child_sk is not null then 9 end , case when l8.child_sk is not null then 8 end , case when l7.child_sk is not null then 7 end , case when l6.child_sk is not null then 6 end , case when l5.child_sk is not null then 5 end , case when l4.child_sk is not null then 4 end , case when l3.child_sk is not null then 3 end , case when l2.child_sk is not null then 2 end , case when l1.child_sk is not null then 1 end ) as lvl , coalesce( l15.parent_sk, l14.parent_sk, l13.parent_sk, l12.parent_sk, l11.parent_sk, l10.parent_sk, l9.parent_sk, l8.parent_sk, l7.parent_sk, l6.parent_sk, l5.parent_sk, l4.parent_sk, l3.parent_sk, l2.parent_sk, l1.parent_sk) as parent_sk , coalesce( l15.child_sk, l14.child_sk, l13.child_sk, l12.child_sk, l11.child_sk, l10.child_sk, l9.child_sk, l8.child_sk, l7.child_sk, l6.child_sk, l5.child_sk, l4.child_sk, l3.child_sk, l2.child_sk, l1.child_sk) as child_sk , split(concat ( l1.child_sk , case when l2.child_sk is not null then concat(',',l2.child_sk) else '' end , case when l3.child_sk is not null then concat(',',l3.child_sk) else '' end , case when l4.child_sk is not null then concat(',',l4.child_sk) else '' end , case when l5.child_sk is not null then concat(',',l5.child_sk) else '' end , case when l6.child_sk is not null then concat(',',l6.child_sk) else '' end , case when l7.child_sk is not null then concat(',',l7.child_sk) else '' end , case when l8.child_sk is not null then concat(',',l8.child_sk) else '' end , case when l9.child_sk is not null then concat(',',l9.child_sk) else '' end , case when l10.child_sk is not null then concat(',',l10.child_sk) else '' end , case when l11.child_sk is not null then concat(',',l11.child_sk) else '' end , case when l12.child_sk is not null then concat(',',l12.child_sk) else '' end , case when l13.child_sk is not null then concat(',',l13.child_sk) else '' end , case when l14.child_sk is not null then concat(',',l14.child_sk) else '' end , case when l15.child_sk is not null then concat(',',l15.child_sk) else '' end ) ,',') the_path , l1.structure_name from root l1 left join node l2 on l2.parent_sk = l1.child_sk and l2.structure_name = l1.structure_name and l1.include_children left join node l3 on l3.parent_sk = l2.child_sk and l3.structure_name = l2.structure_name and l2.include_children left join node l4 on l4.parent_sk = l3.child_sk and l4.structure_name = l3.structure_name and l3.include_children left join node l5 on l5.parent_sk = l4.child_sk and l5.structure_name = l4.structure_name and l4.include_children left join node l6 on l6.parent_sk = l5.child_sk and l6.structure_name = l5.structure_name and l5.include_children left join node l7 on l7.parent_sk = l6.child_sk and l7.structure_name = l6.structure_name and l6.include_children left join node l8 on l8.parent_sk = l7.child_sk and l8.structure_name = l7.structure_name and l7.include_children left join node l9 on l9.parent_sk = l8.child_sk and l9.structure_name = l8.structure_name and l8.include_children left join node l10 on l10.parent_sk = l9.child_sk and l10.structure_name = l9.structure_name and l9.include_children left join node l11 on l11.parent_sk = l10.child_sk and l11.structure_name = l10.structure_name and l10.include_children left join node l12 on l12.parent_sk = l11.child_sk and l12.structure_name = l11.structure_name and l11.include_children left join node l13 on l13.parent_sk = l12.child_sk and l13.structure_name = l12.structure_name and l12.include_children left join node l14 on l14.parent_sk = l13.child_sk and l14.structure_name = l13.structure_name and l13.include_children left join node l15 on l15.parent_sk = l14.child_sk and l15.structure_name = l14.structure_name and l14.include_children ) , expanded_tree as ( select a.lvl , b.root_sk , a.structure_name , a.wh_child_dim_a1_sk , a.wh_parent_dim_a1_sk , a.wh_child_dim_a2_sk , a.wh_parent_dim_a2_sk , a.wh_child_dim_a3_sk , a.wh_parent_dim_a3_sk from ( select t.wh_child_dim_a1_sk , t.wh_parent_dim_a1_sk , t.wh_child_dim_a2_sk , t.wh_parent_dim_a2_sk , t.wh_child_dim_a3_sk , t.wh_parent_dim_a3_sk , t.structure_name , p.lvl , p.the_path from tree t join tree_path p on t.parent_sk = p.parent_sk and t.child_sk = p.child_sk and t.structure_name = p.structure_name ) a lateral view explode(the_path) b as root_sk ) select et.structure_name , et.lvl , t.wh_child_dim_a1_sk as wh_root_dim_a1_sk , et.wh_parent_dim_a1_sk , et.wh_child_dim_a1_sk , t.wh_child_dim_a2_sk as wh_root_dim_a2_sk , et.wh_parent_dim_a2_sk , et.wh_child_dim_a2_sk , t.wh_child_dim_a3_sk as wh_root_dim_a3_sk , et.wh_parent_dim_a3_sk , et.wh_child_dim_a3_sk from expanded_tree et join tree t on et.root_sk = t.child_sk and et.structure_name = t.structure_name order by et.structure_name , case when structure_name = 'S1' then wh_root_dim_a1_sk when structure_name = 'S2' then wh_root_dim_a2_sk when structure_name = 'S3' then wh_root_dim_a3_sk end
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:structure_name, type:string, comment:null), FieldSchema(name:lvl, type:int, comment:null), FieldSchema(name:wh_root_dim_a1_sk, type:bigint, comment:null), FieldSchema(name:wh_parent_dim_a1_sk, type:bigint, comment:null), FieldSchema(name:wh_child_dim_a1_sk, type:bigint, comment:null), FieldSchema(name:wh_root_dim_a2_sk, type:bigint, comment:null), FieldSchema(name:wh_parent_dim_a2_sk, type:bigint, comment:null), FieldSchema(name:wh_child_dim_a2_sk, type:bigint, comment:null), FieldSchema(name:wh_root_dim_a3_sk, type:bigint, comment:null), FieldSchema(name:wh_parent_dim_a3_sk, type:bigint, comment:null), FieldSchema(name:wh_child_dim_a3_sk, type:bigint, comment:null)], properties:null)
INFO : Completed compiling command(queryId=hive_20190507081013_87842815-d223-4f44-aeb4-97c7774b28ef); Time taken: 0.843 seconds
INFO : Executing command(queryId=hive_20190507081013_87842815-d223-4f44-aeb4-97c7774b28ef): create or replace view bug_test.vir_z_tree_compo as
with tree as
( select case when structure_name = 'S1' then wh_parent_dim_a1_sk
when structure_name = 'S2' then wh_parent_dim_a2_sk
when structure_name = 'S3' then wh_parent_dim_a3_sk
end as parent_sk
, case when structure_name = 'S1' then wh_child_dim_a1_sk
when structure_name = 'S2' then wh_child_dim_a2_sk
when structure_name = 'S3' then wh_child_dim_a3_sk
end as child_sk
, structure_name
, wh_child_dim_a1_sk
, wh_parent_dim_a1_sk
, wh_child_dim_a2_sk
, wh_parent_dim_a2_sk
, wh_child_dim_a3_sk
, wh_parent_dim_a3_sk
from bug_test.fct_z_tree
where wh_partition_date = '2099-12-31'
)
, root as
( select parent_sk
, child_sk
, structure_name
, true include_children
from tree
where parent_sk = child_sk
union all
select parent_sk
, child_sk
, structure_name
, false include_children
from tree
where parent_sk = child_sk
)
, node as
( select parent_sk
, child_sk
, structure_name
, true include_children
from tree
where parent_sk != child_sk
union all
select parent_sk
, child_sk
, structure_name
, false include_children
from tree
where parent_sk != child_sk
and concat(child_sk,structure_name) in (select concat(x.parent_sk,x.structure_name) from tree x where x.parent_sk != x.child_sk)
)
, tree_path as
( select coalesce
( case when l15.child_sk is not null then 15 end
, case when l14.child_sk is not null then 14 end
, case when l13.child_sk is not null then 13 end
, case when l12.child_sk is not null then 12 end
, case when l11.child_sk is not null then 11 end
, case when l10.child_sk is not null then 10 end
, case when l9.child_sk is not null then 9 end
, case when l8.child_sk is not null then 8 end
, case when l7.child_sk is not null then 7 end
, case when l6.child_sk is not null then 6 end
, case when l5.child_sk is not null then 5 end
, case when l4.child_sk is not null then 4 end
, case when l3.child_sk is not null then 3 end
, case when l2.child_sk is not null then 2 end
, case when l1.child_sk is not null then 1 end
) as lvl
, coalesce( l15.parent_sk, l14.parent_sk, l13.parent_sk, l12.parent_sk, l11.parent_sk, l10.parent_sk, l9.parent_sk, l8.parent_sk, l7.parent_sk, l6.parent_sk, l5.parent_sk, l4.parent_sk, l3.parent_sk, l2.parent_sk, l1.parent_sk) as parent_sk
, coalesce( l15.child_sk, l14.child_sk, l13.child_sk, l12.child_sk, l11.child_sk, l10.child_sk, l9.child_sk, l8.child_sk, l7.child_sk, l6.child_sk, l5.child_sk, l4.child_sk, l3.child_sk, l2.child_sk, l1.child_sk) as child_sk
, split(concat
( l1.child_sk
, case when l2.child_sk is not null then concat(',',l2.child_sk) else '' end
, case when l3.child_sk is not null then concat(',',l3.child_sk) else '' end
, case when l4.child_sk is not null then concat(',',l4.child_sk) else '' end
, case when l5.child_sk is not null then concat(',',l5.child_sk) else '' end
, case when l6.child_sk is not null then concat(',',l6.child_sk) else '' end
, case when l7.child_sk is not null then concat(',',l7.child_sk) else '' end
, case when l8.child_sk is not null then concat(',',l8.child_sk) else '' end
, case when l9.child_sk is not null then concat(',',l9.child_sk) else '' end
, case when l10.child_sk is not null then concat(',',l10.child_sk) else '' end
, case when l11.child_sk is not null then concat(',',l11.child_sk) else '' end
, case when l12.child_sk is not null then concat(',',l12.child_sk) else '' end
, case when l13.child_sk is not null then concat(',',l13.child_sk) else '' end
, case when l14.child_sk is not null then concat(',',l14.child_sk) else '' end
, case when l15.child_sk is not null then concat(',',l15.child_sk) else '' end
)
,',') the_path
, l1.structure_name
from root l1
left join node l2 on l2.parent_sk = l1.child_sk and l2.structure_name = l1.structure_name and l1.include_children
left join node l3 on l3.parent_sk = l2.child_sk and l3.structure_name = l2.structure_name and l2.include_children
left join node l4 on l4.parent_sk = l3.child_sk and l4.structure_name = l3.structure_name and l3.include_children
left join node l5 on l5.parent_sk = l4.child_sk and l5.structure_name = l4.structure_name and l4.include_children
left join node l6 on l6.parent_sk = l5.child_sk and l6.structure_name = l5.structure_name and l5.include_children
left join node l7 on l7.parent_sk = l6.child_sk and l7.structure_name = l6.structure_name and l6.include_children
left join node l8 on l8.parent_sk = l7.child_sk and l8.structure_name = l7.structure_name and l7.include_children
left join node l9 on l9.parent_sk = l8.child_sk and l9.structure_name = l8.structure_name and l8.include_children
left join node l10 on l10.parent_sk = l9.child_sk and l10.structure_name = l9.structure_name and l9.include_children
left join node l11 on l11.parent_sk = l10.child_sk and l11.structure_name = l10.structure_name and l10.include_children
left join node l12 on l12.parent_sk = l11.child_sk and l12.structure_name = l11.structure_name and l11.include_children
left join node l13 on l13.parent_sk = l12.child_sk and l13.structure_name = l12.structure_name and l12.include_children
left join node l14 on l14.parent_sk = l13.child_sk and l14.structure_name = l13.structure_name and l13.include_children
left join node l15 on l15.parent_sk = l14.child_sk and l15.structure_name = l14.structure_name and l14.include_children
)
, expanded_tree as
( select a.lvl
, b.root_sk
, a.structure_name
, a.wh_child_dim_a1_sk
, a.wh_parent_dim_a1_sk
, a.wh_child_dim_a2_sk
, a.wh_parent_dim_a2_sk
, a.wh_child_dim_a3_sk
, a.wh_parent_dim_a3_sk
from ( select t.wh_child_dim_a1_sk
, t.wh_parent_dim_a1_sk
, t.wh_child_dim_a2_sk
, t.wh_parent_dim_a2_sk
, t.wh_child_dim_a3_sk
, t.wh_parent_dim_a3_sk
, t.structure_name
, p.lvl
, p.the_path
from tree t
join tree_path p
on t.parent_sk = p.parent_sk
and t.child_sk = p.child_sk
and t.structure_name = p.structure_name
) a
lateral view explode(the_path) b as root_sk
)
select et.structure_name
, et.lvl
, t.wh_child_dim_a1_sk as wh_root_dim_a1_sk
, et.wh_parent_dim_a1_sk
, et.wh_child_dim_a1_sk
, t.wh_child_dim_a2_sk as wh_root_dim_a2_sk
, et.wh_parent_dim_a2_sk
, et.wh_child_dim_a2_sk
, t.wh_child_dim_a3_sk as wh_root_dim_a3_sk
, et.wh_parent_dim_a3_sk
, et.wh_child_dim_a3_sk
from expanded_tree et
join tree t
on et.root_sk = t.child_sk
and et.structure_name = t.structure_name
order by et.structure_name
, case when structure_name = 'S1' then wh_root_dim_a1_sk
when structure_name = 'S2' then wh_root_dim_a2_sk
when structure_name = 'S3' then wh_root_dim_a3_sk
end
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20190507081013_87842815-d223-4f44-aeb4-97c7774b28ef); Time taken: 0.039 seconds
INFO : OK
No rows affected (0.925 seconds)
0: jdbc:hive2://c1162-node2.squadron-labs.com> create table if not exists bug_test.app_z_tree_compo
. . . . . . . . . . . . . . . . . . . . . . .> ( structure_name string
. . . . . . . . . . . . . . . . . . . . . . .> , lvl int
. . . . . . . . . . . . . . . . . . . . . . .> , wh_root_dim_a1_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a1_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a1_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_root_dim_a2_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a2_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a2_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_root_dim_a3_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a3_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a3_sk bigint
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> stored as orc
. . . . . . . . . . . . . . . . . . . . . . .> tblproperties ('orc.bloom.filter.columns'='structure_name,wh_root_dim_a1_sk');
INFO : Compiling command(queryId=hive_20190507081043_71e41c54-d52a-4c89-8cb5-154a898c457d): create table if not exists bug_test.app_z_tree_compo ( structure_name string , lvl int , wh_root_dim_a1_sk bigint , wh_parent_dim_a1_sk bigint , wh_child_dim_a1_sk bigint , wh_root_dim_a2_sk bigint , wh_parent_dim_a2_sk bigint , wh_child_dim_a2_sk bigint , wh_root_dim_a3_sk bigint , wh_parent_dim_a3_sk bigint , wh_child_dim_a3_sk bigint ) stored as orc tblproperties ('orc.bloom.filter.columns'='structure_name,wh_root_dim_a1_sk')
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling command(queryId=hive_20190507081043_71e41c54-d52a-4c89-8cb5-154a898c457d); Time taken: 0.042 seconds
INFO : Executing command(queryId=hive_20190507081043_71e41c54-d52a-4c89-8cb5-154a898c457d): create table if not exists bug_test.app_z_tree_compo
( structure_name string
, lvl int
, wh_root_dim_a1_sk bigint
, wh_parent_dim_a1_sk bigint
, wh_child_dim_a1_sk bigint
, wh_root_dim_a2_sk bigint
, wh_parent_dim_a2_sk bigint
, wh_child_dim_a2_sk bigint
, wh_root_dim_a3_sk bigint
, wh_parent_dim_a3_sk bigint
, wh_child_dim_a3_sk bigint
)
stored as orc
tblproperties ('orc.bloom.filter.columns'='structure_name,wh_root_dim_a1_sk')
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20190507081043_71e41c54-d52a-4c89-8cb5-154a898c457d); Time taken: 0.129 seconds
INFO : OK
No rows affected (0.206 seconds)
0: jdbc:hive2://c1162-node2.squadron-labs.com> insert into bug_test.app_z_tree_compo . . . . . . . . . . . . . . . . . . . . . . .> select structure_name . . . . . . . . . . . . . . . . . . . . . . .> , lvl . . . . . . . . . . . . . . . . . . . . . . .> , wh_root_dim_a1_sk . . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a1_sk . . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a1_sk . . . . . . . . . . . . . . . . . . . . . . .> , wh_root_dim_a2_sk . . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a2_sk . . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a2_sk . . . . . . . . . . . . . . . . . . . . . . .> , wh_root_dim_a3_sk . . . . . . . . . . . . . . . . . . . . . . .> , wh_parent_dim_a3_sk . . . . . . . . . . . . . . . . . . . . . . .> , wh_child_dim_a3_sk . . . . . . . . . . . . . . . . . . . . . . .> from bug_test.vir_z_tree_compo; Error: Error while compiling statement: FAILED: SemanticException Can not find bug_test.app_z_tree_compo in genColumnStatsTask (state=42000,code=40000) 0: jdbc:hive2://c1162-node2.squadron-labs.com>
HIveServer2.Log 2019-05-07T08:10:57,147 ERROR [ff0d8118-7499-4116-a4de-e402c5ce7ca0 HiveServer2-Handler-Pool: Thread-85]: ql.Driver (:()) - FAILED: SemanticException Can not find bug_test.app_z_tree_compo in genColumnStatsTask org.apache.hadoop.hive.ql.parse.SemanticException: Can not find bug_test.app_z_tree_compo in genColumnStatsTask at org.apache.hadoop.hive.ql.parse.TaskCompiler.genColumnStatsTask(TaskCompiler.java:597) at org.apache.hadoop.hive.ql.parse.TaskCompiler.compile(TaskCompiler.java:317) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:12448) at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:360) at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:289) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:664) at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1869) at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1816) at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1811) at org.apache.hadoop.hive.ql.reexec.ReExecDriver.compileAndRespond(ReExecDriver.java:126) at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:197) at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:262) at org.apache.hive.service.cli.operation.Operation.run(Operation.java:247) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:575) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:561) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:78) at org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:36) at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:63) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1730) at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:59) at com.sun.proxy.$Proxy46.executeStatementAsync(Unknown Source) at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:315) at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:566) at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1557) at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1542) at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745)
+Attachments :
+hql_script.txt ( can be used to reproduce this issue ).
+WorkAround :
set hive.stats.column.autogather=false;**
+