Given that you're removing the nils from site-trial-ids, how is nil getting into the query?

no idea honestly. the (remove nil... seems to have no effect on the collection being passed in

(HoneySQL 2.x has a :checking :strict mode that would throw an exception here which at least prevent the incorrect query from running)

do you know where I can find an example of that?

HoneySQL 2.x:

dev=> (hsql/format {:where [:in :id :?ids]} {:params {:ids [1 2 3 nil 4 5]}})
["WHERE id IN (?, ?, ?, ?, ?, ?)" 1 2 3 nil 4 5]
dev=> (hsql/format {:where [:in :id :?ids]} {:params {:ids [1 2 3 nil 4 5]} :checking :strict})
Execution error (ExceptionInfo) at honey.sql/format-in (sql.cljc:1122).
IN (NULL) does not match
dev=> (hsql/format {:where [:in :id :?ids]} {:params {:ids (remove nil? [1 2 3 nil 4 5])} :checking :strict})
["WHERE id IN (?, ?, ?, ?, ?)" 1 2 3 4 5]

what’s weird is that I’m tapping the collection and there are no nils in it so there has to be something else going on

https://cljdoc.org/d/com.github.seancorfield/honeysql/2.2.891/doc/getting-started#format-options explains :checking

so I think I found the issue and it has to do with the format function. when calling (sql/format q :quoting :ansi :allow-dashed-names? true :allow-namespaced-names? true :params (or params {})) I’m getting duplicate params passed in. The params object looks as follows and yet the formatted query has duplicates and a nil

If you can create a small, self-contained repro, feel free to open an issue on GH -- but my recommendation would be to migrate at least that query to use HoneySQL 2.x at this point (you can add both 1.x and 2.x as dependencies since they have different coordinates and you can use both 1.x and 2.x in the same ns because they have different namespaces -- no conflicts).

ah ok. let me see if I get the approval from the team (some of the data is sensitive so I have to double check). thank you for the quick replies

At this point, HoneySQL 1.x is "legacy" and is only likely to get important security fixes (or, perhaps, occasionally a fix for a genuine showstopping bug). All work has been focused on 2.x for a long time now. It removes a lot of the warts in 1.x and provides much better PostgreSQL support -- and I'm continually enhancing it. It also has much better documentation (and better test coverage).

looks like they’ll be a discussion to see if we adopt v2. In the meantime, is there a way to indicate that a parameter refers to a specific item in the map of params? I have a union of selects like so

{:select [[:st.id :site_trial_id] [(sql/call :max :stp.acknowledged_at) :last_activity_at]]
                                 :from [[:SiteTrialView :st]]
                                 :join [[:SiteTrialPatientView :stp] [:= :st.id :stp.site_trial_id]]
                                 :where [:in :st.id (sql/param site-trial-ids)]
                                 :group-by [:st.id]}
                                {:select [[:st.id :site_trial_id] [(sql/call :max :pce.created_at) :last_activity_at]]
                                 :from [[:SiteTrialView :st]]
                                 :join [[:SiteTrialPatientView :stp] [:= :st.id :stp.site_trial_id]
                                        [:change_event.SiteTrialPatientAttributeChangeEvent :pace] [:= :pace.site_trial_patient_id :stp.id]
                                        [:change_event.PatientChangeEvent :pce] [:= :pace.change_event_id :pce.id]]
                                 :where [:in :st.id (sql/param :site-trial-ids)]

In 1.x, there is a parameterizer that uses numbered placeholders instead of ? which might do what you want but I suspect it will also repeat the sequence. I'm thinking about how to add that to 2.x because the Node.js PostgreSQL library doesn't support ? (which seems dumb to me!) and I might look at some option to coalesce named parameters in that case.