[jira] [Resolved] (HIVE-11728) WITH clause uses regular table instead of intermidiate relation when regular table exits with same name as of intermidiate relation.

["Pengcheng Xiong (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-11728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Pengcheng Xiong resolved HIVE-11728.
------------------------------------
    Resolution: Duplicate

> WITH clause uses regular table instead of intermidiate relation when regular table exits with same name as of intermidiate relation.
> ------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: HIVE-11728
>                 URL: https://issues.apache.org/jira/browse/HIVE-11728
>             Project: Hive
>          Issue Type: Bug
>          Components: HiveServer2, Parser
>    Affects Versions: 0.13.0, 0.14.0
>         Environment: Linux, Hive 0.13 or 0.14, CDH or HDP cluster.
>            Reporter: Chaitanya Kulkarni
>            Assignee: Pengcheng Xiong
>
> If a table of name 'tab1' exists in a database, and within a SQL statement, a CTE (with clause) is used with intermediate relation having same name as 'tab1' then Hive uses 'tab1' regular table in query rather than using intermediate relation.
> Steps to recreate the issue:
> 1. Create a table with name 'tab1'.
> 2. Load some sample data in table 'tab1'.
> 3. Write a hive query to use some CTE with intermediate relation name as 'tab1', and execute the query.
> e.g. with tab1 as (select * from orders),
> select count(tab1.*) from tab1;
> If we have a table with same name like 'tab1', and CTE is also using 'tab1' as relation name, then query engine refers to 'tab1' which is a regular table. This is not as per implementation in other database systems, like PostgreSQL.
> complete test script:
> create table test 
> ( id int, 
>  name varchar(100));
>  
>  insert into test values (1, 'abc');
>  insert into test values (2, 'xyz');
>  
>  select * from test;
>  
>  with test as (
>  select * from other_table)
>  select * from test;



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)