It's not a bug, it's a documented restriction, see the last point below. The work arounds is using a WITH clause or putting the upper function expression in the output of each select.
From http://www.sqlite.org/lang_select.html : Each ORDER BY expression is processed as follows: 1. If the ORDER BY expression is a constant integer K then the expression is considered an alias for the K-th column of the result set (columns are numbered from left to right starting with 1). 2. If the ORDER BY expression is an identifier that corresponds to the alias of one of the output columns, then the expression is considered an alias for that column. 3. Otherwise, if the ORDER BY expression is any other expression, it is evaluated and the returned value used to order the output rows. If the SELECT statement is a simple SELECT, then an ORDER BY may contain any arbitrary expressions. However, if the SELECT is a compound SELECT, then ORDER BY expressions that are not aliases to output columns must be exactly the same as an expression used as an output column. On Wed, Dec 21, 2016 at 1:27 PM, <lukasz.st...@insoft.com.pl> wrote: > > After content filtering, the message was empty > > Ok. I try to send plain text only with no attachments... > > > SQLite 3.8.6 2014-08-15 11:46:33 > SQLite 3.11.0 > Android ver. 5.0.2 > > Dear Sirs, > > I have problem with ORDER BY UPPER(...) in conjunction with UNION. > The following query returns an error "1st ORDER BY term does not match > any column in the result set". > > SELECT > 0 as TableType, > GroupId as RecordId, > Name as Name > FROM ProductGroup > UNION > SELECT > 1 as TableType, > ProductId as RecordId, > Name as Name > FROM Product > ORDER BY UPPER(Name) > > When I replace the UPPER (Name) by Name - everything works correctly. > Below I attached the database on which the error occurs. > > On the bug list, I found only simillar bug: > http://www.sqlite.org/src/tktview?name=d06a25c844 > > > Lukasz Stela > INSOFT sp. z o.o. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
