No question, such a query should not be written. That's probably the reason why this odd behavior, which is even different in various DBMSes, is not causing everyday problems.
> Procedural SQL [...] Choose your investment here carefully.
I think that the demand for procedual code has dropped drastically in the past decades as the "normal" SQL can solve so many more things with window functions, recursion, and so forth. So I'd say: Yes, choose your investment wisely and stay away from procedural SQL as long as possible.
The existing implementations (Oracle DB, SQL Server, MariaDB, Big Query) come with their problems too. I was a big fan of the new features when it came out in 2011, but pratically there is an unsolved elephant in the room: It doesn't cover schema changes.
Fair point, looks suspicious on first sight. I've checked my tests briefly: It's accepted as table and column name. As mentioned in footnote [0] I haven't checked different contextes yet.
Can you tell me: what kind of identifier is it (view name, function name) and which SQL context it causes problems (select list, create/drop statement, ...) and which system has problems with it. Thx.
> The standard is not generally available. Most of us will never learn what is in it.
It also not aimed at users, but at implementors. Funny enough, they don't read the standard either ;) But more seriously: Some implementations are old and generally vendors prefer not changing the behavior of their product. When the standard comes later, the train has already departed. The most critical incompatibilities are in the elder features. The newer ones have a tendency the be more aligned with standard behavior (e.g. window functions are typically implemented just fine).
> They haven't gotten as far as working out variables or function composition either [0].
Part 2 SQL[0] is declarative and intentionally doesn't have variables. Part 4 SQL (aka "pl" SQL) does have variables. I personally consider Part 4 obsolete on systems that have sufficient modern Part 2 support.
> Tying SQL to your specific database is the best option for performance. Writing database-independent SQL is somethign of a fools errand, it is either trivial performance-insensitive enough that you should be using a code generator or complex enough to deserve DB-specific turning.
While this is certainly true for some cases there are also plenty of examples where the standard SQL is more concise and less error prone than product-specific alternatives. E.g. there COALESCE is the way to go rather than isnull, ifnull, nvl, or the like (typically limited to two arguments, sometimes strange treatment of different types).
There is a lot of *unnecessary* vendor-lock in in the field.
I'm studying the SQL standard for years now and compared to other standards that I know (XSLT, a little CSS, decades ago POSIX, C and C++) the SQL standard is really hard to make sense of. You might overestimate the value of having access to it.
Luckily pretty much nobody needs the standard documents. It's actually my aim at https://modern-sql.com/ to make the relevant information more accessible — in particular including support-matrices ("Can I Use").
Besides portability, there is IMHO nothing against INSERT ... ON CONFLICT if it does what you need.