[ https://issues.apache.org/jira/browse/IMPALA-7782?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Adrian Ng reassigned IMPALA-7782: --------------------------------- Assignee: Paul Rogers > 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 > Assignee: Paul Rogers > Priority: Blocker > Labels: correctness, query_generator > > 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-unsubscr...@impala.apache.org For additional commands, e-mail: issues-all-h...@impala.apache.org