[SPARK-4296] Throw "Expression not in GROUP BY" when using same expression in group by clause and select clause

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 1.1.0, 1.1.1, 1.2.0
• Fix Version/s: 1.2.1, 1.3.0
• Component/s: SQL
• Target Version/s: 1.2.1, 1.3.0
• Assignee: Cheng Lian
• Reporter: Shixiong Zhu
• Created: 07/Nov/14 11:19
• Updated: 21/Mar/17 22:01
• Resolved: 20/Jan/15 22:01

Description

When the input data has a complex structure, using same expression in group by clause and select clause will throw "Expression not in GROUP BY".

val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.createSchemaRDD
case class Birthday(date: String)
case class Person(name: String, birthday: Birthday)
val people = sc.parallelize(List(Person("John", Birthday("1990-01-22")), Person("Jim", Birthday("1980-02-28"))))
people.registerTempTable("people")
val year = sqlContext.sql("select count(*), upper(birthday.date) from people group by upper(birthday.date)")
year.collect

Here is the plan of year:

SchemaRDD[3] at RDD at SchemaRDD.scala:105
== Query Plan ==
== Physical Plan ==
org.apache.spark.sql.catalyst.errors.package$TreeNodeException: Expression not in GROUP BY: Upper(birthday#1.date AS date#9) AS c1#3, tree:
Aggregate [Upper(birthday#1.date)], [COUNT(1) AS c0#2L,Upper(birthday#1.date AS date#9) AS c1#3]
 Subquery people
  LogicalRDD [name#0,birthday#1], MapPartitionsRDD[1] at mapPartitions at ExistingRDD.scala:36

The bug is the equality test for `Upper(birthday#1.date)` and `Upper(birthday#1.date AS date#9)`.

Maybe Spark SQL needs a mechanism to compare Alias expression and non-Alias expression.

Issue Links

• duplicates: SPARK-4322 Struct fields can't be used as sub-expression of grouping fields
• links to: [Github] Pull Request #3910 (liancheng)
• links to: [Github] Pull Request #4010 (yhuai)
• links to: Spark PR 3987