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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.18.0, 1.19.0
• Component/s: SQL Parser
• Assignee: Unassigned
• Reporter: Liubov Udalova
• Created: 21/Jan/22 14:32
• Updated: 23/Sep/22 11:38

Description

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:

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)
    ) 

Query with view creation:

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)
    )
); 

Query the view:

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`)) 

Content of view file:

{
  "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" : [ ]
} 

 

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:

final SqlWriter.Frame frame = writer.startList(SqlWriter.FrameTypeEnum.WITH, "(WITH", ")");