philipse created SPARK-32324: -------------------------------- Summary: 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
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: ``` 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'); ``` Query1: ``` 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') ) ``` Result 1: ``` 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) ``` Query2: ``` 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 ``` 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: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org