<single-table> Wrong result with views , union in maria-5.3-mwl106
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug 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
Related branches
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → Critical |
status: | Confirmed → In Progress |
summary: |
- Wrong result with views , union in maria-5.3-mwl106 + <single-table> Wrong result with views , union in maria-5.3-mwl106 |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Still reproducible on fedora 13 3-85.fc13. i686.PAE #1 SMP Thu May 6 18:27:11 UTC 2010 i686 i686 i386 GNU/Linux
Linux eve 2.6.33.
server compiled with ./BUILD/ compile- pentium- debug-max- no-ndb
server started with
MTR_VERSION=1 perl mysql-test-run.pl --start-and-exit 1st
bzr version-info
revision-id: <email address hidden>
date: 2011-06-06 12:19:35 -0700
build-date: 2011-06-07 07:58:42 +0300
revno: 3028
branch-nick: maria-5.3-mwl106