Wrong result with outer join + NOT IN subquery
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Medium
|
Timour Katchaounov |
Bug Description
Repeatable in maria-5.3, maria-5.2, maria-5.1, mysql-5.5 . Not repeatable in mysql-5.1. Not influenced by optimizer switches.
The following query:
SELECT t2.b , t1.c
FROM t2 LEFT JOIN t1 ON t2.a != 0
WHERE ( t2.b , t1.c ) NOT IN ( SELECT 2 , 9 );
does not return rows for which the WHERE predicate is true. It returns an empty result in maria and mysql-5.5 and in mysql-5.1 it returns:
b c
9 NULL
9 NULL
test case:
CREATE TABLE t1 (c int) ;
CREATE TABLE t2 (a int, b int) ;
INSERT INTO t2 VALUES (20,9),(20,9);
SELECT t2.b , t1.c
FROM t2 LEFT JOIN t1 ON t2.a != 0
WHERE ( t2.b , t1.c ) NOT IN ( SELECT 2 , 9 );
explain:
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
optimizer switch:
index_merge=
revision-id: <email address hidden>
date: 2011-07-20 11:56:28 +0400
build-date: 2011-07-20 15:37:47 +0300
revno: 3123
branch-nick: maria-5.3
test case:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c int) ;
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (a int, b int) ;
INSERT INTO t2 VALUES (20,9),(20,9);
SELECT t2.b , t1.c
FROM t2 LEFT JOIN t1 ON t2.a != 0
WHERE ( t2.b , t1.c ) NOT IN ( SELECT 2 , 9 );
Related branches
Changed in maria: | |
assignee: | nobody → Timour Katchaounov (timour) |
Changed in maria: | |
status: | New → Confirmed |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
This a very old bug that is present in most mysql/mariadb versions.