Clojurians Log v2

I'm new to honey and so far loving it!. V2 has been really solid! I'm composing a sub query and noticed the params are not filled in correctly :thinking_face: .

(sql/format
  {:left-join [[{:select [:c]
                 :from   :a
                 :where  [:= :id 123]}]
               :x]
   :where     [:= :a/id 123]})
=> ["LEFT JOIN (SELECT c FROM a WHERE id = ?) ON x WHERE a.id = ?" 123]

@jeroen.dejong Hmm, that may be a form of JOIN that just isn’t supported yet.

What DB are you using?

postgres

Of course 🙂 OK, I’ll open an issue to review it and see if I can find PG’s docs about that syntax — I know your posted example is simplified but it doesn’t make sense to me as written, so I’ll need to see what the full syntax can be from the docs…

https://github.com/seancorfield/honeysql/issues/326

haha sure thing. I converted this from a snippet I found in another projects codebase 😅; so the exact wizardry is lost on me too to be honest. Thanks !

full syntax for postgres select is here: https://www.postgresql.org/docs/current/sql-select.html

from clause, select: > A sub-SELECT can appear in the FROM clause. This acts as though its output were created as a temporary table for the duration of this single SELECT command. Note that the sub-SELECT must be surrounded by parentheses, and an alias must be provided for it.

I can see where the parameter(s) are getting dropped. There’s an assumption in the formatter for JOIN that parameters will only “appear” for a JOIN .. USING .. statement, not a JOIN .. ON .. statement. That’s an easy bug to fix… OK, v2 has an update that “fixes” your example case, if you want to test via :git/url?

from clause, join type: > A JOIN clause combines two FROM items, which for convenience we will refer to as “tables”, though in reality they can be any type of FROM item. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM-list items.

I’m going to leave that open until I have tests in place and a better understanding of the implication of making that change…

the requirement to alias the sub-select might be something worth noting, @seancorfield, from the current output it looks like that's not being generated

The code snippet above does not have the alias in the correct place.

cool

That's epic! I'll try it out soon and let you know

But that highlights a second bug in the join formatting, which is that ON is optional, which the current formatter does not support 😐

oops, but also glad i could help

OK, fix for that pushed to v2 as well. Now:

user=> (sql/format
  #_=>   {:left-join [[{:select [:c]
  #_=>                  :from   :a
  #_=>                  :where  [:= :id 123]}
  #_=>                :x]]
  #_=>    :where     [:= :a/id 124]})
["LEFT JOIN (SELECT c FROM a WHERE id = ?) AS x WHERE a.id = ?" 123 124]

Note the nesting on the alias @jeroen.dejong

(that bug was due to using partition instead of partition-by)

Awesome! Big thanks for quick response 🙂

Thanks for finding bugs! 🙂