spark-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yin Huai (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (SPARK-17863) SELECT distinct does not work if there is a order by clause
Date Tue, 11 Oct 2016 20:37:20 GMT

     [ 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-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org


Mime
View raw message