SELECT wat FROM sql

Re:SELECT wat FROM sql by rwbaskette on Monday April 27, 2020 @06:46AM (#22994643)

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

[ Parent | on HN ]

Re:SELECT wat FROM sql by progre on Monday April 27, 2020 @07:15AM (#22994758)

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

[ Parent | on HN ]

Re:SELECT wat FROM sql by jamii on Monday April 27, 2020 @01:29PM (#22997913)

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.

[ Parent | on HN ]