SQLite Forum

Order by after a union fails when a function is used in the order by
Login
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
```