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. :(
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.
Re:First thing I'd try
Ovid on 2007-10-08T15:12:47
4.0.18 doesn't allow subqueries.
Re:First thing I'd try
Aristotle on 2007-10-08T15:28:52
Are you sure? ISTR using them in some 4.x version; maybe it was 4.1? Sigh.
Re:First thing I'd try
Ovid on 2007-10-08T15:33:43
From the MySQL docs:
Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.Re:First thing I'd try
vek on 2007-10-08T15:41:33
They added subselects back in 2003 to version 4.1
Re:what about LEFT?
Ovid on 2007-10-09T06:19:08
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.
Re:what about LEFT?
perrin on 2007-10-09T09:12:30
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.Re:what about LEFT?
Aristotle on 2007-10-09T11:23:45
How… relational.
Re:what about LEFT?
perrin on 2007-10-09T16:48:18
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.Re:what about LEFT?
Ovid on 2007-10-09T17:01:37
Agreed. It's like taking potshots at Perl 5.005.
Re:what about LEFT?
Aristotle on 2007-10-09T18:29:36
Huh, that was a criticism of MySQL?
Re:what about LEFT?
perrin on 2007-10-10T01:36:21
It sounded that way. Apologies if I read too much into it. Sarcasm is a tough call sometimes on message boards.Re:what about LEFT?
Aristotle on 2007-10-10T04:28:47
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.
Re:what about LEFT?
mpeters on 2007-10-09T12:41:38
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're working for) decide when the pain of working around these old, fixed bugs becomes too much.
