Group By with Coalesce

[Raveline]

According to the documentation of PG on GROUP BY:

An expression used inside a grouping_element can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary expression formed from input-column values

However, the current version of Squeal is limited to columns, not "an arbitrary expression formed from input-column values". Though I suppose these would be fairy rare, and hard to fit inside the type system, I have one case which should not be that rare: a GROUP BY using a COALESCE over two fields (context: a FULL OUTER JOIN with two similar columns). I'm gonna try to find a way to shortcut this through an UnsafeGrouping expression, but it could be worth it (in the long term, obviously) to investigate ways to model more complex GROUP BY expressions.

[echatav]

I'm gonna try to find a way to shortcut this through an UnsafeGrouping expression

GroupByClauses don't have an unsafe escape hatch so you might run into some difficulty. Instead you might try using a subquery which just outputs the expression you want in your grouping in addition to other columns you want to reference and then group and aggregate over the subquery.

myQuery = select (sum_ #a `as` #sum :* #c) (from (subquery sub) & groupBy #c)
  where
    sub = select (#a :* (#a + #b) `as` #c) (from (table #t)) `as` #sub

As for generalizing grouping elements to arbitrary expressions in Squeal, it would look like replacing By in GroupByClauses with something like Expression. However, the hard part would be in aliasing. Squeal must be a bit more explicit about providing aliases for expressions than SQL.

[Raveline]

Yes, I realized along the way that the escape hatch for GroupBy was not really an option. I think I'll add another subquery like you recommand (that's a pity because what I'm working on is already complex enough without adding all that !), unless I find another way of expressing the same problem in SQL.

I wouldn't necessarily be in favour of grouping for all arbitrary expressions. But the COALESCE case might be worth it, for it might be a common solution when you want to do a UNION-like query, but for whatever reason it doesn't fit your needs so you resort to a FULL OUTER JOIN that you need to flatten down the road. I'll think about it and if I find a way to express it through Squeal I'll ping you back.

[echatav]

that's a pity because what I'm working on is already complex enough without adding all that !

I'm noticing a theme ;-)

[Raveline]

I'm noticing a theme ;-)

Hahaha ! I've been working on the two biggest queries of my codebase recently. If I can write them using Squeal, it will be my pleasure to announce we use Squeal in production !

[echatav]

How many schemum objects does your code have? And merry Christmas!

[Raveline]

We've currently 16 tables and 4 typedefs. (Merry Christmas and happy holidays to you too !)