It seems like the problems were with PostgreSQL flavored queries.
Speaking of which - I know Microsoft has Transact-SQL and Oracle has PL/SQL, is PostgreSQL just PostgreSQL?
Eg here is the `a+1` vs `1+a` thing explained for ORDER BY - https://i.imgur.com/bklpjkP.png. Here are the conditions under which ORDER BY can reach inside SELECT to pick columns from the FROM clause - https://i.imgur.com/aoeqDGn.png.
Chapter 2 of the spec is 1732 pages of that kind of language, so it's hard to be sure that any particular behaviour is not in there somewhere. But I'm fairly sure that all the examples with set-valued functions are postgres-isms.
From that brief read of the ANSI Spec it seems like one would need a great deal of time and some advanced mathematics to understand it.
I think a lot of what makes SQL hard to implement comes down to consistently prioritizing local convenience over global complexity, combined with not being able to break backwards compatibility to simplify the design.
However, there are other PL's for PostgreSQL. PL/Python, PL/Perl, and PL/Tcl are also distributed with the database and there are others available (https://www.postgresql.org/docs/12/external-pl.html... though I've only used JavaScript)
In both Oracle and Postgres, the SQL dialect, as opposed to the procedural language, is just called “SQL”.
All three have variations from ANSI SQL in their non-sproc SQL too, oracle's far worse than the other two, but pg and SQL Server document where they deviate pretty damn well.
There definitely were ANSI examples in there, "ORDER BY 2" is in ANSI (and IIRC plenty of pre-ANSI dialects as well) for a start, I'm too tired (and/or lazy) to go back and look for others, sorry.