[jira] [Updated] (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>]

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

Chaitanya Kulkarni updated HIVE-11728:
--------------------------------------
    Description: 
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;

  was:
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;


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