Zoltán Borók-Nagy created IMPALA-15714:
------------------------------------------
Summary: CREATE VIEW with UNPIVOT stores a view definition that
cannot be parsed
Key: IMPALA-15714
URL: https://issues.apache.org/jira/browse/IMPALA-15714
Project: IMPALA
Issue Type: Bug
Components: Frontend
Affects Versions: Impala 5.0.0
Reporter: Zoltán Borók-Nagy
CREATE VIEW over a query with an UNPIVOT clause succeeds, but the view can't be
queried afterwards:
{code:sql}
CREATE TABLE t (a INT, v1 INT, v2 INT);
CREATE VIEW v AS SELECT * FROM t UNPIVOT (c FOR b IN (v1 AS 'x', v2 AS 'y')) u;
SELECT * FROM v;
{code}
{noformat}
AnalysisException: Failed to load metadata for table: 'default.v'
CAUSED BY: TableLoadingException: Could not load table default.v from catalog
CAUSED BY: TException: TableLoadingException: Failed to parse view-definition
statement of view: default.v
{noformat}
The view text stored in HMS is:
{noformat}
SELECT * FROM default.t UNPIVOT (c FOR b IN ('x' v1,'y' v2)) u
{noformat}
UnpivotTableRef.tableRefToSql() prints the IN list items with
SelectListItem.toSql(), which writes the label first and the column second. The
parser expects the column first, optionally followed by AS and the label.
Labels are not needed to hit this: IN (v1, v2) is stored as IN ('v1' v1,'v2'
v2).
tableRefToSql() also doesn't quote identifiers. With a source table named
`select`, data column `table` and header column `from`, the stored text is:
{noformat}
SELECT * FROM db.select UNPIVOT (table FOR from IN ('v1' v1,'v2' v2)) u
{noformat}
The broken view can't be dropped with DROP VIEW (it fails with "DROP VIEW not
allowed on a table: default.v"); DROP TABLE works.
Views with PIVOT are not affected.
Reproduced on master (ce6a517e21).
Suggested fix: print each IN list item as the quoted column name, AS, then the
label, and quote the source path and the data and header column names with
ToSqlUtils. Add ToSqlTest cases and a CREATE VIEW round trip test.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
