[DRILL-7722] CREATE VIEW with LATERAL UNNEST creates an invalid view

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 1.17.0
• Component/s: SQL Parser
• Assignee: Vova Vysotskyi
• Reporter: Matevž Bradač
• Created: 29/Apr/20 05:45
• Updated: 09/Feb/23 01:27
• Resolved: 07/Aug/22 18:44

Description

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.

a simple JSON database

$ cat /tmp/t.json
[{"name": "item_1", "related": ["id1"]}, {"name": "item_2", "related": ["id1", "id2"]}, {"name": "item_3", "related": ["id2"]}]

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

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

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" : [ ]
}

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 ]

If the view is "fixed" by editing the generated JSON and removing the extra parentheses, e.g.

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" : [ ]
}

then querying works as expected:

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

Issue Links

• is fixed by: DRILL-7523 Update Calcite to 1.31.0
• is related to: CALCITE-4033 Unparser should not apply parentheses for UNNEST (as a built-in table operator)