[MDEV-3439] LP:793448 - <single-table> Wrong result with views , union in maria-5.3-mwl106 Created: 2011-06-06  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug793448.xml    

 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

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-06-07 ]

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

Comment by Philip Stoev (Inactive) [ 2011-06-07 ]

Re: Wrong result with views , union in maria-5.3-mwl106
Sorry last comment was about another bug.

Comment by Philip Stoev (Inactive) [ 2011-07-06 ]

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

Comment by Philip Stoev (Inactive) [ 2011-07-06 ]

Re: <single-table> Wrong result with views , union in maria-5.3-mwl106
Last example may also be an instance of bug #806431

Comment by Igor Babaev [ 2011-07-18 ]

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)

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 793448

Generated at Thu Feb 08 06:48:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.