Re: Disorder result using ORDER BY with ENUM, INT - Jani Tolonen - com.mysql.lists.bugs

5 messages in com.mysql.lists.bugsRe: Disorder result using ORDER BY wi...

From	Sent On	Attachments
Kriengkrai J.	25 Nov 2003 23:12
Sergei Golubchik	26 Nov 2003 08:22
Alexander Keremidarski	26 Nov 2003 15:51
Jani Tolonen	26 Nov 2003 15:57
Alexander Keremidarski	26 Nov 2003 16:05

Subject:	Re: Disorder result using ORDER BY with ENUM, INT
From:	Jani Tolonen (ja...@mysql.com)
Date:	11/26/2003 03:57:44 PM
List:	com.mysql.lists.bugs

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