[
https://issues.apache.org/jira/browse/SPARK-32324?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
philipse resolved SPARK-32324.
------------------------------
Resolution: Not A Problem
> Fix error messages during using PIVOT and lateral view
> ------------------------------------------------------
>
> Key: SPARK-32324
> URL: https://issues.apache.org/jira/browse/SPARK-32324
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 3.0.0
> Reporter: philipse
> Priority: Minor
>
> Currently when we use `lateral view` and `pivot` together in from clause, if
> `lateral view` is before `pivot`, the error message is "LATERAL cannot be
> used together with PIVOT in FROM clause".if if `lateral view` is after
> `pivot`,the query will be normal ,So the error messages "LATERAL cannot be
> used together with PIVOT in FROM clause" is not accurate, we may improve it.
>
> Steps to reproduce:
> {code:java}
> CREATE TABLE person (id INT, name STRING, age INT, class int, address STRING);
> INSERT INTO person VALUES
> (100, 'John', 30, 1, 'Street 1'),
> (200, 'Mary', NULL, 1, 'Street 2'),
> (300, 'Mike', 80, 3, 'Street 3'),
> (400, 'Dan', 50, 4, 'Street 4');
> {code}
>
> Query1:
>
> {code:java}
> SELECT * FROM person
> lateral view outer explode(array(30,60)) tabelName as c_age
> lateral view explode(array(40,80)) as d_age
> PIVOT (
> count(distinct age) as a
> for name in ('Mary','John')
> )
> {code}
> Result 1:
>
> {code:java}
> Error: org.apache.spark.sql.catalyst.parser.ParseException:
> LATERAL cannot be used together with PIVOT in FROM clause(line 1, pos 9)
> == SQL ==
> SELECT * FROM person
> ---------^^^
> lateral view outer explode(array(30,60)) tabelName as c_age
> lateral view explode(array(40,80)) as d_age
> PIVOT (
> count(distinct age) as a
> for name in ('Mary','John')
> ) (state=,code=0)
> {code}
>
>
> Query2:
>
> {code:java}
> SELECT * FROM person
> PIVOT (
> count(distinct age) as a
> for name in ('Mary','John')
> )
> lateral view outer explode(array(30,60)) tabelName as c_age
> lateral view explode(array(40,80)) as d_age
> {code}
>
> Reuslt2:
> +-------+------++---------------++--------
> |id|Mary|John|c_age|d_age|
> +-------+------++---------------++--------
> |300|NULL|NULL|30|40|
> |300|NULL|NULL|30|80|
> |300|NULL|NULL|60|40|
> |300|NULL|NULL|60|80|
> |100|0|NULL|30|40|
> |100|0|NULL|30|80|
> |100|0|NULL|60|40|
> |100|0|NULL|60|80|
> |400|NULL|NULL|30|40|
> |400|NULL|NULL|30|80|
> |400|NULL|NULL|60|40|
> |400|NULL|NULL|60|80|
> |200|NULL|1|30|40|
> |200|NULL|1|30|80|
> |200|NULL|1|60|40|
> |200|NULL|1|60|80|
> +-------+------++---------------++--------
> ```
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
