Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-11728

WITH clause uses regular table instead of intermidiate relation when regular table exits with same name as of intermidiate relation.

    XMLWordPrintableJSON

    Details

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

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

    • Flags:
      Important

      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;

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                pxiong Pengcheng Xiong
                Reporter:
                chaitanyakul Chaitanya Kulkarni
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved: