Bug #81026 LEFT JOIN on PK + extra condition doesn't return matching rows from second table

Submitted: 10 Apr 2016 14:56 Modified: 4 May 2016 19:42
Reporter: Alex Verkuijl Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.11 , 5.7.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[10 Apr 2016 14:56] Alex Verkuijl
Description:
A query where we use left join on a table using an extra condition and the primary key doesn't return the matching row from the left joined table but instead returns all columns as NULL in MySQL 5.7.11. However a valid row exists; a normal join returns this correct row AND in MySQL 5.7.10 & 5.6.29 the left join query gives the correct resultset.

Query:
SELECT test_order.*,'|' as sep, test_machine.* 
  FROM test_order LEFT JOIN test_machine 
   ON  (test_machine.machineid=test_order.machineid 
    AND test_machine.activeorderid=test_order.orderid);

Returned resultset:
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       1 | m2        | |   | NULL      |          NULL |
|       2 | m2        | |   | NULL      |          NULL |
+---------+-----------+-----+-----------+---------------+

Expected resultset (ps: same query in 5.7.10 & 5.6.29):
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       1 | m2        | |   | NULL      |          NULL |
|       2 | m2        | |   | m2        |             2 |
+---------+-----------+-----+-----------+---------------+

Tested on:
Windows 7 64bit 8GB i7 (MySQL 5.7.11)
Windows 10 64bit 8GB i3 (MySQL 5.7.11)
Ubuntu 16.10 64bit 8GB i3 (MySQL 5.7.11)

NOTE: Correct result is returned when testing on 5.7.10 or 5.6.29 (only tested on windows 10 64bit).
Correct result tested on:
Windows 10 64bit 8GB i3 (MySQL 5.6.29 & MySQL 5.7.10)

Extra info:
A normal join will result in a single row with the correct data.
Dropping the primary key on test_machine table will give correct result.
Using CAST(test_machineid AS CHAR)=... in ON condition will result in correct result.

Seeing the LEFT JOIN doesn't work as advertised and returns false data I am tempted to say that this is a critical BUG and should maybe be categorized as S1.

Although different outcome and a more obscure use of the left join, the bugs #80805 AND #80526 also seems to involve strange results when using a left join and are possibly caused by the same underlying problem.

Note: I also tried the same query with MyISAM tables instead of InnoDB AND different types (ea. enum/int instead of varchar) with same outcome. I simplified the case to it's most basic.

I will post more detailed results (ea mysql client output in the next comment).

How to repeat:
-- REMOVE OLD TABLES
DROP TABLE IF EXISTS test_machine;
DROP TABLE IF EXISTS test_order;

-- CREATE 
CREATE TABLE IF NOT EXISTS `test_machine` (
  `machineid` VARCHAR(32) NOT NULL,
  `activeorderid` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`machineid`)
) ENGINE=InnoDB;

REPLACE INTO `test_machine` (`machineid`, `activeorderid`) 
    VALUES ('m1', NULL), ('m2', 2), ('m3', NULL), ('m4', NULL);

CREATE TABLE IF NOT EXISTS `test_order` (
  `orderid` bigint(20) unsigned NOT NULL,
  `machineid` VARCHAR(32) DEFAULT NULL,
  PRIMARY KEY (`orderid`)
) ENGINE=InnoDB;

INSERT IGNORE INTO `test_order` (`orderid`, `machineid`) 
    VALUES (1, 'm2'), (2, 'm2');

-- Query

SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=test_order.orderid);
[10 Apr 2016 14:58] Alex Verkuijl
Detailed output:
Mysql 5.7.11:
mysql> SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=test_order.orderid);
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       1 | m2        | |   | NULL      |          NULL |
|       2 | m2        | |   | NULL      |          NULL |
+---------+-----------+-----+-----------+---------------+
2 rows in set (0.00 sec)

mysql> explain SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=test_order.orderid);
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                       | rows | filtered | Extra       |
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
|  1 | SIMPLE      | test_order   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                      |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | test_machine | NULL       | eq_ref | PRIMARY       | PRIMARY | 98      | test.test_order.machineid |    1 |   100.00 | Using where |
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> alter table test_machine drop primary key;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=test_order.orderid);
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       2 | m2        | |   | m2        |             2 |
|       1 | m2        | |   | NULL      |          NULL |
+---------+-----------+-----+-----------+---------------+
2 rows in set (0.00 sec)

mysql> status
--------------
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe  Ver 14.14 Distrib 5.7.11, for Win64 (x86_64)

Connection id:          4
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.11 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3307
Uptime:                 1 min 25 sec

Threads: 1  Questions: 27  Slow queries: 0  Opens: 111  Flush tables: 1  Open tables: 104  Queries per second avg: 0.317
--------------

--------------------------------------

Mysql 5.7.10:
mysql> SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=test_order.orderid);
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       1 | m2        | |   | NULL      |          NULL |
|       2 | m2        | |   | m2        |             2 |
+---------+-----------+-----+-----------+---------------+
2 rows in set (0.00 sec)
mysql> explain SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=test_order.orderid);
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                       | rows | filtered | Extra       |
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
|  1 | SIMPLE      | test_order   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                      |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | test_machine | NULL       | eq_ref | PRIMARY       | PRIMARY | 98      | test.test_order.machineid |    1 |   100.00 | Using where |
+----+-------------+--------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> status
--------------
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe  Ver 14.14 Distrib 5.7.10, for Win64 (x86_64)

Connection id:          4
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.10 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3307
Uptime:                 4 min 54 sec

Threads: 1  Questions: 45  Slow queries: 0  Opens: 122  Flush tables: 1  Open tables: 111  Queries per second avg: 0.153
--------------
[10 Apr 2016 15:02] Alex Verkuijl
Test script as included in how to reproduce

Attachment: mysql_5.7.11_leftjoin_bug.sql (application/octet-stream, text), 886 bytes.

[10 Apr 2016 16:21] MySQL Verification Team
Thank you for the bug report.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.13 Source distribution PULL: 2016-APR-07

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > CREATE TABLE IF NOT EXISTS `test_machine` (
    ->   `machineid` VARCHAR(32) NOT NULL,
    ->   `activeorderid` bigint(20) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`machineid`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.33 sec)

mysql 5.7 >
mysql 5.7 > REPLACE INTO `test_machine` (`machineid`, `activeorderid`)
    ->     VALUES ('m1', NULL), ('m2', 2), ('m3', NULL), ('m4', NULL);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > CREATE TABLE IF NOT EXISTS `test_order` (
    ->   `orderid` bigint(20) unsigned NOT NULL,
    ->   `machineid` VARCHAR(32) DEFAULT NULL,
    ->   PRIMARY KEY (`orderid`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.39 sec)

mysql 5.7 >
mysql 5.7 > INSERT IGNORE INTO `test_order` (`orderid`, `machineid`)
    ->     VALUES (1, 'm2'), (2, 'm2');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > -- Query
mysql 5.7 >
mysql 5.7 > SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=te
st_order.orderid);
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       1 | m2        | |   | NULL      |          NULL |
|       2 | m2        | |   | NULL      |          NULL |
+---------+-----------+-----+-----------+---------------+
2 rows in set (0.00 sec)

mysql 5.7 >

*****************************************************************************
c:\dbs\mysql-5.7.9>bin\mysql -uroot -p --port=3308 test
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE IF NOT EXISTS `test_machine` (
    ->   `machineid` VARCHAR(32) NOT NULL,
    ->   `activeorderid` bigint(20) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`machineid`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.47 sec)

