Bug #15347 query with subquery has different results on 4.1 and 5.0
Submitted: 30 Nov 2005 12:25 Modified: 2 Feb 2006 1:08
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.17-bk OS:Linux (linux, windows)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[30 Nov 2005 12:25] Shane Bester 
Description:
A certain query returns a different resultset when using today's 4.1.16-bk than when using 5.0.17-bk.  This only happens with certain data, not any random data in the tables.

How to repeat:
Load attached SQL file (dump.sql) and execute the related query.
Notice different results on 4.1 and 5.0.

Suggested fix:
Not sure.
[1 Dec 2005 7:26] Valeriy Kravchuk 
Verified on customer's data after adding indexes, as described in the last comment. See the file attached.
[9 Dec 2005 22:13] 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/57
[11 Jan 2006 20:16] 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/936
[13 Jan 2006 16:25] Evgeny Potemkin 
When subselect is a join with set functions and no record have been found in
it, end_send_group() sets null_row for all tables in order aggregate functions 
to calculate their values correctly. Normally this null_row flag is cleared for 
each table in sub_select(), but flush_cached_records() doesn't do so.
Due to this all fields from the table processed by flush_cached_records() are 
always evaluated as nulls and whole select produces wrong result.

Fixed in 5.0.19, cset 1.1960.105.1
[16 Jan 2006 16:13] Evgeny Potemkin 
Fixed in 5.1.6
[2 Feb 2006 1:08] Mike Hillyer 
Documented in 5.1.6 and 5.0.19 changelogs:

     <listitem>
        <para>
          Certain subqueries where the inner query is the result of a
          aggregate function would return different results on MySQL 5.0
          than on MySQL 4.1. (Bug #15347)
        </para>
      </listitem>