[MDEV-3623] LP:806097 - Wrong result with DISTINCT +nested views after WL#106 Created: 2011-07-05  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: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug806097.xml    

 Description   

A DISTINCT query over nested views fails to return some of the distinct values. Not repeatable with maria-5.3 before WL#106. Not influenced by optimizer_switches.

Test case:

CREATE TABLE t1 (
f1 int(11),
f4 int(11)
);

INSERT INTO t1 VALUES (252,6),(232,0),(174,232),(251,73);

CREATE TABLE t2 (
f1 int(11)
);

INSERT INTO t2 VALUES (1),(2);

CREATE ALGORITHM=MERGE VIEW v2 AS SELECT t1.f1 FROM t2 , t1 ;
CREATE ALGORITHM=MERGE VIEW v5 AS SELECT v2.f1 FROM v2 , t2 ;

SELECT v5.f1 FROM t1 LEFT JOIN v5 ON t1.f4 = 0 returns

NULL
252
232
174
251
252
232
174
251
252
232
174
251
252
232
174
251
NULL
NULL

------

SELECT DISTINCT v5.f1 FROM t1 LEFT JOIN v5 ON t1.f4 = 0 returns

NULL
252

values such as 174 are missing.

explain:

1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Distinct
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Distinct

-----------------------------------------------------------------------+

bzr version-info
revision-id: <email address hidden>
date: 2011-07-05 15:28:15 +0200
build-date: 2011-07-05 21:01:11 +0300
revno: 3081
branch-nick: maria-5.3

 Comments   
Comment by Igor Babaev [ 2011-07-09 ]

Re: Wrong result with DISTINCT +nested views after WL#106
The following simple test case demonstrates the problem:

CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (252,6), (232,0), (174,232);

CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (232), (174);

CREATE TABLE t3 (c int);
INSERT INTO t3 VALUES (1), (2);

CREATE VIEW v1 AS SELECT t2.a FROM t3,t2;

MariaDB [test]> SELECT v1.a FROM t1 LEFT JOIN v1 ON t1.b = 0;
------

a

------

NULL
232
174
232
174
NULL

------
6 rows in set (0.00 sec)

MariaDB [test]> SELECT DISTINCT v1.a FROM t1 LEFT JOIN v1 ON t1.b = 0;
------

a

------

NULL
232

------
2 rows in set (0.00 sec)

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

Launchpad bug id: 806097

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