HAVING not observed with subquery in maria-5.3
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Medium
|
Igor Babaev |
Bug Description
Not repeatable on maria-5.2, mysql-5.5. HAVING is not observed in the query below, so the result set contains 1 row that does not match the having.
Explain:
| 1 | PRIMARY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | SUBQUERY | t3 | system | NULL | NULL | NULL | NULL | 1 | |
test case:
CREATE TABLE t1 ( f2 int, f3 int , KEY (f3) ) ;
INSERT IGNORE INTO t1 VALUES (3,1);
CREATE TABLE t2 ( f1 int NOT NULL ) ;
INSERT IGNORE INTO t2 VALUES (29);
CREATE TABLE t3 ( f3 int) ;
INSERT INTO t3 VALUES (NULL);
SELECT MAX( t1.f3 ) AS field1
FROM t1 JOIN t2 ON t2.f1 != 0
WHERE ( SELECT f3 FROM t3 )
HAVING field1 <> 6 ;
should return an empty result, but instead returns NULL
bzr version-info:
revision-id: <email address hidden>
date: 2011-05-31 12:16:02 +0200
build-date: 2011-06-02 12:42:10 +0300
revno: 3016
branch-nick: maria-5.3
Changed in maria: | |
milestone: | none → 5.3 |
Changed in maria: | |
importance: | Undecided → High |
Changed in maria: | |
importance: | High → Medium |
status: | Confirmed → In Progress |
assignee: | Timour Katchaounov (timour) → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
A bit more obvious example. The subquery in the WHERE clause produces a single NULL,
and is essential for the wrong result. Therefore this bug looks like an edge case.
SELECT MAX( t1.f3 ) AS field1
FROM t1 JOIN t2 ON t2.f1 != 0
WHERE ( SELECT f3 FROM t3 )
HAVING field1 is not null;
+--------+
| field1 |
+--------+
| NULL |
+--------+