MySQL Join not returning data that matches in both tables in new database instance

Question

I've been lurking here for a few months and learning a lot from reading other questions, but this is the first time I've been brave enough to ask one!

SELECT  * FROM tableA a
JOIN tableB b ON b.tableBid = a.tableBid
WHERE a.tableAcol = 'paramValue';

I only have 39 rows in table B and for everyone of these there IS a row in tableA for paramValue with a matching tableBid, so I would expect to get 39 rows back. And indeed in another database instance I do.

I can't see any significant differences in the instances, though this could be ignorance, but in this new instance I only get 7 records with the same query.

If I change the query to

SELECT  * FROM tableA a
LEFT JOIN tableB b ON b.tableBid = a.tableBid
WHERE a.tableAcol = 'paramValue';

or

SELECT  * FROM tableA a
LEFT JOIN tableB b ON a.tableBid = b.tableBid
WHERE a.tableAcol = 'paramValue';

then I get the 39 rows I expect with a.tableBid matching b.tableBid in all cases and no nulls

I am clearly confused. Can anyone explain this please.

Can't post the real data but I can reproduce it

 CREATE TABLE tableB (  
tableBid varchar(30) NOT NULL   PRIMARY KEY,
nameB varchar(25) NOT NULL  ,
description varchar(40) NOT NULL);

CREATE TABLE tableA (
userID varchar(30) NOT NULL,
tableBid Varchar(30) NOT NULL,
info varchar(10) NOT NULL,
PRIMARY KEY(userID, tableBID));

insert into tableB (tableBid, nameB, description)
values 
('a', 'a name', 'blah'),
('b', 'b name', 'blah'),
('c', 'c name', 'blah'),
('d', 'd name', 'blah');

insert into tableA (userID, tableBid, info)
values
('deel','a',  'blah'),
('deel','b',  'blah'),
('deel','c',  'blah'),
('deel','d',  'blah');

Then

SELECT  * FROM tableA a
LEFT JOIN tableB b ON b.tableBid = a.tableBid
WHERE a.userID = 'deel';

gives me 4 rows:

deel    a   blah    a   a name  blah
deel    b   blah    b   b name  blah
deel    c   blah    c   c name  blah
deel    d   blah    d   d name  blah

But

SELECT  * FROM tableA a
JOIN tableB b ON b.tableBid = a.tableBid
WHERE a.userID = 'deel';

gives me

deel    d   blah    d   d name  blah

Tables definitions

CREATE TABLE `tablea` (
   `userID` varchar(30) NOT NULL,
   `tableBid` varchar(30) NOT NULL,
   `info` varchar(10) NOT NULL,
   PRIMARY KEY (`userID`,`tableBid`)
 ) ENGINE=Xeround DEFAULT CHARSET=latin1

CREATE TABLE `tableb` (
   `tableBid` varchar(30) NOT NULL,
   `nameB` varchar(25) NOT NULL,
   `description` varchar(40) NOT NULL,
   PRIMARY KEY (`tableBid`)
 ) ENGINE=Xeround DEFAULT CHARSET=latin1

Answer 1

A logical assumption from your queries output: If this happens, it's a bug.

After the reveal that you are using the Xeround engine, I think that you should write up the test case you have gathered and submit it as a bug in their bug list or forum.