<single-table> Wrong result with views , union in maria-5.3-mwl106

[Philip Stoev]

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

[Philip Stoev (pstoev-askmonty)]

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: <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

[Philip Stoev (pstoev-askmonty)]

Sorry last comment was about another bug.

[Philip Stoev (pstoev-askmonty)]

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: <email address hidden>
date: 2011-07-06 10:30:51 +0400
build-date: 2011-07-06 14:03:52 +0300
revno: 3085
branch-nick: maria-5.3

[Philip Stoev (pstoev-askmonty)]

Last example may also be an instance of bug #806431

[Igor Babaev (igorb-seattle)]

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)