Queries with empty arrays are broken

[mirnovov]

The array interpolation feature usually works great, but when given an empty array, it fails.

const a = new SQLiteTag(':memory:', { readonly: true, persistent: true });
const b = await a.all`SELECT * FROM (VALUES (1), (2)) WHERE column1 NOT IN (${[-1]})`;
console.log(b);

correctly returns [ { column1: 1 }, { column1: 2 } ] but

const a = new SQLiteTag(':memory:', { readonly: true, persistent: true });
const b = await a.all`SELECT * FROM (VALUES (1), (2)) WHERE column1 NOT IN (${[]})`;
console.log(b);

either produces an error or returns nothing.

This is because your dependency static-params doesn't return a value for empty arrays when converting JS format strings to SQL parameters. Your code in utils.js assumes that the amount of statement fragments is always v+1, where v is the amount of substituted values. But since the SQL statement is still split at an empty array but no value is provided, this assumption is incorrect.

A possible solution would be to specifically account for empty arrays by returning a value of '', as I have done in my project that bundles sqlite-tag-spawned. I'm unsure if this would break other things that rely on static-params though.

[WebReflection]

I think you are not fully understanding how template literals work (apologies this was indeed a meant feature) ... you never pass an array, you just pass interpolations:

const b = await a.all`SELECT * FROM (VALUES (1), (2)) WHERE column1 NOT IN (${-1})`;

accordingly, your second query is invalid SQL syntax because you are not passing a value.

[WebReflection]

to clarify, with template literal tags the template is always v+1 length as defined by specs ... please read again the README and the examples, in no circumstances you pass a complex object as interpolation.

[WebReflection]

actually ... never mind, I indeed added the ability to spread lists ... but still, your SQL looks invalid, apologies for the confusion ... how would you write that statement in raw SQL?

[mirnovov]

SELECT * FROM (VALUES (1), (2)) WHERE column1 NOT IN (-1)

SELECT * FROM (VALUES (1), (2)) WHERE column1 NOT IN ()

I have tested all of these SQL statements by directly typing the expected values into SQLite and it works fine; it is only when using your library that empty arrays fail.

[mirnovov]

https://www.sqlite.org/lang_expr.html#booleanexpr

Note that SQLite allows the parenthesized list of scalar values on the right-hand side of an IN or NOT IN operator to be an empty list but most other SQL database engines and the SQL92 standard require the list to contain at least one element.

Given that is an SQLite-specific library I did expect empty arrays to work, and it'd be nice to have this feature. But given the static-params dependency is for SQL in general (where such statements are usually not allowed) I understand if you don't want to add it, it's only a one-line code fix on my end after all.

[WebReflection]

I think I want that to work in here too ... why not filing a PR? 😄

[mirnovov]

I've opened one in your static-params repo since that's where the query parameters are parsed; it'd be hard to include a fix in this repo given how you've designed things.

[WebReflection]

sure and ... thanks! I'll do the update dance right after that gets merged

[mirnovov]

Thanks!