taozhi8833998/node-sql-parser

support QUALIFY clause in Snowflake SQL

Closed this issue · 0 comments

Describe the bug
SQL statements using the QUALIFY clause throw an error

Database Engine
Snowflake

To Reproduce
node: 18.20.1
node-sql-parser: 5.0.0

import { Parser } from "node-sql-parser"

const parser = new Parser()
const opts = { database: "Snowflake" }

const query = `
SELECT i, p, o
    FROM qt
    QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1;
`

const { ast } = parser.parse(query, opts)

Expected behavior
This should be valid Snowflake SQL and should not throw an error

Additional context

In a SELECT statement, the QUALIFY clause filters the results of window functions.

QUALIFY does with window functions what HAVING does with aggregate functions and GROUP BY clauses.

In the execution order of a query, QUALIFY is therefore evaluated after window functions are computed. 
Typically, a SELECT statement’s clauses are evaluated in the order shown below:

From
Where
Group by
Having
Window
QUALIFY
Distinct
Order by
Limit