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
Triage: Needs Triage: D2 (Serious)

[18 Jul 2012 12:49] Richard Kojedzinszky
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)
[18 Jul 2012 14:24] Valeriy Kravchuk
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)
[18 Jul 2013 14:38] Hartmut Holzgraefe
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
[23 Mar 2015 13:39] Guilhem Bichot
Posted by developer:
 
another duplicate: bug#17023060
[13 Jul 2015 16:51] Paul Dubois
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.