2024-03-04 honeysql | Clojure Slack Archive

honeysql

2024-03-04T20:52:30.762619Z

2024-03-04T20:52:31.132789Z

seancorfield 2024-03-04T20:58:21.308719Z

I'm moving closer to a 2.6.x release today, hence closing out some tickets. The old, experimental formatf has been removed since that didn't seem to get any positive feedback (and it got some negative feedback) and I've settled on a Clojure-only (macro) helper called formatv that lets you specify certain symbols in a literal symbolic query that should be resolved to local bindings (using clojure.template under the hood). It's very narrow in scope and intended to help purely with inline literal symbolic query forms -- on the grounds that if you are building a query programmatically, you can already arrange for values to be added into the DSL.

2024-03-04T21:24:23.194129Z

seancorfield 2024-03-04T21:49:57.954809Z

The XTDB dialect support currently only affects how table and column names are handled -- documentation and tests/examples will come as this gets more testing -- but the main issue is that :xt/id is a valid column name in XTQL (and in XTDB's SQL) but in HoneySQL that would normally be treated as xt.id, i.e., table xt with column id. The dialect (`:xtdb`) changes this to treat :xt/id as a column name, but dots still . delimit table/column names: :foo.bar/quux means table foo and column bar/quux in XTDB. A parallel change is needed for next.jdbc's "friendly SQL functions" to make round-tripping possible -- right now next.jdbc.xt will arrange for these sorts of names to be returned with $ instead of /.

2024-03-04T23:57:45.772239Z

2024-03-05T00:18:27.746669Z

Christopher Gsell 2024-03-04T05:27:05.750649Z

Hi guys, quick question. I'm making a compound update statement using honeysql 2.x and I'm having trouble with types. And when I try to cast them as a patch, I get weird errors, so I figured I'd as about the root issue. I am making an update of the form:

(sql/format {:update [:user :u]
               :set {:recorded_at :u2.recorded_at
                     :spend :u2.spend}
               :from [[{:values [[#inst "2024-03-01" "account_1" 100 #inst "2024-03-03"]
                        [#inst "2024-03-01" "account_2" 200 #inst "2024-03-03"]]}
                       [[:u2 :instant :account :spend :recorded_at]]]]
               :where [:and [:= :u.instant :u2.instant] [:= :u.account :u2.account]})

I get the expected vector result, where the parameters are still in datetime objects. However, when I run it with clojure.java.jdbc/execute! I get the following error

ERROR: operator does not exist: date = text
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
                                     Position: 347  Call getNextException to see other errors in the batch.

This is because the sql statement resolves the datetime objects to strings, rather than preserving them as java.sql.date objects, so the statement ends up being this:

UPDATE user u SET recorded_at = u2.recorded_at, 
                  spend = u2.spend
              FROM (VALUES ('2024-03-01', 'account_1', 100, '2024-03-03'),
                           ('2024-03-01', 'account_2', 200, '2024-03-03')) 
              AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account);

To make this work, I need to add explicit timestamp casting in SQL as seen below:

UPDATE user u SET recorded_at = u2.recorded_at, 
                  spend = u2.spend
              FROM (VALUES ('2024-03-01'::timestamp, 'account_1', 100, '2024-03-03'::timestamp),
                           ('2024-03-01'::timestamp, 'account_2', 200, '2024-03-03'::timestamp)) 
              AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account);

I am not sure why this process converts the date objects here as when I have a simpler update statement (shown below) there are no issues and the date objects are preserved:

(sql/format {:update :u
             :set {:spend 0 :recorded_at #inst "2024-03-03"}
             :where [:and [:= :instant #inst"2024-02-29")]
                          [:= :account "account_1"]]})

Any help would be much appreciated!!

seancorfield 2024-03-04T05:39:47.395399Z

The fact that the simpler update works, and the more complex call produces reasonable SQL says this is not a HoneySQL issue. This is about your database driver and the coercions it is prepared to make without hints...

["UPDATE user u SET recorded_at = u2.recorded_at, spend = u2.spend FROM (VALUES (?, ?, ?, ?), (?, ?, ?, ?)) AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account)" #inst "2024-03-01T00:00:00.000-00:00" "account_1" 100 #inst "2024-03-03T00:00:00.000-00:00" #inst "2024-03-01T00:00:00.000-00:00" "account_2" 200 #inst "2024-03-03T00:00:00.000-00:00"]

If you were using next.jdbc, I'd suggest wrapping the #inst "..." expressions in as-timestamp https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.909/api/next.jdbc.types#as-timestamp but clojure.java.jdbc doesn't provide that sort of hinting so, yes, you're probably going to need to tell HoneySQL to generate casts with [:cast #inst "..." :timestamp] in this situation.

seancorfield 2024-03-04T05:40:39.488109Z

(I will admit, I didn't even realize this SQL was possible ... what database/driver are you using?)

Christopher Gsell 2024-03-04T05:46:24.266339Z

Ah ok interesting. It starts to get a little hairy when I do the casting because I'll cast like so:

(sql/format {:update [:user :u]
               :set {:recorded_at :u2.recorded_at
                     :spend :u2.spend}
               :from [[{:values [[[:cast "2024-03-01" :timestamp] "account_1" [:cast 100 :double-precision] [:cast "2024-03-03" :timestamp]]
                        [[:cast "2024-03-01" :timestamp] "account_2" [:cast 200 :double-precision] [:cast "2024-03-03" :timestamp]]]}
                       [[:u2 :instant :account :spend :recorded_at]]]]
               :where [:and [:= :u.instant :u2.instant] [:= :u.account :u2.account]})

and I'll get an error ERROR: type "double_precision" does not exist. Which was one of the examples given https://github.com/seancorfield/honeysql/blob/develop/doc/special-syntax.md lol

Christopher Gsell 2024-03-04T05:48:11.936819Z

I'm using Postgres 12x with clojure.java.jdbc

seancorfield 2024-03-04T05:48:35.431319Z

The code you're pasting is not syntactically correct, BTW. I had to add a ] into that last line.

Christopher Gsell 2024-03-04T05:49:02.637089Z

Ah my bad sorry did it on the fly 🫣

seancorfield 2024-03-04T05:49:19.701109Z

When I run that code above (with the extra ]) I get the correct output:

(sql/format {:update [:user :u]
               :set {:recorded_at :u2.recorded_at
                     :spend :u2.spend}
               :from [[{:values [[[:cast "2024-03-01" :timestamp] "account_1" [:cast 100 :double-precision] [:cast "2024-03-03" :timestamp]]
                        [[:cast "2024-03-01" :timestamp] "account_2" [:cast 200 :double-precision] [:cast "2024-03-03" :timestamp]]]}
                       [[:u2 :instant :account :spend :recorded_at]]]]
               :where [:and [:= :u.instant :u2.instant] [:= :u.account :u2.account]]})
["UPDATE user u SET recorded_at = u2.recorded_at, spend = u2.spend FROM (VALUES (CAST(? AS TIMESTAMP), ?, CAST(? AS DOUBLE PRECISION), CAST(? AS TIMESTAMP)), (CAST(? AS TIMESTAMP), ?, CAST(? AS DOUBLE PRECISION), CAST(? AS TIMESTAMP))) AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account)" "2024-03-01" "account_1" 100 "2024-03-03" "2024-03-01" "account_2" 200 "2024-03-03"]
user=>

seancorfield 2024-03-04T05:50:15.598889Z

So it might help if you actually paste in the real code you're using since the code you've posted isn't...

seancorfield 2024-03-04T05:53:10.334149Z

> I'm using Postgres 12x with clojure.java.jdbc For future debugging, c.j.j is not "the driver" -- that would be the actual org.postgres dep in your project. But I suspect the driver version isn't relevant at this point.

Christopher Gsell 2024-03-04T06:11:47.426939Z

That's interesting because when I run that code that you posted above, I get the following output (with the double_precision)

["UPDATE user u SET recorded_at = u2.recorded_at, spend = u2.spend FROM (VALUES (CAST(? AS timestamp), ?, CAST(? AS double_precision), CAST(? AS timestamp)), (CAST(? AS timestamp), ?, CAST(? AS double_precision), CAST(? AS timestamp))) AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account)" "2024-03-01" "account_1" 100 "2024-03-03" "2024-03-01" "account_2" 200 "2024-03-03"]

Christopher Gsell 2024-03-04T06:13:27.767279Z

> paste in the real code Yeah it just has a ton of functions that calculate the vectors/maps so figured it'd be easier initially. Will get the evaluated map though...

seancorfield 2024-03-04T06:14:14.696529Z

And what version of HoneySQL are you using (exact dependency)?

Christopher Gsell 2024-03-04T06:15:05.186729Z

2.2.840

seancorfield 2024-03-04T06:15:46.010389Z

That's old. Lots of bug fixes and enhancements since then.

seancorfield 2024-03-04T06:16:00.771979Z

2.5.1103 -- 2023-12-03 -- latest released version.

Christopher Gsell 2024-03-04T06:16:12.519499Z

ahh ok, I'll bump it up

seancorfield 2024-03-04T06:16:13.147329Z

2.2.840 is over two years old.

Christopher Gsell 2024-03-04T06:16:22.857039Z

wow verysweat_g

Christopher Gsell 2024-03-04T06:21:07.734839Z

Ok bumped it up and it fixed it. Thanks for the help @seancorfield and sorry for it just being an old version!

seancorfield 2024-03-04T06:21:45.157679Z

NP. c.j.j is no longer maintained so I'd strongly suggest looking at migrating to next.jdbc if you can.

seancorfield 2024-03-04T06:22:05.154159Z

At least you're already on HoneySQL 2.x instead of 1.x 🙂

Clojurians Log v2

honeysql