[
https://issues.apache.org/jira/browse/DRILL-7722?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17118358#comment-17118358
]
ASF GitHub Bot commented on DRILL-7722:
---------------------------------------
bozzzzo commented on pull request #2083:
URL: https://github.com/apache/drill/pull/2083#issuecomment-635129685
The test fails in the same way as already reported in JIRA
https://github.com/apache/drill/runs/715619115?check_suite_focus=true#step:6:10828
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
> 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)
