Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result + view + outer join + correlated subquery
The bug remains unresolved in MariaDB 5.2.
The fix can be easily back-ported though.
Launchpad bug id: 823237
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 );
---
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------3 rows in set, 2 warnings (0.00 sec)
MariaDB [test]> show warnings;
------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------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 );
---
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------4 rows in set, 2 warnings (0.01 sec)
MariaDB [test]> show warnings;
------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------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.