No, it is not. I mean it is, but not in parts where that could be seen as useful and/or convenient. [A]cyclic graph traversal/etc is one of the basic tests in a modern interview at any CRUD studio. How come it could do $%^& to any part of yours?

Because just implementing the standard stuff nearly did my $^&% nut. Also I know about graphs & posets, and it's potentially a little more complex than it seems. The variables

    select x * x as y, 1 as x

    select 
        (select tbl.z from tbl where tbl.y = y) as subq, 
        x * yy as y,
        xx + 1 as x,
        subq + yy as zzz
    from ( 
        select xx, yy
        from ... )

I've implemented the ability to reference select-list aliases before; it's not that hard to do if implemented basically like a macro expansion. The main problem is user confusion due to ambiguous references, e.g.

    select 2 as x, x as `which x?`
    from (select 1 as x) t;

IMO only allowing references to previous select list items is a perfectly reasonable restriction; loosening it isn't worth the implementation headache or user confusion. Though we did allow using aliases in the WHERE clause.

You're just looking for symbols in the symbol table, I think it's a big difference!

> IMO only allowing references to previous select list items is a perfectly reasonable...

agreed, see my other post where I say the same.

> Though we did allow using aliases in the WHERE clause

And the SQL standards people didn't go for this, and I'm sure they were very far from stupid. And nobody's asking why they didn't allow this, which really bothers me.

But yes, I'd be far happier doing left-to-right dependencies only, which I can believe (though I still need evidence) it would be of some value.

I just realised why this was bothering me. That means 't' and 't.x' are actually different variables. In standard SQL it's always the case (right?) that an unqualified variable ('t') is just an convenient shorthand for the fully qualified variable ('t.x', or more fully I suppose, '<db>.<schema>.t.x), and you just broke that.

That’s no different than the first snippet, if you aren’t parsing it with regexps, of course. The resulting AST identifiers would simply refer to not only column names, but also to other defined expressions. This is the case for both snippets. It’s either cyclic or not, and when not, it is easy to substitute/cse/etc as usual. The complexity of these expressions is irrelevant.

I can do that I just don't fancy it, and more to the point nobody is giving me an example of where it would be particularly helpful. So if anyone can, I'm interested.

(also, consider human factors; although an acyclic definition could be extracted from an unordered expression set, a consistent left to right (in the western world anyway, matching textual layout) with dependencies being introduced on the right and depending only on what came before on the left might actually be better for us meatsacks)

I could reach to and post a lenghty example, but it’s nothing but boring reshuffles really, spiced with 3-level joins of “modelling db in db to allow user columns”.

I agree on the LTR idea, because reading a symbol not yet defined may lead to confusion.

    select id, count(...something complicated) as complicated_count
    from ....
    order by complicated_count

This isn't really the large, convincing example I was looking for btw.

IMO it's most useful (though somewhat more difficult to implement) to be able to use the aliases with window functions or large case/when statements, something like

   SELECT
     page,
     SUM(clicks) AS total_clicks,
     100. * total_clicks / (SUM(total_clicks) OVER ()) AS click_pct,
     100. * SUM(total_clicks) OVER (ORDER BY total_clicks DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / (SUM(total_clicks) OVER ()) AS cumulative_click_pct
    FROM weblog
    GROUP BY page;