Bug #13126 join among several tables returns empty set
Submitted: 13 Sep 2005 3:12 Modified: 9 Nov 2005 3:07
Reporter: Timothy Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.12 OS:Any (any)
Assigned to: Sergey Petrunya CPU Architecture:Any

[13 Sep 2005 3:12] Timothy Smith
Description:
I've tested this on 5.0.11, 5.0.12 and 5.0-bk from today.

It seems to behave the same in all three versions.  In general, the problem is something like:

-- This query returns 0
select count(*) from h left join b on h.h = b.h join e on h.h = e.q join f on f.v = e.v join c on e.q = c.q join d;
 
-- But this query returns 7
select count(*) from h left join b on h.h = b.h join e on h.h = e.q join f on f.v = e.v join c on e.q = c.q;

Note that in this example, there are 5 rows in 'd', so the first query really should return 35.

How to repeat:

I will attach the test case as a File to this bug report.

Sorry it's so large - i've trimmed it down a lot from the original report!

Suggested fix:
n/a
[13 Sep 2005 3:13] Timothy Smith
complete test case showing the bug

Attachment: bug2.sql (application/octet-stream, text), 13.55 KiB.

[13 Sep 2005 3:40] Timothy Smith
I forgot to mention a small detail, and maybe it's not important.  One time I tested this, with the customer's actual data, I would get 35 results from the query that ands with "join foo" (with no ON clause for final join).  But I got 0 results when I added an ON clause (I should have gotten 7 rows back).

I can't seem to repeat that exact behavior now, unfortunately.  But I thought I should mention it, in case it helps.

Timothy
[29 Sep 2005 21:58] Sergey Petrunya
BUG#13452 seems to be the same problem, so that bug is a duplicate of this one.
[30 Sep 2005 6:28] Timour Katchaounov
A smaller test query is:

select count(*)
from ((h left join (j join k on (j.w=0 and j.x=k.x)
                         join i on (k.o=i.o and j.o=k.o))
         on h.h = k.h)
       join
     e on h.h = e.q)
         join
       c
where e.q = c.q;

To make the query work it is sufficient to change the last join to
a straigh_join which forces a different join order.
[7 Oct 2005 13:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30814
[25 Oct 2005 15:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31458
[1 Nov 2005 5:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31716
[7 Nov 2005 4:08] Sergey Petrunya
The fix has been pushed into 5.0.16 tree.

Notes for the changelog:
for queries with nested outer joins the optimizer could choose join orders that the executioner could not handle. The fix is that now optimizer doesn't choose such join orders.
[9 Nov 2005 3:07] Paul Dubois
Noted in 5.0.16 changelog.
[9 Feb 2006 9:03] Aaron Fischer
Hello.  I've upgraded to 5.0.18, which includes the fix to this bug.  However, the bug remains.  Most NATURAL JOINs still return the empty set;  (explicitly writing the NATURAL JOIN as a JOIN works just fine).  

Is there a way to verify my branch has the correct version of this fix?  Or to verify that the original fix indeed works in all cases?  Please advise.  Thanks!
[9 Feb 2006 14:35] Sergey Petrunya
Aaron,
 The code released as 5.0.18 includes the fix for *this* bug. However this bug happened irrespectively of whether one used NATURAL JOIN syntax or specified field equalities explicitly. If you're getting wrong results only with NATURAL JOIN syntax, it seems like you're hitting some other issue.

Could you please report a new bug and provide there a test case (table creation/fill statements and a query that produces wrong results)?

Thanks in advance.
[16 Apr 2006 8:56] Aaron Fischer
Sergey, thanks for replying.

Turns out this was my (extremely foolish) error.  I had a common TIMESTAMP attribute named '_last_modified_date' in all my tables.  A natural join would (naturally) try to match on this commonly named attribute across two tables -- and being a timestamp, no wonder I kept getting the empty set.  The explicit inner joins I was trying omited this attribute (didn't even cross my mind).