From 73cc529b51dd4262e81df86491e3f2803946339c Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 25 Jan 2012 18:33:57 +0400 Subject: BUG#920255: Wrong result (extra rows) with loosescan and IN subquery The problem was that LooseScan execution code assumed that tab->key holds the index used for looseScan. This is only true when range or full index scan are used. In case of ref access, the index is in tab->ref.key (and tab->index==0 which explains how LooseScan passed tests with ref access: they used one index) Fixed by setting/using loosescan_key, which always the correct index#. --- mysql-test/r/subselect_sj.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/r/subselect_sj_jcl6.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/t/subselect_sj.test | 19 +++++++++++++++++++ sql/opt_subselect.cc | 1 + sql/sql_select.cc | 4 ++-- sql/sql_select.h | 6 ++++++ 6 files changed, 96 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index f5c3f84bcd7..9e81a0fa1b5 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2169,4 +2169,38 @@ WHERE c = b AND b = a a COUNT(*) NULL 0 DROP TABLE t1, t2, t3; +# +# BUG#920255: Wrong result (extra rows) with loosescan and IN subquery +# +CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) ); +INSERT INTO t1 VALUES +(1,2),(2,1),(3,3),(4,2),(5,5), +(6,3),(7,1),(8,4),(9,3),(10,2); +CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) ); +INSERT INTO t2 VALUES +(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN +( SELECT b, d FROM t1, t2 WHERE b = c ); +a b d +2 1 2 +7 1 2 +2 1 2 +7 1 2 +1 2 1 +4 2 1 +10 2 1 +1 2 1 +4 2 1 +10 2 1 +3 3 3 +6 3 3 +9 3 3 +3 3 3 +6 3 3 +9 3 3 +8 4 2 +8 4 2 +5 5 5 +DROP TABLE t1, t2; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index faa4140d375..f632d3bc89d 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2183,6 +2183,40 @@ WHERE c = b AND b = a a COUNT(*) NULL 0 DROP TABLE t1, t2, t3; +# +# BUG#920255: Wrong result (extra rows) with loosescan and IN subquery +# +CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) ); +INSERT INTO t1 VALUES +(1,2),(2,1),(3,3),(4,2),(5,5), +(6,3),(7,1),(8,4),(9,3),(10,2); +CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) ); +INSERT INTO t2 VALUES +(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN +( SELECT b, d FROM t1, t2 WHERE b = c ); +a b d +1 2 1 +1 2 1 +2 1 2 +2 1 2 +3 3 3 +3 3 3 +4 2 1 +4 2 1 +5 5 5 +6 3 3 +6 3 3 +7 1 2 +7 1 2 +8 4 2 +8 4 2 +9 3 3 +9 3 3 +10 2 1 +10 2 1 +DROP TABLE t1, t2; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index f34cf5ba338..afa471d5889 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2017,5 +2017,24 @@ SELECT a, COUNT(*) FROM t1 DROP TABLE t1, t2, t3; +--echo # +--echo # BUG#920255: Wrong result (extra rows) with loosescan and IN subquery +--echo # +CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) ); +INSERT INTO t1 VALUES + (1,2),(2,1),(3,3),(4,2),(5,5), + (6,3),(7,1),(8,4),(9,3),(10,2); + +CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) ); +INSERT INTO t2 VALUES + (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); + +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN + ( SELECT b, d FROM t1, t2 WHERE b = c ); + +DROP TABLE t1, t2; + + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 8e043b17bcf..63a90891720 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -4107,6 +4107,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, for (uint kp=0; kp < pos->loosescan_picker.loosescan_parts; kp++) keylen += tab->table->key_info[keyno].key_part[kp].store_length; + tab->loosescan_key= keyno; tab->loosescan_key_len= keylen; if (pos->n_sj_tables > 1) tab[pos->n_sj_tables - 1].do_firstmatch= tab; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4058e72c547..3991fff6960 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -15372,7 +15372,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) if (join_tab->loosescan_match_tab && join_tab->loosescan_match_tab->found_match) { - KEY *key= join_tab->table->key_info + join_tab->index; + KEY *key= join_tab->table->key_info + join_tab->loosescan_key; key_copy(join_tab->loosescan_buf, join_tab->table->record[0], key, join_tab->loosescan_key_len); skip_over= TRUE; @@ -15382,7 +15382,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) if (skip_over && !error) { - if(!key_cmp(join_tab->table->key_info[join_tab->index].key_part, + if(!key_cmp(join_tab->table->key_info[join_tab->loosescan_key].key_part, join_tab->loosescan_buf, join_tab->loosescan_key_len)) { /* diff --git a/sql/sql_select.h b/sql/sql_select.h index 8b448130eaf..185bf90ea17 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -379,6 +379,12 @@ typedef struct st_join_table { /* Buffer to save index tuple to be able to skip duplicates */ uchar *loosescan_buf; + /* + Index used by LooseScan (we store it here separately because ref access + stores it in tab->ref.key, while range scan stores it in tab->index, etc) + */ + uint loosescan_key; + /* Length of key tuple (depends on #keyparts used) to store in the above */ uint loosescan_key_len; -- cgit v1.2.1