| Bug #15633 | join order differences can affect optimizer and result in corrupted datasets | ||
|---|---|---|---|
| Submitted: | 9 Dec 2005 13:25 | Modified: | 24 Jan 2006 22:55 |
| Reporter: | Domas Mituzas | ||
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.18-bk, 5.0.16 | OS: | Linux (Linux) |
| Assigned to: | Evgeny Potemkin | Target Version: | |
[9 Dec 2005 13:26]
Domas Mituzas
db dump
Attachment: 15633.sql (application/octet-stream, text), 13.54 KiB.
[15 Dec 2005 22: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 15: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 20: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 20: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 12: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 17:13]
Evgeny Potemkin
fixed in 5.1.6
[24 Jan 2006 22: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.
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*