Wrong result + view + outer join + correlated subquery

[Philip Stoev]

This query:

SELECT * FROM t3 , v4
WHERE v4.c <= (
        SELECT t2.e
        FROM t2
        LEFT JOIN t1
        ON ( t1.a = t2.d )
        WHERE t2.b > v4.b
);

returns no rows when v4 is a view, but returns rows when v4 is a base table. PostgreSQL reports that the correct result is to return rows.

Test case:

CREATE TABLE t1 ( a int ) ;

CREATE TABLE t2 ( b int, d int, e int);
INSERT INTO t2 VALUES (7,8,0);

CREATE TABLE t3 ( c int) ;
INSERT INTO t3 VALUES (0);

CREATE TABLE t4 ( a int , b int, c int) ;
INSERT INTO t4 VALUES (93,1,0),(95,NULL,0);

CREATE VIEW v4 AS SELECT * FROM t4;

SELECT * FROM t3 , v4
WHERE v4.c <= (
        SELECT t2.e
        FROM t2
        LEFT JOIN t1
        ON ( t1.a = t2.d )
        WHERE t2.b > v4.b
);

explain in 5.3 with a view:

MariaDB [test]> explain SELECT * FROM t3 , v4 WHERE v4.c <= ( SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) WHERE t2.b > v4.b );
+----+--------------------+-------+--------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t4 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+--------------------+-------+--------+---------------+------+---------+------+------+-----------------------------------------------------+

explain in 5.3 with a table:
MariaDB [test]> explain SELECT * FROM t3 , t4 WHERE t4.c <= ( SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) WHERE t2.b > t4.b );

+----+--------------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+------+---------+------+------+---------------------+
| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t4 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DEPENDENT SUBQUERY | t1 | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+----+--------------------+-------+--------+---------------+------+---------+------+------+---------------------+

Repeatable in maria-5.3, maria-5.2, mysql-5.5.

[Igor Babaev (igorb-seattle)]

This bug was fixed by the patch for LP bug #823189 (rev. 3150):
The method Item_ref::not_null_tables() returned incorrect bitmap for outer references to view columns. This could cause an invalid conversion of an outer join into an inner join that could lead to a wrong result set for a query with a correlated subquery over an outer join whose where condition had an outer reference to a view.

This is exactly what happened for the query from the test case for bug #823237.

Before the fix for bug #823189 EXPLAIN extended for the query returned:

MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t3 , v4 WHERE v4.c <= ( SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) WHERE t2.b > v4.b );
+----+--------------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | PRIMARY | t4 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+--------------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)

MariaDB [test]> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'v4.b' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t3` join `test`.`t4` where (`test`.`t4`.`c` <= (select 0 from `test`.`t2` join `test`.`t1` where 0)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------...

[Igor Babaev (igorb-seattle)]

The bug remains unresolved in MariaDB 5.2.
The fix can be easily back-ported though.