Bug #11482 4.1.12 produces different resultset for a complex query than in previous 4.1.x
Submitted: 21 Jun 2005 15:39 Modified: 17 Jul 2005 2:36
Reporter: Gökhan Demir
Status: Closed
Category:Server: Optimizer Severity:S2 (Serious)
Version:4.1.12 OS:Linux (linux, windows)
Assigned to: Evgeny Potemkin Target Version:

[21 Jun 2005 15: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 15: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 17: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 17:02] Evgeny Potemkin 
Wrongly applied optimization was adding NOT NULL constraint which results in
reduced result set.
[5 Jul 2005 19: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 21:29] Evgeny Potemkin 
Fixed in 4.1.14, cset 1.2360
[17 Jul 2005 2:36] Paul DuBois 
Noted in 4.1.14 changelog.