SQLite Forum

Order by after a union fails when a function is used in the order by
sqlite> SELECT substr(tagname,6) as name  FROM tag WHERE tagname GLOB 'wiki-*' UNION SELECT 'sandbox' as name order by lower(name);
-- ^^^^ note that the only difference is the order by
Error: 1st ORDER BY term does not match any column in the result set

> Is that a bug

No.  In standard SQL, the ORDER BY clause must be either (1) a positive
integer that means sort by the corresponding column or (2) an exact copy
of one of the expressions that defines the column.  The ORDER BY may not
have arbitrary expressions, in standard SQL.  Many RDBMSes (including
SQLite) extend this so that you can have arbitrary expressions in the
ORDER BY clause, but in the case of a UNION query, that enhancement
does not apply (for technical reasons) and the original standard-SQL
restriction is in force.

So in your query, the ORDER BY can contain "name" or "substr(tagname,6)" or "1"
but no other expressions.