Wrong result with correlated query in select list + aggregate
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
The following query:
SELECT ( SELECT MIN( t1.a ) FROM t1, t2 WHERE t2.a = t3.t1 ) FROM t3;
returns
| 1 |
| 1 |
even though the correct result should be
| NULL |
| NULL |
as there are no rows for which t2.a = t3.t1 is true
explain:
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | DEPENDENT SUBQUERY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DEPENDENT SUBQUERY | t2 | system | NULL | NULL | NULL | NULL | 1 | |
repeatable in maria-5.3, maria-5.2, mysql-5.5
test case:
CREATE TABLE t1 ( a int NOT NULL, PRIMARY KEY (a)) engine=myisam;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 ( a int NOT NULL ) engine=myisam;
INSERT INTO t2 VALUES (10);
CREATE TABLE t3 ( a int NOT NULL , t1 int) engine=myisam;
INSERT INTO t3 VALUES (19,1),(20,5);
SELECT ( SELECT MIN( t1.a ) FROM t1, t2 WHERE t2.a = t3.t1 ) FROM t3;
Changed in maria: | |
assignee: | nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin) |
Changed in maria: | |
importance: | Undecided → High |
status: | New → In Progress |
Changed in maria: | |
status: | In Progress → Confirmed |
milestone: | none → 5.2 |
Changed in maria: | |
assignee: | Oleksandr "Sanja" Byelkin (sanja-byelkin) → Igor Babaev (igorb-seattle) |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |