Friendly reminder:

The UNION and UNION ALL operators may order the rows in the final result set in an unspecified order. For example, it may place rows from the second result set before/after the row from the first result set.

I have encountered a situation where a query with UNION and UNION ALL resulted in a stable different row order...

UNION is often used for something like this:

SELECT * FROM (
    SELECT id FROM t WHERE name = 'user_input'
    UNION
    SELECT id FROM t WHERE name = 'default_value'
)
LIMIT 1

It is important that if a record with 'user_input' is found, it should be the first record in the result.
In such a case, add an ORDER BY of this form to the query:

ORDER BY name = 'default_value'

https://wafris.org/blog/rearchitecting-for-sqlite?

mysql 9.0

https://dev.mysql.com/doc/refman/9.0/en/mysql-nutshell.html

What do you think they never did? modifying CTE and RETURNING

https://masteringpostgres.com/types

thx https://x.com/samokhvalov/status/1846674221419966485?s=46

btw, don’t forget about https://wiki.postgresql.org/wiki/Don't_Do_This

fxtwitter.com/samwillis/status/1848672368819892233

Five years ago, in holistic.dev, we made full-fledged type inferences at any point in a query based on static analysis techniques. In addition to types, nullable and row count classes (none/one/one_or_none/many/many_or_none) were output...

https://www.youtube.com/watch?v=ZzAgacFBr48