MySQL Bugs: #15210: left join fails to return value from left joined table

Bug #15210	left join fails to return value from left joined table
Submitted:	24 Nov 2005 2:10	Modified:	16 Jun 2006 20:18
Reporter:	Kevin Smith	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15, 5.0.18	OS:	Windows (Windows, Redhat Linux Enterprise v4)
Assigned to:		CPU Architecture:	Any

Description:
This query is the exact one executed on my server, I'm not sure if I can replicate this with a simplified version.  However, I did find some other bugs related to problems with LEFT JOINS...  9614, 9622,  15171 and 14708.

The problem is have is this query does return results, except that the column pcs.promotion_id sometimes returns the data "christmas" as expected and sometimes returns the value NULL.  Is mostly returns the expected value "christmas".  However, when calling this query through PHP 5.0.5, but using MySQL and not MySQLi, it will "always" return NULL.

If I change the following join "left join promotional_codes AS pcs ON b.prod_id = pcs.prod_id" to "inner join promotional_codes AS pcs ON b.prod_id = pcs.prod_id", it will always return the expected data.  Of course, this is at the expense of not returning other relevant rows, which doesn't have related records in the pcs table. :(

What's also interesting, the first time the query is executed, it will always return null for the pcs.promotion_id column.  Immediate subsequent executions of the query will return the correct data.  This is via the CLI, not PHP.  If you wait a while, then execute query again, it will return a null value again.  Very strange.

select b.id, pcs.promotion_id, pc.cat_id, pc.top_cat_id, p.prod_id, p.part_code, p.product_type, p.description, p.in_stock, po.option_description, b.price, b.quantity, b.price*b.quantity as total, p.postage_cheap, p.lock_product
from basket_header_temp AS bh
inner join basket_temp AS b ON bh.basket_id = b.basket_id
inner join category_products AS cp ON b.prod_id = cp.prod_id
inner join product_categories AS pc ON cp.cat_id = pc.cat_id
left join product_options AS po ON b.option_id = po.option_id
left join promotional_codes AS pcs ON b.prod_id = pcs.prod_id
inner join products AS p ON cp.prod_id = p.prod_id
where bh.customer_id = -1
AND b.id = 8833;

How to repeat:
Haven't created a test scenerio to work this out. Sorry!

I upgraded to MySQL 5.0.16 and the problem still persists.

Also, I couldn't upgrade to MySQL-shared-standard-5.0.16-0.rhel4.i386.rpm, as I got the following error message:

warning: MySQL-shared-standard-5.0.16-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
        libz.so.0 is needed by MySQL-shared-standard-5.0.16-0.rhel4.i386

Could solve this one either.  Tried creating a sym link to libz.so.1.2.1.2 of libz.so.0, but this had not affect.

Thank you for the problem report. 

Are you really sure that there is a correspondent rown in a table your are LEFT JOINING to?

Because your repeted queries gives you different results, query cache may be a problem here. So, please, send your my.cnf content or SHOW VARIABLES results.

Send the results of EXPLAIN for your query also, after "successful" run and after those that gives you NULL (use mysql command line client, please).

As for the second proble you got (upgrade and dependencies), please, open a separate bug report, if you want. Don't mix several problems in one report.

inner join bug: requested output of explain and query results

Attachment: MySQL Bug.txt (text/plain), 24.46 KiB.

Thank you for the additional information. I'll check.

As for your second problem, it is known already (http://bugs.mysql.com/bug.php?id=15223).

Please, send the dump of the tables used for this test. You may use File tab for that and upload file as private, if you want.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Please, try to repeat with a newer version, 5.0.21, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".