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