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

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 0.13.0, 0.14.0
• Component/s: HiveServer2, Parser

Environment

Linux, Hive 0.13 or 0.14, CDH or HDP cluster.

• Flags: Important
• Assignee: Pengcheng Xiong
• Reporter: Chaitanya Kulkarni
• Due: 15/Sep/15
• Created: 03/Sep/15 22:09
• Updated: 03/Sep/15 22:26
• Resolved: 03/Sep/15 22:26

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;

Issue Links

• is related to: HIVE-10698 query on view results fails with table not found error if view is created with subquery alias (CTE).