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

["Chaitanya Kulkarni (JIRA)" <ji...@apache.org>]

Chaitanya Kulkarni created HIVE-11728:
-----------------------------------------

             Summary: 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.14.0, 0.13.0
         Environment: Linux, Hive 0.13 or 0.14, CDH or HDP cluster.
            Reporter: Chaitanya Kulkarni


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