MySQL Bugs: #29604: Views sometimes return incorrect data with LEFT JOINs

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

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.

Schema and data for test case

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

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.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

A left join between two views could produce incorrect results.