Fork me on GitHub
#honeysql
<
2021-05-10
>
thumbnail17:05:08

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]
I can work around it by using :where [:= :id [:inline 123]] but it I might be doing something wrong

seancorfield18:05:27

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

😮 3
seancorfield18:05:34

What DB are you using?

seancorfield18:05:27

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…

thumbnail18:05:55

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 !

seancorfield18:05:04

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?

🚀 3
seancorfield18:05:00

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

thumbnail19:05:30

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

thumbnail06:05:33

Just tried out 2.0.0-rc2; and it indeed fixes my issue. Epic 🙂 Thanks again

3
Noah Bogart18:05:15

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.

Noah Bogart18:05:09

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.

Noah Bogart18:05:15

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

seancorfield19:05:27

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

seancorfield19:05:43

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

Noah Bogart19:05:35

oops, but also glad i could help

seancorfield19:05:38

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]

3
seancorfield19:05:53

Note the nesting on the alias @jeroen.dejong

😬 3
seancorfield19:05:14

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

thumbnail19:05:25

Awesome! Big thanks for quick response 🙂

seancorfield19:05:35

Thanks for finding bugs! 🙂