2649 Sergey Petrunia 2008-05-31
BUG#35468: Slowdown and wrong result for uncorrelated subquery w/o where
- Moved LooseScan functionality from READ_RECORD functions to join runtime.
(This enables use of insideout together with range access)
- Let best_access_path() save the number of the index to be used by the
insideout scan and #keyparts (just keeping a flag wasn't enough for
full-index insideout scans).
- Made setup_semijoin_dups_elimination() define and use an enum instead
of numeric constants.
- Addressed review feedback
modified:
mysql-test/r/subselect_sj2.result
mysql-test/t/subselect_sj2.test
sql/handler.cc
sql/handler.h
sql/mysql_priv.h
sql/mysqld.cc
sql/sql_select.cc
sql/sql_select.h
sql/structs.h
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result 2008-05-03 08:27:47 +0000
+++ b/mysql-test/r/subselect_sj2.result 2008-05-31 07:14:57 +0000
@@ -654,3 +654,30 @@ call p1();
a
drop procedure p1;
drop table t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int) as select A.a + 10 *(B.a + 10*C.a) as a from t0 A, t0 B, t0 C;
+create table t2 (id int, a int, primary key(id), key(a)) as select a as id, a as a from t1;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `id` int(11) NOT NULL DEFAULT '0',
+ `a` int(11) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+set @a=0;
+create table t3 as select * from t2 limit 0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+alter table t3 add primary key(id), add key(a);
+The following must use loose index scan over t3, key a:
+explain select count(a) from t2 where a in ( SELECT a FROM t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index a a 5 NULL 30000 Using index; LooseScan
+1 PRIMARY t2 ref a a 5 test.t3.a 1 Using index
+select count(a) from t2 where a in ( SELECT a FROM t3);
+count(a)
+1000
+drop table t0,t1,t2,t3;
=== modified file 'mysql-test/t/subselect_sj2.test'
--- a/mysql-test/t/subselect_sj2.test 2008-05-03 08:27:47 +0000
+++ b/mysql-test/t/subselect_sj2.test 2008-05-31 07:14:57 +0000
@@ -837,3 +837,25 @@ delimiter ;|
call p1();
drop procedure p1;
drop table t1;
+
+#
+# BUG#35468 "Slowdown and wrong result for uncorrelated subquery w/o where"
+#
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int) as select A.a + 10 *(B.a + 10*C.a) as a from t0 A, t0 B, t0 C;
+create table t2 (id int, a int, primary key(id), key(a)) as select a as id, a as a from t1;
+show create table t2;
+set @a=0;
+create table t3 as select * from t2 limit 0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+
+alter table t3 add primary key(id), add key(a);
+--echo The following must use loose index scan over t3, key a:
+explain select count(a) from t2 where a in ( SELECT a FROM t3);
+select count(a) from t2 where a in ( SELECT a FROM t3);
+
+drop table t0,t1,t2,t3;
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2008-05-18 08:52:30 +0000
+++ b/sql/handler.cc 2008-05-31 07:14:57 +0000
@@ -4586,7 +4586,7 @@ ha_rows DsMrr_impl::dsmrr_info_const(uin
@retval FALSE No
*/
-bool DsMrr_impl::key_uses_partial_cols(uint keyno)
+bool key_uses_partial_cols(TABLE *table, uint keyno)
{
KEY_PART_INFO *kp= table->key_info[keyno].key_part;
KEY_PART_INFO *kp_end= kp + table->key_info[keyno].key_parts;
@@ -4598,7 +4598,6 @@ bool DsMrr_impl::key_uses_partial_cols(u
return FALSE;
}
-
/**
DS-MRR Internals: Choose between Default MRR implementation and DS-MRR
@@ -4632,7 +4631,7 @@ bool DsMrr_impl::choose_mrr_impl(uint ke
(*flags & HA_MRR_INDEX_ONLY) || (*flags & HA_MRR_SORTED) ||
(keyno == table->s->primary_key &&
h->primary_key_is_clustered()) ||
- key_uses_partial_cols(keyno))
+ key_uses_partial_cols(table, keyno))
{
/* Use the default implementation */
*flags |= HA_MRR_USE_DEFAULT_IMPL;
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2008-05-08 13:01:30 +0000
+++ b/sql/handler.h 2008-05-31 07:14:57 +0000
@@ -2319,6 +2319,7 @@ private:
};
+bool key_uses_partial_cols(TABLE *table, uint keyno);
/**
A Disk-Sweep MRR interface implementation
@@ -2379,7 +2380,6 @@ public:
void *seq_init_param, uint n_ranges, uint *bufsz,
uint *flags, COST_VECT *cost);
private:
- bool key_uses_partial_cols(uint keyno);
bool choose_mrr_impl(uint keyno, ha_rows rows, uint *flags, uint *bufsz,
COST_VECT *cost);
bool get_disk_sweep_mrr_cost(uint keynr, ha_rows rows, uint flags,
=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h 2008-05-21 10:17:29 +0000
+++ b/sql/mysql_priv.h 2008-05-31 07:14:57 +0000
@@ -568,6 +568,7 @@ enum open_table_mode
/* @@optimizer_switch flags */
#define OPTIMIZER_SWITCH_NO_MATERIALIZATION 1
#define OPTIMIZER_SWITCH_NO_SEMIJOIN 2
+#define OPTIMIZER_SWITCH_NO_LOOSE_SCAN 4
/*
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2008-05-22 18:40:15 +0000
+++ b/sql/mysqld.cc 2008-05-31 07:14:57 +0000
@@ -321,7 +321,7 @@ TYPELIB sql_mode_typelib= { array_elemen
static const char *optimizer_switch_names[]=
{
- "no_materialization", "no_semijoin",
+ "no_materialization", "no_semijoin", "no_loosescan",
NullS
};
@@ -329,7 +329,8 @@ static const char *optimizer_switch_name
static const unsigned int optimizer_switch_names_len[]=
{
/*no_materialization*/ 19,
- /*no_semijoin*/ 11
+ /*no_semijoin*/ 11,
+ /*no_loosescan*/ 12,
};
TYPELIB optimizer_switch_typelib= { array_elements(optimizer_switch_names)-1,"",
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-05-23 05:03:07 +0000
+++ b/sql/sql_select.cc 2008-05-31 07:14:57 +0000
@@ -156,13 +156,11 @@ static int join_read_always_key(JOIN_TAB
static int join_read_last_key(JOIN_TAB *tab);
static int join_no_more_records(READ_RECORD *info);
static int join_read_next(READ_RECORD *info);
-static int join_read_next_different(READ_RECORD *info);
static int join_init_quick_read_record(JOIN_TAB *tab);
static int test_if_quick_select(JOIN_TAB *tab);
static int join_init_read_record(JOIN_TAB *tab);
static int join_read_first(JOIN_TAB *tab);
static int join_read_next_same(READ_RECORD *info);
-static int join_read_next_same_diff(READ_RECORD *info);
static int join_read_last(JOIN_TAB *tab);
static int join_read_prev_same(READ_RECORD *info);
static int join_read_prev(READ_RECORD *info);
@@ -1042,6 +1040,12 @@ static
int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, uint no_jbuf_after)
{
table_map cur_map= join->const_table_map | PSEUDO_TABLE_BITS;
+ enum sj_strategy_enum {
+ SJ_STRATEGY_INVALID,
+ SJ_STRATEGY_INSIDEOUT,
+ SJ_STRATEGY_DUPS_WEEDOUT,
+ SJ_STRATEGY_DUPS_WEEDOUT_JBUF
+ };
struct {
/*
0 - invalid (EOF marker),
@@ -1049,7 +1053,7 @@ int setup_semijoin_dups_elimination(JOIN
2 - Temptable (maybe confluent),
3 - Temptable with join buffering
*/
- uint strategy;
+ enum sj_strategy_enum strategy;
uint start_idx; /* Left range bound */
uint end_idx; /* Right range bound */
/*
@@ -1073,7 +1077,7 @@ int setup_semijoin_dups_elimination(JOIN
/*
First pass: locate the duplicate-generating ranges and pick the strategies.
*/
- for (i=join->const_tables ; i < join->tables ; i++)
+ for (i= join->const_tables ; i < join->tables ; i++)
{
JOIN_TAB *tab=join->join_tab+i;
TABLE *table=tab->table;
@@ -1091,7 +1095,7 @@ int setup_semijoin_dups_elimination(JOIN
be that it overlaps with anther semi-join's range. we don't
support InsideOut for joined ranges)
*/
- if (join->best_positions[i].use_insideout_scan)
+ if (join->best_positions[i].insideout_key != MAX_KEY)
emb_insideout_nest= tab->emb_sj_nest;
}
@@ -1161,7 +1165,7 @@ int setup_semijoin_dups_elimination(JOIN
bitmap_covers(cur_map, emb_insideout_nest->sj_inner_tables))
{
/* Save that this range is handled with InsideOut: */
- dups_ranges[cur_range].strategy= 1;
+ dups_ranges[cur_range].strategy= SJ_STRATEGY_INSIDEOUT;
end_of_range= TRUE;
}
else if (bitmap_covers(cur_map, emb_outer_tables | emb_sj_map))
@@ -1170,7 +1174,9 @@ int setup_semijoin_dups_elimination(JOIN
This is a complete range to be handled with either DuplicateWeedout
or FirstMatch
*/
- dups_ranges[cur_range].strategy= dealing_with_jbuf? 3 : 2;
+ dups_ranges[cur_range].strategy=
+ dealing_with_jbuf? SJ_STRATEGY_DUPS_WEEDOUT_JBUF:
+ SJ_STRATEGY_DUPS_WEEDOUT;
/*
This will hold tables from within the range that need to be put
into the join buffer before we can use the FirstMatch on its tail.
@@ -1186,7 +1192,7 @@ int setup_semijoin_dups_elimination(JOIN
emb_sj_map= emb_outer_tables= 0;
emb_insideout_nest= NULL;
dealing_with_jbuf= FALSE;
- dups_ranges[++cur_range].strategy= 0;
+ dups_ranges[++cur_range].strategy= SJ_STRATEGY_INVALID;
}
else
{
@@ -1200,18 +1206,27 @@ int setup_semijoin_dups_elimination(JOIN
THD *thd= join->thd;
SJ_TMP_TABLE **next_sjtbl_ptr= &join->sj_tmp_tables;
/*
- Second pass: setup the chosen strategies
+ The second pass: setup the chosen strategies
*/
for (int j= 0; j < cur_range; j++)
{
JOIN_TAB *tab=join->join_tab + dups_ranges[j].start_idx;
JOIN_TAB *jump_to;
- if (dups_ranges[j].strategy == 1) // InsideOut strategy
+ if (dups_ranges[j].strategy == SJ_STRATEGY_INSIDEOUT)
{
+ /* We jump from the last table to the first one */
tab->insideout_match_tab= join->join_tab + dups_ranges[j].end_idx - 1;
+
+ /* Calculate key length */
+ uint nparts= join->positions[dups_ranges[j].start_idx].insideout_parts;
+ uint keyno= join->positions[dups_ranges[j].start_idx].insideout_key;
+ uint keylen= 0;
+ for (uint kp=0; kp < nparts; kp++)
+ keylen += tab->table->key_info[keyno].key_part[kp].store_length;
+ tab->insideout_key_len= keylen;
jump_to= tab++;
}
- else // DuplicateWeedout strategy
+ else // DuplicateWeedout or FirstMatch
{
SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
table_map cur_map= join->const_table_map | PSEUDO_TABLE_BITS;
@@ -5389,7 +5404,12 @@ best_access_path(JOIN *join,
table_map best_ref_depends_map= 0;
double tmp;
ha_rows rec;
- uint best_is_sj_inside_out= 0;
+ uint best_is_sj_inside_out= MAX_KEY;
+ uint best_sj_keyparts;
+ bool try_sj_inside_out= FALSE;
+ uint sj_insideout_quick_select= FALSE;
+ uint sj_insideout_quick_max_sj_keypart;
+ uint sj_inside_out_scan= MAX_KEY;
DBUG_ENTER("best_access_path");
if (s->keyuse)
@@ -5399,14 +5419,13 @@ best_access_path(JOIN *join,
double best_records= DBL_MAX;
uint max_key_part=0;
ulonglong bound_sj_equalities= 0;
- bool try_sj_inside_out= FALSE;
/*
Discover the bound equalites. We need to do this, if
1. The next table is an SJ-inner table, and
2. It is the first table from that semijoin, and
3. We're not within a semi-join range (i.e. all semi-joins either have
all or none of their tables in join_table_map), except
- s->emb_sj_nest (which we've just entered).
+ s->emb_sj_nest (which we've just entered, see #2).
4. All non-IN-equality correlation references from this sj-nest are
bound
5. But some of the IN-equalities aren't (so this can't be handled by
@@ -5419,12 +5438,15 @@ best_access_path(JOIN *join,
join->cur_emb_sj_nests == s->emb_sj_nest->sj_inner_tables && // (3)
!(remaining_tables &
s->emb_sj_nest->nested_join->sj_corr_tables) && // (4)
- remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on) // (5)
+ remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on &&// (5)
+ !test(thd->variables.optimizer_switch & OPTIMIZER_SWITCH_NO_LOOSE_SCAN))
{
/* This table is an InsideOut scan candidate */
bound_sj_equalities= get_bound_sj_equalities(s->emb_sj_nest,
remaining_tables);
try_sj_inside_out= TRUE;
+ DBUG_PRINT("info", ("Will try InsideOut scan, bound_map=%llx",
+ (longlong)bound_sj_equalities));
}
/* Test how we can use keys */
@@ -5445,6 +5467,9 @@ best_access_path(JOIN *join,
start_key= keyuse;
ulonglong handled_sj_equalities=0;
key_part_map sj_insideout_map= 0;
+ uint max_sj_keypart= 0;
+ DBUG_PRINT("info", ("Considering ref access on key %s",
+ keyuse->table->key_info[keyuse->key].name));
do /* For each keypart */
{
@@ -5492,6 +5517,7 @@ best_access_path(JOIN *join,
{
handled_sj_equalities |= 1ULL << keyuse->sj_pred_no;
sj_insideout_map |= ((key_part_map)1) << keyuse->keypart;
+ set_if_bigger(max_sj_keypart, keyuse->keypart);
}
}
@@ -5510,7 +5536,7 @@ best_access_path(JOIN *join,
if (rec < MATCHING_ROWS_IN_OTHER_TABLE)
rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables
- bool sj_inside_out_scan= FALSE;
+ sj_inside_out_scan= MAX_KEY;
/*
ft-keys require special treatment
*/
@@ -5527,49 +5553,66 @@ best_access_path(JOIN *join,
{
found_constraint= test(found_part);
/*
- Check if InsideOut scan is applicable:
- 1. All IN-equalities are either "bound" or "handled"
- 2. Index keyparts are
- ...
+ Check if we can use InsideOut semi-join strategy. We can if
+ 1. This is the right table at right location
+ 2. All IN-equalities are either
+ - "bound", ie. the outer_expr part refers to the preceding tables
+ - "handled", ie. covered by the index we're considering
+ 3. Index order allows to enumerate subquery's duplicate groups in
+ order. This happens when the index definition matches this
+ pattern:
+
+ (handled_col|bound_col)* (other_col|bound_col)
+
*/
- if (try_sj_inside_out &&
- table->covering_keys.is_set(key) &&
- (handled_sj_equalities | bound_sj_equalities) == // (1)
- PREV_BITS(ulonglong, s->emb_sj_nest->sj_in_exprs)) // (1)
+ if (try_sj_inside_out && // (1)
+ (handled_sj_equalities | bound_sj_equalities) == // (2)
+ PREV_BITS(ulonglong, s->emb_sj_nest->sj_in_exprs) && // (2)
+ (PREV_BITS(key_part_map, max_sj_keypart+1) & // (3)
+ (found_part | sj_insideout_map)) == // (3)
+ (found_part | sj_insideout_map) && // (3)
+ !key_uses_partial_cols(s->table, key))
{
- uint n_fixed_parts= max_part_bit(found_part);
- if (n_fixed_parts != keyinfo->key_parts &&
- (PREV_BITS(uint, n_fixed_parts) | sj_insideout_map) ==
- PREV_BITS(uint, keyinfo->key_parts))
+ /* Ok, can use the strategy */
+ sj_inside_out_scan= key;
+ if (s->quick && s->quick->index == key &&
+ s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
{
- /*
- Not all parts are fixed. Produce bitmap of remaining bits and
- check if all of them are covered.
- */
- sj_inside_out_scan= TRUE;
- DBUG_PRINT("info", ("Using sj InsideOut scan"));
- if (!n_fixed_parts)
- {
- /*
- It's a confluent ref scan.
+ sj_insideout_quick_select= TRUE;
+ sj_insideout_quick_max_sj_keypart= max_sj_keypart;
+ }
+ DBUG_PRINT("info", ("Can use InsideOut scan"));
- That is, all found KEYUSE elements refer to IN-equalities,
- and there is really no ref access because there is no
- t.keypart0 = {bound expression}
+ /*
+ Check if this is a confluent where there are no usable bound
+ IN-equalities, e.g. we have
- Calculate the cost of complete loose index scan.
- */
- records= (double)s->table->file->stats.records;
+ outer_expr IN (SELECT innertbl.key FROM ...)
+
+ and outer_expr cannot be evaluated yet, so it's actually full
+ index scan and not a ref access
+ */
+ if (!(found_part & 1 ) && /* no usable ref access for 1st key part */
+ table->covering_keys.is_set(key))
+ {
+ DBUG_PRINT("info", ("Can use full index scan for InsideOut"));
+ /* Calculate the cost of complete loose index scan. */
+ records= rows2double(s->table->file->stats.records);
- /* The cost is entire index scan cost (divided by 2) */
- best_time= s->table->file->index_only_read_time(key, records);
+ /* The cost is entire index scan cost (divided by 2) */
+ best_time= s->table->file->index_only_read_time(key, records);
- /* Now figure how many different keys we will get */
- ulong rpc;
- if ((rpc= keyinfo->rec_per_key[keyinfo->key_parts-1]))
- records= records / rpc;
- start_key= NULL;
- }
+ /*
+ Now find out how many different keys we will get (for now we
+ ignore the fact that we have "keypart_i=const" restriction for
+ some key components, that may make us think think that loose
+ scan will produce more distinct records than it actually will)
+ */
+ ulong rpc;
+ if ((rpc= keyinfo->rec_per_key[max_sj_keypart]))
+ records= records / rpc;
+ start_key= NULL;
+ /* Fall through */
}
}
@@ -5824,7 +5867,7 @@ best_access_path(JOIN *join,
tmp= best_time; // Do nothing
}
- if (sj_inside_out_scan && !start_key)
+ if ((sj_inside_out_scan != MAX_KEY) && !start_key)
{
tmp= tmp/2;
if (records)
@@ -5841,8 +5884,9 @@ best_access_path(JOIN *join,
best_max_key_part= max_key_part;
best_ref_depends_map= found_ref;
best_is_sj_inside_out= sj_inside_out_scan;
+ best_sj_keyparts= max_sj_keypart;
}
- }
+ } /* for each key */
records= best_records;
}
@@ -5881,6 +5925,7 @@ best_access_path(JOIN *join,
! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
!(s->table->force_index && best_key && !s->quick)) // (4)
{ // Check full join
+ sj_inside_out_scan= MAX_KEY;
ha_rows rnd_records= s->found_records;
/*
If there is a filtering condition on the table (i.e. ref analyzer found
@@ -5920,6 +5965,11 @@ best_access_path(JOIN *join,
tmp= record_count *
(s->quick->read_time +
(s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);
+
+ if (sj_insideout_quick_select && idx == join->const_tables)
+ {
+ sj_inside_out_scan= s->quick->index;
+ }
}
else
{
@@ -5971,7 +6021,8 @@ best_access_path(JOIN *join,
best_key= 0;
/* range/index_merge/ALL/index access method are "independent", so: */
best_ref_depends_map= 0;
- best_is_sj_inside_out= FALSE;
+ best_is_sj_inside_out= sj_inside_out_scan;
+ best_sj_keyparts= sj_insideout_quick_max_sj_keypart;
}
}
@@ -5981,7 +6032,8 @@ best_access_path(JOIN *join,
join->positions[idx].key= best_key;
join->positions[idx].table= s;
join->positions[idx].ref_depend_map= best_ref_depends_map;
- join->positions[idx].use_insideout_scan= best_is_sj_inside_out;
+ join->positions[idx].insideout_key= best_is_sj_inside_out;
+ join->positions[idx].insideout_parts= best_sj_keyparts + 1;
if (!best_key &&
idx == join->const_tables &&
@@ -6924,7 +6976,7 @@ get_best_combination(JOIN *join)
DBUG_PRINT("info",("type: %d", j->type));
if (j->type == JT_CONST)
continue; // Handled in make_join_stat..
-
+ j->insideout_match_tab= NULL;
j->ref.key = -1;
j->ref.key_parts=0;
@@ -6933,6 +6985,7 @@ get_best_combination(JOIN *join)
if (j->keys.is_clear_all() || !(keyuse= join->best_positions[tablenr].key))
{
j->type=JT_ALL;
+ j->index= join->best_positions[tablenr].insideout_key;
if (tablenr != join->const_tables)
join->full_join=1;
}
@@ -7227,6 +7280,7 @@ make_simple_join(JOIN *join,TABLE *tmp_t
join_tab->ref.key_parts= 0;
join_tab->flush_weedout_table= join_tab->check_weed_out_table= NULL;
join_tab->do_firstmatch= NULL;
+ join_tab->insideout_match_tab= NULL;
bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record));
tmp_table->status=0;
tmp_table->null_row=0;
@@ -8260,9 +8314,8 @@ make_join_readinfo(JOIN *join, ulonglong
sorted= 0; // only first must be sorted
if (tab->insideout_match_tab)
{
- if (!(tab->insideout_buf= (uchar*)join->thd->alloc(tab->table->key_info
- [tab->index].
- key_length)))
+ if (!(tab->insideout_buf=
+ (uchar*)join->thd->alloc(tab->insideout_key_len)))
return TRUE;
}
switch (tab->type) {
@@ -8323,8 +8376,7 @@ make_join_readinfo(JOIN *join, ulonglong
if (tab->type == JT_REF)
{
tab->read_first_record= join_read_always_key;
- tab->read_record.read_record= tab->insideout_match_tab?
- join_read_next_same_diff : join_read_next_same;
+ tab->read_record.read_record= join_read_next_same;
}
else
{
@@ -13442,9 +13494,25 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
Note: psergey has added the 2nd part of the following condition; the
change should probably be made in 5.1, too.
*/
+ bool skip_over= FALSE;
while (rc == NESTED_LOOP_OK && join->return_tab >= join_tab)
{
+ if (join_tab->insideout_match_tab && join_tab->insideout_match_tab->found_match)
+ {
+ KEY *key= join_tab->table->key_info + join_tab->index;
+ key_copy(join_tab->insideout_buf, info->record, key,
+ join_tab->insideout_key_len);
+ skip_over= TRUE;
+ }
error= info->read_record(info);
+
+ if (skip_over && !error &&
+ !key_cmp(join_tab->table->key_info[join_tab->index].key_part,
+ join_tab->insideout_buf, join_tab->insideout_key_len))
+ {
+ continue;
+ }
+
rc= evaluate_join_record(join, join_tab, error);
}
@@ -14171,37 +14239,6 @@ join_no_more_records(READ_RECORD *info _
return -1;
}
-static int
-join_read_next_same_diff(READ_RECORD *info)
-{
- TABLE *table= info->table;
- JOIN_TAB *tab=table->reginfo.join_tab;
- if (tab->insideout_match_tab->found_match)
- {
- KEY *key= tab->table->key_info + tab->index;
- do
- {
- int error;
- /* Save index tuple from record to the buffer */
- key_copy(tab->insideout_buf, info->record, key, 0);
-
- if ((error=table->file->index_next_same(table->record[0],
- tab->ref.key_buff,
- tab->ref.key_length)))
- {
- if (error != HA_ERR_END_OF_FILE)
- return report_error(table, error);
- table->status= STATUS_GARBAGE;
- return -1;
- }
- } while (!key_cmp(tab->table->key_info[tab->index].key_part,
- tab->insideout_buf, key->key_length));
- tab->insideout_match_tab->found_match= 0;
- return 0;
- }
- else
- return join_read_next_same(info);
-}
static int
join_read_next_same(READ_RECORD *info)
@@ -14298,17 +14335,7 @@ join_read_first(JOIN_TAB *tab)
tab->read_record.file=table->file;
tab->read_record.index=tab->index;
tab->read_record.record=table->record[0];
- if (tab->insideout_match_tab)
- {
- tab->read_record.do_insideout_scan= tab;
- tab->read_record.read_record=join_read_next_different;
- tab->insideout_match_tab->found_match= 0;
- }
- else
- {
- tab->read_record.read_record=join_read_next;
- tab->read_record.do_insideout_scan= 0;
- }
+ tab->read_record.read_record=join_read_next;
if (!table->file->inited)
table->file->ha_index_init(tab->index, tab->sorted);
@@ -14323,32 +14350,6 @@ join_read_first(JOIN_TAB *tab)
static int
-join_read_next_different(READ_RECORD *info)
-{
- JOIN_TAB *tab= info->do_insideout_scan;
- if (tab->insideout_match_tab->found_match)
- {
- KEY *key= tab->table->key_info + tab->index;
- do
- {
- int error;
- /* Save index tuple from record to the buffer */
- key_copy(tab->insideout_buf, info->record, key, 0);
-
- if ((error=info->file->index_next(info->record)))
- return report_error(info->table, error);
-
- } while (!key_cmp(tab->table->key_info[tab->index].key_part,
- tab->insideout_buf, key->key_length));
- tab->insideout_match_tab->found_match= 0;
- return 0;
- }
- else
- return join_read_next(info);
-}
-
-
-static int
join_read_next(READ_RECORD *info)
{
int error;
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2007-12-21 20:10:05 +0000
+++ b/sql/sql_select.h 2008-05-31 07:14:57 +0000
@@ -273,13 +273,18 @@ typedef struct st_join_table {
struct st_join_table *do_firstmatch;
/*
- ptr - this join tab should do an InsideOut scan. Points
- to the tab for which we'll need to check tab->found_match.
-
+ ptr - We're doing and InsideOut scan, and this is the first (aka
+ "driving" join tab). ptr to the last tab, for which we'll need
+ to check tab->found_match to see if the current value group had
+ a match.
NULL - Not an insideout scan.
*/
struct st_join_table *insideout_match_tab;
- uchar *insideout_buf; // Buffer to save index tuple to be able to skip dups
+ /* Buffer to save index tuple to be able to skip duplicates */
+ uchar *insideout_buf;
+
+ /* How many key components to store in the above */
+ uint insideout_key_len;
/* Used by InsideOut scan. Just set to true when have found a row. */
bool found_match;
@@ -349,8 +354,16 @@ typedef struct st_position
/* If ref-based access is used: bitmap of tables this table depends on */
table_map ref_depend_map;
-
- bool use_insideout_scan;
+
+ /*
+ keyno - This is an insideout scan on this key. If keyuse is NULL then
+ this is a full index scan, otherwise this is a ref + insideout
+ scan (and keyno matches the KEUSE's)
+ MAX_KEY - This is not an InsideOut scan
+ */
+ uint insideout_key;
+ /* Number of key parts to be used by insideout */
+ uint insideout_parts;
} POSITION;
=== modified file 'sql/structs.h'
--- a/sql/structs.h 2008-04-01 15:13:57 +0000
+++ b/sql/structs.h 2008-05-31 07:14:57 +0000
@@ -137,7 +137,6 @@ typedef struct st_read_record { /* Par
uchar *cache,*cache_pos,*cache_end,*read_positions;
IO_CACHE *io_cache;
bool print_error, ignore_not_found_rows;
- struct st_join_table *do_insideout_scan;
} READ_RECORD;
| Thread |
|---|
| • bzr push into mysql-6.0 branch (sergefp:2649) Bug#35468 | Sergey Petrunia | 31 May |
