| Bug #81034 | Incorrect query results using left join against derived table in mysql 5.7.11.0 | ||
|---|---|---|---|
| Submitted: | 11 Apr 2016 18:55 | Modified: | 4 May 2016 19:42 |
| Reporter: | Michael Hogue | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.7.11.0/5.7.12 | OS: | Windows (Windows Server 2008 R2 Standard) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[11 Apr 2016 20:27]
MySQL Verification Team
Not sure if duplicate of http://bugs.mysql.com/bug.php?id=81026 also: mysql 5.6 > SELECT -> s.adslot, -> lid.ionumber1, -> lid.ionumber2, -> lid.ioattribute -> FROM mysql57issue_adslot s -> LEFT JOIN -> ( -> SELECT -> lid.adslot, -> i.ionumber as ionumber1, -> lid.ionumber as ionumber2, -> i.ioattribute -> FROM mysql57issue_adiolineitemdrop lid -> JOIN mysql57issue_adio i USING (ionumber) -> ) AS lid USING (adslot); +--------+-----------+-----------+-------------+ | adslot | ionumber1 | ionumber2 | ioattribute | +--------+-----------+-----------+-------------+ | 1 | 01602 | 01602 | BOB | | 2 | NULL | NULL | NULL | | 3 | 01602 | 01602 | BOB | +--------+-----------+-----------+-------------+ 3 rows in set (0.00 sec) mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%"; +-------------------------+---------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------+ | innodb_version | 5.6.31 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.31 | | version_comment | Source distribution PULL: 2016-APR-07 | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+---------------------------------------+ 7 rows in set (0.00 sec) ------------------------------------------------------------------- mysql 5.7 > -- This is the original query mysql 5.7 > SELECT -> s.adslot, -> lid.ionumber1, -> lid.ionumber2, -> lid.ioattribute -> FROM mysql57issue_adslot s -> LEFT JOIN -> ( -> SELECT -> lid.adslot, -> i.ionumber as ionumber1, -> lid.ionumber as ionumber2, -> i.ioattribute -> FROM mysql57issue_adiolineitemdrop lid -> JOIN mysql57issue_adio i USING (ionumber) -> ) AS lid USING (adslot); +--------+-----------+-----------+-------------+ | adslot | ionumber1 | ionumber2 | ioattribute | +--------+-----------+-----------+-------------+ | 1 | 01602 | 01602 | BOB | | 2 | NULL | NULL | NULL | | 3 | | 01602 | | +--------+-----------+-----------+-------------+ 3 rows in set (0.00 sec) mysql 5.7 > show variables like "%version%"; +-------------------------+---------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------+ | innodb_version | 5.7.13 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | 1 | | version | 5.7.13 | | version_comment | Source distribution PULL: 2016-APR-07 | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+---------------------------------------+ 8 rows in set (0.00 sec)
[11 Apr 2016 20:34]
MySQL Verification Team
mysql> -- This is the original query
mysql> SELECT
-> s.adslot,
-> lid.ionumber1,
-> lid.ionumber2,
-> lid.ioattribute
-> FROM mysql57issue_adslot s
-> LEFT JOIN
-> (
-> SELECT
-> lid.adslot,
-> i.ionumber as ionumber1,
-> lid.ionumber as ionumber2,
-> i.ioattribute
-> FROM mysql57issue_adiolineitemdrop lid
-> JOIN mysql57issue_adio i USING (ionumber)
-> ) AS lid USING (adslot);
+--------+-----------+-----------+-------------+
| adslot | ionumber1 | ionumber2 | ioattribute |
+--------+-----------+-----------+-------------+
| 1 | 01602 | 01602 | BOB |
| 2 | NULL | NULL | NULL |
| 3 | 01602 | 01602 | BOB |
+--------+-----------+-----------+-------------+
3 rows in set (0.05 sec)
mysql> SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.9 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.9 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+------------------------------+
7 rows in set (0.08 sec)
mysql>
[12 Apr 2016 0:05]
MySQL Verification Team
Thank you for the bug report.
mysql> -- This is the original query
mysql> SELECT
-> s.adslot,
-> lid.ionumber1,
-> lid.ionumber2,
-> lid.ioattribute
-> FROM mysql57issue_adslot s
-> LEFT JOIN
-> (
-> SELECT
-> lid.adslot,
-> i.ionumber as ionumber1,
-> lid.ionumber as ionumber2,
-> i.ioattribute
-> FROM mysql57issue_adiolineitemdrop lid
-> JOIN mysql57issue_adio i USING (ionumber)
-> ) AS lid USING (adslot);
+--------+-----------+-----------+-------------+
| adslot | ionumber1 | ionumber2 | ioattribute |
+--------+-----------+-----------+-------------+
| 1 | 01602 | 01602 | BOB |
| 2 | NULL | NULL | NULL |
| 3 | | 01602 | |
+--------+-----------+-----------+-------------+
3 rows in set (0.00 sec)
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.12 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.12 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)
mysql>
[14 Apr 2016 13:51]
Simon Smithson
I am seeing a similar issue with left joins across multiple tables and can provide an more complicated example if required. The issue I am seeing is present in 5.7.12 however 5.7.9 exhibits the correct behaviour.
[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: Executing left join against derived table, and receiving incorrect results in output. Exact SQL statements provided in how to repeat. Expected results: adslot ionumber1 ionumber2 ioattribute 1 01602 01602 BOB 2 3 01602 01602 BOB Actual results: adslot ionumber1 ionumber2 ioattribute 1 01602 01602 BOB 2 3 01602 [The associated data pulled from mysql57issue_adio from the inner query seems to vanish in the outer select for the 3rd row (for adslot 3). Instead of getting accurate values, we get an empty string. A query that looks like this has been working for many many years across various MySql versions (most recently 5.6.27). Upon upgrading to 5.7.11, the query began to fail as described above (incorrectly returning empty string for several columns). I looked through the bug reports, and it's hard to tell if this is a duplicate or not. There are certainly other reports of left join issues such as 80805, 80526, and 81026, although it's hard to tell if those are really the same underlying issue or not.) This was executed on a standard download of the 5.7.12 community edition of MySQL Server (mysql-5.7.11-winx64.zip). How to repeat: drop table if exists mysql57issue_adslot; CREATE TABLE `mysql57issue_adslot` ( `adslot` varchar(5) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into mysql57issue_adslot(adslot) values ('1'), ('2'), ('3'); -- Create the first table drop table if exists mysql57issue_adiolineitemdrop; CREATE TABLE `mysql57issue_adiolineitemdrop` ( `ionumber` varchar(20) NOT NULL, `adslot` varchar(5) NOT NULL ) ENGINE=InnoDB CHARSET=latin1; insert into mysql57issue_adiolineitemdrop(ionumber, adslot) values ('01602', 1), ('01602', 3); drop table if exists mysql57issue_adio; CREATE TABLE `mysql57issue_adio` ( `ionumber` varchar(20) NOT NULL, `ioattribute` varchar(5) NOT NULL, PRIMARY KEY (`ionumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into mysql57issue_adio values ('01602', 'BOB'), ('01603', 'SALLY'); -- This is the original query SELECT s.adslot, lid.ionumber1, lid.ionumber2, lid.ioattribute FROM mysql57issue_adslot s LEFT JOIN ( SELECT lid.adslot, i.ionumber as ionumber1, lid.ionumber as ionumber2, i.ioattribute FROM mysql57issue_adiolineitemdrop lid JOIN mysql57issue_adio i USING (ionumber) ) AS lid USING (adslot);