Hacker News Reader

Comment by Gaelan | Hacker News Reader

Note the example above:

    (select a from nums) order by b;
     a
    ---
     3
     0
     1
     2
    (4 rows)

If things worked as you described, that would also fail.

So Postgres is, in some cases, cheating with the "new set" thing. The weirdness is that it does it inconsistently.

View on HN

But that's the thing, it's not

    (select a from nums) order by b;

but

    (select a from nums order by b);

What I’ve noticed in my years with various databases is that many SQL parsers error on ambiguity and not necessarily strict compliance. In the case above the parser is likely dropping the parentheses where in the union there’s ambiguity because of the use of two sets and it can’t tell between which set’s b you’d like to order by.

I wonder what it would do if you aliased the subquery and ordered by alias_one.b

I think it's even simpler. Starting a subquey here doesn't make sense since there is no main query. So the parens gets dropped promoting this query to main. Same with the union

ORDER BY actually happens last.

    jamie=# (select a, a+1 as c from nums) order by b,c;
     a | c 
    ---+---
     3 | 4
     0 | 1
     1 | 2
     2 | 3

(4 rows)

It's a separate part of the grammar:

    <query primary> ::=
        <simple table>
      | <left paren> <query expression body> [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] <right paren>

But, if "QE is a <query expression body> that is a <query term> that is a <query primary> that is a <simpletable> that is a <query specification>" then the <order by clause> may select columns from the <table expression> even though those don't really exist any more by the time the <order by clause> runs. It's tricky to implement.

I agree, I should have written that it helps to think in sets rather than tables. SQL implementors do a lot of fantastic shit to get our queries to run in a reasonable time, and SQL is not one of those langages that are like "understand this one core principle, and you get it" like lisp, prolog, TCL, Smalltalk, Forth etc. Have a look at the query plan if your DBMS can output it, it's not always easy to guess what it will spit out. In the case of

    (select a from nums) order by b;

my best guess is that the parser helpfully strips out the parens as because the "order by" needs to be a part of a table expression to make sense. This expression is a syntax error in SQLite by the way.

Reply on news.ycombinator.com