Clojurians Log v2

Here is the latest v2 "puzzle": The SQL I'm trying to generate is:

LEAD(g) OVER (PARTITION BY fid ORDER BY m)

(sql.helpers/over [[:lead :g]
                   (-> (sql.helpers/partition-by :fid) (order-by :m))])

[[:over [[:lead :g] {:partition-by [:fid], :order-by [:m]}]]]

Can 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]

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?

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;

(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 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.

Yes, this is working, thank you! Now, pne last giant blob of :raw to convert....

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…

Understood! I'm thinking more about the "user error" case....

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

(SELECT fid, m, ST_3DDistance(g,pca) AS dist FROM D WHERE (fid, id) IN (SELECT fid, b FROM E)) B

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)"]

Nice! Thank you again!

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)

perfect use case for quasi-quote

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]))

[
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
]

(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 ...

Had to edit the above 3 times to get it right

There’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)

That makes me feel better since I spent the last couple hours trying and failing

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?

Don;t know if it matters, but all this ends up in a from

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?

yes the original P was part of a with/CTE above

but the inner joins apply to the select/from within the larger from

I’d have to see the whole raw query to be able to help further at this point.