[
https://issues.apache.org/jira/browse/SPARK-11275?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14981338#comment-14981338
]
Andrew Ray commented on SPARK-11275:
------------------------------------
I think that I understand what is happening here. Any expression involving one
of the group by columns in a cube/rollup gets evaluated wrong. This is missed
in unit testing since the tests only check that the dataframe operations give
the same result as SQL, but they are both handled by the same incorrect logic.
I'll put a PR together to fix it and provide better unit tests.
> [SQL] Regression in rollup/cube
> --------------------------------
>
> Key: SPARK-11275
> URL: https://issues.apache.org/jira/browse/SPARK-11275
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 1.5.1
> Reporter: Xiao Li
>
> Spark SQL is unable to generate a correct result when the following query
> using rollup.
> "select a, b, sum(a + b) as sumAB, GROUPING__ID from mytable group by a,
> b with rollup"
> Spark SQL generates a wrong result:
> [2,4,6,3]
> [2,null,null,1]
> [1,null,null,1]
> [null,null,null,0]
> [1,2,3,3]
> The table mytable is super simple, containing two rows and two columns:
> testData = Seq((1, 2), (2, 4)).toDF("a", "b")
> After turning off codegen, the query plan is like
> == Parsed Logical Plan ==
> 'Rollup ['a,'b],
> [unresolvedalias('a),unresolvedalias('b),unresolvedalias('sum(('a + 'b)) AS
> sumAB#20),unresolvedalias('GROUPING__ID)]
> 'UnresolvedRelation `mytable`, None
> == Analyzed Logical Plan ==
> a: int, b: int, sumAB: bigint, GROUPING__ID: int
> Aggregate [a#2,b#3,grouping__id#23], [a#2,b#3,sum(cast((a#2 + b#3) as
> bigint)) AS sumAB#20L,GROUPING__ID#23]
> Expand [0,1,3], [a#2,b#3], grouping__id#23
> Subquery mytable
> Project [_1#0 AS a#2,_2#1 AS b#3]
> LocalRelation [_1#0,_2#1], [[1,2],[2,4]]
> == Optimized Logical Plan ==
> Aggregate [a#2,b#3,grouping__id#23], [a#2,b#3,sum(cast((a#2 + b#3) as
> bigint)) AS sumAB#20L,GROUPING__ID#23]
> Expand [0,1,3], [a#2,b#3], grouping__id#23
> LocalRelation [a#2,b#3], [[1,2],[2,4]]
> == Physical Plan ==
> Aggregate false, [a#2,b#3,grouping__id#23], [a#2,b#3,sum(PartialSum#24L) AS
> sumAB#20L,grouping__id#23]
> Exchange hashpartitioning(a#2,b#3,grouping__id#23,5)
> Aggregate true, [a#2,b#3,grouping__id#23],
> [a#2,b#3,grouping__id#23,sum(cast((a#2 + b#3) as bigint)) AS PartialSum#24L]
> Expand [List(null, null, 0),List(a#2, null, 1),List(a#2, b#3, 3)],
> [a#2,b#3,grouping__id#23]
> LocalTableScan [a#2,b#3], [[1,2],[2,4]]
> Below are my observations:
> 1. Generation of GROUP__ID looks OK.
> 2. The problem still exists no matter whether turning on/off CODEGEN
> 3. Rollup still works in a simple query when group-by columns have only one
> column. For example, "select b, sum(a), GROUPING__ID from mytable group by b
> with rollup"
> 4. The buckets in "HiveDataFrameAnalytcisSuite" are misleading.
> Unfortunately, they hide the bugs. Although the buckets passed, they just
> compare the results of SQL and Dataframe. This way is unable to capture the
> regression when both return the same wrong results.
> 5. The same problem also exists in cube. I have not started the investigation
> in cube, but I believe the root causes should be the same.
> 6. It looks like all the logical plans are correct.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
