News for hackers. Stuff that matters.
Re:The SQL filter clause: selective aggregates by MichaelBurge on Monday May 30, 2016 @08:46AM (#11801036)
At an old job, me and another person wrote an in-house SQL-like dialect that compiled to access our C++ data structures. It was kind of nice just throwing whatever junk you wanted into your SQL without having to worry about any standards.

Want a filter clause? Got it.

Need a weighted distinct count? Now it's part of the language.

Also, you can rephrase a lot of these SQL features as subqueries. It's surprising how many database bugs you can find when you do it. Not so much in Postgres, but I probably found a dozen in Redshift. I mean rephrasing this:

    select
      sum(x) filter (where x < 5),
      sum(x) filter (where x < 7)
    from
      generate_series(1,10,1) s(x)
    ;
as this(where t is either a permanent table, view, or CTE as appropriate):

    with t as (
      select * from generate_series(1,10,1) s(x)
    )
    select
      (select sum(x) from t where x < 5),
      (select sum(x) from t where x < 7)
    ;
Though for Postgres the filter will likely have better performance.
Re:The SQL filter clause: selective aggregates by brudgers on Monday May 30, 2016 @10:33AM (#11801514)
Depending on the number of years ago, it may have been ahead of its time. C# and Scala have SQL like language abstractions a few years ago via LINQ. Scala has something similar.
Re:The SQL filter clause: selective aggregates by duaneb on Monday May 30, 2016 @11:35AM (#11801770)
Scala doesn't have anything like this where the query is compiled monadically before execution, although the data structures do expose excellent ways to chain functional combinators. Squeryl (and there's a similar competitor) implement this for SQL, and I'd say it's 80% there--still a lot of rough edges fitting dynamic queries (e.g. number of tables joinable in a single statement) into a static language.
Re:The SQL filter clause: selective aggregates by virtualwhys on Monday May 30, 2016 @02:28PM (#11802439)
> Scala doesn't have anything like this where the query is compiled monadically before execution

Nonsense, Scala is arguably leading the way in the statically typed composable query dsl department; see Slick [1] and Quill [2] among others.

Outside of Haskell's Esqueleto I am not aware of any statically typed query dsl that comes remotely close to the aforementioned. LINQ to SQL/Objects provides static query generation but doesn't compose; beyond that, what is there?

[1] https://github.com/slick/slick [2] https://github.com/getquill/quill

Re:The SQL filter clause: selective aggregates by tome on Monday May 30, 2016 @06:07PM (#11803493)
Re:The SQL filter clause: selective aggregates by Arnavion on Monday May 30, 2016 @03:26PM (#11802659)
>LINQ to SQL/Objects provides static query generation but doesn't compose

How do you mean? The result of a LINQ query is a sequence that can be used as the input of another LINQ query.

Re:The SQL filter clause: selective aggregates by virtualwhys on Monday May 30, 2016 @04:01PM (#11802849)
By LINQ query I mean LINQ to SQL, not the basic LINQ collections functionality (the equivalent in Scala being for comprehensions, BTW).

If you have an example of .NET based query composition to share I'd like to see it; last I checked there was no such functionality available.

Re:The SQL filter clause: selective aggregates by MichaelGG on Monday May 30, 2016 @04:23PM (#11802965)
LINQ to SQL ends up creating an expression on IQueryable. You can build this expression up any way you want, including in bits and pieces. You can tack on Wheres and Selects any time you like. It's more composable than pain text SQL since the parts can go in any order.

It's only after you force it to materialize (I.e. call ToList) that you can't further "compose" it.

Re:The SQL filter clause: selective aggregates by rubyfan on Monday May 30, 2016 @03:30PM (#11802679)
The Scala package Circumflex ORM had such a feature ~6 years ago. It had some problems here and there but was interesting to write SQL as native Scala.

http://circumflex.ru/projects/orm/index.html

Re:The SQL filter clause: selective aggregates by rm999 on Monday May 30, 2016 @01:10PM (#11802161)
Re:The SQL filter clause: selective aggregates by goldenkey on Monday May 30, 2016 @09:20AM (#11801165)
Arent subselects better performing than comparable joins and aggregates when the query optimizer screws up? I remember reading an HN post not too long ago.
Re:The SQL filter clause: selective aggregates by MichaelBurge on Monday May 30, 2016 @09:31AM (#11801216)
If you're talking specifically about the comment at the end, that was because the example query will do a seq scan on the table twice. The filter equivalent will only do one scan.

I find subselects are more predictable, but I wouldn't say they're generally more performant. The explain plan's tree maps more closely to the subquery dependency tree with selects, so someone tweaking their query and staring at the explain plan to get it how they want will have an easier time with the subselect strategy.

Be careful of CTEs if you try that, though. A long chain of CTEs with the transformation I gave is almost guaranteed to give a bad plan(unless you want seq scans all the way down).

Re:The SQL filter clause: selective aggregates by snissn on Monday May 30, 2016 @01:21PM (#11802209)
Could you give a quick sketch of how you designed your sql parser (traspiler?)? Thank you!