MySQL Bugs: #40560: Join condition with OR fails incorrectly in three-table join

Bug #40560	Join condition with OR fails incorrectly in three-table join
Submitted:	6 Nov 2008 18:47	Modified:	16 Nov 2008 21:57
Reporter:	Alexis Dimitriadis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	Ver 14.12 Distrib 5.0.18	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
The following query gives incorrect results; removing the clause
"OR (v.type_id = 'XXX')" makes it behave as expected.

SELECT * FROM datatable d
    INNER JOIN parameterDef p ON d.parameter_def_id = p.parameter_id
    INNER JOIN valueDefinition v ON 
      ( (d.value_definition_id = v.value_id) 
        AND (
          (v.type_id = p.value_type_id)
          OR (v.type_id = 'XXX') 
        ) 
      );

The query incorrectly returns an empty set, although all related records are
present. If the clause "OR (v.type_id = 'XXX')" is removed, the query works
properly. Since there is no record with the value 'XXX', the two versions
SHOULD have given the same result.

Changing the second INNER JOIN into a LEFT JOIN gives NULLs for all v
columns of v, suggesting that the OR clause makes the join condition
fail.

Using INNODB tables; all join keys are varchar fields. The problem appears to be intermittent(!), but frequent. The particular manifestation reported has been more stable.

How to repeat:
I can provide a database dump that will hopefully misbehave

Database dump plus a couple of queries

Attachment: join-bug.sql (text/x-sql), 7.51 KiB.

Thank you for the bug report. I couldn't repeat with current source server. Could you please upgrade to latest released version (your 5.0.18 version is quite older). Thanks in advance.

Thanks for the quick reaction!

Ok... I cannot reproduce it on another server running 5,0.51a-log; it's always been intermittent so i'll have to keep an eye on it, but I'll try to get us upgraded and hope for the best.

If possible, please leave this bug open a bit till I see how this plays out.

Please, inform us if this problem will happen with a newer versions.

I can't reproduce the behavior on 5.0.67, so it seems that the problem has been resolved in the meantime. sorry for the spurious report!