Clojurians Log v2

What is wrong with my query here:

(db/query
  data-src
  (-> (select :u.id :u.name)
      (from [:user :u])
      (join [:class :c] [:= :c.id :uc.class_id])
      (join [:user_class :uc] [:= :uc.user_id :u.id])
      (where [:= :c.id (str->uuid "93de84cc-d0da-42e4-b436-a1713a3aaf50")])
      (db/fmt)))

I am using next.jdbc + honeysql

@somedude314 It's a common mistake people make with the helpers. You have two join calls and the second one overwrites the first one. Use merge-join instead.

(I have an issue open on HoneySQL to fix this in v2 which will have a set of new namespaces since it will not be compatible)

The same applies to where, select, and a bunch of other helpers -- you'll see there are merge-* variants for a lot of them. If you always use merge-* you'll be safe -- you don't need to remember a mix of merge-* and non-`merge` calls.

For next.jdbc users who don't mind testing stuff on the develop branch: https://github.com/seancorfield/next-jdbc/issues/121 is implemented. If you have camel-snake-kebab on your classpath, next.jdbc.result-set has two extra builders: as-kebab-maps and as-unqualified-kebab-maps; and next.jdbc has two extra vars: snake-kebab-opts and unqualified-snake-kebab-opts which are hash maps containing the options you'd need for the next.jdbc.sql functions for snake_case as well as :builder-fn for kebab-case. I'm trying to figure out the best place to document this optional functionality (since it's the first functionality that is dependent on whether you have a specific library on your classpath).

Thanks @seancorfield - merge-join worked, but only after I switched the ordering of the join clauses. Is that normal? Somewhere in the docs it says order doesn’t matter. That doesn’t apply to joins?

join will overwrite any merge - join

Right. Perhaps the docs could be improved by adding a note that ordering matters when using merge-join. I don’t have join anymore, what I meant is this works ... (merge-join :user_class [:= :user_class.user_id :user.id]) (merge-join :class [:= :class.id :user_class.class_id]) ... This doesn’t ... (merge-join :class [:= :class.id :user_class.class_id]) (merge-join :user_class [:= :user_class.user_id :user.id]) ... > Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2440). > ERROR: missing FROM-clause entry for table “user_class”

That doesn't make sense. Show me the whole expression that doesn't work.

Actually I can replicate the error even outside of Honey/Next. On Postgres console. This works:

SELECT u.id, u.name
FROM "user" u
JOIN user_class uc ON user_id = u.id
JOIN class c ON c.id = uc.class_id
WHERE c.id = '93de84cc-d0da-42e4-b436-a1713a3aaf50'

SELECT u.id, u.name
FROM "user" u
JOIN class c ON c.id = uc.class_id
JOIN user_class uc ON user_id = u.id
WHERE c.id = '93de84cc-d0da-42e4-b436-a1713a3aaf50'

Doesn’t work:

(db/query
  data-src
  (-> (select :user.id :user.name)
      (from :user)
      (merge-join :class [:= :class.id :user_class.class_id])
      (merge-join :user_class [:= :user_class.user_id :user.id])
      (where [:= :class.id (str->uuid "93de84cc-d0da-42e4-b436-a1713a3aaf50")])
      (db/fmt)))

OK, so that's PostgreSQL causing you problems 😉

Yep 🙂

I would never write the joins that way round so maybe it doesn't work in other DBs either...?

Probably. I am just implementing a many-to-many relationship via a standard junction table.

When I create v2 of HoneySQL, it will be documented to the level of next.jdbc rather than just a long README 🙂

Is it possible to say NOT IN in HoneySQL?

I can only find an example for IN

:not-in is supported.

Just not documented I guess.

Thanks, will give it a try

FYI https://github.com/seancorfield/honeysql/blob/develop/src/honeysql/format.cljc#L125-L132 aliases for a number of supported functions

There doesn’t seem to be a (not-in) function. Is it only available via the vector format?

It seems to be:

(sql/call :not-in :id ...)

@somedude314 According to the REPL

