DBeaver incorrectly gives syntax error highlighting

[ufuk-ergin-carbon]

Description

The syntax highlighting is incorrectly red even though all the references are fine and the query runs just fine. Attaching the screenshot.

DBeaver Version

Version 24.0.4.202405051837

Operating System

MacOs Sonoma 14.4.1 (23E224)

Database and driver

Snowflake Server: Snowflake 8.16.10
Driver: Snowflake 3.14.4

Steps to reproduce

No response

Additional context

No response

[ShadelessFox]

Hello @ufuk-ergin-carbon,

Please provide the mentioned script and the table's DDL so we can reproduce the issue. Thanks!

[ufuk-ergin-carbon]

Hi @ShadelessFox , here is the script file. It has multiple tables. Do you need the DDL of all the joined tables?

WITH parent_child_relation AS (
    SELECT
        t2.objectid,
        t1.parentobjectid,
        t1.parentobjecttype
    FROM ANALYTICS_PROD_DB.ANALYTICS_AUDITTRAIL.AUDIT_TRAILS_HIERARCHY AS t1
    INNER JOIN ANALYTICS_PROD_DB.ANALYTICS_AUDITTRAIL.AUDIT_TRAILS_HIERARCHY AS t2
        ON t1.objectid = t2.parentobjectid
)
SELECT
    CASE
        WHEN a.objecttype = 1 THEN a.objectid
        WHEN a.objecttype = 2 THEN level_1.parentobjectid
        WHEN a.objecttype = 3 THEN level_1.parentobjectid
        WHEN a.objecttype = 4 THEN level_2.parentobjectid
        WHEN a.objecttype = 5 THEN level_2.parentobjectid
        WHEN a.objecttype = 6 THEN level_1.parentobjectid
        WHEN a.objecttype = 7 THEN NULL
        WHEN a.objecttype = 15 THEN NULL
        WHEN a.objecttype = 24 THEN level_2.parentobjectid
        WHEN a.objecttype = 25 THEN level_1.parentobjectid
        WHEN a.objecttype = 26 THEN level_1.parentobjectid
        WHEN a.objecttype = 27 THEN level_1.parentobjectid
        WHEN a.objecttype = 28 THEN NULL
    END AS source_bill_id,
    a.id,
    a.actiontime AS action_time_utc,
    to_date(convert_timezone(coalesce(dc.timezone, 'America/Los_Angeles'), a.actiontime)) AS adjusted_user_date,
    a.actiontype AS action_type,
    a.actortype AS actor_type,
    a.actorid AS actor_id,
    dc.doctor_id AS doctor_id,
    dc.practice_name,
    a.objectid AS object_id,
    a.objecttype AS object_type_id,
    CASE
        WHEN a.objecttype = 1 THEN 'Bill'
        WHEN a.objecttype = 2 THEN 'Billable'
        WHEN a.objecttype = 3 THEN 'Payment Report'
        WHEN a.objecttype = 4 THEN 'Payment Report Item'
        WHEN a.objecttype = 5 THEN 'Billable Split'
        WHEN a.objecttype = 6 THEN 'Billable Notes'
        WHEN a.objecttype = 7 THEN 'Payment Report Item Adjustment'
        WHEN a.objecttype = 15 THEN 'Insurance Coverage'
        WHEN a.objecttype = 24 THEN 'Refunds'
        WHEN a.objecttype = 25 THEN 'Payments'
        WHEN a.objecttype = 26 THEN 'WriteOffs'
        WHEN a.objecttype = 27 THEN 'Claim Submission'
        WHEN a.objecttype = 28 THEN 'Queued Bills'
    END AS object_type,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):billId) = true THEN null
        ELSE try_parse_json(a.changes):billId::STRING
    END AS bill_id,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):reviewed) = true THEN null
        ELSE try_parse_json(a.changes):reviewed::STRING
    END AS reviewed_at,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):reviewedBy) = true THEN null
        ELSE try_parse_json(a.changes):reviewedBy::STRING
    END AS reviewed_by,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):insuranceCoverageId) = true THEN null
        ELSE try_parse_json(a.changes):insuranceCoverageId::STRING
    END AS insurance_coverage_id,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):secondaryInsuranceCoverageId) = true THEN null
        ELSE try_parse_json(a.changes):secondaryInsuranceCoverageId::STRING
    END AS secondary_insurance_coverage_id,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):tertiaryInsuranceCoverageId) = true THEN null
        ELSE try_parse_json(a.changes):tertiaryInsuranceCoverageId::STRING
    END AS tertiary_insurance_coverage_id,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):organizationId) = true THEN null
        ELSE try_parse_json(a.changes):organizationId::STRING
    END AS organization_id,
    coalesce(try_parse_json(a.changes):organizationId::STRING IS NOT NULL OR is_null_value(try_parse_json(a.changes):organizationId) = false, false) AS is_organization_responsibility,
    coalesce(try_parse_json(a.changes):insuranceCoverageId::STRING IS NOT NULL OR is_null_value(try_parse_json(a.changes):insuranceCoverageId) = false, false) AS is_insurance_responsibility,
    coalesce((is_null_value(try_parse_json(a.changes):insuranceCoverageId) = true OR try_parse_json(a.changes):insuranceCoverageId IS NULL) AND (is_null_value(try_parse_json(a.changes):organizationId) = true OR try_parse_json(a.changes):organizationId IS NULL), false) AS is_patient_responsibility,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):invoiceSentTime) = true THEN null
        ELSE try_parse_json(a.changes):invoiceSentTime::STRING
    END AS invoice_sent_time,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):status) = true THEN null
        ELSE try_parse_json(a.changes):status::STRING
    END AS status,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):createdAt) = true THEN null
        ELSE try_parse_json(a.changes):createdAt::STRING
    END AS created_at,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):deleted) = true THEN null
        ELSE try_parse_json(a.changes):deleted::STRING
    END AS deleted_at,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):frequencyCode) = true THEN null
        ELSE try_parse_json(a.changes):frequencyCode::STRING
    END AS frequency_code,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):text) = true THEN null
        ELSE try_parse_json(a.changes):text::STRING
    END AS changes_text,
    CASE
        WHEN is_null_value(try_parse_json(a.changes):queue_id) = true THEN null
        ELSE try_parse_json(a.changes):queue_id::STRING
    END AS queue_id,
    a.changes AS audit_trails_change,
    q."TYPE" AS queues_type
FROM ANALYTICS_PROD_DB.ANALYTICS_AUDITTRAIL.AUDIT_TRAILS AS a
LEFT JOIN analytics_prod_db.analytics.stg_doctors AS dc
    ON a.actorid = dc.user_id
LEFT JOIN ANALYTICS_PROD_DB.ANALYTICS_BILLING_HUB.QUEUES AS q
    ON
        q.id = try_parse_json(a.changes):queue_id::STRING
        AND q.type IN ('DEFERRAL', 'ESCALATION')
LEFT JOIN ANALYTICS_PROD_DB.ANALYTICS_AUDITTRAIL.AUDIT_TRAILS_HIERARCHY AS level_1
    ON
        level_1.objectid = a.objectid
        AND level_1.parentobjecttype = 1
LEFT JOIN parent_child_relation AS level_2
    ON
        level_2.objectid = a.objectid
        AND level_2.parentobjecttype = 1
WHERE
    a.objecttype IN (1, 2, 3, 4, 5, 6, 7, 15, 24, 25, 26, 27, 28)
    AND a.actortype = 'U'
;

[ShadelessFox]

Thank you for your quick answer. We will try reproducing the issue without the DDL; the query analysis probably failed due to the script's complexity, so it shouldn't depend on the schema.

Can you also provide log files? We might find something helpful there: https://github.com/dbeaver/dbeaver/wiki/Log-files#log-files

[ufuk-ergin-carbon]

Sure. Here are the log files. Thank you for your analysis.

dbeaver-debug-1715096569377.log
dbeaver-debug-1715324262819.log
dbeaver-debug-1715345341257.log
dbeaver-debug.log

[ged-yuko]

Taking a look at the parser's grammar used in dbeaver, while it parses typecast of the form expr::typename, it doesn't handle expr:something::typename. Which is also easily discoverable by observing where the identifiers stop being highlighted in this select statement. So its about the support of the json-related syntax constructs apparently.

[ShadelessFox]

@ged-yuko please create a separate issue and provide samples and other useful details there.

[ged-yuko]

@ged-yuko please create a separate issue and provide samples and other useful details there.

Snowflake documentation demonstrates expressions of the expr:something::typename form, the lack of support for which is the reason behind the behavior found by @ufuk-ergin-carbon

Here some offtopic follows:
Looks like there are also inline XPath-expressions and a FLATTEN function with a strange lambda-like arguments in Snowflake, and then there is more. These pieces doesn't really needed until you are targeting to cover all the SQL extensions provided by all the databases (or at least complete syntax of some DBs). I also don't think you would really like to implement a complete XPath handling for your semantic analysis, so another issue doesn't worth it. Otherwise it would require appropriate investigation of the SQL extensions and corresponding planning. Maybe even modular parsing, or at least modular grammars, which you apparently decided not to use at all, while different DBs have pretty different syntax extensions.

[E1izabeth]

Thank you for report