MySQL Bugs: #25527: myISAM goes into a tight loop on "distinct" queries over bit fields

Bug #25527	myISAM goes into a tight loop on "distinct" queries over bit fields
Submitted:	10 Jan 2007 17:40	Modified:	11 Jan 2007 2:13
Reporter:	Patrick Casey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Windows (XP & Red-Hat)
Assigned to:		CPU Architecture:	Any

Description:
This bug appeared in myISAM 5.0. It did not appear in myISAM 3.23, nor does it appear in INNODB/5.0.

Symptomatically, certain queries put the system into a tight loop (see below). The only way to terminate the loop is to log in with a command line and kill the process. This loop has been verified:

MyISAM 5.0.22 on XP and Red-Hat
MyISAM 5.0.27 on XP

The query in question works propertly on:

myISAM 3.23 on XP and Red-Hat
INNODB 5.0.22 on XP and Red-Hat

How to repeat:
Given this table:

mysql> describe task;
+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| sys_class_name      | varchar(80)  | YES  |     | NULL    |       |
| number              | varchar(40)  | YES  | MUL | NULL    |       |
| short_description   | varchar(80)  | YES  |     | NULL    |       |
| parent              | char(32)     | YES  | MUL | NULL    |       |
| active              | bit(1)       | YES  | MUL | NULL    |       |
| comments            | mediumtext   | YES  |     | NULL    |       |
| description         | mediumtext   | YES  |     | NULL    |       |
| assigned_to         | char(32)     | YES  | MUL | NULL    |       |
| assignment_group    | char(32)     | YES  | MUL | NULL    |       |
| cmdb_ci             | char(32)     | YES  | MUL | NULL    |       |
| company             | char(32)     | YES  | MUL | NULL    |       |
| state               | int(11)      | YES  |     | NULL    |       |
| priority            | int(11)      | YES  |     | NULL    |       |
| urgency             | int(11)      | YES  |     | NULL    |       |
| impact              | int(11)      | YES  |     | NULL    |       |
| escalation          | int(11)      | YES  |     | NULL    |       |
| approval            | varchar(40)  | YES  |     | NULL    |       |
| approval_history    | mediumtext   | YES  |     | NULL    |       |
| approval_set        | datetime     | YES  |     | NULL    |       |
| work_notes          | mediumtext   | YES  |     | NULL    |       |
| close_notes         | mediumtext   | YES  |     | NULL    |       |
| opened_at           | datetime     | YES  |     | NULL    |       |
| closed_at           | datetime     | YES  |     | NULL    |       |
| time_worked         | datetime     | YES  |     | NULL    |       |
| calendar_duration   | datetime     | YES  |     | NULL    |       |
| business_duration   | datetime     | YES  |     | NULL    |       |
| opened_by           | char(32)     | YES  | MUL | NULL    |       |
| closed_by           | char(32)     | YES  | MUL | NULL    |       |
| watch_list          | mediumtext   | YES  |     | NULL    |       |
| group_list          | mediumtext   | YES  |     | NULL    |       |
| knowledge           | bit(1)       | YES  |     | NULL    |       |
| location            | char(32)     | YES  | MUL | NULL    |       |
| contact_type        | varchar(40)  | YES  |     | NULL    |       |
| follow_up           | datetime     | YES  |     | NULL    |       |
| due_date            | datetime     | YES  |     | NULL    |       |
| sla_due             | datetime     | YES  |     | NULL    |       |
| activity_due        | datetime     | YES  |     | NULL    |       |
| correlation_id      | varchar(100) | YES  |     | NULL    |       |
| correlation_display | varchar(100) | YES  |     | NULL    |       |
| user_input          | mediumtext   | YES  |     | NULL    |       |
| made_sla            | bit(1)       | YES  |     | NULL    |       |
| sys_id              | char(32)     | NO   | PRI |         |       |
| sys_updated_by      | varchar(40)  | YES  |     | NULL    |       |
| sys_updated_on      | datetime     | YES  |     | NULL    |       |
| sys_created_by      | varchar(40)  | YES  |     | NULL    |       |
| sys_created_on      | datetime     | YES  |     | NULL    |       |
| sys_mod_count       | int(11)      | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+

Either of these queries:

select distinct (active) from task; <-- simnple case

select `task`.`active` as `c0`, `task`.`active` as `c1` from `task` as `task` group by `task`.`active` order by `task`.`active` ASC <-- actual app query

Puts the system into a tight loop.

There are a total of 75 rows in the table. 
25 rows have active=false
50 rows have active=true
0 rows have active=null

mysql> select count(*) from task where active=true;
+----------+
| count(*) |
+----------+
|       50 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from task;
+----------+
| count(*) |
+----------+
|       75 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from task where active=false;
+----------+
| count(*) |
+----------+
|       25 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from task where active is null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Thank you for the bug report. Could you please provide a dump file
of the table mentioned with insert data commands?. Thanks in advance.

dump file

Attachment: task.sql (application/octet-stream, text), 42.43 KiB.

Dump file attached as per your request.

C:\dev\mysql527\bin>mysqldump -u root glide task > task.sql

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Thank you for the feedback. Tested on Fedora Core 6 and XP Windows:

[miguel@light 5.0]$ bin/mysqladmin -uroot create glide
[miguel@light 5.0]$ bin/mysql -uroot glide < /home/miguel/dbs/task.sql 
[miguel@light 5.0]$ bin/mysql -uroot glide
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select distinct (active) from task;
+--------+
| active |
+--------+
|        | 
|       | 
+--------+
2 rows in set (0.00 sec)

mysql> select `task`.`active` as `c0`, `task`.`active` as `c1` from `task` as `task`
    -> group by `task`.`active` order by `task`.`active` ASC;
+------+------+
| c0   | c1   |
+------+------+
|      |      | 
|     |     | 
+------+------+
2 rows in set (0.00 sec)

------------------------------------------------------------------------
C:\mydb>bin\mysql -uroot glide
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.34 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select distinct (active) from task;
+--------+
| active |
+--------+
|        |
| ☺      |
+--------+
2 rows in set (0.00 sec)

mysql> select `task`.`active` as `c0`, `task`.`active` as `c1` from `task` as `task`
    -> group by `task`.`active` order by `task`.`active` ASC;
+------+------+
| c0   | c1   |
+------+------+
|      |      |
| ☺    | ☺    |
+------+------+
2 rows in set (0.00 sec)

mysql>

Sorry to be a bit dense here, but did you replicate and check in a fix for this, or did you have trouble replicating it?

What I did was: confirmed the behavior for you reported with 5.0.27 on
XP then tested against current source server on Linux and XP that is
our procedure and I don't know say you where and when the fix was done.