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!
Re:The SQL filter clause: selective aggregates by Xophmeister on Monday May 30, 2016 @08:35AM (#11800985)
Syntactic sugar that's only natively supported in PostgreSQL? The site's banner says, "A lot has changed since SQL-92". Be that as it may, but it seems no one has really bothered catching up. I wonder why that is...

My guess is that such extensions, while useful, are somewhat marginalised features in terms of usage. Thus, no one ever learns them formally and just Googles for what they need -- if it comes up -- and get the CASE solution, in this case (pun unintentional). Hence perpetuating that pattern. Also, of course, the CASE solution is a lot more powerful as the returned expression, that gets fed into the aggregate function, can be basically anything.

Re:The SQL filter clause: selective aggregates by driusan on Monday May 30, 2016 @11:21AM (#11801703)
I think the most well-thought-out explanation I've seen for why widespread support for the SQL standard seemed to stop after SQL-92 is this one:

http://www.wiscorp.com/is_sql_a_real_standard.pdf

Re:The SQL filter clause: selective aggregates by Xophmeister on Monday May 30, 2016 @11:58AM (#11801879)
This was quite interesting; thank you for posting... I may be cheeky and submit it to HN.
Re:The SQL filter clause: selective aggregates by driusan on Monday May 30, 2016 @12:24PM (#11801981)
I'm fairly sure I originally saw it on HN.
Re:The SQL filter clause: selective aggregates by MarkusWinand on Monday May 30, 2016 @08:40AM (#11801005)
I see the value of FILTER mostly in ease to read and understand.

Many things — e.g., Pivot — can be understood way more easily using FILTER rather than CASE.

Re:The SQL filter clause: selective aggregates by baq on Monday May 30, 2016 @04:27PM (#11802988)
and in js transpilers are bread and butter tech. sql isn't as cool i guess.
Re:The SQL filter clause: selective aggregates by westurner on Monday May 30, 2016 @01:27PM (#11802234)
Re:The SQL filter clause: selective aggregates by codegeek on Monday May 30, 2016 @08:55AM (#11801064)
"with" is not supported by mysql but sqlite supports it ? wow, didn't know that.
Re:The SQL filter clause: selective aggregates by goldenkey on Monday May 30, 2016 @09:17AM (#11801147)
You can still do many subselect queries without with. Its really only an issue with the more complex usages.
Re:The SQL filter clause: selective aggregates by Xophmeister on Monday May 30, 2016 @09:24AM (#11801187)
I personally find CTEs much easier to read than subqueries.
Re:The SQL filter clause: selective aggregates by wesd on Monday May 30, 2016 @10:39AM (#11801533)
and you can use them in multiple places. I'm not sure if you have the same subquery twice if the query optimizer uses a single dataset.
Re:The SQL filter clause: selective aggregates by goldenkey on Monday May 30, 2016 @05:19PM (#11803272)
Be careful though, above poster mentions that a chain of CTE is almost certain to lead to sequential scans rather than parallelized (optimized) execution.
Re:The SQL filter clause: selective aggregates by rubyfan on Monday May 30, 2016 @03:41PM (#11802746)
Is this all that different from Hive's analytic functions? I don't think I can put a WHERE filter right in the OVER clause though.

SELECT a, COUNT(b) OVER (PARTITION BY c) FROM T;

https://cwiki.apache.org/confluence/display/Hive/LanguageMan...

Re:The SQL filter clause: selective aggregates by ibejoeb on Monday May 30, 2016 @01:19PM (#11802201)
For those on Oracle or MS SQL, be aware of the PIVOT and UNPIVOT features. It's not as general as this, but it's terse and works well for the case, i.e., running an aggregate across specific buckets.
Re:The SQL filter clause: selective aggregates by ams6110 on Monday May 30, 2016 @02:47PM (#11802509)
If I'm understanding this correctly, you can also do this sort of thing as a UNION of aggregate queries, each one with its own WHERE clause corresponding to the desired FILTER.
Re:The SQL filter clause: selective aggregates by d33 on Monday May 30, 2016 @09:40AM (#11801264)
It would be nice to see an example for this kind of query.

BTW, SQL seems like a terrible language. For example, why would it need to enforce ordering for keywords like ORDER BY, WHERE, GROUP BY? So many times I had made a mistake that boiled down to putting them in the correct order...

Re:The SQL filter clause: selective aggregates by MichaelBurge on Monday May 30, 2016 @09:45AM (#11801297)
One reason is to remind you of the SQL order-of-operations:

* The from clause retrieves records from data sources like set functions, tables, or views.

* The on/using clause joins tables

* The where clause filters out records

* The group by clause assigns records to buckets

* The having clause filters out records

* The select clause runs

* The order by sorts

* The limit clause picks records

In this query:

> select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true order by 1 limit 1;

The only out-of-place component is the select and window function(which runs after the group by clause). In a more complex query with multiple selects in scope, the order of terms helps remind you which parts are running in which order.

Note that the optimizer won't strictly enforce the order; it's just a guide to interpret what the results should be.

If you need to do something out-of-order, you can use a subselect:

> select * from (select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true limit 1 ) x order by 1;

Here, I've swapped the limit clause and the order-by clause, although you can still see the order of operations in the explain plan being separated by a 'Subquery scan'.

Re:The SQL filter clause: selective aggregates by kbenson on Monday May 30, 2016 @02:12PM (#11802390)
> The having clause filters out records

I think you mean "The having clause filters out buckets", right?

Re:The SQL filter clause: selective aggregates by jakobegger on Monday May 30, 2016 @01:07PM (#11802146)
Assume that you have a table 'orders' similar to the following:

    nr | date       | product | quantity
    ---+------------+---------+----------
     1 | 2016-05-29 | a       | 2
     2 | 2016-05-29 | a       | 7
     3 | 2016-05-29 | b       | 3
     4 | 2016-05-29 | a       | 1
     5 | 2016-05-30 | b       | 5
     6 | 2016-05-30 | a       | 2

And you want to get daily order totals for each product.

Then you do:

    SELECT
      date,
      SUM(quantity) FILTER(WHERE product='a') AS product_a,
      SUM(quantity) FILTER(WHERE product='b') AS product_b,
      SUM(quantity) AS total
    FROM orders
    GROUP BY date
    ORDER BY date
And the result is

    date       | product_a | product_b | total
    -----------+-----------+-----------+-------
    2016-05-29 | 10        | 3         | 13
    2016-05-30 | 3         | 5         | 8
Re:The SQL filter clause: selective aggregates by snissn on Monday May 30, 2016 @01:25PM (#11802230)
Thank you.
Re:The SQL filter clause: selective aggregates by return0 on Monday May 30, 2016 @09:59AM (#11801366)
> SQL seems like a terrible language.

The reason you give is too trivial to justify calling it "a terrible language"

Re:The SQL filter clause: selective aggregates by JustUhThought on Monday May 30, 2016 @10:51AM (#11801580)
E.F. Codd (author of the whitepaper that is the foundation of all RDBMSs, for which SQL was created to query) was of the opinion that the SQL was a terrible language. His reasoning for saying so are very clearly and well speelled out in several books and articles of his.
Re:The SQL filter clause: selective aggregates by mamcx on Monday May 30, 2016 @02:22PM (#11802421)
That reason is in fact a good one. The order is "wrong" from the point of view of a developer and maybe ok from the POV of the original intent of SQL: The end-user was supposed to run SQL against databases. Few ad-hoc queries, even complicated, can be managed. But using SQL all the time, and the language is clearly the wrong thing to talk to the DB (is like JS: Is what you have instead of what you want).

Also: The fact that everyone run (even if eventually fail) for a ORM instead of SQL, or the way people imagine NOSQL is "better" -is very telling is NO SQL- show this.

The relational model is so simple and powerfull, but SQL is not a good seller of the idea.

Re:The SQL filter clause: selective aggregates by bluejekyll on Monday May 30, 2016 @03:36PM (#11802717)
Is there another language agnostic option?

I suppose it would be interesting if you could use something like the llvm to target a DB's core features, but through your language of choice.

I haven't seen anything like that, if it exists. SQL is that standard API today, and ORMs provide the language specific implementation (though sometimes in ways that make it inobvious what the generated SQL will look like).

Re:The SQL filter clause: selective aggregates by pjungwir on Monday May 30, 2016 @04:12PM (#11802900)
I actually like SQL, but I'm glad people are willing to ask, "Can we do better?" I think for a working programmer, SQL is the only practical choice, but Tutorial D is an interesting attempt to improve (by C.J. Date, described at length in his books Database in Depth and The Third Manifesto), with an open source implementation called Rel. There is also a commercial database called Dataphor that uses D4, a cousin to Tutorial D. There is also a newer project called Andl that is another non-SQL relational query language.

Personally, although I'm not a researcher, I've become intrigued by ways we model change-over-time in relational databases. Snodgrass's book _Developing Time-Oriented Database Applications in SQL_ is a good entry point to all that, and more recently we have SQL2011's Temporal standard. I still have a lot to read here myself, but I am keen to see what happens in the next ten years in how the relational model can incorporate change.

Re:The SQL filter clause: selective aggregates by smt88 on Monday May 30, 2016 @06:15PM (#11803527)
SQL is a lot like JavaScript: a lot of people hate working with it, but it's the only practical choice, as you say.

It's interesting to note that transpiling to SQL is even more popular than the recent trend of transpiling to JavaScript. Every major language has a query builder (many of which end up with syntax similar to Andl). We're abstracting away from SQL and have been for a long time.

With that in mind, I wonder whether we need a new query language so desperately. SQL is so highly optimized in the major RDBMS that any new language would probably compile internally to SQL, meaning it has the same semantics (and is essentially sugar) OR it would be way behind SQL in terms of optimization.

Am I just totally off base here?

Re:The SQL filter clause: selective aggregates by pjungwir on Monday May 30, 2016 @06:37PM (#11803614)
After I wrote that comment I thought it might be worth submitting this Andl story to HN about Andl-on-Postgres: [0]. They write, "Relational expressions are converted into equivalent SQL and passed back to the Postgres SPI interface for execution," so you are right about transpiling. But the software world is more mature than it was 20 years ago, so it seems reasonable for a project to get a toehold by building on top of existing systems. That needn't limit its future growth necessarily. It sounds like Andl will work on more than just Postgres, so I can imagine them eventually building a "native" version too. Using existing RDBMSes is a pretty good way to get an MVP though. :-)

[0] https://news.ycombinator.com/item?id=11802917

Re:The SQL filter clause: selective aggregates by smt88 on Monday May 30, 2016 @07:37PM (#11803805)
> Relational expressions are converted into equivalent SQL and passed back to the Postgres SPI interface for execution

I actually don't think I like that approach.

If I transpile my own queries (meaning I use a query builder), then I can cache the generated SQL. There would be some placeholders for arguments, but the query itself doesn't change.

Andl's approach could add significant overhead in certain applications where lots of queries are being executed at once.

And then what happens to EXPLAIN support? Is it harder to debug an Andl query?

Re:The SQL filter clause: selective aggregates by MichaelGG on Monday May 30, 2016 @04:29PM (#11802997)
Wasn't QUEL a lot better?
Re:The SQL filter clause: selective aggregates by return0 on Monday May 30, 2016 @09:39PM (#11804105)
I think people use an ORM so that they can "switch data stores". Personally i never used one since SQL is very easy to write and its actually a Standard. Even when i did switch databases, having a standard guarantees there will be fewer changes needed, than if switching orms. Also, what people "imagine" is of zero concern.
Re:The SQL filter clause: selective aggregates by mamcx on Tuesday May 31, 2016 @08:40AM (#11806178)
In theory maybe. I think instead is looking for a nicer interface (BTW: I learn new languages building a ORM!).
Re:The SQL filter clause: selective aggregates by d33 on Monday May 30, 2016 @10:14AM (#11801433)
Okay, so another reasons are: that 1) it's terribly difficult to parse and 2) on large tables, messing with query planner is actually more work than just specifying the algorithm you'd like to use. Also: 3) yes, there is an SQL standard, but generally if you're writing any application that uses SQL, you're going to end up writing code for a specific database or use an ORM.
Re:The SQL filter clause: selective aggregates by ufmace on Tuesday May 31, 2016 @02:39PM (#11808920)
The only real beef I have with SQL is that some of the aspects of the ordering seem weird.

Why does SELECT put the column list at the start instead of the end? For all but the simplest queries, I want to set up my tables and joins and group bys and whatnot and then think about what columns I want from them. Seems kinda cumbersome to start with SELECT * FROM ... and then go back and fill in the columns later.

Why does UPDATE put the SET before the WHERE? Feels like an accident waiting to happen. I got into the habit long ago of writing SET x = x WHERE..., writing the correct WHERE statement, then going back and writing correct/valid sets, just to make sure I don't accidentally execute the query too soon and update the whole table.

Re:The SQL filter clause: selective aggregates by snaky on Monday May 30, 2016 @10:20PM (#11804209)
> SQL seems like a terrible language

That's because idea of SQL was the same as COBOL - kinda-natural more or less ad-hoc end-user-oriented language.

Codd ('Relational Completeness of Data Base Sublanguages') - “Clearly, the majority of users should not have to learn either the relational calculus or algebra in order to interact with data bases. However, requesting data by its properties is far more natural than devising a particular algorithm or sequence of operations for its retrieval. Thus, a calculus-oriented language provides a good target language for a more user-oriented source language.”