Wrong result with in_to_exists=on in maria-5.3-mwl89

[Philip Stoev]

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_exists=on,materialization=off,semijoin=off';
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

[Timour Katchaounov (timour)]

Could not repeat with 5.3, thus concluding it is 5.3-mwl89 specific bug.

[Timour Katchaounov (timour)]

A bit simpler example, where it is important that:
- table 't2' has 1 row,
- table 't1' has at least one row which is the same as the only row of table 't2'

CREATE TABLE t1 (a1 int, a2 int) ;
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (3, 4);

CREATE TABLE t2 (b1 int, b2 int) ;
INSERT INTO t2 VALUES (1, 2);

SET SESSION optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off';

SELECT * FROM t1
WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2);

[Timour Katchaounov (timour)]

Analysis:

When calling Item_in_subselect::inject_in_to_exists_cond(),
The WHERE clause join_arg->conds is an Item_equal with
0 elements in the Item_equal::fields list, and
Item_equal::const_item is the field 'b1'.

This Item_equal represents the equality "b1 = b2" in the
subquery. Therefore the equality is erroneously substituted
with a constant.

Later, the call to substitute_for_best_equal_field():
#0 substitute_for_best_equal_field at sql_select.cc:9874
#1 0x000000000074e77d in substitute_for_best_equal_field at sql_select.cc:9837
#2 0x000000000073767a in JOIN::optimize at sql_select.cc:1028
#3 0x000000000059c605 in st_select_lex::optimize_unflattened_subqueries at sql_lex.cc:3140

substitutes the WHERE clause with the constant "1" (Item_int(1)), which
makes the WHERE clause TRUE, and the whole IN predicate TRUE.

TODO:
Figure out why "b1 = b2" gets substituted with "b1" as a constant.

[Timour Katchaounov (timour)]

The patches for BUG#717577 and BUG#776274 correct this bug.

[Timour Katchaounov (timour)]

Pushed test case only.