You're probably right that SQL should have earlier defined this sort of recursion, so that you could sequence groupings and filters more easily, but don't fret: that future is here with common table expressions. Recursion is even supported!

I'm reminded of this abomination of a query I wrote in 2010 that produced a numbers table:

http://blogs.msdn.com/b/sqlazure/archive/2010/09/16/10063301...

Code:

    DECLARE @N int = 1000000;

      WITH RecursiveRowGenerator (Row#, Iteration) AS (
           SELECT 1, 1
            UNION ALL
           SELECT Row# + Iteration, Iteration * 2
             FROM RecursiveRowGenerator
            WHERE Iteration * 2 < CEILING(SQRT(@N))
            UNION ALL
           SELECT Row# + (Iteration * 2), Iteration * 2
             FROM RecursiveRowGenerator
            WHERE Iteration * 2 < CEILING(SQRT(@N))
         )
         , SqrtNRows AS (
           SELECT *
             FROM RecursiveRowGenerator
            UNION ALL
           SELECT 0, 0
         )
    SELECT TOP(@N) 1 + A.Row# * POWER(2,CEILING(LOG(SQRT(@N))/LOG(2))) + B.Row# Row#
      FROM SqrtNRows A, SqrtNRows B
     ORDER BY A.Row#, B.Row#;

What I couldn't see at a quick glance was a way to constrain the data in a table or enforce foreign key constraints. I guess those are the trade offs against something like Postgres for schema flexibility and easy distribution. Is that a correct way to view it?

Under the hood, yes. How much of that is exposed to the user is debatable.

> What I couldn't see at a quick glance was a way to constrain the data in a table or enforce foreign key constraints. I guess those are the trade offs against something like Postgres for schema flexibility and easy distribution. Is that a correct way to view it?

That's about right. Some of this is by design (lack of schema enforcement), and we might add features to do that later. There are no architectural constraints preventing us from doing that.

Foreign key constraints will probably never be added to Rethink (or at least not for a long, long time) because doing those efficiently in distributed systems is anywhere from hard to impossible.

I think, If I were implementing a database from the ground up, I'd indeed make my own query language--SQL does suck--but I would probably strive to accept SQL as an option, with the proviso that it's "just SQL syntax with ThisDB's semantics."

This would let people point their old applications, written under "the database is just an SQL-speaking dumb store for simple CRUD operations" assumptions (e.g. any ActiveRecord app), at the new DB and get somewhat-sensible results out, while still letting them write new applications against the new API.

The alternative is forcing any user with existing services to hack up some sort of application-level back-and-forth replication between their old DB and your new DB during the long-and-possibly-indefinite migration period, rather than just migrating in one fell swoop.

I think that the lack of a SQL interface is a big negative. It's really hard to find analyst (not programmers) with good technical skills or train them to proficiency in new technologies. I would have a very hard time selling a platform that existing analysts wouldn't be able to use right away and that few people on the market could work with.

Also, designing a new language makes integration with other applications that much harder.

"We're assuming people will be using ReQL fifteen years from now."

What problem does your product solve that, for example, PostgreSQL doesn't?

  r.table('foo').filter(...)
  r.table('foo').group('category').filter(...)

From the examples you have posted, it appears that ReQL is a lower-level abstraction that SQL. In SQL you specify what you want logically and the DB turns this into a query plan. It appears that ReQL is more like a query plan itself, where you explicitly specify the data flow from stage to stage of query evaluation.

As a more specific example of this, it appears that in many cases in ReQL the user specifies what index should be used in the query itself. SQL is more abstract than this; the idea is for the query planner to figure out what index(ex) should be used.

It's suggesting:

    SELECT category,
       SUM(num_comments)
    FROM posts
    GROUP BY category
    HAVING num_comments > 7

    r.table("posts")
     .filter(r.row['num_comments']>7)
     .group('category')
    .sum('num_comments')

I don't think they are? Your ReQL to me looks like it's applying WHERE num_comments > 7 and then aggregating that.

I mean regardless, your example SQL should be doing the >7 against an aggregate (e..g sum(num_comments)) not a field, that SQL does not work as written.

In ReQL any command you call after `group` runs on each group. So once you've called `group`, you can run anything you could run on a table on each group and that just works.

SQL just happens to call those WHERE and HAVING instead of "filter" both times.

  # get a sample of 3 elements from a table
  r.table('foo').sample(3)
  
  # get a sample of 3 elements from each group
  r.table('foo').group('category').sample(3)

HAVING exists because it was created prior to subqueries/dynamic tables, otherwise we'd have been likely to just use:

    select * from (select category, sum(num_comments) as comments from posts group by category) as temp where comments > 7