Empty value list with IN, using MySQL, gives invalid syntax

[owickstrom]

Hi!

I have a query like this (changed some names for this example):

-- :name update-young-people-coolness :! :n
UPDATE people
       SET cool = CASE
           WHEN id IN (:v*:cool_people) THEN TRUE
           ELSE FALSE
       END
WHERE age < 18;

If I use this with an empty sequence, together with MySQL, I get a syntax error:

> (update-young-people-coolness db-spec {:cool_people []})

Error:

MySQLSyntaxErrorException You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') THEN TRUE
           ELSE FALSE
       END
WHERE age < 18' at line 3  sun.reflect.NativeConstructorAccessorImpl.newInstance0 (NativeConstructorAccessorImpl.java:-2)

I'm not entirely sure, but it seems the empty value list causes a syntax error in MySQL. This is temporarily bypassed by checking the value list before running the query, and falling back to another query if it's an empty seq. I guess this might be something HugSQL could (and should?) handle, as the documentation says:

The * indicates a sequence of zero or more values.

Thanks!

[csummers]

@owickstrom HugSQL tries not to guess what might be valid SQL surrounding HugSQL parameters for the very reason that in some dialects there are valid uses of an empty list of values. For instance, Postgresql arrays can have a list of values or be empty:

select array['a','b','c']::text;  -- some items
select array[]::text[]; -- no items

However, HugSQL Clojure Expressions allow you to conditionally check for an empty list and modify your SQL query accordingly. See the Clojure Expression section of the docs for reference.

[owickstrom]

OK, thanks! :)