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

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

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

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

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

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

[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  "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