Bug #6435 strange behaviour of left join

• Submitted: 4 Nov 2004 16:06
• Modified: 8 Nov 2004 11:50
• Reporter: Tomas Ulin
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S1 (Critical)
• Version: 4.1.8 bk
• OS: Linux (gentoo 2.6)
• Assigned to: Jonas Oreland
• CPU Architecture: Any

[4 Nov 2004 16:06] Tomas Ulin

Description:
join does not give same result with myisam and ndbcluster

+ drop table if exists test1, test2;
+ CREATE TABLE `test1` (
+ `SubscrID` int(11) NOT NULL auto_increment,
+ `UsrID` int(11) NOT NULL default '0',
+ PRIMARY KEY  (`SubscrID`),
+ KEY `idx_usrid` (`UsrID`)
+ ) ENGINE=myisam DEFAULT CHARSET=latin1;
+ INSERT INTO `test1` VALUES (2,224),(3,224),(1,224);
+ CREATE TABLE `test2` (
+ `SbclID` int(11) NOT NULL auto_increment,
+ `SbcrID` int(11) NOT NULL default '0',
+ PRIMARY KEY  (`SbclID`),
+ KEY `idx_sbcrid` (`SbcrID`)
+ ) ENGINE=myisam DEFAULT CHARSET=latin1;
+ INSERT INTO `test2` VALUES (3,2),(1,1),(2,1),(4,2);
+ select * from test1;
+ SubscrID      UsrID
+ 2     224
+ 3     224
+ 1     224
+ select * from test2;
+ SbclID        SbcrID
+ 3     2
+ 1     1
+ 2     1
+ 4     2
+ SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON
+ l.SbcrID=s.SubscrID WHERE s.UsrID=224;
+ SubscrID      SbclID
+ 2     3
+ 2     4
+ 3     NULL
+ 1     1
+ 1     2

+ drop table if exists test1, test2;
+ CREATE TABLE `test1` (
+ `SubscrID` int(11) NOT NULL auto_increment,
+ `UsrID` int(11) NOT NULL default '0',
+ PRIMARY KEY  (`SubscrID`),
+ KEY `idx_usrid` (`UsrID`)
+ ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
+ INSERT INTO `test1` VALUES (2,224),(3,224),(1,224);
+ CREATE TABLE `test2` (
+ `SbclID` int(11) NOT NULL auto_increment,
+ `SbcrID` int(11) NOT NULL default '0',
+ PRIMARY KEY  (`SbclID`),
+ KEY `idx_sbcrid` (`SbcrID`)
+ ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
+ INSERT INTO `test2` VALUES (3,2),(1,1),(2,1),(4,2);
+ select * from test1;
+ SubscrID      UsrID
+ 2     224
+ 3     224
+ 1     224
+ select * from test2;
+ SbclID        SbcrID
+ 2     1
+ 4     2
+ 3     2
+ 1     1
+ SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON
+ l.SbcrID=s.SubscrID WHERE s.UsrID=224;
+ SubscrID      SbclID
+ 2     NULL
+ 3     NULL
+ 1     NULL

How to repeat:
Test case for mysql-test-run

--source include/have_ndb.inc

--disable_warnings
drop table if exists test1, test2;
--enable_warnings

CREATE TABLE `test1` (
`SubscrID` int(11) NOT NULL auto_increment,
`UsrID` int(11) NOT NULL default '0',
PRIMARY KEY  (`SubscrID`),
KEY `idx_usrid` (`UsrID`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

INSERT INTO `test1` VALUES (2,224),(3,224),(1,224);

CREATE TABLE `test2` (
`SbclID` int(11) NOT NULL auto_increment,
`SbcrID` int(11) NOT NULL default '0',
PRIMARY KEY  (`SbclID`),
KEY `idx_sbcrid` (`SbcrID`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

INSERT INTO `test2` VALUES (3,2),(1,1),(2,1),(4,2);
select * from test1;
select * from test2;
SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON
l.SbcrID=s.SubscrID WHERE s.UsrID=224;