SQLite Forum

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

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 [source]

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 [link] [source]

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.