[
https://issues.apache.org/jira/browse/DRILL-7722?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17496732#comment-17496732
]
James Turton commented on DRILL-7722:
-------------------------------------
[~bozzo] you can delete any CRC files created by Drill and it will carry on.
I've just checked that this bug is still present in Drill today, hopefully we
can get it fixed soon.
> CREATE VIEW with LATERAL UNNEST creates an invalid view
> -------------------------------------------------------
>
> Key: DRILL-7722
> URL: https://issues.apache.org/jira/browse/DRILL-7722
> Project: Apache Drill
> Issue Type: Bug
> Components: SQL Parser
> Affects Versions: 1.17.0
> Reporter: Matevž Bradač
> Priority: Blocker
>
> Creating a view from a query containing LATERAL UNNEST results in a view that
> cannot be parsed by the engine. The generated view contains superfluous
> parentheses, thus the failed parsing.
> {code:bash|title=a simple JSON database}
> $ cat /tmp/t.json
> [{"name": "item_1", "related": ["id1"]}, {"name": "item_2", "related":
> ["id1", "id2"]}, {"name": "item_3", "related": ["id2"]}]
> {code}
> {code:SQL|title=drill query, working}
> SELECT
> item.name,
> relations.*
> FROM dfs.tmp.`t.json` item
> JOIN LATERAL(
> SELECT * FROM UNNEST(item.related) i(rels)
> ) relations
> ON TRUE
> name rels
> 0 item_1 id1
> 1 item_2 id1
> 2 item_2 id2
> 3 item_3 id2
> {code}
> {code:SQL|title=create a drill view from the above query}
> CREATE VIEW dfs.tmp.unnested_view AS
> SELECT
> item.name,
> relations.*
> FROM dfs.tmp.`t.json` item
> JOIN LATERAL(
> SELECT * FROM UNNEST(item.related) i(rels)
> ) relations
> ON TRUE
> {code}
> {code:bash|title=contents of view file}
> # note the extra parentheses near LATERAL and FROM
> $ cat /tmp/unnested_view.view.drill
> {
> "name" : "unnested_view",
> "sql" : "SELECT `item`.`name`, `relations`.*\nFROM `dfs`.`tmp`.`t.json` AS
> `item`\nINNER JOIN LATERAL((SELECT *\nFROM (UNNEST(`item`.`related`)) AS `i`
> (`rels`))) AS `relations` ON TRUE",
> "fields" : [ {
> "name" : "name",
> "type" : "ANY",
> "isNullable" : true
> }, {
> "name" : "rels",
> "type" : "ANY",
> "isNullable" : true
> } ],
> "workspaceSchemaPath" : [ ]
> }
> {code}
> {code:SQL|title=query the view}
> SELECT * FROM dfs.tmp.unnested_view
> PARSE ERROR: Failure parsing a view your query is dependent upon.
> SQL Query: SELECT `item`.`name`, `relations`.*
> FROM `dfs`.`tmp`.`t.json` AS `item`
> INNER JOIN LATERAL((SELECT *
> FROM (UNNEST(`item`.`related`)) AS `i` (`rels`))) AS `relations` ON TRUE
> ^
> [Error Id: fd816a27-c2c5-4c2a-b6bf-173ab37eb693 ]
> {code}
> If the view is "fixed" by editing the generated JSON and removing the extra
> parentheses, e.g.
> {code:bash|title=fixed view}
> $ cat /tmp/fixed_unnested_view.view.drill
> {
> "name" : "fixed_unnested_view",
> "sql" : "SELECT `item`.`name`, `relations`.*\nFROM `dfs`.`tmp`.`t.json` AS
> `item`\nINNER JOIN LATERAL(SELECT *\nFROM UNNEST(`item`.`related`) AS `i`
> (`rels`)) AS `relations` ON TRUE",
> "fields" : [ {
> "name" : "name",
> "type" : "ANY",
> "isNullable" : true
> }, {
> "name" : "rels",
> "type" : "ANY",
> "isNullable" : true
> } ],
> "workspaceSchemaPath" : [ ]
> }
> {code}
> then querying works as expected:
> {code:sql|title=fixed view query}
> SELECT * FROM dfs.tmp.fixed_unnested_view
> name rels
> 0 item_1 id1
> 1 item_2 id1
> 2 item_2 id2
> 3 item_3 id2
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
