Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
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