Re: Disorder result using ORDER BY with ENUM, INT

Hi!

Sergei Golubchik writes:
> Hi!
> 
> On Nov 26, Kriengkrai J. wrote:
> > -- System: MySQL 4.0.13, 4.0.16 on Linux x86
> > -- Table type: MyISAM, InnoDB
> > -- Description / How-To-Repeat:
> > -- 1. When I use
> > -- SELECT id, type FROM test.report ORDER BY type, id;
> > -- the result is in wrong order
> > -- --
> > -- +----+---------+
> > -- | id | type    |
> > -- +----+---------+
> > -- |  4 | general |
> > -- |  3 | general |
> > -- |  1 | general |
> > -- |  2 | general |
> > -- |  5 | inhouse |
> > -- |  6 | inhouse |
> > -- ..
> > -- --
> > -- 2. But when I use
> > -- SELECT id, type FROM test.report ORDER BY type AND id;
> > -- the result is in right order
> > -- --
> > -- +----+---------+
> > -- | id | type    |
> > -- +----+---------+
> > -- |  1 | general |
> > -- |  2 | general |
> > -- |  3 | general |
> > -- |  4 | general |
> > -- |  5 | inhouse |
> > -- |  6 | inhouse |
> > -- ..
> > -- 3. After running statement(2) I rerun statement(1) again and the
> > result is then in order as (2).
> 
> I cannot repeat this behavour on your test data.
> 
> Regards,
> Sergei

I tried too, but could not repeat it either.

Some notes though;

SELECT id, type FROM test.report ORDER BY type AND id;

is actually exactly the same as doing just:

SELECT id, type FROM test.report ORDER BY "1";

in this case.

The reason is that 'type AND id' will be evaluated as
- Get value from 'type' and 'id' fields
- operate the values using 'AND'

the result will be passed to ORDER BY function as an argument.  The
result, in this case, will be '1' in all cases. You can test it with
'SELECT id and type FROM report;'

Since "1" is "1" for all rows, no sorting is needed.  In this case the
results should be exactly the same, as without ORDER BY clause at all.

In other words, using 'type AND id' is valid MySQL syntax, but
it does not do what you want. You must use "type, id".

On the other hand I don't understand how you got:

" -- 3. After running statement(2) I rerun statement(1) again and the
 result is then in order as (2). "

Running many ORDER BY queries should not change the order for the same
order by clause depending on the run, it should always produce the
same order.

Can you still repeat this behavior?

If you can, please do following:

start client like this: 'mysql --tee=/tmp/mysql.log'

Run the test case from the scratch and send us the mysql.log
file. It logs all events and results to the file.

Start by creating and populating the table from the scratch.

Regards,

- Jani

For technical support contracts, visit https://order.mysql.com/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Jani Tolonen <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland
       <___/   www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]