MySQL Bugs: #80526: LEFT OUTER JOIN returns incorrect results on the outer side

Bug #80526	LEFT OUTER JOIN returns incorrect results on the outer side
Submitted:	26 Feb 2016 10:26	Modified:	27 Apr 2016 15:17
Reporter:	Attila Rózsár	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.11	OS:	Windows (Win7, 64bit)
Assigned to:		CPU Architecture:	Any

Description:
Incorrect results returned when executing query
SELECT ta.a1, tb.b1, tb.b2 FROM ta LEFT OUTER JOIN tb ON ((ta.a2 = tb.b1) AND (tb.b3 = 0));

Sample database is in section "How to repeat". 

Incorrect results:
+------+------+------+
| a1   | b1   | b2   |
+------+------+------+
| row1 | NULL | NULL |
| row2 |    0 |      |
+------+------+------+

Expected results:
+------+------+------+
| a1   | b1   | b2   |
+------+------+------+
| row1 | NULL | NULL |
| row2 | NULL | NULL |
+------+------+------+

MySQL 5.7 command line client used on the target system.

How to repeat:
CREATE DATABASE IF NOT EXISTS dbtest /*!40100 DEFAULT CHARACTER SET utf8 */;
USE dbtest;

CREATE TABLE IF NOT EXISTS `ta` (
  `a1` varchar(1024) NOT NULL,
  `a2` int(11) NOT NULL,
  KEY `user_id` (`a2`)
);

INSERT INTO `ta` (`a1`, `a2`) VALUES
	('row1', 4),
	('row2', 4);

CREATE TABLE IF NOT EXISTS `tb` (
  `b1` int(11) NOT NULL,
  `b2` varchar(1024) NOT NULL,
  `b3` int(11) NOT NULL,
  PRIMARY KEY (`b1`)
);

INSERT INTO `tb` (`b1`, `b2`, `b3`) VALUES
	(1, 'text1', 0),
	(2, 'text2', 0),
	(3, 'text3', 1),
	(4, 'text4', 1);

SELECT ta.a1, tb.b1, tb.b2 FROM ta LEFT OUTER JOIN tb ON ((ta.a2 = tb.b1) AND (tb.b3 = 0));

DROP DATABASE dbtest;

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.12 Source distribution PULL: 2016-FEB-19

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 `ta` (
    ->   `a1` varchar(1024) NOT NULL,
    ->   `a2` int(11) NOT NULL,
    ->   KEY `user_id` (`a2`)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO `ta` (`a1`, `a2`) VALUES
    ->  ('row1', 4),
    ->  ('row2', 4);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > CREATE TABLE IF NOT EXISTS `tb` (
    ->   `b1` int(11) NOT NULL,
    ->   `b2` varchar(1024) NOT NULL,
    ->   `b3` int(11) NOT NULL,
    ->   PRIMARY KEY (`b1`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO `tb` (`b1`, `b2`, `b3`) VALUES
    ->  (1, 'text1', 0),
    ->  (2, 'text2', 0),
    ->  (3, 'text3', 1),
    ->  (4, 'text4', 1);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > SELECT ta.a1, tb.b1, tb.b2 FROM ta LEFT OUTER JOIN tb ON ((ta.a2 = tb.b1) AND (tb.b3 = 0));
+------+------+------+
| a1   | b1   | b2   |
+------+------+------+
| row1 | NULL | NULL |
| row2 |    0 |      |
+------+------+------+
2 rows in set (0.02 sec)

mysql 5.7 > exit
Bye

C:\dbs>net start mysqld56
The MySQLD56 service is starting..
The MySQLD56 service was started successfully.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.30 Source distribution PULL: 2016-FEB-19

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.6 > use test
Database changed
mysql 5.6 > CREATE TABLE IF NOT EXISTS `ta` (
    ->   `a1` varchar(1024) NOT NULL,
    ->   `a2` int(11) NOT NULL,
    ->   KEY `user_id` (`a2`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql 5.6 >
mysql 5.6 > INSERT INTO `ta` (`a1`, `a2`) VALUES
    ->  ('row1', 4),
    ->  ('row2', 4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.6 >
mysql 5.6 > CREATE TABLE IF NOT EXISTS `tb` (
    ->   `b1` int(11) NOT NULL,
    ->   `b2` varchar(1024) NOT NULL,
    ->   `b3` int(11) NOT NULL,
    ->   PRIMARY KEY (`b1`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 5.6 >
mysql 5.6 > INSERT INTO `tb` (`b1`, `b2`, `b3`) VALUES
    ->  (1, 'text1', 0),
    ->  (2, 'text2', 0),
    ->  (3, 'text3', 1),
    ->  (4, 'text4', 1);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.6 >
mysql 5.6 > SELECT ta.a1, tb.b1, tb.b2 FROM ta LEFT OUTER JOIN tb ON ((ta.a2 = tb.b1) AND (tb.b3 = 0));
+------+------+------+
| a1   | b1   | b2   |
+------+------+------+
| row1 | NULL | NULL |
| row2 | NULL | NULL |
+------+------+------+
2 rows in set (0.01 sec)

mysql 5.6 >

Posted by developer:
 
Noted in 5.7.13 changelog.

The fix for Bug #79194 did not cover the eq_ref access method, with
the result that left joins could return incorrect results.
      
NOTE:
This bug fix has a very small negative performance effect such that
it fails to cache an eq_ref-accessed row that is on the inner side of
an outer join. Regular inner joins are not affected.

Bug#81034
Bug#81026 	
Bug#80984
Bug#80805
Bug#80280
Bug#80038

were marked as duplicates.