MySQL Bugs: #13126: join among several tables returns empty set

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

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

complete test case showing the bug

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

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

BUG#13452 seems to be the same problem, so that bug is a duplicate of this one.

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.

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

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

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

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.

Noted in 5.0.16 changelog.

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!

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.

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).