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

[26 Feb 2016 10:26] Attila Rózsár
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;
[26 Feb 2016 10:39] 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.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 >
[27 Apr 2016 15:17] Paul DuBois
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.
[4 May 2016 19:41] Erlend Dahl
Bug#81034
Bug#81026 	
Bug#80984
Bug#80805
Bug#80280
Bug#80038

were marked as duplicates.

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

Page generated in 0.046 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.