You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Apache Spark (JIRA)" <ji...@apache.org> on 2016/10/14 19:19:20 UTC
[jira] [Assigned] (SPARK-17863) SELECT distinct does not work if
there is a order by clause
[ https://issues.apache.org/jira/browse/SPARK-17863?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Apache Spark reassigned SPARK-17863:
------------------------------------
Assignee: (was: Apache Spark)
> SELECT distinct does not work if there is a order by clause
> -----------------------------------------------------------
>
> Key: SPARK-17863
> URL: https://issues.apache.org/jira/browse/SPARK-17863
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Reporter: Yin Huai
> Priority: Blocker
> Labels: correctness
>
> {code}
> select distinct struct.a, struct.b
> from (
> select named_struct('a', 1, 'b', 2, 'c', 3) as struct
> union all
> select named_struct('a', 1, 'b', 2, 'c', 4) as struct) tmp
> order by struct.a, struct.b
> {code}
> This query generates
> {code}
> +---+---+
> | a| b|
> +---+---+
> | 1| 2|
> | 1| 2|
> +---+---+
> {code}
> The plan is wrong because the analyze somehow added {{struct#21805}} to the project list, which changes the semantic of the distinct (basically, the query is changed to {{select distinct struct.a, struct.b, struct}} from {{select distinct struct.a, struct.b}}).
> {code}
> == Parsed Logical Plan ==
> 'Sort ['struct.a ASC, 'struct.b ASC], true
> +- 'Distinct
> +- 'Project ['struct.a, 'struct.b]
> +- 'SubqueryAlias tmp
> +- 'Union
> :- 'Project ['named_struct(a, 1, b, 2, c, 3) AS struct#21805]
> : +- OneRowRelation$
> +- 'Project ['named_struct(a, 1, b, 2, c, 4) AS struct#21806]
> +- OneRowRelation$
> == Analyzed Logical Plan ==
> a: int, b: int
> Project [a#21819, b#21820]
> +- Sort [struct#21805.a ASC, struct#21805.b ASC], true
> +- Distinct
> +- Project [struct#21805.a AS a#21819, struct#21805.b AS b#21820, struct#21805]
> +- SubqueryAlias tmp
> +- Union
> :- Project [named_struct(a, 1, b, 2, c, 3) AS struct#21805]
> : +- OneRowRelation$
> +- Project [named_struct(a, 1, b, 2, c, 4) AS struct#21806]
> +- OneRowRelation$
> == Optimized Logical Plan ==
> Project [a#21819, b#21820]
> +- Sort [struct#21805.a ASC, struct#21805.b ASC], true
> +- Aggregate [a#21819, b#21820, struct#21805], [a#21819, b#21820, struct#21805]
> +- Union
> :- Project [1 AS a#21819, 2 AS b#21820, [1,2,3] AS struct#21805]
> : +- OneRowRelation$
> +- Project [1 AS a#21819, 2 AS b#21820, [1,2,4] AS struct#21806]
> +- OneRowRelation$
> == Physical Plan ==
> *Project [a#21819, b#21820]
> +- *Sort [struct#21805.a ASC, struct#21805.b ASC], true, 0
> +- Exchange rangepartitioning(struct#21805.a ASC, struct#21805.b ASC, 200)
> +- *HashAggregate(keys=[a#21819, b#21820, struct#21805], functions=[], output=[a#21819, b#21820, struct#21805])
> +- Exchange hashpartitioning(a#21819, b#21820, struct#21805, 200)
> +- *HashAggregate(keys=[a#21819, b#21820, struct#21805], functions=[], output=[a#21819, b#21820, struct#21805])
> +- Union
> :- *Project [1 AS a#21819, 2 AS b#21820, [1,2,3] AS struct#21805]
> : +- Scan OneRowRelation[]
> +- *Project [1 AS a#21819, 2 AS b#21820, [1,2,4] AS struct#21806]
> +- Scan OneRowRelation[]
> {code}
> If you use the following query, you will get the correct result
> {code}
> select distinct struct.a, struct.b
> from (
> select named_struct('a', 1, 'b', 2, 'c', 3) as struct
> union all
> select named_struct('a', 1, 'b', 2, 'c', 4) as struct) tmp
> order by a, b
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org