| 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: | |
| 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: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
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);