Wrong result with in_to_exists=on in maria-5.3-mwl89
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Timour Katchaounov |
Bug Description
Repeatable in both maria-5.3 and maria-5.3-mwl89
The following query returns 1 row even though the IN predicate should evaluate to FALSE since there are no rows to be returned by the IN subquery, since there are no rows that satisfy the ON clause of the subquery.
test case:
CREATE TABLE t1 ( f2 int(11), f3 int(11), f5 varchar(1)) ;
INSERT INTO t1 VALUES (NULL,'6','f');
CREATE TABLE t2 ( f3 int(11), f5 varchar(1)) ;
INSERT INTO t2 VALUES ('7','f');
SET SESSION optimizer_switch = 'in_to_
SELECT t1.* FROM t2
JOIN t1 ON t1.f5 IN (
SELECT C_SQ1_alias1.f5
FROM t1 AS C_SQ1_alias1
JOIN t1 AS C_SQ1_alias2
ON C_SQ1_alias2.f3 = C_SQ1_alias2.f2
);
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
1 PRIMARY t1 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY C_SQ1_alias1 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY C_SQ1_alias2 system NULL NULL NULL NULL 1
Related branches
summary: |
- Wrong result in maria-5.3-mwl89 with in_to_exists=on + Wrong result with in_to_exists=on |
Changed in maria: | |
assignee: | nobody → Timour Katchaounov (timour) |
milestone: | none → 5.3 |
summary: |
- Wrong result with in_to_exists=on + Wrong result with in_to_exists=on in maria-5.3-mwl89 |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Could not repeat with 5.3, thus concluding it is 5.3-mwl89 specific bug.