MSQL BUG: ORDER BY not working correctly on resultset - using source fields rather than result field

Hello,

I am running the Debian stable (Linux) packaging of MySQL:
MySQL 3.23.49

I have noted occasional issues with ORDER BY on GROUPED results,
but as our datasets are large I have never been able to pin down
what is going wrong. However, by varying the SQL, I believe that
I have identified the area where the problem is occurring:

This SQL works, but sometimes gets the ORDERING incorrect:

select ccy, sum(myval) as myval
from mytable
group by ccy
order by abs(myval) desc


This SQL reports an error:

select ccy, sum(myval) as mysumval
from mytable
group by ccy
order by abs(mysumval) desc

It reports that the derived field mysumval is not found.

Note that the only difference is in the name of the derived field
- in both cases, the intent is to sort by a result-set field,
not a field from the source data.


My conclusion is that mysql is not always using the computed
value, but values from the underlying datasets when functions such
as abs() are used. This is backed up by the error when the name of
the derived column in the resultset is changed.


Regards,

Jeff Armstrong
Ph +44-(20)-7959 3335

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MSQL BUG: ORDER BY not working correctly on resultset - using source fields rather than result f

Jeff Armstrong writes:
> Hello,
>
> I am running the Debian stable (Linux) packaging of MySQL:
> MySQL 3.23.49
>
> I have noted occasional issues with ORDER BY on GROUPED results,
> but as our datasets are large I have never been able to pin down
> what is going wrong. However, by varying the SQL, I believe that
> I have identified the area where the problem is occurring:
>
> This SQL works, but sometimes gets the ORDERING incorrect:
>
> select ccy, sum(myval) as myval
> from mytable
> group by ccy
> order by abs(myval) desc
>
>
> This SQL reports an error:
>
> select ccy, sum(myval) as mysumval
> from mytable
> group by ccy
> order by abs(mysumval) desc
>

[skip]

> Regards,
>
> Jeff Armstrong
> Ph +44-(20)-7959 3335
>
> --
> MySQL Bugs Mailing List
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe: http://lists.mysql.com/bugs?unsub=sinisa@cytanet.com.cy
>

Hi!

The above is not a bug.

ORDER BY can be used only with a column name, alias from the select
list, an integer constant denoting number of field in the select list
or it can be used with expressions (MySQL supports it in 4.1), or
conbinations of the above in the list.

When used with and expression, RDBMS should treat that part of ORDER
BY as invalid, id est there would be no sorting done on the
expression, which is not what you require, I guess.



for the filter:


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org