MySQL Bugs: #33944: A row gets lost in a complex query

Bug #33944	A row gets lost in a complex query
Submitted:	19 Jan 2008 23:10	Modified:	28 Jan 2008 14:19
Reporter:	Martin Wimmer	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.45-Debian_1ubuntu3.1-log	OS:	Linux (Ubuntu 7.10 Gutsy)
Assigned to:		CPU Architecture:	Any

Description:
With a more complex SELECT query, a row gets lost.
The problem appears because of: AND (a80_1_temp.expires IS NULL)
If I remove this condition the query works. Actually the referenced table is empty, so this statement should always return NULL.

I can provide a dump of the database to help you find this bug. As it's a test database, its nearly empty

Query:
SELECT a78_1_dbo.oid dboq_a4238_oid, a79_1_dbo.oid dboq_a4238_type_oid, a79_1_dbo_type.derived_from_oid dboq_a4238_type_derived_from, a79_1_dbo_type.class_uri dboq_a4238_type_class, a79_1_dbo_type.flags dboq_a4238_flags, a78_1_person.first_name dbo_aal_2, a78_1_person.last_name dbo_aal_3, a80_1_dbo.oid dboq_cd545_oid, a81_1_dbo.oid dboq_cd545_type_oid, a81_1_dbo_type.derived_from_oid dboq_cd545_type_derived_from, a81_1_dbo_type.class_uri dboq_cd545_type_class, a81_1_dbo_type.flags dboq_cd545_flags, a80_1_organization.name dbo_aal_4, a78_1_person.home_phone dbo_aal_6, a78_1_person.work_phone dbo_aal_7, a78_1_person.fax dbo_aal_8, a78_1_person.email dbo_aal_9 FROM (at_pfau_crm.plib_objects_plib_objects_dbo a78_1_dbo, (at_pfau_crm.plib_objects_com_sanethis_crm_person a78_1_person) LEFT JOIN ((at_pfau_crm.plib_objects_plib_objects_dbo a80_1_dbo, (at_pfau_crm.plib_objects_com_sanethis_crm_organization a80_1_organization) ) LEFT JOIN ((at_pfau_crm.plib_objects_plib_objects_dbo a81_1_dbo, (at_pfau_crm.plib_objects_plib_objects_dbo_type a81_1_dbo_type) ) ) ON a80_1_dbo.type_oid = a81_1_dbo.oid AND a81_1_dbo_type.oid = a81_1_dbo.oid LEFT JOIN ((at_pfau_crm.plib_objects_plib_objects_dbo_temp a80_1_temp) ) ON a80_1_temp.oid = a80_1_dbo.oid) ON a78_1_person.workplace_oid = a80_1_dbo.oid AND a80_1_organization.oid = a80_1_dbo.oid) LEFT JOIN ((at_pfau_crm.plib_objects_plib_objects_dbo a79_1_dbo, (at_pfau_crm.plib_objects_plib_objects_dbo_type a79_1_dbo_type) ) ) ON a78_1_dbo.type_oid = a79_1_dbo.oid AND a79_1_dbo_type.oid = a79_1_dbo.oid LEFT JOIN ((at_pfau_crm.plib_objects_plib_objects_dbo_temp a78_1_temp) ) ON a78_1_temp.oid = a78_1_dbo.oid WHERE (a78_1_temp.expires IS NULL) AND (a80_1_temp.expires IS NULL) AND a78_1_person.oid = a78_1_dbo.oid    

How to repeat:
Mail me to get a dump of the testdatabase.

Using this query, should make this bug appear.

If I add a single dummy line to the temp table, the bug disappears. If I delete it, the bug is here again

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/

Our newest version at the moment is: MySQL version 5.0.51

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.

Also provide a shorter test case, that we can try to reproduce your problem.