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)
