Stories
Slash Boxes
Comments

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Monday October 08, 2007
10:11 AM

Mysql bug of the day: right joins

[ #34631 ]

Closely related to this bug, older versions of MySQL don't handle right joins correctly.

drop table if exists t1,t2,t3;
create table t1 (i int);
create table t2 (i int);
create table t3 (i int);
insert into t1 values(1),(2);
insert into t2 values(2),(3);
insert into t3 values(2),(4);

That sets things up. Now let's try the query:

select t1.i, t2.i, t3.i
from t1
inner join t2 on t2.i = t1.i
right join t3 on t3.i = t2.i;

On 4.0.18 this returns:

+------+------+------+
| i    | i    | i    |
+------+------+------+
|    1 | NULL |    2 |
|    2 |    2 |    2 |
|    1 | NULL |    4 |
|    2 | NULL |    4 |
+------+------+------+

On 5.0.38 this correctly returns:

+------+------+------+
| i    | i    | i    |
+------+------+------+
|    2 |    2 |    2 |
| NULL | NULL |    4 |
+------+------+------+

That's why this SQL, after being fixed, still failed on our production server. I've blown much of today trying to find this bug. Now that I've found it, I don't know how to fix it using only SQL.

It turns out that it's this bug on all versions of MySQL less than version 5. RIGHT JOINs return incorrect results after INNER JOINs. :(


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Mysql bug of the day: right joins 15 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.

  • SELECT i.i1, i.i2, t3.i
    FROM (
        SELECT t1.i AS i1, t2.i AS i2
        FROM t1
        INNER JOIN t2 ON t2.i = t1.i
    ) i
    RIGHT JOIN t3 ON t3.i = i.i2;

    No idea if this actually fixes the problem. Depends on what the query planner turns it into, I guess.

  • Clearly the best solution is to upgrade to a recent version. 5.0 has been the stable release for years and has many improvements over older versions.

    Failing that, isn't a RIGHT JOIN just a LEFT JOIN with the table order reversed? If you list the tables in reverse order and use a LEFT JOIN will it work?

    • No, because reversing the order gives you this:

      select t1.i, t2.i, t3.i
      from t3
      left  join t2 on t3.i = t2.i
      inner join t1 on t1.i = t2.i;

      That final INNER JOIN excludes the NULL row we need.

      • In that case, the simplest fix is probably a temp table, used in the same way you would use a subquery in a more recent version.

        • How… relational.

          • It seems a bit unfair to criticize MySQL for something that was literally fixed years ago. The 4.0 series isn't even supported anymore.

            • Agreed. It's like taking potshots at Perl 5.005.

            • Huh, that was a criticism of MySQL?

              • It sounded that way. Apologies if I read too much into it. Sarcasm is a tough call sometimes on message boards.

                • It just struck me as funny after reading the posts Ovid recently wrote about real relational databases, noting among other things that a select query there returns a bonafide table.

    • Surprise, surprise. 4-year old OSS software has bugs. If only there was a solution...

      I understand the frustration since it's sometimes hard to upgrade critical pieces like MySQL or Perl, but how many bugs do you publicly complain about in Perl 5.8.1. Probably not many. You instead use a more recent version without those bugs or you work around them.

      At least you don't have a vendor company forcing you to upgrade. Which means you do it at your own pace and consequences. Only you (meaning whatever company you'
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.