[
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: [email protected]
For additional commands, e-mail: [email protected]
