Adding dialect specific syntax variations

[prabhur]

Thanks for creating this library! It's been lot of fun working with it. The SDK is very simple. With an IDE, it is so much easy to add custom functionality. Much appreciated.

I'm trying to figure out ways I can add support for a situation like below. There is this function called FIRST_VALUE that is available in both Snowflake and sqlite. There is slight variation in syntax for both.

Snowflake

FIRST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ] OVER (
    [ PARTITION BY <expr1> ]
    ORDER BY <expr2>  [ { ASC | DESC } ]
    [ <window_frame> ]
)

sqlite

FIRST_VALUE(expression) OVER (
    PARTITION BY expression1, expression2,...
    ORDER BY expression1 [ASC | DESC], expression2,..
    frame_clause
)

The optional, [ { IGNORE | RESPECT } NULLS ] keywords seem specific to Snowflake.

How do I go about this?

[tobymao]

you should add ignore | respect nulls to the generic parser, and for the sqlite output, remove that

there are various examples of the parser being quite generic but the generator having options.

[prabhur]

Thank you!

[prabhur]

@tobymao - What is your general suggestion around syntax checking?
For example, in the above scenario, making the generator drop / ignore the "respect | ignore" tokens means that, the "sqlite" parser allows the keyword and even builds a tree.

Is this ok?

For now, I have been able to make a small change to _parse_window and Generator.window_sql methods to achieve what I want. However, had this question around how to deal with situations where a SQL statement, for whatever reasons, has a keyword that the parser allows but not valid inside the actual datastore.

there are various examples of the parser being quite generic but the generator having options.

Would you mind pointing me to one?

[tobymao]

yes, sqlglot does not need to be strict on the input, the guarantee is just that the output is correct

grep for unsupported to find cases where things are not supported