mysql>
mysql> REPLACE INTO `test_machine` (`machineid`, `activeorderid`)
    ->     VALUES ('m1', NULL), ('m2', 2), ('m3', NULL), ('m4', NULL);
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE IF NOT EXISTS `test_order` (
    ->   `orderid` bigint(20) unsigned NOT NULL,
    ->   `machineid` VARCHAR(32) DEFAULT NULL,
    ->   PRIMARY KEY (`orderid`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.35 sec)

mysql>
mysql> INSERT IGNORE INTO `test_order` (`orderid`, `machineid`)
    ->     VALUES (1, 'm2'), (2, 'm2');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> -- Query
mysql>
mysql> SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=test_or
der.orderid);
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       1 | m2        | |   | NULL      |          NULL |
|       2 | m2        | |   | m2        |             2 |
+---------+-----------+-----+-----------+---------------+
2 rows in set (0.01 sec)
[10 Apr 2016 18:15] Alex Verkuijl
Ubuntu 16.04 64bit + MySQL 5.7.11 test result.
Sorry little mix-up between 15.10 & 16.04 in my first post should have been 16.04 instead of 16.10 (just upgraded my test machine)

mysql> SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=test_order.orderid);
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       1 | m2        | |   | NULL      |          NULL |
|       2 | m2        | |   | NULL      |          NULL |
+---------+-----------+-----+-----------+---------------+
2 rows in set (0,01 sec)

When we cast the machineid it results in correct result:

mysql> SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (CAST(test_machine.machineid AS CHAR)=test_order.machineid AND test_machine.activeorderid=test_order.orderid);
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       2 | m2        | |   | m2        |             2 |
|       1 | m2        | |   | NULL      |          NULL |
+---------+-----------+-----+-----------+---------------+
2 rows in set (0,00 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using  EditLine wrapper

Connection id:		3
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.11-0ubuntu5 (Ubuntu)
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			6 min 37 sec

Threads: 1  Questions: 24  Slow queries: 0  Opens: 111  Flush tables: 1  Open tables: 30  Queries per second avg: 0.060
--------------
[15 Apr 2016 19:26] Alex Verkuijl
After upgrade to MySQL 5.7.12, no change, so added 5.7.12 to version.

note: only tested on Windows 7 x64 after upgrade to 5.7.12.

---- dump of console ----

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> -- REMOVE OLD TABLES
mysql> DROP TABLE IF EXISTS test_machine;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS test_order;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> -- CREATE
mysql> CREATE TABLE IF NOT EXISTS `test_machine` (
    ->   `machineid` VARCHAR(32) NOT NULL,
    ->   `activeorderid` bigint(20) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`machineid`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> REPLACE INTO `test_machine` (`machineid`, `activeorderid`)
    ->     VALUES ('m1', NULL), ('m2', 2), ('m3', NULL), ('m4', NULL);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE IF NOT EXISTS `test_order` (
    ->   `orderid` bigint(20) unsigned NOT NULL,
    ->   `machineid` VARCHAR(32) DEFAULT NULL,
    ->   PRIMARY KEY (`orderid`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> INSERT IGNORE INTO `test_order` (`orderid`, `machineid`)
    ->     VALUES (1, 'm2'), (2, 'm2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> -- Query
mysql>
mysql> SELECT test_order.*,'|' as sep, test_machine.* FROM test_order LEFT JOIN test_machine ON (test_machine.machineid=test_order.machineid AND test_machine.activeorderid=test_order.orderid);
+---------+-----------+-----+-----------+---------------+
| orderid | machineid | sep | machineid | activeorderid |
+---------+-----------+-----+-----------+---------------+
|       1 | m2        | |   | NULL      |          NULL |
|       2 | m2        | |   | NULL      |          NULL |
+---------+-----------+-----+-----------+---------------+
2 rows in set (0.00 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.12, for Win64 (x86_64)

Connection id:          3
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.12-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3306
Uptime:                 1 min 5 sec

Threads: 1  Questions: 20  Slow queries: 0  Opens: 110  Flush tables: 1  Open tables: 103  Queries per second avg: 0.307
--------------

mysql>
[4 May 2016 19:39] Erlend Dahl
Fixed in 5.7.13 as a duplicate of

Bug#80526 LEFT OUTER JOIN returns incorrect results on the outer side

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.