[jira] [Commented] (SPARK-18861) Spark-SQL unconsistent behavior with "struct" expressions

["Herman van Hovell (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SPARK-18861?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15748503#comment-15748503 ] 

Herman van Hovell commented on SPARK-18861:
-------------------------------------------

Your code fails because the names of the struct fields need to be the same: {{struct<a: int, b: int> != struct<c: int, c: int>}}. The following works:
{noformat}
select case when a > b then named_struct('x', a, 'y', b) else named_struct('x', c, 'y', c) end from t1
{noformat}
The behavior of struct can be confusing because it uses a different naming strategies for different expressions:
- NamedExpressions (like {{a}}), we use the name of expression.
- Expressions, we name it {{col}} concatenated with the index of the column in struct, for example {{col2}}.

Note that most of these cases (except) for the first one will work in Spark 2.1 (see SPARK-16839 for more details).

> Spark-SQL unconsistent behavior with "struct" expressions
> ---------------------------------------------------------
>
>                 Key: SPARK-18861
>                 URL: https://issues.apache.org/jira/browse/SPARK-18861
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 2.0.0
>            Reporter: Ohad Raviv
>
> We are getting strangly inconsistent behavior with expressions involving "struct". Let's start with this simple table:
> {quote}
> Seq((1, 2, 3), (2, 3, 4)).toDF("a", "b", "c").createOrReplaceTempView("t1")
> sql("desc t1").show()
> {quote}
> Then we get this DF:
> {quote}
> |col_name|data_type|comment|
> |       a|      int|       |
> |       b|      int|       |
> |       c|      int|       |
> {quote}
> Now, although we can clearly see that all the fields are of type int, we we run:
> {quote}
> sql("SELECT case when a>b then struct(a,b) else struct(c,c) end from t1")
> {quote}
> we get this error:
> {quote}
> org.apache.spark.sql.AnalysisException: cannot resolve 'CASE WHEN (t1.`a` > t1.`b`) THEN struct(t1.`a`, t1.`b`) ELSE struct(t1.`c`, t1.`c`) END' due to data type mismatch: THEN and ELSE expressions should all be same type or coercible to a common type; line 1 pos 7
> {quote}
> if we try this:
> {quote}
> sql("SELECT case when a>b then struct(cast(a as int), cast(b as int)) else struct(cast(c as int), cast(c as int)) end from t1")
> {quote}
> we get another exception:
> {quote}
> requirement failed: Unresolved attributes found when constructing LocalRelation.
> java.lang.IllegalArgumentException: requirement failed: Unresolved attributes found when constructing LocalRelation.
> 	at scala.Predef$.require(Predef.scala:224)
> 	at org.apache.spark.sql.catalyst.plans.logical.LocalRelation.<init>(LocalRelation.scala:49)
> {quote}
> However, these do work:
> {quote}
> sql("SELECT case when a>b then struct(cast(a as double), cast(b as double)) else struct(cast(c as double), cast(c as double)) end from t1")
> sql("SELECT case when a>b then struct(cast(a as string), cast(b as string)) else struct(cast(c as string), cast(c as string)) end from t1")
> {quote}
> any ideas?



--
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