MySQL Bugs: #21933: Incorrect Result Set for Left Join

Bug #21933	Incorrect Result Set for Left Join
Submitted:	30 Aug 2006 22:17	Modified:	22 Jun 2007 20:27
Reporter:	jeh Haluska	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.27, 5.0.24	OS:	Linux (Red Hat 3.4.3-9.EL4)
Assigned to:		CPU Architecture:	Any
Tags:	Inconsistent Results, Incorrect Results, Left Join Error

Description:
A large left join query returns incorrect/inconsistent results.  It appears as though the query returns the values (for the outer joined table only) from memory from the previous query, and then writes the correct values to memory.

First query returns NULL for table E, second returns data for the first query, etc.  Restarting MySQL does not help, exhibits same behaviour.  It is not a data issue, because it's not a matter of how many, but what records are returned.  No matter what value you start with, the next query will return the first query's results.

Example of affected query: Actual query has several other tables included, but this is the minimal query that causes this problem.

select d.id did, e.id eid   
  from a, b, (c,d)   
  left join e on ( e.locid = d.locid   
     and ifnull(e.uid,d.uid) = d.uid   
     and ifnull(e.fc,d.fc)=d.fc   
     and e.mid = ifnull(c.net_id,c.id) 
        )   
 where d.id = 101
   and c.id = d.mid   
   and a.uid = d.uid  
   and b.mid = d.mid  
   and b.fc = d.fc  
   and b.app = a.app  
   and b.ver = a.ver  
 order by e.uid desc, e.fc desc   

Notes: If I remove the last ifnull in the Left Join or remove tables A or B (or both) this behaviour goes away.  Have recently installed 5.0.24 for development, this problem does not exist in 5.0.18 which we use for production.

How to repeat:
Due to privacy constraints, I cannot release actual data.  The table constructs are relatively simple, however, when i recreated them for test, i could NOT reproduce this event.  I did recreate each table and reload data, and the same results occur again.

I believe that this is possibly related to volume: 

Table A: < 5000 rows
Table B: < 1000 rows
Table C: =~ 100 rows
Table D: 5 million rows, (~200Mb MYD file)
Table E: 150k rows (~175 Mb MYD file)

Thank you for a problem report. Please, send SHOW CREATE TABLE results for all the tables involved. I have to know exact data types, storage engines and indexes used.

Any progress?  I've submitted the CREATE TABLE statements, and have had no response in nearly a month.

Please, try to repeat with a newer version, 5.0.26, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have recently upgraded to 5.0.27-standard and am still having the same problem.  There appears to be a very *serious* bug within MySQL that returns incorrect data from a valid SQL (albeit complex) statement.

Please, specify the exact storage engine used for each of the tables (or just send SHOW TABLE STATUS results). Send the results of EXPLAIN for problematic query. Send also your my.cnf file content. You can upload all these as private file(s).

What we need is a repeatable test case. We need a more detailed information to create it.

Is this your file:

mysqldev@production:~> ls -l /supportftp/pub/mysql/upload/*21933*
--w-rw----  1 ftpuploads myftp 1495040 Feb  9 00:31 /supportftp/pub/mysql/upload
/mysql-bug-21933.tgz

on our FTP server? If yes, I have it, but need some time before making further comments.

We are currently using 5.0.18 in a production environment and it is now problematic.  We have had several crashes (glibc errors); the only suggestions reported are to **upgrade**.  However, due to this bug, it is not an option. 

Our only choices are to downgrade back to 3.23 where we ran for several years without any issues or to migrate to a new platform.

Any chance that this bug will be fixed in near future?

It's been yet another month and i still haven't received any response.
Is this error being reviewed?

There is a CRITICAL BUG within the application that CONSISTENTLY RETURNS THE WRONG RESULTS!!!  New versions still have the same error.

Any response would be greatly appreciated.

So, inconsistent results (per initial description) or cosistently wrong results? Have you tried to repeat with MySQL 5.0.37 already?

Please, send also your my.cnf file content.

Sorry for a delay with this bug anyway. I'll try to repeat with your file uploaded on latest 5.0.40-BK this week.

So, inconsistent results (per initial description) or cosistently wrong results? Have you tried to repeat with MySQL 5.0.37 already?

Please, send also your my.cnf file content.

Sorry for a delay with this bug anyway. I'll try to repeat with your file uploaded on latest 5.0.40-BK this week.

The results are incorrect, every time.  It has happened on many of the releases since 5.0.18 (the one currently used as production).

my.conf is included in tarball uploaded last month.
The tarball includes a README for explicit directions and observations, along with sample data and the query in question.

This is very repeatable, but complicated; the inner-joined tables return the corret results but the outer joined table does not.  The outer joined table returns the results from the previous query (if still cached).

I have not tried the newest release, but suspect it will still fail.  I believe it is a core process that is reading/writing out of order.  I suspect that it is also related to memory/buffer management.

OK, I'll check it with 5.0.37 and current 5.0.40.

I have a similar issue with 5.0.27, running the query in MySQL Query Browser:

  select * from a
  where col = 123

returns some rows

  select * from a
  left join b
    on b.id = a.id
  where col = 123

says "Query returns no resultset".

Running it from the command line does work as expected though.
My problem looks like a bug in the query browser.

It's been 9 months since this report was filed, and you have done nothing about it.  I have submitted all requested files/docs and have yet to even have a proper response from anyone about how to proceed.

3 months ago you claimed to need more time, hopefully 3 months is enough!
2 months ago you were going to re-test new version....no response.
 
I have test/re-tested/re-tested several versions.  AGAIN, I believe this to be a problem with the CORE instruction set for memory management.
Is there ANY WAY to ESCALATE this problem?  

MySQL IS NOT RELIABLE UNDER THESE SPECIFIC CIRCUMSTANCES!!!!

It would be nice to resolve this issue within 1 year's time!

Sorry for a delay with this bug report. My fault. One of the reasons is simple, though: I was not able to use your file uploaded, whatever I tried, on different machines. It is corrupted somehow. Here is what I get usually:

mysqldev@production:/supportftp/pub/mysql/upload> ls -l *21933*
--w-rw----  1 ftpuploads myftp 1495040 Feb  9 00:31 mysql-bug-21933.tgz
mysqldev@production:/supportftp/pub/mysql/upload> file *21933*
mysql-bug-21933.tgz: gzip compressed data, from Unix
mysqldev@production:/supportftp/pub/mysql/upload> gunzip *21933*

gunzip: mysql-bug-21933.tgz: unexpected end of file
mysqldev@production:/supportftp/pub/mysql/upload>

So, please, check if file size is correct and upload your test case again, with name, send MD5 checksum for it here.

Please, take a look at somewhat similar bug also that I verified today, Bug #28571. Maybe, you can create a smaller test case based on that bug.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".