[
https://issues.apache.org/jira/browse/DRILL-8110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17608710#comment-17608710
]
James Turton commented on DRILL-8110:
-------------------------------------
Interesting query syntax! This problem still exists in Drill master. Have you
been able to rewrite your query using extra views or nested selects instead of
CTEs in the meantime?
> CREATE VIEW with WITH clause as function argument creates invalid view
> ----------------------------------------------------------------------
>
> Key: DRILL-8110
> URL: https://issues.apache.org/jira/browse/DRILL-8110
> Project: Apache Drill
> Issue Type: Bug
> Components: SQL Parser
> Affects Versions: 1.18.0, 1.19.0
> Reporter: Liubov Udalova
> Priority: Major
>
> Creating a view from query with WITH clause as function argument (if function
> has multiple arguments) results in a view which cannot be parsed. There are
> missing parentheses around WITH expression, thus parsing is failed.
> Although execution of view subquery itself works fine.
> Drill query:
> {code:java}
> select age(
> (with first_date as (select date '2021-12-20')
> select * from first_date),
> (with second_date as (select date '2022-12-20')
> select * from second_date)
> ) {code}
> Query with view creation:
> {code:java}
> create or replace view storage.datasets.`debug_view_creation_1` as (
> select age(
> (with first_date as (select date '2021-12-20')
> select * from first_date),
> (with second_date as (select date '2022-12-20')
> select * from second_date)
> )
> ); {code}
> Query the view:
> {code:java}
> select * from storage.datasets.`debug_view_creation_1`;
> Error: PARSE ERROR: Failure parsing a view your query is dependent upon.SQL
> Query: SELECT `age`(WITH `first_date` AS (SELECT DATE '2021-12-20') (SELECT *
> FROM `first_date`), WITH `second_date` AS (SELECT DATE '2022-12-20') (SELECT *
> ^
> FROM `second_date`)) {code}
> Content of view file:
> {code:java}
> {
> "name" : "debug_view_creation_1",
> "sql" : "SELECT `age`(WITH `first_date` AS (SELECT DATE '2021-12-20')
> (SELECT *\nFROM `first_date`), WITH `second_date` AS (SELECT DATE
> '2022-12-20') (SELECT *\nFROM `second_date`))",
> "fields" : [ {
> "name" : "EXPR$0",
> "type" : "ANY",
> "isNullable" : true
> } ],
> "workspaceSchemaPath" : [ ]
> } {code}
>
> It looks like a problem in Calcite
> org.apache.calcite.sql.SqlWith.SqlWithOperator - perhaps in unparse method
> first of SqlWriter.Frame frame should be created with additional parentheses:
> {code:java}
> final SqlWriter.Frame frame = writer.startList(SqlWriter.FrameTypeEnum.WITH,
> "(WITH", ")"); {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
