Wrong result with DISTINCT +nested views after WL#106
| Affects | Status | Importance | Assigned to | Milestone | |
|---|---|---|---|---|---|
| MariaDB |
Fix Released
|
High
|
Igor Babaev | ||
Bug 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)
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
| Changed in maria: | |
| milestone: | none → 5.3 |
| assignee: | nobody → Igor Babaev (igorb-seattle) |
| Changed in maria: | |
| status: | New → Confirmed |
| importance: | Undecided → High |
| Igor Babaev (igorb-seattle) wrote | #1 |
| Changed in maria: | |
| status: | Confirmed → In Progress |
| Changed in maria: | |
| status: | In Progress → Fix Committed |
| Changed in maria: | |
| status: | Fix Committed → Fix Released |
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)