Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 806097
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;
------
------
------
6 rows in set (0.00 sec)
MariaDB [test]> SELECT DISTINCT v1.a FROM t1 LEFT JOIN v1 ON t1.b = 0;
------
------
------
2 rows in set (0.00 sec)