[bigquery] SPLIT(BYTE) is incorrectly roundtripped

[erindru]

From the BigQuery docs for SPLIT:

For STRING, the default delimiter is the comma ,.
For BYTES, you must specify a delimiter.

For a STRING input, SQLGlot injects the delimiter as ',' since this lines up with the BQ default:

>>> parse_one("SELECT SPLIT('foo,bar') AS a", dialect="bigquery").sql(dialect="bigquery")
"SELECT SPLIT('foo,bar', ',') AS a"

This works fine.

However, for a BYTES input, SQLGlot still injects the same STRING delimiter:

>>> parse_one("SELECT SPLIT(b'foo,bar') AS a", dialect="bigquery").sql(dialect="bigquery")
"SELECT SPLIT(b'foo,bar', ',') AS a"

This causes BigQuery to throw an error:

No matching signature for function SPLIT Argument types: BYTES, STRING Signature: SPLIT(STRING, [STRING]) Argument 1: Unable to coerce type BYTES to expected type STRING Signature: SPLIT(BYTES, BYTES) Argument 2: Unable to coerce type STRING to expected type BYTES at [1:8]

I think there are two options here:

• Follow the BQ docs and don't infer a delimiter for a BYTES input if one isn't specified
• or, if the input is BYTES, infer the delimiter as b',' instead of ','

[erindru]

STRING_AGG has an almost identical problem:

>>> parse_one("SELECT STRING_AGG(a) FROM UNNEST([b'foo',b'bar',b'baz']) as a", dialect="bigquery").sql(dialect="bigquery")
"SELECT STRING_AGG(a, ',') FROM UNNEST([b'foo', b'bar', b'baz']) AS a"

Which produces the following error:

No matching signature for aggregate function STRING_AGG Argument types: BYTES, STRING Signature: STRING_AGG(STRING) Signature accepts at most 1 argument, found 2 arguments Signature: STRING_AGG(STRING, STRING) Argument 1: Unable to coerce type BYTES to expected type STRING Signature: STRING_AGG(BYTES) Signature accepts at most 1 argument, found 2 arguments Signature: STRING_AGG(BYTES, BYTES) Argument 2: Unable to coerce type STRING to expected type BYTES at [1:8]

In this case, injecting the delimiter could be considered correct, because the BQ Docs say that it defaults to , and if you omit the delimiter the call still works (unlike SPLIT).

So if SQLGlot is going to make this explicit, it should generate it as b',' instead of ','

[VaggelisD]

Nice finds Erin! I guess there's 2 ways of going about it:

a) Undoing the delimiter canonicalizations (7 total broken tests):

• For SPLIT we add the separator at parse time in bigquery.py (PR)
• For STRING_AGG we add it at generation time in dialect.py::groupconcat_sql (PR)

b) Keeping the canonicalizations for transpilation purposes and attempting to fix this issue at generation time through type annotation

I believe (a) is both safer and simple, though sadly a "regression" in our smartness.

[georgesittas]

@erindru regarding SPLIT, I think we're fine. I verified this snippet from the docs you shared:

For BYTES, you must specify a delimiter.

(sqlglot) ➜  sqlglot git:(main) bq query "select split(b'b,c,d')"
Error in query string: ... No matching signature for function SPLIT
Argument types: BYTES
Signature: SPLIT(STRING, [STRING])
Argument 1: Unable to coerce type BYTES to expected type STRING
Signature: SPLIT(BYTES, BYTES)
Signature requires at least 2 arguments, found 1 argument at [1:8]

So the query you used to test this, i.e., SPLIT(b'foo,bar'), is invalid in BigQuery, and the usual garbage in -> garbage out rule ensues. If there's no delimiter, then we can safely assume that the input is a string value.

[erindru]

the usual garbage in -> garbage out rule ensues

Oh yeah, duh! For some reason I didn't click that the query was invalid to begin with, but when you point it out it makes perfect sense 👍