Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-31519

Cast in having aggregate expressions returns the wrong result

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.2.3, 2.3.4, 2.4.5, 3.0.0
    • Fix Version/s: 2.4.6, 3.0.0
    • Component/s: SQL
    • Labels:

      Description

      Cast in having aggregate expressions returns the wrong result.

      See the below tests: 

      
      scala> spark.sql("create temp view t(a, b) as values (1,10), (2, 20)")
      res0: org.apache.spark.sql.DataFrame = []
      
      scala> val query = """
           | select sum(a) as b, '2020-01-01' as fake
           | from t
           | group by b
           | having b > 10;"""
      
      scala> spark.sql(query).show()
      +---+----------+
      |  b|      fake|
      +---+----------+
      |  2|2020-01-01|
      +---+----------+
      
      scala> val query = """
           | select sum(a) as b, cast('2020-01-01' as date) as fake
           | from t
           | group by b
           | having b > 10;"""
      
      scala> spark.sql(query).show()
      +---+----+
      |  b|fake|
      +---+----+
      +---+----+
      

      The SQL parser in Spark creates Filter(..., Aggregate(...)) for the HAVING query, and Spark has a special analyzer rule ResolveAggregateFunctions to resolve the aggregate functions and grouping columns in the Filter operator.
       
      It works for simple cases in a very tricky way as it relies on rule execution order:
      1. Rule ResolveReferences hits the Aggregate operator and resolves attributes inside aggregate functions, but the function itself is still unresolved as it's an UnresolvedFunction. This stops resolving the Filter operator as the child Aggrege operator is still unresolved.
      2. Rule ResolveFunctions resolves UnresolvedFunction. This makes the Aggrege operator resolved.
      3. Rule ResolveAggregateFunctions resolves the Filter operator if its child is a resolved Aggregate. This rule can correctly resolve the grouping columns.
       
      In the example query, I put a CAST, which needs to be resolved by rule ResolveTimeZone, which runs after ResolveAggregateFunctions. This breaks step 3 as the Aggregate operator is unresolved at that time. Then the analyzer starts next round and the Filter operator is resolved by ResolveReferences, which wrongly resolves the grouping columns.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                XuanYuan Yuanjian Li
                Reporter:
                XuanYuan Yuanjian Li
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: