[
https://issues.apache.org/jira/browse/HIVE-11728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pengcheng Xiong reassigned HIVE-11728:
--------------------------------------
Assignee: Pengcheng Xiong
> 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)
