SQL Parse Issue

1

Hello,

I am struggling to figure out why the following statement fails to parse. It looks like Dremio is dropping a (.

Here is the SQL:

  create or replace view "zais data mart (dev)"."mart"."application"."loanz"."instrument" as (
    WITH baseline_plus_delta AS (
    SELECT
        *
    FROM
        "zais data mart (dev)"."staging"."ratings"."moodys"."stg_instrument"
    WHERE
        op_type = 'BASELINE' AND
        asof = '2022-06-27 12:00:00'
    UNION ALL
        SELECT
            *
        FROM
            "zais data mart (dev)"."staging"."ratings"."moodys"."stg_instrument"
        WHERE
            op_type <> 'BASELINE' AND
            asof > '2022-06-27 12:00:00'
),
deduped_baseline_plus_delta AS (
    SELECT
        DISTINCT *
    FROM
        baseline_plus_delta
),
current_active_ids AS (
    SELECT
        instrument_id AS instrument_id,
        max(asof) AS asof,
        max(created_at) AS created_at
    FROM
        deduped_baseline_plus_delta
    WHERE
        op_type != 'DELETE'
    GROUP BY
        instrument_id
),
markit_mappings_deduped AS (
    SELECT
        *
    FROM
        "zais data mart (dev)"."mart"."application"."loanz"."markit_mapping_moodys"
),
final as (
    SELECT
        i.*,
        m.*
    FROM
        current_active_ids c
    INNER JOIN deduped_baseline_plus_delta i ON
        i.instrument_id = c.instrument_id AND
        i.asof = c.asof AND
        i.created_at = c.created_at
    INNER JOIN markit_mappings_deduped m ON
        c.instrument_id = m.provider_id
)

SELECT
    *
FROM
    final
  )

Here is a snippet of the stack trace in the Jobs section:

Caused By (com.dremio.exec.planner.sql.parser.impl.ParseException) Encountered "SELECT" at line 1, column 31.
Was expecting:
    "(" ...

Here is another snippet from the stack trace with the parsed SQL:

         PARSE ERROR: Failure parsing the query.

SQL Query WITH "baseline_plus_delta" AS SELECT *
FROM "zais data mart (dev)"."staging"."ratings"."moodys"."stg_instrument"
WHERE "op_type" = 'BASELINE' AND "asof" = '2022-06-27 12:00:00'
UNION ALL
SELECT *
FROM "zais data mart (dev)"."staging"."ratings"."moodys"."stg_instrument"
WHERE "op_type" <> 'BASELINE' AND "asof" > '2022-06-27 12:00:00', "deduped_baseline_plus_delta" AS (SELECT DISTINCT *
FROM "baseline_plus_delta"), "current_active_ids" AS (SELECT "instrument_id" AS "instrument_id", MAX("asof") AS "asof", MAX("created_at") AS "created_at"
FROM "deduped_baseline_plus_delta"
WHERE "op_type" <> 'DELETE'
GROUP BY "instrument_id"), "markit_mappings_deduped" AS (SELECT *
FROM "zais data mart (dev)"."mart"."application"."loanz"."markit_mapping_moodys"), "final" AS (SELECT "i".*, "m".*
FROM "current_active_ids" AS "c"
INNER JOIN "deduped_baseline_plus_delta" AS "i" ON "i"."instrument_id" = "c"."instrument_id" AND "i"."asof" = "c"."asof" AND "i"."created_at" = "c"."created_at"
INNER JOIN "markit_mappings_deduped" AS "m" ON "c"."instrument_id" = "m"."provider_id") (SELECT *
FROM "final")
startLine 1
startColumn 31
endLine 1
endColumn 36
SQL Query WITH "baseline_plus_delta" AS SELECT *
FROM "zais data mart (dev)"."staging"."ratings"."moodys"."stg_instrument"
WHERE "op_type" = 'BASELINE' AND "asof" = '2022-06-27 12:00:00'
UNION ALL
SELECT *
FROM "zais data mart (dev)"."staging"."ratings"."moodys"."stg_instrument"
WHERE "op_type" <> 'BASELINE' AND "asof" > '2022-06-27 12:00:00', "deduped_baseline_plus_delta" AS (SELECT DISTINCT *
FROM "baseline_plus_delta"), "current_active_ids" AS (SELECT "instrument_id" AS "instrument_id", MAX("asof") AS "asof", MAX("created_at") AS "created_at"
FROM "deduped_baseline_plus_delta"
WHERE "op_type" <> 'DELETE'
GROUP BY "instrument_id"), "markit_mappings_deduped" AS (SELECT *
FROM "zais data mart (dev)"."mart"."application"."loanz"."markit_mapping_moodys"), "final" AS (SELECT "i".*, "m".*
FROM "current_active_ids" AS "c"
INNER JOIN "deduped_baseline_plus_delta" AS "i" ON "i"."instrument_id" = "c"."instrument_id" AND "i"."asof" = "c"."asof" AND "i"."created_at" = "c"."created_at"
INNER JOIN "markit_mappings_deduped" AS "m" ON "c"."instrument_id" = "m"."provider_id") (SELECT *
FROM "final")

It looks like when parsing it is dropping the initial ( after the WITH statement. If I remove the create/replace part of the statement the query runs fine. It just doesn’t work when wrapped in a create/replace view statement.

We are using onprem Dremio version 22.0.0-202206221430090603-1fa4049f

Thanks for your help!

2

After some debugging, we found that the UNION ALL statement seems to cause the Dremio SQL parser to incorrectly parse which drops the leading ( from the WITH statement.

Still doing some investigation, but that is probably what is going on.

3

@drew I have seen similar issues, you are absolutely right, saving the SQL via “Save View As” would work. I will report this internally and see what is going on

4

@drew Are you able to send us the profile so we can try to reproduce the issue

5

43242e78-6bb7-4353-b1bf-76e1a9230cb7.zip (18.7 KB)
See attached profile. Thanks for your help