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
