Bug #15633 join order differences can affect optimizer and result in corrupted datasets
Submitted: 9 Dec 2005 12:25 Modified: 24 Jan 2006 21:55
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.18-bk, 5.0.16 OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[9 Dec 2005 12:25] Domas Mituzas
Description:
In some cases optimizer doesn't join properly rows, if const optimization is applied to all joined tables and inner joins are after left joins. 

If queries are repeated, proper result sets are returned, therefore it may be a bug in optimizer states.

JOIN order matters. as well if 'd' is joined with inner join, bug can not be reproduced.

How to repeat:

mysql> select a.x,d.y from a join b on (a.x=b.x) left join d on (a.x=d.x) join c on (a.x=c.x) where a.x=2338602331;+------------+------+
| x          | y    |
+------------+------+
| 2338602331 | NULL |
+------------+------+
1 row in set (0.00 sec)

mysql> select a.x,d.y from a join b on (a.x=b.x) left join d on (a.x=d.x) join c on (a.x=c.x) where a.x=2338602331;
+------------+------------+
| x          | y          |
+------------+------------+
| 2338602331 | 2147483647 |
+------------+------------+
1 row in set (0.00 sec)

mysql> explain select a.x,d.y from a join b on (a.x=b.x) left join d on (a.x=d.x) join c on (a.x=c.x) where a.x=2338602331;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
|  1 | SIMPLE      | b     | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
|  1 | SIMPLE      | d     | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | c     | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
4 rows in set (0.00 sec)

Suggested fix:
*shrug*
[9 Dec 2005 12:26] Domas Mituzas
db dump

Attachment: 15633.sql (application/octet-stream, text), 13.54 KiB.

[15 Dec 2005 21:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/189
[26 Dec 2005 14:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/417
[11 Jan 2006 19:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/932
[11 Jan 2006 19:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/934
[13 Jan 2006 11:34] Evgeny Potemkin
Item equal objects are employed only at the optimize phase. Usually they are not
supposed to be evaluated.  Yet in some cases we call the method val_int() for
them. Here we have to take care of restricting the predicate such an object
represents f1=f2= ...=fn to the projection of known fields fi1=...=fik.

Fixed in 5.0.19, cset 1.1968.14.1
[16 Jan 2006 16:13] Evgeny Potemkin
fixed in 5.1.6
[24 Jan 2006 21:55] Jon Stephens
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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.19 and 5.1.6 changelogs. Closed.