[MDEV-2494] LP:715759 - Wrong result with in_to_exists=on in maria-5.3-mwl89 Created: 2011-02-09  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug715759.xml    

 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_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

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-02-15 ]

Re: Wrong result with in_to_exists=on
Could not repeat with 5.3, thus concluding it is 5.3-mwl89 specific bug.

Comment by Timour Katchaounov (Inactive) [ 2011-02-22 ]

Re: Wrong result with in_to_exists=on
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);

Comment by Timour Katchaounov (Inactive) [ 2011-02-23 ]

Re: Wrong result with in_to_exists=on
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.

Comment by Timour Katchaounov (Inactive) [ 2011-05-04 ]

Re: Wrong result with in_to_exists=on in maria-5.3-mwl89
The patches for BUG#717577 and BUG#776274 correct this bug.

Comment by Timour Katchaounov (Inactive) [ 2011-05-04 ]

Re: Wrong result with in_to_exists=on in maria-5.3-mwl89
Pushed test case only.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 715759

Generated at Thu Feb 08 06:42:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.