Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
2.3.5
-
None
-
None
Description
unable to create hive view with EXISTS clause:
Error:
FAILED: SemanticException Line 0:-1 Invalid table alias or column reference 'sq_1': (possible column names are: table_or_col lf) file_date) sq_corr, (. (tok_table_or_col sq_1) sq_corr_1))
Below reproduction steps:
– Setup Tables
create table bug_part_1 (table_name string, partition_date date, file_date timestamp);
create table bug_part_2 (id string, file_date timestamp) partitioned by (partition_date date);
– Example 1 - Works if just query.
select vlf.id
from bug_part_2 vlf
where 1=1
and exists (
select null
from (
select max(file_date) file_date, max(partition_date) as partition_date
from bug_part_1
) lf
where lf.partition_date = vlf.partition_date and lf.file_date = vlf.file_date
);
– Example 2 - Fails in view.
create or replace view bug_view
as
select vlf.id
from bug_part_2 vlf
where 1=1
and exists (
select null
from (
select max(file_date) file_date, max(partition_date) as partition_date
from bug_part_1
) lf
where lf.partition_date = vlf.partition_date and lf.file_date = vlf.file_date
);