Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[luuk34]

On 26-11-10 13:02, Drake Wilson wrote:
> Quoth luuk34, on 2010-11-26 12:49:53 +0100:
>> The extra column seems to work,
>> but i thought this should work too?
> I would imagine so, at first glance.
>
>> But the ORDER is wrong...
> How?  The example you provided seems properly sorted.
>
>

oeps, i made an error in the order by.
so, indeed its working OK...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[Drake Wilson]

Quoth luuk34 , on 2010-11-26 12:49:53 +0100:
> The extra column seems to work,
> but i thought this should work too?

I would imagine so, at first glance.

> But the ORDER is wrong...

How?  The example you provided seems properly sorted.

> sqlite> SELECT a,b FROM (
> ...> SELECT ID a, Price b FROM OrderTest WHERE Price < 200
> ...> UNION
> ...> SELECT ID a, Price b FROM OrderTest WHERE Price > 500
> ...> )
> ...> ORDER BY a IS 0, b;
> a|b
> 3|0.0
> 4|25.0
> 1|50.0
> 2|75.0
> 5|100.0
> 7|1000.0
> 8|1.0
> sqlite>

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[luuk34]

On 26-11-10 12:37, Drake Wilson wrote:
> Quoth Waldemar Derr, on 2010-11-26 12:24:27 +0100:
>> --Don't working: (Error: 1st ORDER BY term does not match any column in the
>> result set.)
>>
>> SELECT * FROM OrderTest WHERE Price<  200
>> UNION
>> SELECT * FROM OrderTest WHERE Price>  500
>> ORDER BY Price IS 0, Price;
>  From http://sqlite.org/lang_select.html:
> | Otherwise, if the ORDER BY expression is any other expression, it is
> | evaluated and the 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.
>
>> Is this a bug? In MySQL it works as expected. Is there a workaround?
> Add another output column with the expression you want, give it a
> name, and ORDER BY that name.
>
> My guess is that this is because the multiple output column
> specification parts of a compound SELECT may result in different
> intrepretations of an arbitrary expr in that position, and it's
> not clear how any kind of inwards propagation would work without
> yielding surprising results.
>
> --->  Drake Wilson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The extra column seems to work,
but i thought this should work too?
But the ORDER is wrong...

sqlite> SELECT a,b FROM (
...> SELECT ID a, Price b FROM OrderTest WHERE Price < 200
...> UNION
...> SELECT ID a, Price b FROM OrderTest WHERE Price > 500
...> )
...> ORDER BY a IS 0, b;
a|b
3|0.0
4|25.0
1|50.0
2|75.0
5|100.0
7|1000.0
8|1.0
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[Drake Wilson]

Quoth Waldemar Derr , on 2010-11-26 12:24:27 +0100:
> --Don't working: (Error: 1st ORDER BY term does not match any column in the
> result set.)
> 
> SELECT * FROM OrderTest WHERE Price < 200 
> UNION
> SELECT * FROM OrderTest WHERE Price > 500 
> ORDER BY Price IS 0, Price;

>From http://sqlite.org/lang_select.html:
| Otherwise, if the ORDER BY expression is any other expression, it is
| evaluated and the 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.

> Is this a bug? In MySQL it works as expected. Is there a workaround?

Add another output column with the expression you want, give it a
name, and ORDER BY that name.

My guess is that this is because the multiple output column
specification parts of a compound SELECT may result in different
intrepretations of an arbitrary expr in that position, and it's
not clear how any kind of inwards propagation would work without
yielding surprising results.

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users