Comment by BeefWellington

This is another in a series of these kinds of proposals that look excellent on first glance for perhaps the 75% case but start getting syntactically messy when I want to customize the resultset returned.

On the surface, they're always neat but when you start to dig into how you'd implement something in an RDBMS, it begins to fall apart.

Let's look at the example syntax:

    from employees
    filter country = "USA"                         # Each line transforms the previous result.
    gross_salary = salary + payroll_tax            # This _adds_ a column / variable.
    gross_cost   = gross_salary + healthcare_cost  # Variable can use other variables.
    filter gross_cost > 0
    aggregate split:[title, country] [             # Split are the columns to group by.
        average salary,                            # These are the calcs to run on the groups.
        sum     salary,
        average gross_salary,
        sum     gross_salary,
        average gross_cost,
        sum     gross_cost,
        count,
    ]
    sort sum_gross_cost                            # Uses the auto-generated column name.
    filter count > 200
    take 20

Where in here is it clearly stated which fields are returned? In the original SQL it's right up front but here it's buried into the "aggregate" function, and I'm not clear that this isn't an oversight.

Another example that speaks to the "how do I implement this" side of the equation:

    from employees
    filter country = "USA"                         # Each line transforms the previous result.
    gross_salary = salary + payroll_tax            # This _adds_ a column / variable.
    gross_cost   = gross_salary + healthcare_cost  # Variable can use other variables.
    filter gross_cost > 0

Does this mean that the database must scan all records of the employee table in order to return the result before moving to the next step in the query? Must I index all fields? If not, how does a query planner prepare for this scenario?

The major tradeoff you make in most ORMs is exactly this: You lose out on being able to be explicit about how many queries are sent to the DB (and in many cases how efficient those queries are). Now this would become a language feature? What do I gain for that loss?

I'm not saying that SQL Syntax is perfect; far from it. I'm not seeing how this is an improvement.

I think if you want traction though, a proof of concept using an existing RDBMS would go a long way into providing evidence that this will work and is sufficiently thought out to deal with even the basics of what existing SQL databases have to. Query planning is hard, especially if you want it to be fast.