Wrong result with in_to_exists=on in maria-5.3-mwl89
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Sergey Petrunia |
Bug Description
Not repeatable with maria-5.3. The following query:
SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
WHERE (6, 234) IN (
SELECT t3.f1, t3.f1
FROM t3 JOIN t4 ON t4.f11 = t3.f10
);
returns rows even though there is no value of 234 in the database and therefore there is no way for the IN predicate to be TRUE.
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1 Using index condition
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 21 Using where; Using join buffer (flat, BNL join)
test case:
CREATE TABLE t1 ( f11 int) ;
INSERT IGNORE INTO t1 VALUES (0);
CREATE TABLE t2 ( f10 int) ;
INSERT IGNORE INTO t2 VALUES (0);
CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
CREATE TABLE t4 ( f11 int) ;
INSERT IGNORE INTO t4 VALUES
(0),(0)
(0),(0)
SET SESSION optimizer_
SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
WHERE (6, 234) IN (
SELECT t3.f1, t3.f1
FROM t3 JOIN t4 ON t4.f11 = t3.f10
);
Related branches
Changed in maria: | |
assignee: | nobody → Timour Katchaounov (timour) |
milestone: | none → 5.3 |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Reduced test case:
CREATE TABLE t1 ( f11 int) ;
INSERT INTO t1 VALUES (1);
CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
INSERT INTO t3 VALUES (6,0),(10,0);
CREATE TABLE t4 ( f11 int) ;
INSERT INTO t4 VALUES (0),(1);
SELECT * FROM t1 WHERE (6, 0) IN (SELECT t3.f1, t3.f1 FROM t3, t4 WHERE t4.f11 = t3.f10);