MySQL Bugs: #77707: Right outer join return wrong result when literal expression involved.

Bug #77707	Right outer join return wrong result when literal expression involved.
Submitted:	13 Jul 2015 18:30	Modified:	15 Jul 2015 14:47
Reporter:	Tracy Y	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	MySQL 5.7.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Right outer join return wrong result when literal expression involved.

select t1.c1, t1.c2, t1.c3 from
(select rnum as c1, 1 as c3, cchar as c2 from tchar where rnum in (3)) t1
right outer join
(select rnum as c1, 2 as c3, cchar as c2 from tchar where rnum in (3, 4)) t2
on t1.c1 = t2.c1

In 5.7.7, the second column of row 2 return 1
In 5.6 and 5.5, the second column of row 2 return expected <null>

How to repeat:
CREATE TABLE tchar
(
   rnum int NOT NULL,
   cchar char(32)
)
;

INSERT INTO tchar (rnum,cchar) VALUES (0,null);
INSERT INTO tchar (rnum,cchar) VALUES (1,'                                ');
INSERT INTO tchar (rnum,cchar) VALUES (2,'                                ');
INSERT INTO tchar (rnum,cchar) VALUES (3,'BB                              ');
INSERT INTO tchar (rnum,cchar) VALUES (4,'EE                              ');
INSERT INTO tchar (rnum,cchar) VALUES (5,'FF                              ');

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 2
Server version: 5.7.8-rc Source distribution PULL: 2015/06/28

Copyright (c) 2000, 2015, 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 > CREATE DATABASE td;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > USE td
Database changed
mysql 5.7 > CREATE TABLE tchar
    -> (
    ->    rnum int NOT NULL,
    ->    cchar char(32)
    -> )
    -> ;
Query OK, 0 rows affected (0.30 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO tchar (rnum,cchar) VALUES (0,null);
Query OK, 1 row affected (0.05 sec)
<CUT>

mysql 5.7 > select t1.c1, t1.c2, t1.c3 from
    -> (select rnum as c1, 1 as c3, cchar as c2 from tchar where rnum in (3)) t1
    -> right outer join
    -> (select rnum as c1, 2 as c3, cchar as c2 from tchar where rnum in (3, 4)) t2
    -> on t1.c1 = t2.c1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    3 | BB   |    1 |
| NULL | NULL |    1 |
+------+------+------+
2 rows in set (0.02 sec)

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 2
Server version: 5.6.27-debug Source distribution PULL: 2015/06/28

Copyright (c) 2000, 2015, 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 > CREATE DATABASE td;
Query OK, 1 row affected (0.00 sec)

mysql 5.6 > USE td
Database changed
mysql 5.6 > CREATE TABLE tchar
    -> (
    ->    rnum int NOT NULL,
    ->    cchar char(32)
    -> )
    -> ;
Query OK, 0 rows affected (0.31 sec)

mysql 5.6 > INSERT INTO tchar (rnum,cchar) VALUES (0,null);
Query OK, 1 row affected (0.03 sec)
<CUT>

mysql 5.6 > select t1.c1, t1.c2, t1.c3 from
    -> (select rnum as c1, 1 as c3, cchar as c2 from tchar where rnum in (3)) t1
    -> right outer join
    -> (select rnum as c1, 2 as c3, cchar as c2 from tchar where rnum in (3, 4)) t2
    -> on t1.c1 = t2.c1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    3 | BB   |    1 |
| NULL | NULL | NULL |
+------+------+------+
2 rows in set (0.02 sec)

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.45-log Source distribution PULL: 2015/06/05

Copyright (c) 2000, 2015, 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.5 > CREATE DATABASE td;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 > USE td
Database changed
mysql 5.5 > CREATE TABLE tchar
    -> (
    ->    rnum int NOT NULL,
    ->    cchar char(32)
    -> )
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql 5.5 > INSERT INTO tchar (rnum,cchar) VALUES (0,null);
Query OK, 1 row affected (0.06 sec)

<CUT>

mysql 5.5 > select t1.c1, t1.c2, t1.c3 from
    -> (select rnum as c1, 1 as c3, cchar as c2 from tchar where rnum in (3)) t1
    -> right outer join
    -> (select rnum as c1, 2 as c3, cchar as c2 from tchar where rnum in (3, 4)) t2
    -> on t1.c1 = t2.c1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    3 | BB   |    1 |
| NULL | NULL | NULL |
+------+------+------+
2 rows in set (0.00 sec)

Posted by developer:
 
git bisect says it is fixed by:

commit 855ef39c280bb20b8944111378b99c7ac463033e
Author: Roy Lyseng <roy.lyseng@oracle.com>
Date:   Mon Jun 29 14:40:51 2015 +0200

    Bug#14358878: Wrong results on table left join view
    Bug#15936817: Table left join view, unmatched rows problem where view has an if
    Bug#15967464: View evaluation incorrect when joining to view with literal
    Bug#20708288: Literal selected from derived table mentioned in outer join ..null
    Bug#20841369: Left join to view with <> test causing too many results