Nil doesn't generate '?' for :value* lists

[timds]

On hugsql 0.7.5:

-- :name q
select * from t where id in (:v*:ids);
...
(q-sqlvec {:ids nil}) => ["select * from t where id in ()"] ;; not ["...where id in (?)" nil]

Is this behaviour intentional and desirable? In postgresql at least, you can legally write IN (NULL). In yesql, where value lists are treated the same as other values, a nil on a parameter destined for an IN-list would still generate a '?' placeholder with a nil value.

I realise that (NULL) is a list of one item, not a nil list, so I see why this could be considered one nil pun too far. We have some code to migrate from yesql which uses this oddity, and I'd like to know whether this should be considered a bug in our code or in the hugsql library. If the latter, would you accept a pull request changing this?

[csummers]

one nil pun too far

Yes, that might be what makes me a bit uncomfortable about this, though I definitely see the use case here. While IN (NULL) is perfectly valid SQL, it is confusing SQL because of the treatment of null.

For now, a solution to handle the nil and empty list is to use a Clojure expression:

-- :name q
select * from t where id in (/*~ (if (seq (:ids params)) ":v*:ids" "null") ~*/);
...
(q-sqlvec {:ids nil}) =>["select * from t where id in (null)"]

You could also use the Clojure expression to omit the id in clause all together.

I'll keep mulling this over, and also look into the behavior of more than just Postgresql. I know that IN () is not valid SQL in Postgresql, but I'm curious as to other databases' behaviors.

[csummers]

Closing this. I think, while a bit annoying, the work-around for this case is reasonable.