[Bug 706988] Re: Major Bug in MySQL 5.1.49 up to 5.1.52

[Koopee]

Please update the mysql. It could solve this issue too, where adding an
index breaks a query.

Below mysql run describing the situation:
1. Execute query -> no results.
2. Remove index
3. Execute query -> Correct result found
4. Re-create index
5. Execute query again -> no results.


mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.49-1ubuntu8.1 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT `units`.* FROM `units` INNER JOIN `units_users` ON `units`.id = `units_users`.unit_id WHERE (`units`.`id` = 72) AND ((`units_users`.user_id = 22));
Empty set (0.00 sec)

mysql> drop index index_units_users_on_unit_id on units_users;
Query OK, 330 rows affected (0.26 sec)
Records: 330  Duplicates: 0  Warnings: 0

mysql> SELECT `units`.* FROM `units` INNER JOIN `units_users` ON `units`.id = `units_users`.unit_id WHERE (`units`.`id` = 72) AND ((`units_users`.user_id = 22));
+----+------------+-------------------+------------+-------+------+-------+------+----------+--------------------+---------------------+---------------------+---------------------+----------------+--------------+------+
| id | company_id | name              | address_id | phone | fax  | email | www  | valid_to | created_by_user_id | modified_by_user_id | created_at          | updated_at          | receipt_number | lock_version | type |
+----+------------+-------------------+------------+-------+------+-------+------+----------+--------------------+---------------------+---------------------+---------------------+----------------+--------------+------+
| 72 |         22 | Company 1 |       NULL |       |      |       |      | NULL     |               NULL |                NULL | 2009-11-03 07:45:55 | 2010-08-25 20:32:29 |          69760 |        21722 | NULL |
+----+------------+-------------------+------------+-------+------+-------+------+----------+--------------------+---------------------+---------------------+---------------------+----------------+--------------+------+
1 row in set (0.00 sec)

mysql> create index index_units_users_on_unit_id on units_users (unit_id);
Query OK, 330 rows affected (0.32 sec)
Records: 330  Duplicates: 0  Warnings: 0

mysql> SELECT `units`.* FROM `units` INNER JOIN `units_users` ON `units`.id = `units_users`.unit_id WHERE (`units`.`id` = 72) AND ((`units_users`.user_id = 22));
Empty set (0.00 sec)

mysql>


** Tags added: query

-- 
You received this bug notification because you are a member of Ubuntu
Server Team, which is subscribed to mysql-5.1 in Ubuntu.
https://bugs.launchpad.net/bugs/706988

Title:
  Major Bug in MySQL 5.1.49 up to 5.1.52