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