[
https://issues.apache.org/jira/browse/DRILL-7722?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17117807#comment-17117807
]
ASF GitHub Bot commented on DRILL-7722:
---------------------------------------
bozzzzo opened a new pull request #2083:
URL: https://github.com/apache/drill/pull/2083
# [DRILL-7722](https://issues.apache.org/jira/browse/DRILL-7722): Add
reproducer unit test
## Description
Add a unit test that demonstrates that a view with UNNEST cannot be
sucessfully queried.
## Documentation
Fixing the bug will make documentation as-is correct
## Testing
The added test fails.
A slightly modified test that does not use the view, but directly executes
the view query, passes:
```
$ git diff
diff --git
a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java
b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java
index 1a92e192f..7c317f427 100644
---
a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java
+++
b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java
@@ -857,7 +857,8 @@ public class TestViewSupport extends TestBaseViewSupport
{
DFS_TMP_SCHEMA,
null,
viewSQL,
- "SELECT * FROM TEST_SCHEMA.TEST_VIEW_NAME LIMIT 4",
+ //"SELECT * FROM TEST_SCHEMA.TEST_VIEW_NAME LIMIT 4",
+ viewSQL + " LIMIT 4",
baselineColumns("customer_id", "order_id"),
baselineRows(row(1.0, 1.0),
row(1.0, 2.0),
```
----------------------------------------------------------------
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)
