Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION
Or use a collation instead, although "collate" is an operator it's not treated as a function: select 'abc' n union select 'ABC' n order by n collate nocase On Tue, Dec 27, 2016 at 1:34 AM, Jean-Christophe Deschamps wrote: > At 00:45 27/12/2016, you wrote: > > The work arounds is using a WITH clause or putting the upper function >> expression in the output of each select. >> > > Another way to rewrite is to wrap the compound select inside a simple > outer select: > > select n > from > ( > select 'Abc' n > union > select 'aaa' n > ) > order by upper(n) > > > ___ > 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
Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION
At 00:45 27/12/2016, you wrote: The work arounds is using a WITH clause or putting the upper function expression in the output of each select. Another way to rewrite is to wrap the compound select inside a simple outer select: select n from ( select 'Abc' n union select 'aaa' n ) order by upper(n) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION
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, 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
Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION
On Fri Dec 23, 2016 at 02:25:29PM +0100, Clemens Ladisch wrote: > Lukasz.Stela wrote: > >The following query returns an error "1st ORDER BY term does not match > >any column in the result set". > > This restriction comes from the SQL standard. > > >When I replace the UPPER (Name) by Name - everything works correctly. > > In theory, it would be possible to sort by something that can be derived > from some column in the result set. But that is not implemented > in SQLite. It seems the restriction in SQLite only applies in certain circumstances (when it is sorting a UNION?): sqlite> with a as (select 'A' as name union select 'a' as name) select a.name from a order by upper(a.name) asc; name -- A a sqlite> with a as (select 'A' as name union select 'a' as name) select a.name from a union select a.name from a order by upper(a.name) asc; Error: 1st ORDER BY term does not match any column in the result set -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION
Lukasz.Stela wrote: >The following query returns an error "1st ORDER BY term does not match >any column in the result set". This restriction comes from the SQL standard. >When I replace the UPPER (Name) by Name - everything works correctly. In theory, it would be possible to sort by something that can be derived from some column in the result set. But that is not implemented in SQLite. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION
> 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