Bug #80143 Wrong result with LEFT/RIGHT JOINs, subqueries, index

• Submitted: 25 Jan 2016 18:11
• Modified: 26 Jan 2016 1:31
• Reporter: Elena Stepanova
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[25 Jan 2016 18:11] Elena Stepanova

Description:
The same query was executed with and without an index on one of tables. Results are different, so obviously one of them is wrong. My calculations say that the first result is wrong, the second is correct. 

MySQL [test]> SELECT * FROM t1 LEFT JOIN ( 
    ->   SELECT f3 FROM t2 LEFT JOIN t3 ON (f3 = f2)
    -> ) AS sq ON (f1 != f3);
+------+------+
| f1   | f3   |
+------+------+
| a    | NULL |
| y    | NULL |
+------+------+
2 rows in set (0.00 sec)

MySQL [test]> 
MySQL [test]> ALTER TABLE t2 DROP KEY f2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> SELECT * FROM t1 LEFT JOIN ( 
    ->   SELECT f3 FROM t2 LEFT JOIN t3 ON (f3 = f2)
    -> ) AS sq ON (f1 != f3);
+------+------+
| f1   | f3   |
+------+------+
| y    | a    |
| y    | a    |
| a    | NULL |
+------+------+
3 rows in set (0.00 sec)

MySQL [test]> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.10-debug |
+--------------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS t1, t2, t3;

CREATE TABLE t1 (f1 VARCHAR(1));
INSERT INTO t1 VALUES ('a'),('y');

CREATE TABLE t2 (f2 VARCHAR(1), KEY(f2));
INSERT INTO t2 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('a');

CREATE TABLE t3 (f3 VARCHAR(1));
INSERT INTO t3 VALUES ('a'),('z');

SELECT f3 FROM t2 LEFT JOIN t3 ON (f3 = f2);

SELECT * FROM t1 LEFT JOIN ( 
  SELECT f3 FROM t2 LEFT JOIN t3 ON (f3 = f2)
) AS sq ON (f1 != f3);

ALTER TABLE t2 DROP KEY f2;

SELECT * FROM t1 LEFT JOIN ( 
  SELECT f3 FROM t2 LEFT JOIN t3 ON (f3 = f2)
) AS sq ON (f1 != f3);

DROP TABLE t1, t2, t3;

[26 Jan 2016 1:31] MySQL Verification Team

Thank you for the bug report. Repeatable with version reported 5.7.10 but not anymore with more recent source compiled version.

mysql> SELECT * FROM t1 LEFT JOIN (
    ->   SELECT f3 FROM t2 LEFT JOIN t3 ON (f3 = f2)
    -> ) AS sq ON (f1 != f3);
+------+------+
| f1   | f3   |
+------+------+
| a    | NULL |
| y    | NULL |
+------+------+
2 rows in set (0.03 sec)

mysql>
mysql> ALTER TABLE t2 DROP KEY f2;
Query OK, 0 rows affected (0.12 sec)

mysql>
mysql> SELECT * FROM t1 LEFT JOIN (
    ->   SELECT f3 FROM t2 LEFT JOIN t3 ON (f3 = f2)
    -> ) AS sq ON (f1 != f3);
+------+------+
| f1   | f3   |
+------+------+
| y    | a    |
| y    | a    |
| a    | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> show variables like "%version%";
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| innodb_version          | 5.7.10                               |
| protocol_version        | 10                                   |
| slave_type_conversions  |                                      |
| tls_version             | TLSv1,TLSv1.1                        |
| version                 | 5.7.10-debug                         |
| version_comment         | MySQL Community Server - Debug (GPL) |
| version_compile_machine | x86_64                               |
| version_compile_os      | Win64                                |
+-------------------------+--------------------------------------+
8 rows in set (0.05 sec)

------------------------------------------------------------------

mysql 5.7 > SELECT * FROM t1 LEFT JOIN (
    ->   SELECT f3 FROM t2 LEFT JOIN t3 ON (f3 = f2)
    -> ) AS sq ON (f1 != f3);
+------+------+
| f1   | f3   |
+------+------+
| a    | NULL |
| y    | a    |
| y    | a    |
+------+------+
3 rows in set (0.00 sec)

mysql 5.7 >
mysql 5.7 > ALTER TABLE t2 DROP KEY f2;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > SELECT * FROM t1 LEFT JOIN (
    ->   SELECT f3 FROM t2 LEFT JOIN t3 ON (f3 = f2)
    -> ) AS sq ON (f1 != f3);
+------+------+
| f1   | f3   |
+------+------+
| y    | a    |
| y    | a    |
| a    | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql 5.7 > show variables like "%version%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.7.12                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| tls_version             | 1                                     |
| version                 | 5.7.12-debug                          |
| version_comment         | Source distribution PULL: 2016-JAN-14 |
| version_compile_machine | x86_64                                |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
8 rows in set (0.01 sec)