Fork me on GitHub
#honeysql
<
2021-11-04
>
valerauko14:11:57

From the README I get the impression that :nest should unquestioningly just wrap parens around its children but that doesn't seem to be the case? Am i missing something?

user=> (honey.sql/format
 {:select :*
  :from :table
  :where 
  [:nest [:not [:or [:= :a 1] [:= :b 2]]]]})
["SELECT * FROM table WHERE NOT (a = ?) OR (b = ?)" 1 2]
If this behavior is intended, is there a way to force-wrap parens around a part of the query?

valerauko14:11:47

I'm running into behavior that i suspect is due to operator precedence and without the parens it's really hard to test (through honeysql)

valerauko15:11:12

I quickly confirmed by using pr-str that it's a problem of operator precedence. Query, current result and expected result below (it's a quick-and-dirty mysql export so ignore the fact that the query is disgusting):

{:select :*
 :from :vblog_posts
 :where [:and
         [:between :post_id 23 2105]
         [:not [:and
                [:or
                 [:like :post_main "%[img%"]
                 [:like :post_long "%[img%"]]
                [:< :post_date 1239462000]]]
         [:not [:or
                [:like :post_main "%[devfav%"]
                [:like :post_long "%[devfav%"]]]
         [:= :post_id 415]]
 :limit 1}
Result (2.1.818)
["SELECT * FROM vblog_posts WHERE post_id BETWEEN ? AND ? AND NOT ((post_main LIKE ?) OR (post_long LIKE ?)) AND (post_date < ?) AND NOT (post_main LIKE ?) OR (post_long LIKE ?) AND (post_id = ?) LIMIT ?" 23 2105 "%[img%" "%[img%" 1239462000 "%[devfav%" "%[devfav%" 415 1]
This doesn't work as intended. I don't know which bit of the precedence between AND OR and NOT causes the problem but there is a problem. Expected (works as intended):
["SELECT * FROM vblog_posts WHERE (post_id BETWEEN ? AND ?) AND (NOT ((post_main LIKE ?) OR (post_long LIKE ?)) AND (post_date < ?)) AND (NOT (post_main LIKE ?) OR (post_long LIKE ?)) AND (post_id = ?) LIMIT ?" 23 2105 "%[img%" "%[img%" 1239462000 "%[devfav%" "%[devfav%" 415 1]
It might be safe (and maybe simple?) to wrap everything in parens without thinking?

valerauko15:11:07

Wrapping [:nest ,,,] around the problematic bits does nothing

seancorfield16:11:15

:where takes a sequence of expressions where :and is implicit at the beginning. The following works:

dev=> (honey.sql/format
 #_=>  {:select :*
 #_=>   :from :table
 #_=>   :where 
 #_=>   [[:nest [:not [:or [:= :a 1] [:= :b 2]]]]]})
["SELECT * FROM table WHERE (NOT (a = ?) OR (b = ?))" 1 2]

seancorfield16:11:58

I think you just need the ( .. ) around the :between expansion to solve this so

{:select :*
 :from :vblog_posts
 :where [:and
         [:nest [:between :post_id 23 2105]]
         [:not [:and
                [:or
                 [:like :post_main "%[img%"]
                 [:like :post_long "%[img%"]]
                [:< :post_date 1239462000]]]
         [:not [:or
                [:like :post_main "%[devfav%"]
                [:like :post_long "%[devfav%"]]]
         [:= :post_id 415]]
 :limit 1}
(because :between expands to something containing AND)

valerauko17:11:04

Good learning about the implicit :and! But :nest still has no effect and doesn't add parens As for the priorities, I think NOT and AND don't mix well either...

seancorfield20:11:18

Yeah, looks like a bug that :not isn't wrapping its argument and also a bug that :nest isn't working in that context. I'm kind of in and out of my office today so if you could, please create issues for both of those on GH for me so I don't forget when I am back at my desk for more than a few minutes.

valerauko06:11:00

Alright will do!