Order by after a union fails when a function is used in the order by

(1) By Stephan Beal (stephan) on 2020-07-29 22:59:54 [link]

i'm working on a new Fossil SCM feature which requires a list of all wiki pages in the repository, with the caveat that the list should also contain the name "sandbox", for the special-case sandbox wiki page:

```
$ fossil sqlite
sqlite> SELECT substr(tagname,6) as name  FROM tag WHERE tagname GLOB 'wiki-*' UNION SELECT 'sandbox' as name order by name;
'Apache On Windows XP'
'Auto-completion'
'Branching'
'Cookbook'
'Documentation outline'
'Extending Fossil''s Wiki'
...
```

That's all fine and good, but if i change the order by to use `lower(name)` in order to get case-insensitive sorting:

```
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 (certainly not) or is a function not permitted in the order by of a union (that seems odd but less likely than a bug)?

Interestingly, replacing `lower(name)` with `name collate nocase` gives me the desired result:

```
sqlite> SELECT substr(tagname,6) as name  FROM tag WHERE tagname GLOB 'wiki-*' UNION SELECT 'sandbox' as name order by name collate nocase;
'Apache On Windows XP'
'Auto-completion'
'branch/accordion-experiments'
'branch/andygoth-restore-related'
...
```

This is using the current tip of the fossil trunk, so it's:

```
SQLite 3.33.0 2020-07-18 18:59:11 020dbfa2ae
```

(2) By Richard Hipp (drh) on 2020-07-30 00:12:00 in reply to 1

~~~~~
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.