MySQL Bugs: #9614: LEFT JOIN returns empty set when you use second table in WHERE

Bug #9614	LEFT JOIN returns empty set when you use second table in WHERE
Submitted:	4 Apr 2005 15:01	Modified:	27 Jan 2006 19:30
Reporter:	Tonis	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.10a	OS:	Linux (suse 9.2)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
LEFT JOIN returns empty set when I use second table in WHERE statement and ON condition doesn't match.

How to repeat:
Have simple two-table query where visits.period is DATE:

SELECT a.period,b.period
FROM visits a LEFT JOINT visits b ON b.period='2001-01-01'
WHERE a.period='2004-01-01' AND b.period ='2004-01-01'

Here 'Where' statement returns records but because in ON statement period doesn't match in any recored according to left join b.period should contain NULLs. Instead, mysql returns empty set, so no matching records at all! It works when I don't use second table in where clause ..

 I have to create temporary tables to make it still work with reasonable speed.

Seems that it is duplicate of bug #9017: Rows are omitted from result set when using JOIN, XOR and ISNULL

The server behaves in the same way.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Why do you think it's a bug ?

You query cannot return any row where b.period is NULL because you have

  WHERE ... b.period='2004-01-01'

So, empty set is correct answer.

Damn, I'm little confused here,

7.2.9 How MySQL Optimizes LEFT JOIN and RIGHT JOIN says:

If there is a row in A that matches the WHERE clause, but there is no row in B that matches the ON condition, an extra B row is generated with all columns set to NULL.

As i understand, Where clause will be applyied first and it succeeds but ON fails, so it should return nulls for row.

try moving conditions with second table fields to 'ON...' part of query, works for me.

ok, I'll move it to "documentation" category, and let out documentation team to decide what could be done here (if anything)

There's nothing to do here.  The column in b cannot
be both NULL and equal to the given date at the same
time.