[MDEV-3618] LP:823237 - Wrong result + view + outer join + correlated subquery Created: 2011-08-09  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug823237.xml    

 Description   

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.

 Comments   
Comment by Igor Babaev [ 2012-10-02 ]

Re: Wrong result + view + outer join + correlated subquery
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))

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)

We see that outer join in the subquery was converted into an inner join. This was incorrect.

After the fix EXPLAIN EXTENDED 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 t2 system NULL NULL NULL NULL 1 100.00  
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found

--------------------------------------------------------------------------------------------
4 rows in set, 2 warnings (0.01 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` left join `test`.`t1` on(0) where (7 > `test`.`t4`.`b`)))

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)

The test case from this report will be added in to the regression test suite in mariadb-5.3.10.

Comment by Rasmus Johansson (Inactive) [ 2012-10-02 ]

Launchpad bug id: 823237

Comment by Igor Babaev [ 2012-10-02 ]

Re: Wrong result + view + outer join + correlated subquery
The bug remains unresolved in MariaDB 5.2.
The fix can be easily back-ported though.

Generated at Thu Feb 08 06:49:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.