MySQL Bugs: #65936: wrong results on table left join view

Bug #65936	wrong results on table left join view
Submitted:	18 Jul 2012 12:49	Modified:	13 Jul 2015 16:51
Reporter:	Richard Kojedzinszky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.24, 5.5.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I assume mysql produces wrong results for the following commands. I suppose the second 'id' field should be NULL.

How to repeat:
mysql> create table a (id int);
Query OK, 0 rows affected (0.07 sec)

mysql> create table b (id int);
Query OK, 0 rows affected (0.09 sec)

mysql> create view v as select 1 as id, id as b_id from b;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into a(id) values (1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from a;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from b;
Empty set (0.01 sec)

mysql> select * from v;
Empty set (0.00 sec)

mysql> select * from a left join v on a.id = v.id;
+------+----+------+
| id   | id | b_id |
+------+----+------+
|    1 |  1 | NULL |
+------+----+------+
1 row in set (0.00 sec)

Thank you for the bug report. Verified with 5.5.26 on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.26-debug-log Source distribution

Copyright (c) 2000, 2011, 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> create table a (id int);
Query OK, 0 rows affected (0.14 sec)

mysql> create table b (id int);
Query OK, 0 rows affected (0.22 sec)

mysql> create view v as select 1 as id, id as b_id from b;
Query OK, 0 rows affected (0.28 sec)

mysql> insert into a(id) values (1);
Query OK, 1 row affected (0.07 sec)

mysql> select * from a;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> select * from b;
Empty set (0.00 sec)

mysql> select * from v;
Empty set (0.00 sec)

mysql> select * from a left join v on a.id = v.id;
+------+----+------+
| id   | id | b_id |
+------+----+------+
|    1 |  1 | NULL |
+------+----+------+
1 row in set (0.01 sec)

mysql> select * from a join v on a.id = v.id;
Empty set (0.03 sec)

mysql> explain select * from a left join v on a.id = v.id;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.13 sec)

Still reproducible in 5.6.12 ...

The test case is a bit confusing though, it looks as if the duplicate column name may be part of the story, but it actually isn't.

It is as simple as expressions in the view definitions not depending on any actual row values always get evaluated and passed on as result value.

So the view could be simplified as

  CREATE VIEW w AS SELECT 1 AS foo, b.id FROM b;

and the actual query as

  SELECT foo FROM a LEFT JOIN w ON a.id = w.id;

and the result would still be

  +-----+
  | foo |
  +-----+
  |   1 |
  +-----+

instead of the expected foo=NULL

PS: FWIW postgresql correctly returns the expected NULL result

Posted by developer:
 
another duplicate: bug#17023060

Noted in 5.7.8, 5.8.0 changelogs.

Incorrect results could be produced tor views and derived tables on
the inner side of an outer join and from which non-nullable
expressions such as literals were selected.