google / google/googlesql

BigQuery Dialect - Qualify Usage Issue

Open
#124 3 comments 0 reactions 0 assignees View on GitHub
Dominant language
C++
Stars
2.6k
Forks
260
PR merge metrics
No merged PRs in 30d

Description

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 );

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.