MySQL Bugs: #25109: Query on a view drops a record

Bug #25109	Query on a view drops a record
Submitted:	16 Dec 2006 12:19	Modified:	12 Jan 2007 11:27
Reporter:	Jrme Despatis (Candidate Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.34-BK, 5.0.30	OS:	Linux (Linux, Debian Testing Kernel 2.6.18)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I do a query on several tables, all work find, but when i do the same query on views (which as views of tables i'm using), i don't get the same result

How to repeat:
I've done a .sql script in order to repeat the problem (named scenario.sql)

I do this query:
SELECT rewriting.*, UNIX_TIMESTAMP(rewriting.date_cache) AS unix_date_cache, rewriting.id AS id_rewriting, pages.*, pages.path AS page_path, pages.id AS id_page, cache_this, type, contenu, arborescence.ref_arborescence_redir FROM (rewriting) LEFT JOIN pages ON rewriting.ref_page = pages.id LEFT JOIN arborescence ON rewriting.ref_arborescence = arborescence.id WHERE rewriting.ref_portail = '2' AND rewriting.path = '/'

I get one record, which is correct

But if i do the same query on associated views:
SELECT rewriting.*, UNIX_TIMESTAMP(rewriting.date_cache) AS unix_date_cache, rewriting.id AS id_rewriting, pages.*, pages.path AS page_path, pages.id AS id_page, cache_this, type, contenu, arborescence.ref_arborescence_redir FROM (rewriting) LEFT JOIN __vue_pages_fr AS pages ON rewriting.ref_page = pages.id LEFT JOIN __vue_arborescence_fr AS arborescence ON rewriting.ref_arborescence = arborescence.id WHERE rewriting.ref_portail = '2' AND rewriting.path = '/'

I get no record at all ! which is false

I've already reported a bug http://bugs.mysql.com/bug.php?id=24345
At the end of the bug, i've detected another bug (on MySQL 5.0.26), it may be the same as the one i'm describing now, i dunno...

Suggested fix:
The fix is to query directly the tables, and not the views

Script .sql i was talking in the bug report to reproduce the problem

Attachment: scenario.sql (text/x-sql), 11.58 KiB.

Thank you for a bug report. Verified just as described (based on your test data uploaded) with 5.0.34-BK on SuSE Linux.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

This bug is fixed by the fix of Bug#24345.