Bug #5511 Query with a nested outer join using a view returns a wrong result

Submitted: 10 Sep 2004 5:49 Modified: 11 Sep 2004 16:16
Reporter: Igor Babaev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 OS:Any (all)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[10 Sep 2004 5:49] Igor Babaev
Description:
If a query contains a nested outer join that refers to a view with a where condition then the query in many cases returns a wrong result. 

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

mysql> insert into t1 values (0), (1), (2), (3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 select a from t1 where a > 1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create view v1 as select a from t1 where a > 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select a from t1;
+------+
| a    |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.00 sec)

mysql> select a from t2;
+------+
| a    |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql> select a from v1;
+------+
| a    |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t1 left join (t2 as t, t2) on t2.a=t1.a;
+------+------+------+
| a    | a    | a    |
+------+------+------+
|    0 | NULL | NULL |
|    1 | NULL | NULL |
|    2 |    2 |    2 |
|    2 |    3 |    2 |
|    3 |    2 |    3 |
|    3 |    3 |    3 |
+------+------+------+
6 rows in set (0.00 sec)

mysql> select * from t1 left join (t2 as t, v1) on v1.a=t1.a;
+------+------+------+
| a    | a    | a    |
+------+------+------+
|    2 |    2 |    2 |
|    2 |    3 |    2 |
|    3 |    2 |    3 |
|    3 |    3 |    3 |
+------+------+------+
4 rows in set (0.00 sec)

Suggested fix:
in the function st_table_list::setup_ancestor
[10 Sep 2004 19:41] Oleksandr Byelkin
ChangeSet 
  1.1753 04/09/10 22:39:04 bell@sanja.is.com.ua +3 -0 
  view with WHERE in nested join (BUG#5511)
[11 Sep 2004 16:16] Oleksandr Byelkin
Thank you for bugreport! Bug is fixed, patch is pushed into source repository.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.