$ clj -A:test
Clojure 1.10.1
user=> (require '[honeysql.core :as h] '[honeysql.helpers :refer :all])
WARNING: update already refers to: #'clojure.core/update in namespace: user, being replaced by: #'honeysql.helpers/update
nil
user=> (-> (select :*) (from :table) (where [:not-in :foo [1 2 3]]) (h/format))
["SELECT * FROM table WHERE (foo not in (?, ?, ?))" 1 2 3]
user=>

(run in the honeysql repo)

So it's just like every other SQL operator?

sorry, I got confused - sql/call works but this is definitely cleaner

Be careful -- the semantics are not the same in all cases.

(also, as a general reminder, there is a #honeysql channel for dedicated chat about that library)

I'm reposting this here so it's visible to more folks tonight/tomorrow after the HoneySQL chatter in between now and my original post: https://clojurians.slack.com/archives/C1Q164V29/p1594255353176400

I've made a pass over the documentation on develop to write this up https://github.com/seancorfield/next-jdbc/tree/develop/doc -- see also several recent commits: https://github.com/seancorfield/next-jdbc/commits/develop -- I'm planning to release 1.1.next (probably 1.1.566) over the weekend.

Oh, that's cool - I can remove a bunch of code in our next.jdbc+hugsql adapter/wrapper thing - thank you @seancorfield

Always pleased to hear that library additions result in code removal elsewhere 🙂

at the cost of adding a dependency ;-) no free lunch

Is it common to explicitly set the timezone for your database, database connection or maybe even the JVM? I can't quite wrap my head around what's happening here, I'm probably approaching it from the wrong angle. MySQL http://5.6.in a docker container on my machine and [org.clojure/java.jdbc "0.7.11"]:

(comment
  (jdbc/query test-db "SELECT @@system_time_zone;")                    #_=> '({"@@system_time_zone" "UTC"})
  (.. (java.util.Calendar/getInstance) (getTimeZone) (getDisplayName)) #_=> "Greenwich Mean Time"

  (jdbc/execute! test-db "create table foo (f1 date not null, f2 datetime not null)")

  (jdbc/query test-db "describe foo") #_=> '({:field "f1", :type "date", :null "NO", :key "", :default nil, :extra ""}
                                             {:field "f2", :type "datetime", :null "NO", :key "", :default nil, :extra ""})

  (jdbc/insert! test-db :foo {:f1 "1970-01-01" 
                              :f2 "1970-01-01"})

  ;; [mysql/mysql-connector-java "8.0.19"]
  (jdbc/query test-db "select * from foo") #_=> '({:f1 #inst"1970-01-01T00:00:00.000000000-00:00",
                                                   :f2 #inst"1970-01-01T00:00:00.000000000-00:00"})

  ;; [mysql/mysql-connector-java "8.0.20"]
  (jdbc/query test-db "select * from foo") #_=> '({:f1 #inst"1969-12-31T23:00:00.000-00:00",
                                                   :f2 #inst"1970-01-01T00:00:00.000000000-00:00"}))

@ssjoleary746 Pretty much the only sane choice for servers is: set the DB to UTC, set the server itself to UTC, set the JVM to UTC.

We have everything set to UTC so we didn't see that bug (we use the MySQL connector and only recently updated to 8.0.20, against Percona 5.7).

UTC has no daylight savings time adjustment, which I think is contributing to what you're seeing.

Thanks @seancorfield! Setting my own machine to UTC sorted the time adjustment, which makes sense. Verifying that the DB, server and JVM are set to UTC in production should be enough to get a release rolling, I'll need another solution for devs running tests locally regardless of what timezone they're in.

Yeah, local dev is always a bit of a problem in that respect. In theory if you explicitly set everything -- including the Docker container -- to the same TZ, you should be fine. Locally, I have everything set to Pacific TZ, including Docker, and it all "just works".

Solid theory really. The docker container is set to UTC regardless of what the host is so I'm considering something like (java.util.TimeZone/setDefault (java.util.TimeZone/getTimeZone "UTC")) during test set up.

You can use a JVM option instead, I believe.

Ah, even better. Thanks