MySQL Bugs: #15633: join order differences can affect optimizer and result in corrupted datasets

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

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*

db dump

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

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

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

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

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

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

fixed in 5.1.6

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.