Bug #4083 2-join select works as expected on db2 but not on mysql
Submitted: 9 Jun 2004 21:11 Modified: 9 Jun 2004 22:11
Reporter: o s Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.18 OS:Windows (win xp pro)
Assigned to: Assigned Account CPU Architecture:Any

[9 Jun 2004 21:11] o s
Description:
i am doing a fairly straight-forward 2-join select which works as expected on DB2, but does strange things on mysql 4.0.18.

i have 3 tables: players, teams, and team_members. TABLE players has a pool of all available players. TABLE teams has all teams. a group of teams comprises one league. TABLE team_members reflects which players are on which team. so if player joe was on team jets, there would be an entry in team_members referencing the jets and joe. there is a restriction that no player appear on more than one team in any given league. that is, only the jets can have joe in their league. but in some another league, a different team can have joe.

my query selects all the players NOT on any team in a given league with no nested queries because i am on mysql 4.0.18.

here is the database dump, reduced to its absolute bare minimum (it's very small):

CREATE TABLE team_members (
  team_member_id int(10) unsigned NOT NULL auto_increment,
  team_id int(10) unsigned NOT NULL default '0',
  player_id mediumint(8) NOT NULL default '0',
  PRIMARY KEY  (team_member_id)
) TYPE=MyISAM;

INSERT INTO team_members VALUES (730,515,4590);
INSERT INTO team_members VALUES (729,514,302154);
INSERT INTO team_members VALUES (728,513,396138);
INSERT INTO team_members VALUES (727,512,396139);
INSERT INTO team_members VALUES (726,511,4590);

CREATE TABLE players (
  player_id mediumint(8) NOT NULL default '0',
  first_name varchar(50) default NULL,
  PRIMARY KEY  (player_id)
) TYPE=MyISAM;

INSERT INTO players VALUES (133460,'Ryan');
INSERT INTO players VALUES (396138,'Walter');
INSERT INTO players VALUES (396139,'Dave');
INSERT INTO players VALUES (302154,'Pat');
INSERT INTO players VALUES (146369,'Joe');
INSERT INTO players VALUES (133461,'Peppi');
INSERT INTO players VALUES (133462,'Amos');
INSERT INTO players VALUES (4590,'Jeff');
INSERT INTO players VALUES (192939,'Chris');
INSERT INTO players VALUES (397803,'Adam');
INSERT INTO players VALUES (484894,'Kenny');
INSERT INTO players VALUES (235186,'Paul');

CREATE TABLE teams (
  team_id int(10) unsigned NOT NULL auto_increment,
  team_name varchar(100) NOT NULL default '',
  league_id varchar(45) NOT NULL default '0',
  PRIMARY KEY  (team_id)
) TYPE=MyISAM;

INSERT INTO teams VALUES (511,'Jets','league1');
INSERT INTO teams VALUES (512,'Browns','league1');
INSERT INTO teams VALUES (513,'Falcons','league1');
INSERT INTO teams VALUES (514,'Patriots','league2');
INSERT INTO teams VALUES (515,'Bengals','league3');

----------------------------------------
and here is the query:

SELECT
    p.*
FROM players p
        LEFT JOIN
            team_members m JOIN teams t
            ON t.league_id = 'league1'
                AND t.team_id = m.team_id
        ON p.player_id = m.player_id
WHERE m.team_id IS NULL;

on db2, it yields the set of players in TABLE players but not on any team in league1, { ryan, pat, joe, peppi, amos, chris, adam, kenny, paul }. on mysql, it yields a larger mess that i cannot make sense of.

How to repeat:
just load the database dump i have above, and try the select statement. either mysql is handling it wrong, or i am doing something very wrong in which case i'd very much appreciate an explanation of why this statement works on db2 (or any other commercial db) and not mysql.

thanks a lot.
[9 Jun 2004 22:11] Dean Ellis
The explanation is that you are performing a nested join, which MySQL does not currently support.  The capability is of course on our TODO and will be implemented, but it is not available today.  You would, basically, need to rewrite the query to avoid the nesting of joins.