[MDEV-2960] LP:778434 - Wrong result with in_to_exists=on in maria-5.3-mwl89 Created: 2011-05-06  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: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug778434.xml    

 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),(0),(0),(0),(0),(0),(0),(NULL),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);

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

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
);

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

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

Comment by Timour Katchaounov (Inactive) [ 2011-07-07 ]

Re: Wrong result with in_to_exists=on in maria-5.3-mwl89
The bug is related to index condition pushdown:
set @@optimizer_switch='index_condition_pushdown=on';

My current analysis shows that the condition on table t3 is
pushed to the index as a whole, but apparently is not used
to filter the rows of table t3.

Comment by Timour Katchaounov (Inactive) [ 2011-07-07 ]

Re: Wrong result with in_to_exists=on in maria-5.3-mwl89
Analysis:
The reason why the pushed index condition is not checked is because
in the function ha_myisam::idx_cond_push() the condition

if (active_index == pushed_idx_cond_keyno)

is false (active_index = 64, pushed_idx_cond_keyno = 0).
As a result, the function mi_set_index_cond_func() is not called,
and when we get to the while loop in mi_rkey():

while ((info->lastpos >= info->state->data_file_length &&
(search_flag != HA_READ_KEY_EXACT ||
last_used_keyseg != keyinfo->seg + keyinfo->keysegs)) ||
(info->index_cond_func &&
(res= mi_check_index_cond(info, inx, buf)) == ICP_NO_MATCH))

the member info->index_cond_func is NULL, thus mi_check_index_cond()
is not called at all, and the index condition is not applied.

The method idx_cond_push() is called as follows:

JOIN::optimize -> make_join_readinfo -> push_index_cond ->
ha_myisam::idx_cond_push()

The most likely reason for active_index == MAX_KEY, and
handler::inited == handler::NONE is because idx_cond_push is
called without any call to handler::ha_index_init().

Comment by Timour Katchaounov (Inactive) [ 2011-07-08 ]

Re: Wrong result with in_to_exists=on in maria-5.3-mwl89
This appears to be a bug in ICP, reassigning to SergeyP.

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

Launchpad bug id: 778434

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