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