Parsing error on Duckdb QUALIFY

[bgarcevic]

This ticket requests several things. Checked when they are done

• Union all by name: https://duckdb.org/docs/sql/query_syntax/setops#union-all-by-name
• Pivot: https://duckdb.org/docs/sql/statements/pivot
• Exclude: https://duckdb.org/docs/sql/expressions/star.html#exclude-clause
• Replace: https://duckdb.org/docs/sql/functions/char.html
• Qualify: https://duckdb.org/docs/sql/query_syntax/qualify.html
• // is integer division: https://duckdb.org/docs/sql/functions/numeric.html

Search before asking

• I searched the issues and found no similar issues.

What Happened

This is a valid duckdb query

with

source as (

    select * from {{ source('danish_parliament', 'raw_aktoer_type') }}
    qualify row_number() over (partition by id order by opdateringsdato desc) = 1
),

renamed as (

    select
        id as actor_type_id,
        type as actor_type,
        opdateringsdato as updated_at
    from source

)

select * from renamed

I get an unparseble error using both dbt and jinja templater.

Expected Behaviour

Should be able to be parsed and not return error

Observed Behaviour

sqlfluff lint models\staging\stg_actor_types.sql
=== [dbt templater] Sorting Nodes...
20:35:22  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 1 unused configuration paths:
- models.danish_democracy_data.intermediate
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
== [models\staging\stg_actor_types.sql] FAIL
L:   6 | P:   5 |  PRS | Line 6, Position 5: Found unparsable section: 'qualify
                       | row_number() over (partition by ...'

How to reproduce

1. save the sample sql into file example.sql
2. use the sample .sqlfluff file
3. Run sqlfluff lint against example.sql

Dialect

DuckDB

Version

sqlfluff, version 2.2.0

Configuration

.sqlfluff

[sqlfluff]
dialect = duckdb
templater = dbt
runaway_limit = 10
max_line_length = 80
indent_unit = space
exclude_rules = RF05

[sqlfluff:indentation]
tab_space_size = 4

[sqlfluff:layout:type:comma]
spacing_before = touch
line_position = trailing

[sqlfluff:rules:capitalisation.keywords] 
capitalisation_policy = lower

[sqlfluff:rules:aliasing.table]
aliasing = explicit

[sqlfluff:rules:aliasing.column]
aliasing = explicit

[sqlfluff:rules:aliasing.expression]
allow_scalar = False

[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower

[sqlfluff:rules:ambiguous.column_references]  # Number in group by
group_by_and_order_by_style = implicit

.sqlfluffignore

reports
target
dbt_packages
macros
extract-load
.dbtenv
.vscode
logs

Are you willing to work on and submit a PR to address the issue?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[droher]

There are a number of newer DuckDB syntax features which cause parsing errors - the ones I know of are UNION ALL BY NAME, PIVOT, EXCLUDE, REPLACE, and //. Would it be better to include them in this ticket, open up a new ticket for these five, or open up new tickets for each one?

[alanmcruickshank]

I think they can all go on the same issue, but if you're able to link to the DuckDB docs for each syntax element that will make it easier for someone to pick them up either as a batch or individually.

…

On Thu, 10 Aug 2023, 18:47 David Roher, ***@***.***> wrote: There are a number of newer DuckDB syntax features which cause parsing errors - the ones I know of are UNION ALL BY NAME, PIVOT, EXCLUDE, and REPLACE. Would it be better to include them in this ticket, open up a new ticket for these four, or open up new tickets for each one? — Reply to this email directly, view it on GitHub <#5051 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABDULOGMPWP2YRO3GWPTXUTXUUNCPANCNFSM6AAAAAA3EVUFMY> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[bgarcevic]

Here are the links i could for some of the features @droher mentioned:

• Union all by name: https://duckdb.org/docs/sql/query_syntax/setops#union-all-by-name
• Pivot: https://duckdb.org/docs/sql/statements/pivot
• Exclude: https://duckdb.org/docs/sql/expressions/star.html#exclude-clause
• Replace: https://duckdb.org/docs/sql/functions/char.html
• Qualify: https://duckdb.org/docs/sql/query_syntax/qualify.html

I could not find the // in the docs. Is that a comment thing?

Should I add them to this issue or create a new?

[droher]

// is integer division: https://duckdb.org/docs/sql/functions/numeric.html

[keraion]

DuckDB supports a from-first syntax which allows the FROM clause to be stated either without or in front of a SELECT clause. https://duckdb.org/docs/archive/0.9.2/sql/query_syntax/from