different DISTINCT ordering with InnoDB and MyISAM

It appears that there's something weird going on with InnoDB tables
and DISTINCT.

4.0.11-gamma-Max, on Linux-x86


Here's how I can reproduce it:

mysql> ALTER TABLE Queues TYPE=InnoDB;
Query OK, 19 rows affected (0.21 sec)
Records: 19 Duplicates: 0 Warnings: 0

mysql> describe Queues;
+-------------------+--------------+------+-----+---------+- ---------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+- ---------------+
| id | int(11) | | PRI | NULL | auto_increment |
| Name | varchar(40) | | UNI | | |
| Description | varchar(120) | YES | | NULL | |
| CorrespondAddress | varchar(40) | YES | | NULL | |
| CommentAddress | varchar(40) | YES | | NULL | |
| InitialPriority | int(11) | YES | | NULL | |
| FinalPriority | int(11) | YES | | NULL | |
| DefaultDueIn | int(11) | YES | | NULL | |
| Creator | int(11) | YES | | NULL | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | YES | | NULL | |
| LastUpdated | datetime | YES | | NULL | |
| Disabled | smallint(6) | | | 0 | |
+-------------------+--------------+------+-----+---------+- ---------------+
13 rows in set (0.00 sec)

mysql> SHOW INDEX FROM Queues;
+--------+------------+----------+--------------+----------- --+-----------+-------------+----------+--------+------+---- --------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+----------- --+-----------+-------------+----------+--------+------+---- --------+---------+
| Queues | 0 | PRIMARY | 1 | id | A | 19 | NULL | NULL | | BTREE | |
| Queues | 0 | Queues1 | 1 | Name | A | 19 | NULL | NULL | | BTREE | |
+--------+------------+----------+--------------+----------- --+-----------+-------------+----------+--------+------+---- --------+---------+
2 rows in set (0.00 sec)


mysql> SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;

(trimming actual output. Rows are sorted by 'id' instead of by Name.)

mysql> EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;
+-------+------+---------------+------+---------+------+---- --+-----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+---- --+-----------------+
| main | ALL | NULL | NULL | NULL | NULL | 19 | Using temporary |
+-------+------+---------------+------+---------+------+---- --+-----------------+


mysql> ALTER TABLE Queues TYPE=MyISAM;
Query OK, 19 rows affected (0.03 sec)
Records: 19 Duplicates: 0 Warnings: 0

mysql> SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;

(trimming actual output. Rows are sorted properly by Name.)


mysql> EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;
+-------+------+---------------+------+---------+------+---- --+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+---- --+---------------------------------+
| main | ALL | NULL | NULL | NULL | NULL | 19 | Using temporary; Using filesort |
+-------+------+---------------+------+---------+------+---- --+---------------------------------+
1 row in set (0.00 sec)


