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: [email protected]
For additional commands, e-mail: [email protected]
