Support for SNOWFLAKE specific query grammer

[ghost]

Can we support the following type of query : select FIRST_VALUE (bc.merch_l1_name [IGNORE NULLS]) over (partition by sku, channel_id order by feed_date desc) as merch_l1_name, FIRST_VALUE (bc.brand [IGNORE NULLS]) over (partition by sku, channel_id order by feed_date desc) as brand from temp.abc bc

Im currently on version 0.9.5.

[wumpz]

I guess the [IGNORE NULLS] is the problem. Is this some kind of option? The main problem I see is, that JSqlParser supports SqlServer quotation using square brackets. This would collide within the grammar.

[bobtiernay-okta]

There several statements for which don't parse. Some examples include:

USE DATABASE DV1_REPORTS
...
USE ROLE SYSADMIN
...
CREATE OR REPLACE SCHEMA DV1_REPORTS.STAGING
...
CREATE OR REPLACE WAREHOUSE DV1_ELT_WH
    WITH WAREHOUSE_SIZE = 'XSMALL'
    WAREHOUSE_TYPE = 'STANDARD'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE
    COMMENT = '...'
...
GRANT INSERT, UPDATE, DELETE on FUTURE TABLES IN SCHEMA DV1_REPORTS.STAGING TO ROLE DV1_REPORTS_STAGING_WRITER
...
GRANT USAGE ON DATABASE DV1_REPORTS TO ROLE DV1_REPORTS_STAGING_WRITER
...

I'm wondering if it is reasonable to add such support to this library, or if it would be too much of a lift. Full (informal) syntax here.

[manticore-projects]

Greetings.

It was definitely possible to add these statements above and it was not even very difficult as the statements have no recursion or nesting. If you are really interested in getting full Snowflake coverage you may

a) add these statements by yourself and send PRs (which is not difficult, you may want to check the PR for the Oracle AlterSession statement for getting an impression. I also would lend you a helping hand.)

b) or sponsor the work an place a bounty

[manticore-projects]

Original issue about support of { IGNORE | RESPECT } NULLS has been resolved in JSQLParser 4.10-Snapshot and you can test it online here