Fork me on GitHub

hi. I have a function that constructs a query as follows

{:select [[ :site_trial_id] [(sql/call :max :pce.created_at) :last_activity_at]]
                      :from [[:SiteTrialView :st]]
                      :join [[:SiteTrialPatientView :stp] [:= :stp.site_trial_id]
                             [:change_event.SiteTrialPatientAttributeChangeEvent :pace] [:= :pace.site_trial_patient_id]
                             [:change_event.PatientChangeEvent :pce] [:= :pace.change_event_id]]
                      :where [:in (sql/param :site-trial-ids)]
                      :group-by []}
   {:site-trial-ids (remove nil? site-trial-ids)}
and I’m passing it a list of uuids. The function appears to be called multiple times and during one of those calls it is inserting a nil which breaks the query. Is there a way to remove nils from this collection at the time of query evaluation? When I look at the query that’s being evaluated I see this list of parameters (notice the nil)
"6ff7d3d3-fefe-5e58-8733-d235593c96dd" #uuid "25ffa790-6175-5988-842c-ccf42d6f2562" #uuid "240d1ac7-d382-54e4-a3fe-f62ee8f3d5f1" nil #uuid "488f3fb6-00ec-528f-9de6-373549ff4edf" 


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]
If you can create a repro case for HoneySQL 1.x, with that remove nil? in place, I can take a look but I'm a bit skeptical... 🙂


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


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 [[ :site_trial_id] [(sql/call :max :stp.acknowledged_at) :last_activity_at]]
                                 :from [[:SiteTrialView :st]]
                                 :join [[:SiteTrialPatientView :stp] [:= :stp.site_trial_id]]
                                 :where [:in (sql/param site-trial-ids)]
                                 :group-by []}
                                {:select [[ :site_trial_id] [(sql/call :max :pce.created_at) :last_activity_at]]
                                 :from [[:SiteTrialView :st]]
                                 :join [[:SiteTrialPatientView :stp] [:= :stp.site_trial_id]
                                        [:change_event.SiteTrialPatientAttributeChangeEvent :pace] [:= :pace.site_trial_patient_id]
                                        [:change_event.PatientChangeEvent :pce] [:= :pace.change_event_id]]
                                 :where [:in (sql/param :site-trial-ids)]
but the param should be the same one in each where clause. I think what’s happening is that sql/format repeats the sequence site-trial-ids so it can reference params by position. And in doing so it introduces a nil somewhere. The formatted query has multiple ?s and each one of those refers to an item in 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.