Differences between IN queries between v1 and v2

[austb]

Hi, I'm in the process of upgrading from honeysql v1 to v2. We make use of a lot of queries with IN expressions, and I'm curious if this change is intentional between the two versions of honeysql, and if so, if I have the proper workaround.

=> (require '[honeysql.core :as hsqlv1])
=> (require '[honey.sql :as hsqlv2])

=> (def in-query {:select [:foo] :from [:bar] :where [:in [:bar :baz] {:select [:bar :baz] :from [:oof]}]})

=> (hsqlv1/format in-query)
["SELECT foo FROM bar WHERE ((bar, baz) in (SELECT bar, baz FROM oof))"]

In v1 it works in the way that we intended, creating an IN statement using two columns. However in hsql v2 it turns the columns into a function call. Based on some of the docs, I thought that function calls required to be doubly nest, like [[:d :e]] from the migration doc?

=> (hsqlv2/format in-query)
["SELECT foo FROM bar WHERE BAR(baz) IN (SELECT bar, baz FROM oof)"]

I have found a workaround, by adding :inline wrappers to the columns, I can get the query that I want. Is this the proper way to construct this type of query?

=> (def inlined-query {:select [:foo] :from [:bar] :where [:in [[:inline :bar] [:inline :baz]] {:select [:bar :baz] :from [:oof]}]})
=> (hsqlv2/format inlined-query)
["SELECT foo FROM bar WHERE (bar, baz) IN (SELECT bar, baz FROM oof)"]

It also affects single column queries

=> (def single-column-query {:select [:foo] :from [:bar] :where [:in [:bar] {:select [:bar] :from [:oof]}]})
=> (hsqlv2/format single-column-query)
["SELECT foo FROM bar WHERE BAR() IN (SELECT bar FROM oof)"]

but those have a simpler workaround of just omitting the square brackets all together

=> (def single-column-query {:select [:foo] :from [:bar] :where [:in :bar {:select [:bar] :from [:oof]}]})
=> (hsqlv2/format single-column-query)
["SELECT foo FROM bar WHERE bar IN (SELECT bar FROM oof)"]

[seancorfield]

Interesting. I had no idea that multi-column IN expressions were legal SQL nor that HoneySQL (v1) used to produce them correctly.

The "doubly nest" for function calls is only in SELECT (and similar contexts that support aliasing) -- purely to distinguish between [:a :b] meaning a AS b and [[:a b]] meaning A(b) -- the full form is [[:a :b] :c] meaning A(b) AS c but the alias can be omitted.

The v2 way to get a tuple of columns is using :composite:

dev=> (sql/format {:select [:foo] :from [:bar] :where [:in [:composite :bar :baz] {:select [:bar :baz] :from [:oof]}]})
["SELECT foo FROM bar WHERE (bar, baz) IN (SELECT bar, baz FROM oof)"]
dev=>

I'll update the :in documentation to warn about this difference and how to produce the multi-column version.

[austb]

Thanks! That makes sense.