[jira] [Created] (DRILL-8110) CREATE VIEW with WITH clause as function argument creates invalid view

["Liubov Udalova (Jira)" <ji...@apache.org>]

Liubov Udalova created DRILL-8110:
-------------------------------------

             Summary: 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.19.0, 1.18.0
            Reporter: Liubov Udalova


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.1#820001)