[jira] [Created] (IMPALA-7782) discrepancy in results with a subquery containing an agg that produces an empty set

["Michael Brown (JIRA)" <ji...@apache.org>]

Michael Brown created IMPALA-7782:
-------------------------------------

             Summary: discrepancy in results with a subquery containing an agg that produces an empty set
                 Key: IMPALA-7782
                 URL: https://issues.apache.org/jira/browse/IMPALA-7782
             Project: IMPALA
          Issue Type: Bug
          Components: Frontend
    Affects Versions: Impala 2.12.0, Impala 3.1.0
            Reporter: Michael Brown


A discrepancy exists between Impala and Postgres when a subquery contains an agg and results in an empty set, yet the WHERE clause looking at the subquery should produce a "True" condition.

Example queries include:
{noformat}
USE functional;
SELECT id
FROM alltypestiny
WHERE -1 NOT IN (SELECT COUNT(id) FROM alltypestiny HAVING false);
SELECT id
FROM alltypestiny
WHERE NULL NOT IN (SELECT COUNT(id) FROM alltypestiny HAVING false);
SELECT id
FROM alltypestiny
WHERE (SELECT COUNT(id) FROM alltypestiny HAVING false) IS NULL;
{noformat}

These queries do not produce any rows in Impala. In Postgres, the queries produce all 8 rows for the functional.alltypestiny id column.

Thinking maybe there were Impala and Postgres differences with {{NOT IN}} behavior, I also tried this:

{noformat}
USE functional;
SELECT id
FROM alltypestiny
WHERE -1 NOT IN (SELECT 1 FROM alltypestiny WHERE bool_col IS NULL);
{noformat}

This subquery also produces an empty set just like the subquery in the problematic queries at the top, but unlike those queries, this full query returns the same results in Impala and Postgres (all 8 rows for the functional.alltypestiny id column).

For anyone interested in this bug, you can migrate data into postgres in a dev environment using
{noformat}
tests/comparison/data_generator.py --use-postgresql --migrate-table-names alltypestiny --db-name functional migrate
{noformat}

This is in 2.12 at least, so it's not a 3.1 regression.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org