[
https://issues.apache.org/jira/browse/DRILL-7722?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Matevž Bradač updated DRILL-7722:
---------------------------------
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.
{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}
was:
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}
> 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.3.4#803005)
