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

[Jira]

Matevž Bradač created DRILL-7722:
------------------------------------

             Summary: 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č


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.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.3.4#803005)