Search code examples
hiveimpala

Impala sub-query returns strange result

I am running the below impala query and i am getting strange results. Why does the 2th query below returns zero results and how to overcome this. I am doing several data pipelines with multiple tables, so i have to use the "with" in the beginning. 

1. Query: select * from test where name <> 'INSERT'
+----+--------+
| id | name   |
+----+--------+
| 2  | DELETE |
| 2  | HELLO  |
+----+--------+
Fetched 2 row(s) in 0.13s


2. Query: with temp as (select * from test where name <> 'INSERT') select * from temp
Modified 0 row(s) in 0.23s


3. Query: with temp as (select * from test where name <> 'HELLO') select * from temp
+----+--------+
| id | name   |
+----+--------+
| 2  | DELETE |
| 1  | INSERT |
+----+--------+
Fetched 2 row(s) in 0.12s


It should give the record names with 'HELLO' and 'DELETE' for the 2nd query. but its giving no results. Also noticed the output says "modified", so i am guessing its trying to execute it as DML.

Note : Using Impala Shell v2.11.0-cdh5.14.2

The same query works fine in hive.
Solution

  • It seems to work on my side

    with temp as (SELECT *
FROM
  (SELECT 'DELETE' AS name
   UNION SELECT 'HELLO' AS name
   UNION SELECT 'INSERT' AS name) AS subq
WHERE name <> 'INSERT')
select * from temp;

    +---------+
|  name   |
+---------+
| HELLO   |
| DELETE  |
+---------+
2 rows selected (0.118 seconds)

    Could you post the EXPLAIN PLAN of your second query?