Bug #29604 Views sometimes return incorrect data with LEFT JOINs
Submitted: 6 Jul 2007 18:54 Modified: 27 Jul 2007 4:37
Reporter: Harrison Fisk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S1 (Critical)
Version:5.0.44 OS:Any
Assigned to: Igor Babaev CPU Architecture:Any
Tags: incorrect data, left join, Views

[6 Jul 2007 18:54] Harrison Fisk
Description:
If you have two views and left join them, you can sometimes get an empty set or other incorrect results.

For example:

mysql> select count(*) from v1;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from v2;
+----------+
| count(*) |
+----------+
|      132 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM v1 LEFT JOIN v2 ON v2.carrier = v1.carrier;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Obviously in this case, there should be results, since the LEFT JOIN should return at least as many rows as v1 has.

The exact data in the tables seems important for repeating this.  I will upload a script which contains the example views.

How to repeat:
1.  Run the attached script to create the tables and views.
2.  Execute the following commands:

select count(*) from v1;
select count(*) from v2;
SELECT count(*) FROM v1 LEFT JOIN v2 ON v2.carrier = v1.carrier;

Notice the last one returns incorrect results.

Suggested fix:
Make the views return the correct information.
[6 Jul 2007 18:55] Harrison Fisk
Schema and data for test case

Attachment: test_case.sql.gz (application/x-gzip, text), 3.87 KiB.

[12 Jul 2007 1:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30752

ChangeSet@1.2525, 2007-07-11 18:45:35-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #29604.
  A bug in the restore_prev_nj_state function allowed interleaving 
  inner tables of outer join operations with outer tables. With the
  current implementation of the nested loops algorithm it could lead
  to wrong result sets for queries with nested outer joins.
  Another bug in this procedure effectively blocked evaluation of some
  valid execution plans for queries with nested outer joins.
[20 Jul 2007 23:45] Bugs System
Pushed into 5.1.21-beta
[20 Jul 2007 23:49] Bugs System
Pushed into 5.0.48
[27 Jul 2007 4:37] Paul Dubois
Noted in 5.0.48, 5.1.21 changelogs.

A left join between two views could produce incorrect results.