This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2021-03-16
Channels
- # announcements (11)
- # atom-editor (4)
- # babashka (26)
- # beginners (126)
- # calva (35)
- # chlorine-clover (14)
- # clj-kondo (50)
- # cljfx (1)
- # cljs-dev (1)
- # cljsrn (3)
- # clojure (31)
- # clojure-europe (144)
- # clojure-germany (2)
- # clojure-nl (3)
- # clojure-serbia (17)
- # clojure-spain (11)
- # clojure-uk (38)
- # clojurescript (87)
- # community-development (1)
- # conjure (1)
- # datalog (1)
- # datascript (160)
- # datomic (28)
- # duct (2)
- # emacs (4)
- # events (1)
- # figwheel-main (1)
- # fulcro (15)
- # graalvm (4)
- # honeysql (53)
- # jobs (2)
- # jobs-discuss (14)
- # juxt (6)
- # lsp (59)
- # malli (13)
- # music (1)
- # off-topic (8)
- # pathom (22)
- # portal (7)
- # re-frame (2)
- # reagent (3)
- # releases (1)
- # remote-jobs (1)
- # rewrite-clj (1)
- # shadow-cljs (25)
- # sql (3)
- # tools-deps (38)
- # xtdb (17)
Here is the latest v2 "puzzle": The SQL I'm trying to generate is:
LEAD(g) OVER (PARTITION BY fid ORDER BY m)
I've tried this:
(sql.helpers/over [[:lead :g]
(-> (sql.helpers/partition-by :fid) (order-by :m))])
which generates:
[[:over [[:lead :g] {:partition-by [:fid], :order-by [:m]}]]]
which sql/format
chokes onCan you provide a bit more context? This works:
user=> (sql/format (-> (select (over [[:lead :g] (-> (h/partition-by :fid) (order-by :m))]))))
["SELECT LEAD(g) OVER (PARTITION BY fid ORDER BY m ASC)"]
Here’s a more complex example from the docs:
user=> (sql/format (-> (select :id
#_=> (over [[:avg :salary] (-> (h/partition-by :department) (order-by :designation)) :Average]
#_=> [[:max :salary] :w :MaxSalary]))
#_=> (from :employee)
#_=> (window :w (h/partition-by :department))))
["SELECT id, AVG(salary) OVER (PARTITION BY department ORDER BY designation ASC) AS Average, MAX(salary) OVER w AS MaxSalary FROM employee WINDOW w AS (PARTITION BY department)"]
I definitely looked at the docs that I could find in https://github.com/seancorfield/honeysql/blob/v2/doc/clause-reference.md
The broader context is that the snipped above is part of a with
CTE, here is the raw
version of it (that works):
[:E [:raw "(SELECT DISTINCT ON (fid) fid, id AS a, id + 1 AS b, ST_3DDistance(ST_MakeLine(g, LEAD(g) OVER (PARTITION BY fid ORDER BY m)), pca_raw) AS dist FROM D ORDER BY fid, 4)"]]
Here is what I tried:
[:E (-> (sql.helpers/select-distinct-on :fid
:fid
[:id :a]
[[:raw "id + 1"] :b]
[[:ST_3DDistance [:ST_MakeLine :g
(sql.helpers/over [[:lead :g]
(-> (sql.helpers/partition-by :fid)
(order-by :m))])] :pca_raw] :dist])
(from :D)
(order-by [:fid 4]))]
[I was wrong: HoneySQL treats :over
as special syntax so it should work in any expression]
Oh, I see the issue @dcj :select-distinct-on
expects the first argument to be a sequence of column names per the docs: "Similar to :select-distinct above but the first element in the sequence should be a sequence of columns for the DISTINCT ON clause"
Also, the order-by
should have a direction (`:asc` or :desc
) instead of 4
-- can you explain what you're trying to do there?
Maybe this is what you're aiming for:
dev=> (sql/format (-> (h/select-distinct-on [:fid]
#_=> #_=> :fid
#_=> #_=> [:id :a]
#_=> #_=> [[:+ :id 1] :b]
#_=> #_=> [[:ST_3DDistance [:ST_MakeLine :g
#_=> #_=> (h/over [[:lead :g]
#_=> #_=> (-> (h/partition-by :fid)
#_=> #_=> (h/order-by :m))])] :pca_raw] :dist])
#_=> #_=> (h/from :D) (h/order-by :fid [[:inline 4]])))
["SELECT DISTINCT ON(fid) fid, id AS a, id + ? AS b, ST_3DDISTANCE(ST_MAKELINE(g, (LEAD(g) OVER (PARTITION BY fid ORDER BY m ASC))), pca_raw) AS dist FROM D ORDER BY fid ASC, 4 ASC" 1]
Not sure whether the id + 1
is better for you as an expression rather than raw like that?1. Thank you for the syntax fix!
2. Clearly I need to read the docs more carefully
3. Is there some way I can run my proposed query DSL through Spec, to veriify it is correct?
4. Thank you for [[:+ :id 1] :b]
that is way better
Also, the order-by should have a direction (:asc or :desc) instead of 4 -- can you explain what you're trying to do there?
The short answer is that I can't explain this, the query I am working on was created by an extremely experienced PostGIS developer, and I don't understand how it works 😞
That being said, I checked the Postgres docs on order-by: https://www.postgresql.org/docs/9.5/queries-order.html
Note this:
A sort_expression can also be the column label or number of an output column, as in:
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;
So, at this point, here is what I have:
(pprint tqe)
{:select-distinct-on
[[:fid]
:fid
[:id :a]
[[:raw "id + 1"] :b]
[[:ST_3DDistance
[:ST_MakeLine
:g
[[:over [[:lead :g] {:partition-by [:fid], :order-by [:m]}]]]]
:pca_raw]
:dist]],
:from [:D],
:order-by [[[:raw "fid, 4"]]]}
(println (sql/format tqe {:pretty true}))
[
SELECT DISTINCT ON(fid) fid, id AS a, id + 1 AS b, ST_3DDISTANCE(ST_MAKELINE(g, (LEAD(g) OVER (PARTITION BY fid ORDER BY m ASC))), pca_raw) AS dist
FROM D
ORDER BY fid, 4 ASC
]
I haven't tested this yet, need to head to a meeting, back in a couple hoursI showed an order-by
that would work for :fid, 4
: (h/order-by :fid [[:inline 4]])
— you don’t need :raw
here.
Re: #3 — there’s an issue on GitHub for a Spec for the DSL but I’m not sure how tractable that really is since it would need to pretty much match the entire recursive descent “formatter”: SQL is extremely complex as a language and Spec isn’t really suited to describing language grammars.
Could each helper fn check its args? Not saying this would enable checking everything.... I will try and give this some thought
Not really: the helpers are nearly all generic — they mostly just assoc/merge arguments into the underlying data structure (they’re nearly all just calls to generic
under the hood).
And the problem is that the syntax of SQL is recursive: SELECT expressions can contain SELECT expressions in all sorts of complex ways.
Any Spec that I end up writing is only going to catch the most egregious mistakes.
There are a few clauses where the structure required is unique enough that Spec could catch some errors (`:select-distinct-on` is a particular case where it expects a sequence where the first element must be a sequence — but you already got an error from that… just not a very informative one).
I can try to add more “specific error checking” in cases where, if you pass in bad data today you get a generic error (either an NPE or a CCE), but many of those would be hard to predict…
There are also two distinct classes of “user error” here: one that blows up the formatter and one that formats successfully but produces bogus SQL. The former I can probably do something about, at least in terms of narrowing down where the error occurred, but the latter is pretty much impossible to detect.
There are also two distinct classes of “user error” here: one that blows up the formatter and one that formats successfully but produces bogus SQL. The former I can probably do something about, at least in terms of narrowing down where the error occurred, but the latter is pretty much impossible to detect.
Today's v2 puzzle, trying to convert this from :raw
FROM
(SELECT fid, pca, dca FROM P) P
INNER JOIN
(SELECT fid, m, ST_3DDistance(g,pca) AS dist FROM D WHERE (fid, id) IN (SELECT fid, a FROM E)) A ON P.fid = A.fid
INNER JOIN
(SELECT fid, m, ST_3DDistance(g,pca) AS dist FROM D WHERE (fid, id) IN (SELECT fid, b FROM E)) B ON P.fid = B.fid
My approach to these tasks is to try and build "up" from an innermost.
So in this case, I started trying to write:
(SELECT fid, m, ST_3DDistance(g,pca) AS dist FROM D WHERE (fid, id) IN (SELECT fid, b FROM E)) B
The part I can't figure out is WHERE (fid, id) IN (SELECT fid, b FROM E)
, specifically the IN
part...user=> (sql/format {:where [:in [:composite :fid :id] {:select [:fid :b] :from :e}]})
["WHERE (fid, id) IN (SELECT fid, b FROM e)"]
(assuming you want the data structure version, not the helper version)
user=> (sql/format (where [:in (composite :fid :id) (-> (select :fid :b) (from :e))]))
["WHERE (fid, id) IN (SELECT fid, b FROM e)"]
It’s probably worth pointing out that this is one of those places where the V2 support for symbols instead of keywords makes for a more readable piece of code
user=> (sql/format '{where [in [composite fid id] {select [fid b] from e}]})
["WHERE (fid, id) IN (SELECT fid, b FROM e)"]
(that was not possible in V1 although symbols were accepted in a few places)
Obviously you can’t use a quoted structure if you have Clojure variables in the structure but…
(although you could use named parameters for that)
Recall my "today's V2 puzzle" above... Here is what I've got now:
(-> (select :fid :pca :dca)
(from :P)
(inner-join [(-> (select :fid :m [[:ST_3DDistance :g :pca] :dist])
(from :D)
(where [:in (composite :fid :id) (-> (select :fid :a) (from :E))]))
:A]
[:= :P.fid :A.fid])
(inner-join [(-> (select :fid :m [[:ST_3DDistance :g :pca] :dist])
(from :D)
(where [:in (composite :fid :id) (-> (select :fid :b) (from :E))]))
:B]
[:= :P.fid :B.fid]))
Which results in this:
[
SELECT fid, pca, dca
FROM P
INNER JOIN (SELECT fid, m, ST_3DDISTANCE(g, pca) AS dist FROM D WHERE (fid, id) IN (SELECT fid, a FROM E)) AS A ON P.fid = A.fid INNER JOIN (SELECT fid, m, ST_3DDISTANCE(g, pca) AS dist FROM D WHERE (fid, id) IN (SELECT fid, b FROM E)) AS B ON P.fid = B.fid
]
This seems really close, but I can't figure out how to get the first SELECT
to end with AS P
It is supposed to be:
(SELECT fid, pca, dca FROM P) P
You don’t need it to be aliased tho’ since the table name is already P
, right?
I’m not even sure how to get an alias on that without nesting it in a SELECT
… SELECT * FROM (SELECT fid, pca, dca FROM P) AS P
which would be (-> (select :*) (from [(-> (select :fid :pca :dca) (from :p)) :p]) ...)
I think @dcj?
user=> (sql/format (-> (select :*) (from [(-> (select :fid :pca :dca) (from :p)) :p])))
["SELECT * FROM (SELECT fid, pca, dca FROM p) AS p"]
(SELECT .. FROM ..) AS alias INNER JOIN ..
is certainly not legal SQL as far as MySQL is concerned.
AFAICT, it is supposed to be:
(SELECT some-stuff FROM OTHER_P) P
INNER JOIN ...
INNER JOIN ...
AFAIK this is legal Postgres, my raw query above works fineThere’s no way to generate that with HoneySQL — the closest you’ll get is the nested select I showed above.
I can’t even think how to write that syntax in the DSL.
SELECT some-stuff FROM OTHER_P AS P
would be “equivalent” though, wouldn’t it?
(and that is expressible in the DSL)
I also think your point that "you already have P, so why alias it again" bears some thought, seems like I ought to be able to make my query work without that
I can’t see how it’s legal based on the Postgres docs either but, hey, maybe PG accepts syntax that isn’t documented?
The FROM
can alias the result — but it would be the entire result, not just that select
/`from` part.
Oh, I see what you mean. So it is (-> .. (from [ (-> (select :fid :pca :dca) (from :other_p)) :p ]) (inner-join .. ) )
because overall your query is select stuff from (select fid, pca, dca from p) as p ...
right?
I’d have to see the whole raw query to be able to help further at this point.