Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

2016-12-26 Thread Darko Volaric
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

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

2016-12-26 Thread Jean-Christophe Deschamps
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

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

2016-12-26 Thread Darko Volaric
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

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

2016-12-23 Thread nomad
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 -

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

2016-12-23 Thread Clemens Ladisch
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

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

2016-12-21 Thread Lukasz . Stela
> 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