MySQL Bugs: #34533: a VIEW of InnoDB tables/views can give an invalid result for algorithm=merge

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

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.

sql test case

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

sql test case output, with comments.

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

Workaround: Use temptable algorithm

Just tried it with the latest 5.0 and with the 5.0.70 release. The bug is no longer there.

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.