glue_sql converts length-0 vectors to NULL in asterisk-expansion

[mmuurr]

x <- character(0)
glue::glue_sql("{x*}")
# <SQL> NULL

When used as part of an SQL expression, this can happen:

glue::glue_sql("select foo from bar where baz in ({x*})")
# <SQL> select foo from bar where baz in (NULL)

... which has different semantics (in most SQL variants) than what I believe most users likely are intending when x is empty:

# <SQL> select foo from bar where baz in ()  <-- the _intended_ SQL, I think.

I've confirmed this also happens with DB object identified expansion, too, i.e.:

"{`x`*}"
# <SQL> NULL

[hadley]

x <- character(0)
glue::glue_sql("({x*})")
#> <SQL> (NULL)

^{Created on 2023-01-25 with reprex v2.0.2}

[jasonheffnerpsu]

Post upgrade to glue version 1.7.0, we encountered a breaking change in how glue_sql handles empty vectors. Previously, empty vectors were collapsed to "NULL" in SQL queries, which was functional for our use case. However, since the update, empty vectors are now collapsed to an empty string, resulting in syntax errors in SQL queries with IN clauses.

This change impacts all database queries where we dynamically generate IN clauses based on possibly empty vectors. The new behavior leads to malformed SQL queries and runtime errors, significantly affecting our data retrieval processes.

Could we consider reintroducing the previous behavior of collapsing empty vectors to "NULL" in SQL queries, or provide an option to toggle between the two behaviors? This would allow for backward compatibility and flexibility for different use cases.

We've implemented a workaround where we manually check for empty vectors and adjust the SQL query accordingly. However, this adds complexity and boilerplate code, which was elegantly handled by glue_sql in previous versions.

[hadley]

@jasonheffnerpsu can you please file an new issue instead of commenting on a closed issue?

[jasonheffnerpsu]

Apologies .. I don't raise issues often and don't know process

[peterdesmet]

Sorry, did the same (commenting on a closed issue). I will report my use case at #318.