[jira] [Comment Edited] (DRILL-7722) CREATE VIEW with LATERAL UNNEST creates an invalid view

["James Turton (Jira)" <ji...@apache.org>]

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

James Turton edited comment on DRILL-7722 at 7/19/22 6:47 AM:
--------------------------------------------------------------

[~volodymyr], I think this issue can be added to our list of those that we check for closing after the Calcite upgrade.


was (Author: dzamo):
[~volodymyr], I think this issue can be to our list of those that we check for closing after the Calcite upgrade.

> 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.10#820010)