Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
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.