BigQuery Dialect - Qualify Usage Issue

[jrbangit]

I am using ZetaSQL to parse queries executed from BigQuery. I came across this scenario. Apparently, in BigQuery, when you use the QUALIFY function, whether you use it with or without a where clause, it is gonna push through. However, if you parse that query in ZetaSQL and the query utilizes QUALIFY function without the WHERE clause, it's gonna fail to parse it.

com.google.zetasql.io.grpc.StatusRuntimeException: INVALID_ARGUMENT: QUALIFY clause must be used in conjunction with WHERE or GROUP BY or HAVING clause

is this an expected scenario? which in case of BigQuery dialect, we will have to create a work around or adding WHERE 1=1 or something? just to add context, the queries we are parsing are from audit logs.

sample query:

CREATE OR REPLACE TABLE
  `test_project`.`test_dataset`.`test_target_table`
PARTITION BY
  TIMESTAMP_TRUNC(test_datetime, day) AS (
  WITH
    test AS (
    SELECT
      *
    FROM
      `test_project`.`test_dataset`.`test_source_table`
    QUALIFY ROW_NUMBER() OVER(PARTITION BY test_col1, test_col2 ORDER BY test_datetime ASC) = 1 )
  SELECT
    *
  FROM
    test );

[pgrivachev]

Here is something that can help: https://github.com/google/zetasql/issues/115.

[jrbangit]

thank you for the response @pgrivachev - I forgot to mention, I already had this enabled ZetaSQLOptions.LanguageFeature.FEATURE_V_1_3_QUALIFY). However, as for the second part, I'm not quite sure how do we add reserved keywords for java?

[sridivakar]

@jrbangit , you can try this way :

languageOptions.enableReservableKeyword("QUALIFY");