[SPARK-17114] Adding a 'GROUP BY 1' where first column is literal results in wrong answer

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.6.2, 2.0.0
• Fix Version/s: 2.0.1, 2.1.0
• Component/s: SQL
• Labels: correctness
• Assignee: Herman van Hövell
• Reporter: Josh Rosen
• Created: 17/Aug/16 20:10
• Updated: 08/Dec/16 22:54
• Resolved: 15/Sep/16 18:26

Description

Consider the following example:

sc.parallelize(Seq(128, 256)).toDF("int_col").registerTempTable("mytable")

// The following query should return an empty result set because the `IN` filter condition is always false for this single-row table.
val withoutGroupBy = sqlContext.sql("""
  SELECT 'foo'
  FROM mytable
  WHERE int_col == 0
""")
assert(withoutGroupBy.collect().isEmpty, "original query returned wrong answer")

// After adding a 'GROUP BY 1' the query result should still be empty because we'd be grouping an empty table:
val withGroupBy = sqlContext.sql("""
  SELECT 'foo'
  FROM mytable
  WHERE int_col == 0
  GROUP BY 1
""")
assert(withGroupBy.collect().isEmpty, "adding GROUP BY resulted in wrong answer")

Here, this fails the second assertion by returning a single row. It appears that running group by 1 where column 1 is a constant causes filter conditions to be ignored.

Both PostgreSQL and SQLite return empty result sets for the query containing the GROUP BY.

Issue Links

• links to: [Github] Pull Request #15076 (hvanhovell)
• links to: [Github] Pull Request #15101 (hvanhovell)