Is this ordering of join and aggregation really intentional?

[tomjaguarpaw]

Suppose I have some queries which aggregate

sumFoo :: SqlQuery (SqlExpr (Value (Maybe Int)))
sumFoo = from $ \foo -> do
  return (sum_ (foo ^. FooFoo))

sumBar :: SqlQuery (SqlExpr (Value (Maybe Int)))
sumBar = from $ \bar -> do
  return (sum_ (bar ^. BarBar))

[EDIT: corrected a typo in my own code above]

and then I join them, supposedly after the aggregation has been performed.

join = do
  f <- sumFoo
  b <- sumBar
  return (f, b)

I would expect the generated SQL to be equivalent to this

SELECT "foo", "bar"
FROM (SELECT SUM("foo"."foo") FROM "foo"),
     (SELECT SUM("bar"."bar") FROM "bar")

However instead I get

SELECT SUM("foo"."foo"), SUM("bar"."bar")
FROM "foo", "bar"

This seems very odd to me, and an impediment to composability. Is this really the intended semantics of those queries?

[meteficha]

Yes, that's intended. esqueleto does not make any attempt to try to guess which kind of SQL you want, instead it tries to be as close to raw SQL as possible. If you want a subquery, you need to explicitly ask for one.

[tomjaguarpaw]

OK, but suppose sumFoo and sumBar are provided by an external API, and I, the application programmer, have no idea whether they involve summation (despite the names!). Then I try

join :: SqlQuery (SqlExpr (Value (Maybe Int)))
join = do
  f <- sumFoo
  b <- sumBar

  where_ (f ==. b)

  return (f, b)

and this gives me an invalid query:

SELECT SUM("foo"."foo"), SUM("bar"."bar")
FROM "foo", "bar"
WHERE SUM("foo"."foo") = SUM("bar"."bar")

What's the solution to this kind of problem? Is the provider of sumFoo and sumBar responsible for documenting that they contain aggregation, and when I see that I'm never allowed to perform aggregation on them? Explicit subselect doesn't seem to help here as that returns a SqlExpr (ValueList (Maybe Int)) and what I want is an SqlQuery (SqlExpr (Value (Maybe Int))).

[meteficha]

The thing is, esqueleto does not try to solve this kind of problem. Although it does allow you to have a lot more flexibility in separating queries than plain SQL (without stored procedures), it does not try to shield you from this kind of problems. The problem esqueleto tries to solve is to write SQL in a EDSL that is as close to SQL as possible, even at the expense of some invalid queries sometimes. You'll still catch a lot more bugs than if you used SQL strings.

What you actually want is some more flexibility in FROM. Since I've never had this problem I won't invest energy in trying to figure out how that would fit in esqueleto, though. If you want to try to crack this nut, I'll be glad to help :).

[chrisdone]

@tomjaguarpaw IIRC HaskellDB will properly compose a composition like this, right? I don't have an up-and-running HaskellDB anymore to confirm, though. But my intuition is that whenever you project, that creates a subquery and you end up with SQL like SELECT (SELECT … FROM …) FROM …, as deep as needs be. Not too efficent on query planners outside of the PostgreSQL world, but correct.

[tomjaguarpaw]

Yes, HaskellDB composes these properly (modulo the many bugs which have appeared in its optimizer). If you like the sound of this kind of thing please listen out for my upcoming announcement about Opaleye which is like HaskellDB The Next Generation. It will be publically released on 1st December 2014.

[chrisdone]

@tomjaguarpaw Aha. I thought so—it seems the criticisms of HaskellDB lie not in its relational algebra roots, but rather its sloppy implementation. A modern take on the same compositional, type-safe approach is definitely welcome. :-)

[meteficha]

The criticism Esqueleto makes about HaskellDB is wrt being too far from SQL. Which can be an advantage or disadvantage depending on your perspective.