- I was unable to reproduce it on a small test table; I saw this a
few months ago, and converting InnoDB->MyISAM->InnoDB seemed to fix
the indexes. (I've also seen this on much larger tables.)

- If I change anything about the query, adding a LIMIT, removing the
DISTINCT, it works fine.

-R

--
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: different DISTINCT ordering with InnoDB and MyISAM

Robert,

the query does a table scan, which means that the rows are returned from
InnoDB in the order of id, which is the primary key.

For some reason MySQL does NOT do a filesort below to get the rows in the
order of Name.

If you run

EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;

when the query works ok also on an InnoDB table, what does it say? The bug
is probably in the MySQL optimizer which in some cases forgets the filesort.

I have forwarded this bug report to the MySQL optimizer developers.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com



.....................

Subject: different DISTINCT ordering with InnoDB and MyISAM
From: Robert Spier
Date: Tue, 29 Apr 2003 19:54:25 -0700


------------------------------------------------------------ ----------------
----


It appears that there's something weird going on with InnoDB tables
and DISTINCT.

4.0.11-gamma-Max, on Linux-x86


Here's how I can reproduce it:

mysql> ALTER TABLE Queues TYPE=InnoDB;
Query OK, 19 rows affected (0.21 sec)
Records: 19 Duplicates: 0 Warnings: 0

mysql> describe Queues;
+-------------------+--------------+------+-----+---------+- ---------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+- ---------------+
| id | int(11) | | PRI | NULL | auto_increment |
| Name | varchar(40) | | UNI | | |
| Description | varchar(120) | YES | | NULL | |
| CorrespondAddress | varchar(40) | YES | | NULL | |
| CommentAddress | varchar(40) | YES | | NULL | |
| InitialPriority | int(11) | YES | | NULL | |
| FinalPriority | int(11) | YES | | NULL | |
| DefaultDueIn | int(11) | YES | | NULL | |
| Creator | int(11) | YES | | NULL | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | YES | | NULL | |
| LastUpdated | datetime | YES | | NULL | |
| Disabled | smallint(6) | | | 0 | |
+-------------------+--------------+------+-----+---------+- ---------------+
13 rows in set (0.00 sec)

mysql> SHOW INDEX FROM Queues;
+--------+------------+----------+--------------+----------- --+-----------+-
------------+----------+--------+------+------------+------- --+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality
| Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+----------- --+-----------+-
------------+----------+--------+------+------------+------- --+
| Queues | 0 | PRIMARY | 1 | id | A |
19 | NULL | NULL | | BTREE | |
| Queues | 0 | Queues1 | 1 | Name | A |
19 | NULL | NULL | | BTREE | |
+--------+------------+----------+--------------+----------- --+-----------+-
------------+----------+--------+------+------------+------- --+
2 rows in set (0.00 sec)


mysql> SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;

(trimming actual output. Rows are sorted by 'id' instead of by Name.)

mysql> EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name
ASC;
+-------+------+---------------+------+---------+------+---- --+-------------
----+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+---- --+-------------
----+
| main | ALL | NULL | NULL | NULL | NULL | 19 | Using
temporary |
+-------+------+---------------+------+---------+------+---- --+-------------
----+


mysql> ALTER TABLE Queues TYPE=MyISAM;
Query OK, 19 rows affected (0.03 sec)
Records: 19 Duplicates: 0 Warnings: 0

mysql> SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;

(trimming actual output. Rows are sorted properly by Name.)


mysql> EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name
ASC;
+-------+------+---------------+------+---------+------+---- --+-------------
--------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+---- --+-------------
--------------------+
| main | ALL | NULL | NULL | NULL | NULL | 19 | Using
temporary; Using
filesort |
+-------+------+---------------+------+---------+------+---- --+-------------
--------------------+
1 row in set (0.00 sec)


- I was unable to reproduce it on a small test table; I saw this a
few months ago, and converting InnoDB->MyISAM->InnoDB seemed to fix
the indexes. (I've also seen this on much larger tables.)

- If I change anything about the query, adding a LIMIT, removing the
DISTINCT, it works fine.

-R



--
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: different DISTINCT ordering with InnoDB and MyISAM

Hi!

Actually, this may be the following bug, which is fixed in upcoming 4.0.13:

http://www.mysql.com/doc/en/News-4.0.13.html

"
Fix problem with ORDER BY being discarded for some DISTINCT queries. (Bug
#275)
"

Regards,

Heikki

----- Original Message -----
From: "Heikki Tuuri"
To:
Sent: Wednesday, April 30, 2003 10:36 AM
Subject: Re: different DISTINCT ordering with InnoDB and MyISAM


> Robert,
>
> the query does a table scan, which means that the rows are returned from
> InnoDB in the order of id, which is the primary key.
>
> For some reason MySQL does NOT do a filesort below to get the rows in the
> order of Name.
>
> If you run
>
> EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;
>
> when the query works ok also on an InnoDB table, what does it say? The bug
> is probably in the MySQL optimizer which in some cases forgets the
filesort.
>
> I have forwarded this bug report to the MySQL optimizer developers.
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> http://www.innodb.com
>
>
>
> ....................
>
> Subject: different DISTINCT ordering with InnoDB and MyISAM
> From: Robert Spier
> Date: Tue, 29 Apr 2003 19:54:25 -0700
>
>
> ------------------------------------------------------------ --------------
--
> ----
>
>
> It appears that there's something weird going on with InnoDB tables
> and DISTINCT.
>
> 4.0.11-gamma-Max, on Linux-x86
>
>
> Here's how I can reproduce it:
>
> mysql> ALTER TABLE Queues TYPE=InnoDB;
> Query OK, 19 rows affected (0.21 sec)
> Records: 19 Duplicates: 0 Warnings: 0
>
> mysql> describe Queues;
>
+-------------------+--------------+------+-----+---------+- ---------------+
> | Field | Type | Null | Key | Default | Extra
|
>
+-------------------+--------------+------+-----+---------+- ---------------+
> | id | int(11) | | PRI | NULL | auto_increment
|
> | Name | varchar(40) | | UNI | |
|
> | Description | varchar(120) | YES | | NULL |
|
> | CorrespondAddress | varchar(40) | YES | | NULL |
|
> | CommentAddress | varchar(40) | YES | | NULL |
|
> | InitialPriority | int(11) | YES | | NULL |
|
> | FinalPriority | int(11) | YES | | NULL |
|
> | DefaultDueIn | int(11) | YES | | NULL |
|
> | Creator | int(11) | YES | | NULL |
|
> | Created | datetime | YES | | NULL |
|
> | LastUpdatedBy | int(11) | YES | | NULL |
|
> | LastUpdated | datetime | YES | | NULL |
|
> | Disabled | smallint(6) | | | 0 |
|
>
+-------------------+--------------+------+-----+---------+- ---------------+
> 13 rows in set (0.00 sec)
>
> mysql> SHOW INDEX FROM Queues;
>
+--------+------------+----------+--------------+----------- --+-----------+-
> ------------+----------+--------+------+------------+------- --+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
|
> Cardinality
> | Sub_part | Packed | Null | Index_type | Comment |
>
+--------+------------+----------+--------------+----------- --+-----------+-
> ------------+----------+--------+------+------------+------- --+
> | Queues | 0 | PRIMARY | 1 | id | A
|
> 19 | NULL | NULL | | BTREE | |
> | Queues | 0 | Queues1 | 1 | Name | A
|
> 19 | NULL | NULL | | BTREE | |
>
+--------+------------+----------+--------------+----------- --+-----------+-
> ------------+----------+--------+------+------------+------- --+
> 2 rows in set (0.00 sec)
>
>
> mysql> SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;
>
> (trimming actual output. Rows are sorted by 'id' instead of by Name.)
>
> mysql> EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name
> ASC;
>
+-------+------+---------------+------+---------+------+---- --+-------------
> ----+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
>
+-------+------+---------------+------+---------+------+---- --+-------------
> ----+
> | main | ALL | NULL | NULL | NULL | NULL | 19 | Using
> temporary |
>
+-------+------+---------------+------+---------+------+---- --+-------------
> ----+
>
>
> mysql> ALTER TABLE Queues TYPE=MyISAM;
> Query OK, 19 rows affected (0.03 sec)
> Records: 19 Duplicates: 0 Warnings: 0
>
> mysql> SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name ASC;
>
> (trimming actual output. Rows are sorted properly by Name.)
>
>
> mysql> EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name
> ASC;
>
+-------+------+---------------+------+---------+------+---- --+-------------
> --------------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
>
+-------+------+---------------+------+---------+------+---- --+-------------
> --------------------+
> | main | ALL | NULL | NULL | NULL | NULL | 19 | Using
> temporary; Using
> filesort |
>
+-------+------+---------------+------+---------+------+---- --+-------------
> --------------------+
> 1 row in set (0.00 sec)
>
>
> - I was unable to reproduce it on a small test table; I saw this a
> few months ago, and converting InnoDB->MyISAM->InnoDB seemed to fix
> the indexes. (I've also seen this on much larger tables.)
>
> - If I change anything about the query, adding a LIMIT, removing the
> DISTINCT, it works fine.
>
> -R
>



--
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: different DISTINCT ordering with InnoDB and MyISAM

Thanks Heikki-

Sorry for the delay in getting back to you. I'm not subscribed to
bugs@lists.mysql.com, so I didn't see the messages until today.
(Please CC me on future responses.)

It does look like Bug 275. I'll eagerly await 4.0.13 (and may try
the CVS if I get a chance.)

You're right, it does look like it is ignoring filesort in that
case. Creator is not indexed. But Name was. It looks like it's
not using the index when it could be.

Thanks!

-R

mysql> EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY
mysql> main.Creator;
+-------+------+---------------+------+---------+------+---- --+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+---- --+---------------------------------+
| main | ALL | NULL | NULL | NULL | NULL | 19 | Using
temporary; Using filesort |
+-------+------+---------------+------+---------+------+---- --+---------------------------------+




>"Heikki Tuuri" wrote:
>Hi!
>
>Actually, this may be the following bug, which is fixed in upcoming
>4.0.13:
>
>http://www.mysql.com/doc/en/News-4.0.13.html
>
>"
>Fix problem with ORDER BY being discarded for some DISTINCT
>queries. (Bug
>#275)
>"
>
>Regards,
>
>Heikki
>
>----- Original Message -----
>From: "Heikki Tuuri"
>To:
>Sent: Wednesday, April 30, 2003 10:36 AM
>Subject: Re: different DISTINCT ordering with InnoDB and MyISAM
>
>
>> Robert,
>>
>> the query does a table scan, which means that the rows are returned
>> from
>> InnoDB in the order of id, which is the primary key.
>>
>> For some reason MySQL does NOT do a filesort below to get the rows
>> in the
>> order of Name.
>>
>> If you run
>>
>> EXPLAIN SELECT DISTINCT main.* FROM Queues main ORDER BY main.Name
>> ASC;
>>
>> when the query works ok also on an InnoDB table, what does it say?
>> The bug
>> is probably in the MySQL optimizer which in some cases forgets the
>filesort.
>>
>> I have forwarded this bug report to the MySQL optimizer developers.
>>
>> Best regards,
>>
>> Heikki Tuuri
>> Innobase Oy
>> http://www.innodb.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: different DISTINCT ordering with InnoDB and MyISAM

Robert,

Robert Spier wrote:
> Thanks Heikki-



> It does look like Bug 275. I'll eagerly await 4.0.13 (and may try
> the CVS if I get a chance.)

Just small note. We don't use CVS. Instead we rely on BitKeeper. You can find
MySQL BitKeeper trees at http://mysql.bkbits.net



Best regards

--
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