Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
-
None
-
None
Description
Not repeatable in maria-5.3. If UNION is used inside a VIEW inside a subquery, rows that match the WHERE predicate are not returned:
CREATE TABLE t1 ( f1 int, f2 int) ;
INSERT INTO t1 VALUES (9,3), (2,5);
CREATE OR REPLACE VIEW v1 AS SELECT 9 , 3 UNION SELECT 2 , 5 ;
SELECT f1 FROM t1 WHERE ( f1 , f2 ) IN ( SELECT * FROM v1 );
In maria-5.3-mwl106, this query returns no rows, even though there are 2 rows for which the IN predicate is TRUE.
Explain:
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | <derived3> | index_subquery | key0 | key0 | 16 | func,func | 2 | Using where |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 4 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union3,4> | ALL | NULL | NULL | NULL | NULL | NULL | Â |
note that the NULL in the final row of the ID column of the EXPLAIN causes the entire table to become misaligned.
bzr version-info:
revision-id: <email address hidden>
date: 2011-06-05 21:54:25 -0700
build-date: 2011-06-06 13:21:13 +0300
revno: 3027
branch-nick: maria-5.3-mwl106