MySQL Bugs: #19156: LEFT JOIN not finding matching rows from second table

Bug #19156	LEFT JOIN not finding matching rows from second table
Submitted:	18 Apr 2006 2:34	Modified:	4 Jul 2006 11:51
Reporter:	Marcus Doemling	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.20	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I use a LEFT JOIN to find entries in a table that do not have a corresponding entry in a second table.
Since upgrading to 5.0.20 this no longer works. Matching rows in the second table are not found and instead a NULL value row is produced incorrectly identifying the row in the first table as not matching anything in the second table.

Here is the query:

SELECT A.myid,B.* FROM cars A LEFT JOIN availability B ON A.cid=B.cid AND A.uindex=B.uindex AND A.myid=B.object AND B.type='sedan' WHERE A.cid='smith' AND A.uindex=3 AND B.object IS NULL;

And the result (I'm only showing the "z_adx" entry):

| myid   | cid   | uindex | object | size | filled |
+--------+-------+--------+--------+------+--------+
| z_adx  |       | NULL   |        |      |        |

The matching entries from both tables:

mysql> select * from cars where myid='z_adx';
| cid     | myid   | uindex | size  |
+---------+--------+--------+-------+
| smith   | z_adx  | 3      | large |

mysql> select * from availability where object='z_adx';
| cid     | uindex | object  | type   | filled              |
+---------+--------+---------+--------+---------------------+
| smith   | 3      | z_adx   | sedan  | 2006-04-16 19:38:12 |

The table definitions:
mysql> show columns from cars;
+---------+--------------+------+-----+---------------------+-------+
| Field   | Type         | Null | Key | Default             | Extra |
+---------+--------------+------+-----+---------------------+-------+
| cid     | varchar(30)  | NO   | PRI |                     |       |
| myid    | varchar(30)  | NO   | PRI |                     |       |
| uindex  | int(11)      | NO   |     | 0                   |       |
| size    | varchar(10)  | NO   |     |                     |       |
+---------+--------------+------+-----+---------------------+-------+

mysql> show columns from availability;
+--------+-----------+------+-----+---------------------+-------+
| Field  | Type      | Null | Key | Default             | Extra |
+--------+-----------+------+-----+---------------------+-------+
| cid    | char(30)  | NO   | PRI |                     |       |
| uindex | int(11)   | NO   | PRI | 0                   |       |
| object | char(100) | NO   | PRI |                     |       |
| type   | char(10)  | NO   | PRI |                     |       |
| filled | datetime  | NO   |     | 0000-00-00 00:00:00 |       |
+--------+-----------+------+-----+---------------------+-------+

How to repeat:
Use the table setup described and run the query.

Thank you for a problem report. Please, provide the exact data for the tables to demonstrate the problem (as a dump or, simply, a set of INSERT statements). I was unable to get similar results with the following statements:

mysql> create table cars (cid varchar(30) not null,
    -> myid varchar(30) not null,
    -> uindex int,
    -> size varchar(10),
    -> primary key(cid, myid));
Query OK, 0 rows affected (0.03 sec)

mysql> create table availability (cid char(30) not null,
    -> uindex int not null default 0,
    -> object char(100) not null,
    -> type char(10) not null,
    -> filled datetime not null default '0000-00-00 00:00:00',
    -> primary key (cid, uindex, object, type));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into cars values('smith', 'z_adx', 3, 'large');
Query OK, 1 row affected (0.00 sec)

mysql> insert into availability values('smith', 3, 'z_adx', 'sedan', now());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT A.myid,B.* FROM cars A LEFT JOIN availability B ON A.cid=B.cid AND
    -> A.uindex=B.uindex AND A.myid=B.object AND B.type='sedan' WHERE A.cid='smith' AND
    -> A.uindex=3 AND B.object IS NULL;
Empty set (0.01 sec)

Indeed when creating the tables new the problem is gone!
I just created identical tables in a different database, then copied the entire data for both tables using INSERT INTO db2.table1 SELECT * FROM db1.table1.
Using the new tables which should be identical the problem is gone.

I guess the problem lies in the upgrade. My previous version of MySQL was 4.0.17 and I upgraded using Red Hat Enterprise Linux 3 RPM (x86) packages. The upgrade command used with rpm:

  > rpm -U MySQL-server-standard-5.0.20-0.rhel3.i386.rpm MySQL-client-standard-5.0.20-0.rhel3.i386.rpm MySQL-shared-compat-5.0.20-0.rhel3.i386.rpm MySQL-devel-standard-5.0.20-0.rhel3.i386.rpm

I checked for the mysql_upgrade utility described in the documentation but apparently it is not included in the RPMs. Checking the status of the tables and also using OPTIMIZE seems fine but doesn't fix the issue.

Please, check with 5.0.21 (if it contains mysql_update and, if it does, will it report any problem). Alternatively, run CHECK TABLE  ... FOR UPGRADE (http://dev.mysql.com/doc/refman/5.0/en/check-table.html) and check if it solves tha binary upgrade problem for you.

In the meantime I had updated all my tables by doing an ALTER on them that would recreate the table and the problem was fixed. Apparently some of the changes in this version of MySQL really require an update of the tables.

Need for recreation of tables (especially with VARCHAR columns) when you migrate from 4.x.y to 5.0.x is known and documented. I am closing this bug report, as proper upgrade or recreation of tables solves this problem.