Bug #18066 Different results when running query twice
Submitted: 8 Mar 2006 9:06 Modified: 7 Apr 2006 12:21
Reporter: Lars Borg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Linux (Linux Fedora core 1)
Assigned to: CPU Architecture:Any

[8 Mar 2006 9:06] Lars Borg
Description:
Running a SELECT query with a JOIN that looks like this... 

JOIN tbl_a ON tbl_a.col_1 = tbl_b.col_2 OR (tbl_a.col_1 = 'A B' AND tbl_b.col_2 = 'AB') 

... I get 32 rows in the result the first time, but if I run the query againg (and again, and again, and again...) I get 275 rows back. 275 is the correct number of rows.

If I comment out the OR...

JOIN tbl_a ON tbl_a.col_1 = tbl_b.col_2 #OR (tbl_a.col_1 = 'A B' AND tbl_b.col_2 = 'AB')

... I allways get the correct result.

The "Explain" from the 1st and 2nd runs are identical.

To me it feels like the DB's cache must be loaded before it can return the correct result.
This problem occurs regardless of what client is used.
Running the query from the mysql shell a few times and then wait 5 or 10 min, the problem reappears.

The problem do not exist on the Windows platform.

How to repeat:
Just open a new connection and run the query again.

Suggested fix:
Breaking apart the select into two united selects avoids the problem, but it is slightly slower.
[8 Mar 2006 10:38] Heikki Tuuri
Lars,

are these MyISAM or InnoDB tables?

Are you using the query cache?

4.1.7 is rather old. Does the bug still exist in 4.1.18?

Regards,

Heikki
[8 Mar 2006 10:45] Lars Borg
All tables are InnoDB.
I'll try to upgrade to 4.1.18 to see if the problem remains.

/Lasse
[8 Mar 2006 14:35] MySQL Verification Team
Thank you for the bug report.
Could you please test with latest released server, if the issue continues
could you please provide a dump for to test the offended query?

Thanks in advance.
[6 Apr 2006 14:20] Lars Borg
Tried the exact same query on version 5.0.19.
This version seems to behave correctly.

I've not tried any other versions than 4.1.7 and 5.0.19.
If it is extreamly necessary to test other versions, let me know and I'll try to find the time for it.
[6 Apr 2006 15:14] MySQL Verification Team
Thank you for the feedback. Could you please try with the latest release
4.1.18. Thanks in advance.
[7 Apr 2006 10:36] Lars Borg
4.1.18 seems to behave correctly.
[7 Apr 2006 12:21] MySQL Verification Team
Thank you for the feedback. I am closing this bug report.