[jira] [Commented] (HIVE-22472) Unable to create hive view

["Eric Lin (Jira)" <ji...@apache.org>]

    [ 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)