Feature: support transpilation of GREATEST from BigQuery to DuckDB

[fivetran-felixhuang]

For the GREATEST function

In BigQuery:
- if any argument is NULL, the result is NULL
In DuckDB:
- NULLs are ignored, returns greatest non-NULL value.

The workaround here is to make sure that the transpiled DuckDB query returns NULL is any argument is NULL

BigQuery: GREATEST(1, 2, NULL, 3) -> DuckDB: CASE WHEN 1 IS NULL OR 2 IS NULL OR NULL IS NULL OR 3 IS NULL THEN NULL ELSE GREATEST(1, 2, NULL, 3) END

[geooo109]

Can we also add tests for this ?

[fivetran-felixhuang]

Can we also add tests for this ?

@geooo109 added some tests, please let me know if they are sufficient

[georgesittas]

is greatest null / nan handling preserved for bigquery -> duckdb? perhaps least should be supported in a follow-up pr

[geooo109]

So, @fivetran-felixhuang the LEAST + NaN seems a bug for duckdb (nice finding). We will confirm it.
For now, It's safer to make a separate PR for LEAST as @georgesittas suggested.
As a result, lets keep the build_greatest as is. Just remove the test from transpile.py file.

[fivetran-felixhuang]

is greatest null / nan handling preserved for bigquery -> duckdb? perhaps least should be supported in a follow-up pr

For GREATEST, the NaN handling is the same for both the BigQuery and DuckDB, but NULL handling is not. This PR is to address the NULL handling issue.

I will work on the NaN handling issue for LEAST in a different PR

[georgesittas]

Just one last comment, I don't think we need _build_greatest for DuckDB.

[georgesittas]

We don't need this right? It should be parsed just fine out of the box.

[fivetran-felixhuang]

I think we do. When I removed, the arguments were not parsed correctly for some reason....

[georgesittas]

2c462a8

[georgesittas]

It's due to is_var_len_args. Without doing what I did in parser.py, we'd break the AST by spilling args[2:] to null_if_any_null, which is not the right representation.

[fivetran-felixhuang]

I see, thanks!

[georgesittas]

@fivetran-felixhuang let's not worry about nan for now, I've reached out to the duckdb community to see if this is a bug instead of a feature. We can revisit later.