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)
