[ https://issues.apache.org/jira/browse/HIVE-22472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16978034#comment-16978034 ]
Eric Lin commented on HIVE-22472: --------------------------------- Setting hive.cbo.enable=true can help to avoid the issue, but not sure why yet. > Unable to create hive view > --------------------------- > > Key: HIVE-22472 > URL: https://issues.apache.org/jira/browse/HIVE-22472 > Project: Hive > Issue Type: Bug > Components: Hive > Affects Versions: 2.3.5 > Reporter: Ashok > Priority: Major > > 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 > ); > -- This message was sent by Atlassian Jira (v8.3.4#803005)