Why the given syntax is valid in mysql?

mysql, sql

Solution

It's the alternative `UNION` syntax with a final `ORDER BY`.

This is what such a union between two selects looks like:

(SELECT ...)
UNION
(SELECT ...) ORDER BY ... LIMIT ...

And this is what such a union between one select looks like:

(SELECT ...) ORDER BY ... LIMIT ...

Not related to subqueries at all.

This isn't documented in MySQL, but is obvious from the grammar:

top_level_select_init:
        SELECT_SYM
        {
            Lex->sql_command= SQLCOM_SELECT;
        }
        select_init2
        | '(' select_paren ')' union_opt
        ;


/* Need select_init2 for subselects. */
union_select_init:
        SELECT_SYM select_init2
        | '(' select_paren ')' union_opt
        ;

...

union_opt:
        /* Empty */ { $$= 0; }
        | union_list { $$= 1; }
        | union_order_or_limit { $$= 1; }
        ;

Problem

In another answer I've spotted a weird syntax: ``` (SELECT * FROM `articles` WHERE date >= UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 30 DAY)) ORDER BY `views` DESC LIMIT 20 ) ORDER by `views` ASC ``` which was executed by mysql well though. Why I think it should fail: - The subquery doesn't have alias - The whole query lacks `SELECT` clause I find it unexpected to run and don't have an explanation why it works. It does not fit the grammar defined on https://dev.mysql.com/doc/refman/5.5/en/select.html So, why is it valid? Any references?

Original source