Disorder result using ORDER BY with ENUM, INT

-- 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).
--
-- Dummy data & structure for testing is dumped as shown below.
-- Thank you,
--
-- -kk-
-- 2003/11/26
--
-- MySQL dump 9.09
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 4.0.16

--
-- Table structure for table `report`
--

CREATE TABLE report (
id int(10) unsigned NOT NULL default '0',
type enum('general','inhouse') NOT NULL default 'general',
PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='Available Reports';

--
-- Dumping data for table `report`
--

INSERT INTO report VALUES (1,'general');
INSERT INTO report VALUES (2,'general');
INSERT INTO report VALUES (3,'general');
INSERT INTO report VALUES (4,'general');
INSERT INTO report VALUES (5,'inhouse');
INSERT INTO report VALUES (6,'inhouse');
INSERT INTO report VALUES (7,'inhouse');
INSERT INTO report VALUES (8,'inhouse');
INSERT INTO report VALUES (9,'inhouse');
INSERT INTO report VALUES (10,'inhouse');
INSERT INTO report VALUES (11,'inhouse');
INSERT INTO report VALUES (12,'inhouse');
INSERT INTO report VALUES (13,'inhouse');
INSERT INTO report VALUES (14,'inhouse');

--
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: Disorder result using ORDER BY with ENUM, INT

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

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ 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

Re: Disorder result using ORDER BY with ENUM, INT

Hi,

So far nobody was able to repeat your observation.
Can you prepare repeatable test case containgin ddata enough to repeat the
problem on another server?

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

I doubt you can define "right" order in this case

according to MySQL Boolean logic expression (type AND id) is evaluated to 1 =
True or 0 = False

All values of column `type` in your example has numeric represenyayion of 0 so
(type AND id) will be always false.

In other words:

SELECT id, type FROM test.report ORDER BY type AND id;
is equivalent to:
SELECT id, type FROM test.report ORDER BY 0;

which does not make much sense.

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

Can you elaborate more on this?

Best regards

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ 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

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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Disorder result using ORDER BY with ENUM, INT

Alexander Keremidarski wrote:

> All values of column `type` in your example has numeric representayion of 0 so
> (type AND id) will be always false.

I am wrong here. ENUM t5ype internally is represented as integer so in this case
result will be always 1 not 0 as I wrote.

Sorry for this confusion.

Best regards

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ 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