Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with views , union in maria-5.3-mwl106
Sorry last comment was about another bug.
Re: <single-table> Wrong result with views , union in maria-5.3-mwl106
Another example:
CREATE TABLE t2 (f2 int, f3 int);
INSERT INTO t2 VALUES (0,143),(224,0);
CREATE TABLE t1 (f2 int, f3 int);
INSERT INTO t1 VALUES (0,0),(0,0);
CREATE ALGORITHM=MERGE VIEW v1 AS
( SELECT f2, f3 FROM t1 )
UNION
( SELECT f2, f3 FROM t2 )
;
SET SESSION optimizer_switch='derived_with_keys=on';
SELECT COUNT
FROM t1 JOIN v1 USING ( f3 ) WHERE v1.f3 = t1.f2;
SET SESSION optimizer_switch='derived_with_keys=off';
SELECT COUNT FROM t1 JOIN v1 USING ( f3 ) WHERE v1.f3 = t1.f2;
Still repeatable with:
bzr version-info
revision-id: psergey@askmonty.org-20110706063051-1x1x67sbg5q57sai
date: 2011-07-06 10:30:51 +0400
build-date: 2011-07-06 14:03:52 +0300
revno: 3085
branch-nick: maria-5.3
Re: <single-table> Wrong result with views , union in maria-5.3-mwl106
Last example may also be an instance of bug #806431
Re: <single-table> Wrong result with views , union in maria-5.3-mwl106
This bug can be demonstrated just with a materialized view:
MariaDB [test]> CREATE TABLE t1 (a int, b int);
Query OK, 0 rows affected (0.02 sec)
MariaDB [test]> INSERT INTO t1 VALUES (9,3), (2,5);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> CREATE TABLE t2 (a int, b int);
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> INSERT INTO t2 VALUES (9,3), (3,7), (9,1), (2,5), (2,4), (3,8);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [test]> CREATE VIEW v1(a,b) AS SELECT a, MAX(b) FROM t2 GROUP BY a;
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> SELECT * FROM v1;
----------+
| a | b |
----------+
| 2 | 5 |
| 3 | 8 |
| 9 | 3 |
----------+
3 rows in set (0.00 sec)
MariaDB [test]> SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1);
Empty set (0.00 sec)
Launchpad bug id: 793448
Re: Wrong result with views , union in maria-5.3-mwl106
Still reproducible on fedora 13
Linux eve 2.6.33.3-85.fc13.i686.PAE #1 SMP Thu May 6 18:27:11 UTC 2010 i686 i686 i386 GNU/Linux
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: igor@askmonty.org-20110606191935-bbf5xptvw0wuwcww
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