hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chaitanya Kulkarni (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-11728) WITH clause uses regular table instead of intermidiate relation when regular table exits with same name as of intermidiate relation.
Date Thu, 03 Sep 2015 22:12:47 GMT

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

Mime
View raw message