MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Josh Trutwin Date:December 21 2005 2:57pm
Subject:Status of OUTER JOIN bug 1591?
View as plain text  
This is referencing an old thread in the mailing list:

I'm trying to write a justification for upgrading to MySQL 5.0 and I 
know it fixed some issues with LEFT/RIGHT joins.  In particular I 
thought it fixed bugs 1591, 1677 and 3765.  But bug 1591 is still 
listed as "To be fixed later".  Is that correct?  If it is is there a 
different bug against how MySQL 4 handled OUTER JOINS which was fixed 
in MySQL 5 that I can reference in my justification letter?


Josh Trutwin

FWIW - here is an off-list conversation with another list member 
regarding this issue:

Josh Trutwin <josh@stripped> wrote on 12/20/2005 05:45:04 PM:

  > Josh Trutwin wrote:
  > > SGreen@stripped wrote:
  > >> Josh Trutwin <josh@stripped> wrote on 09/20/2004
10:41:46 PM:
  > >>
  > >>> On Mon, 20 Sep 2004 10:25:16 -0400
  > >>> SGreen@stripped wrote:
  > >>>
  > >>>> I think you missed my point. I think the 5.0.1 behavior was 
  > >>>> and the others are wrong. There is a known bug (or two) about 
  > >>>> outer joins and inner joins and it looks like it may be fixed. IF
  > >>>> you want to see all of the students THAT TABLE (students) 
needs to
  > >>>> be on the LEFT side of a LEFT JOIN or the RIGHT side of a RIGHT
  > >>>> JOIN. That's what the directions mean....
  > >>> Interesting - do you have a link to more information on this bug?
  > >>>
  > >>
  > >> and and
  > >>
  > >
  > > Shawn,
  > >
  > > - a while back you helped me debug some MySQL Join queries that
  > > didn't work after upgrading from 4.0 to 5.0 alpha.  The bugs are
  > > referenced in the links above.  Do you know why these bugs are still
  > > listed as "To be fixed later" in the bug database?  I am trying to
  > > a document for persuading someone to support MySQL 5.0 in their
  > > and wanted to use this example as a major fix made in the database
  > >
  > > Thread reference:
  > >
  > >
  > > Thanks,
  > >
  > > Josh
  > >

I don't know why they are not fixed. It may be that they are but nobody
has gone back to test the new algorithms against them. Mixing left joins
and right joins are problematic anyway. There also isn't a FULL OUTER
JOIN, yet either. I was trying to find a workaround for the lack of FULL
OUTER JOIN when I tried a query that included both LEFT and RIGHT joins
and noticed that it didn't quite mesh. That's how I found out about the
bugs I told you about. There have been 16 sets of changes since 5.0.1 so
any one of them may have fixed it but the regression tests just haven't
picked it up.

99.9% of the queries out there do not mix LEFT and RIGHT joins and there
are other ways to work around the lack of a FULL OUTER JOIN so that
probably explains why the fixes are low on the priority list.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Status of OUTER JOIN bug 1591?Josh Trutwin21 Dec