Bug #11482 4.1.12 produces different resultset for a complex query than in previous 4.1.x
Submitted: 21 Jun 2005 13:39 Modified: 17 Jul 2005 0:36
Reporter: Gökhan Demir Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.12 OS:Linux (linux, windows)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[21 Jun 2005 13:39] Gökhan Demir 
Description:
MySQL server version 4.1.12 produces different resultset for a long and complex query with many left joins and subqueries within them.

How to repeat:
I will attach a zip file, that can be used as a test case.

Suggested fix:
Stay with 4.1.11 version, do not upgrade to 4.1.12.
[21 Jun 2005 13:43] Gökhan Demir 
there are 3 sql and 1 readme files in the zip. File Names are self explanatory.

Attachment: test_behaviour_change.zip (application/x-zip-compressed, text), 4.27 KiB.
[4 Jul 2005 15:01] 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/internals/26646
[4 Jul 2005 15:02] Evgeny Potemkin 
Wrongly applied optimization was adding NOT NULL constraint which results in
reduced result set.
[5 Jul 2005 17:00] Evgeny Potemkin 
The problem was that add_notnull_conds() while checking subquery adds NOT NULL
constraint to left joined table, to which, normally, optimization don't have to be applied.
[15 Jul 2005 19:29] Evgeny Potemkin 
Fixed in 4.1.14, cset 1.2360
[17 Jul 2005 0:36] Paul DuBois 
Noted in 4.1.14 changelog.