honeysql 2023-03-29 | Slack Archive

oly09:03:50

hitting a wierd issue with next.jdbc / honey I am doing a group by (substring postcode 1 4) and also the same in the select, pushing the query through sql/format gives me a correct sql query I can run against my db how ever sending it to execute! I get this error.

ERROR: column "accounts_address.postcode" must appear in the GROUP BY clause or be used in an aggregate function

Which would usually make sense except the query works and I do not need postcode in the group by so feels like I am hitting some caveat or bug or something. Anyone have any suggestions ?

p-himik09:03:26

That's really weird indeed. Are you sure you're not accidentally executing some outdated code that's still referenced from somewhere else?

oly09:03:28

pretty sure but I will jack out and jack in to confirm

oly09:03:32

that's did not help got a bit further, tried this

GROUP BY SUBSTRING(\"accounts_address\".\"postcode\", 1, 4)"])

GROUP BY SUBSTRING(\"accounts_address\".\"postcode\", ?, ?)" 1 4])

so something todo with the dynamic params

oly09:03:47

I have resorted to pushing vector versions of the query into execute!

p-himik09:03:24

So what is the difference exactly, in code, between the version that works and the version that doesn't?

oly10:03:07

one has the values as part of the query the other has them as parameters, ie with ? and with out

p-himik10:03:24

But which one of them ended up working? The one with ??

oly10:03:50

the one with out the ? works ie part of the query I could use raw I guess to fix this

p-himik10:03:10

There :inline but it's suboptimal w.r.t. caching.

oly10:03:03

I would be happy for works at this point, but equally curious what I am doing wrong as feels like it should work

p-himik10:03:12

It might be that the DB can't figure out the types of those ? for sure because the substring function is overloaded. Maybe it'll help providing explicit casts, like substring(x, ?::int, ?::int), but no idea.

oly10:03:51

could be worth a try as well

oly10:03:48

inline works a treat, so at least I have an option cheers for that suggestion

👍 2

oly10:03:35

Using :cast how ever gives me the same error,

(sqlh/group-by [:substring :accounts_address.postcode
                      [:cast 1 :int]
                      [:cast 4 :int]
                      #_#_[:inline 1]
                        [:inline chars]])

So i guess I will use inline for now, cheers for the assistance appreciated 🙂

seancorfield15:03:30

@UU67HFS2X I've seen that with different versions of MySQL: older versions allowed you to omit grouped columns/expressions from the SELECT but later versions required you provide them (although there was a DB setting to relax the new restriction). I think if you add that [:substring ..] call into the :select with an alias, you will be able to :group-by that alias.

oly18:03:47

does the same apply for postgres ? that's my target in this instance I also realized the cast above is wrong as it should be ::integer not ::int when using the cast function so will need to retry that

seancorfield18:03:37

Given the ERROR: column "accounts_address.postcode" must appear in the GROUP BY clause or be used in an aggregate function error you're getting, I'm going to say "Yes, it applies to PostgreSQL" 🙂

oly18:03:15

okay I will give it a try cheers for the tip 🙂

p-himik19:03:08

FWIW in PostgreSQL you cannot mention in GROUP BY aliases defined in SELECT.

oly10:03:44

if you have not thought of doing this, using the chat gpt style apps are pretty good at generating you honeysql code with the correct format, I just asked you chat this question.

give me an example of using a summed case statement using honeysql version 2

Which saved me piecing the parts together, surprised how good it is considering honeysql is a bit more niche

oly11:03:14

ha, very impressed it does entire queries and if you specify give it to me in threading macro style it can even use the helper functions which are much nicer to read 🙂

seancorfield15:03:28

Beware of very confident but completely wrong answers from ChatGPT: it will happily invent new functions in libraries, just on the basis that the names sound like they solve the problem -- remember that it is a language model and has no real understanding of Clojure or the specifics of any library!

👍 2

oly19:03:28

yeah very true, but its handy to give me an idea of the general syntax else I keep having to reference existing queries or lookup in the docs for the format I need, but I have noticed it does get things wrong often just close enough to save me some time 🙂

2023-03-29

Channels