[
https://issues.apache.org/jira/browse/SPARK-17863?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yin Huai updated SPARK-17863:
-----------------------------
Description:
{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}
was:
{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
{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}
> 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: Critical
> 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}
--
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
