Bug #34533 a VIEW of InnoDB tables/views can give an invalid result for algorithm=merge
Submitted: 14 Feb 2008 4:18 Modified: 31 Oct 2008 11:46
Reporter: Sean Pringle Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.54,5.1.23 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[14 Feb 2008 4:18] Sean Pringle
Description:
Creating a VIEW of multiple InnoDB TABLEs with a common foreign key and joined with another VIEW, can allow results to go missing... somehow.  MyISAM is not affected.  This seems to occur when:

- Multiple InnoDB tables have a common foreign key.
- View algorithm=merge (same view using algorithm=temptable is not affected).
- EXPLAIN somehow decides "Impossible WHERE noticed after reading const tables" when that is not so.  This assumption remains until FLUSH TABLES is executed.

No doubt this seems vague!

How to repeat:
testcase and example output will be attached.
[14 Feb 2008 4:19] Sean Pringle
sql test case

Attachment: testcase.sql (text/x-sql), 2.32 KiB.

[14 Feb 2008 4:19] Sean Pringle
sql test case output, with comments.

Attachment: testcase_out.txt (text/plain), 8.10 KiB.

[6 Mar 2008 20:44] Omer Barnir
Workaround: Use temptable algorithm
[31 Oct 2008 10:58] Georgi Kodinov
Just tried it with the latest 5.0 and with the 5.0.70 release. The bug is no longer there.
[31 Oct 2008 11:46] Georgi Kodinov
Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.