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

Using complex type in Aggregation with cube failed Analysis error

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 2.4.0, 3.0.0
    • 3.1.0
    • SQL
    • None

    Description

      Will wrong with below SQL

      test("TEST STRUCT FIELD WITH GROUP BY with CUBE") {
        withTable("t1") {
          sql(
            """create table t1(
              |a string,
              |b int,
              |c array<struct<row_id:int,json_string:string>>)
              |using orc""".stripMargin)
      
          sql(
            """
              |select a, coalesce(get_json_object(each.json_string,'$.iType'),'-127') as iType, sum(b)
              |from t1
              |LATERAL VIEW explode(c) x AS each
              |group by a, get_json_object(each.json_string,'$.iType')
              |with cube
              |""".stripMargin).explain(true)
        }
      }
      

      ErrorĀ 

      expression 'x.`each`' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.;;
      Aggregate [a#230, get_json_object(each#222.json_string AS json_string#223, $.iType)#231, spark_grouping_id#229L], [a#230, coalesce(get_json_object(each#222.json_string, $.iType), -127) AS iType#218, sum(cast(b#220 as bigint)) AS sum(b)#226L]
      +- Expand [List(a#219, b#220, c#221, each#222, a#227, get_json_object(each#222.json_string AS json_string#223, $.iType)#228, 0), List(a#219, b#220, c#221, each#222, a#227, null, 1), List(a#219, b#220, c#221, each#222, null, get_json_object(each#222.json_string AS json_string#223, $.iType)#228, 2), List(a#219, b#220, c#221, each#222, null, null, 3)], [a#219, b#220, c#221, each#222, a#230, get_json_object(each#222.json_string AS json_string#223, $.iType)#231, spark_grouping_id#229L]
         +- Project [a#219, b#220, c#221, each#222, a#219 AS a#227, get_json_object(each#222.json_string, $.iType) AS get_json_object(each#222.json_string AS json_string#223, $.iType)#228]
            +- Generate explode(c#221), false, x, [each#222]
               +- SubqueryAlias spark_catalog.default.t1
                  +- Relation[a#219,b#220,c#221] orc
      

      Attachments

        Issue Links

          Activity

            People

              angerszhuuu angerszhu
              angerszhuuu angerszhu
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: