diff options
Diffstat (limited to 'sql/sql_select.cc')
| -rw-r--r-- | sql/sql_select.cc | 144 |
1 files changed, 113 insertions, 31 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6758f9d68d0..9a4e93dfb94 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -158,8 +158,8 @@ static int join_read_prev_same(READ_RECORD *info); static int join_read_prev(READ_RECORD *info); static int join_ft_read_first(JOIN_TAB *tab); static int join_ft_read_next(READ_RECORD *info); -static int join_read_always_key_or_null(JOIN_TAB *tab); -static int join_read_next_same_or_null(READ_RECORD *info); +int join_read_always_key_or_null(JOIN_TAB *tab); +int join_read_next_same_or_null(READ_RECORD *info); static COND *make_cond_for_table(COND *cond,table_map table, table_map used_table); static Item* part_of_refkey(TABLE *form,Field *field); @@ -505,11 +505,12 @@ err: DBUG_RETURN(-1); /* purecov: inspected */ } + /* test if it is known for optimisation IN subquery - SYNOPSYS - JOIN::test_in_subselect + SYNOPSIS + JOIN::test_in_subselect() where - pointer for variable in which conditions should be stored if subquery is known @@ -544,6 +545,35 @@ bool JOIN::test_in_subselect(Item **where) /* + Check if the passed HAVING clause is a clause added by subquery optimizer + + SYNOPSIS + is_having_subq_predicates() + having Having clause + + RETURN + TRUE The passed HAVING clause was added by the subquery optimizer + FALSE Otherwise +*/ + +bool is_having_subq_predicates(Item *having) +{ + if (having->type() == Item::FUNC_ITEM) + { + if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC) + return TRUE; + if (((Item_func *) having)->functype() == Item_func::TRIG_COND_FUNC) + { + having= ((Item_func*)having)->arguments()[0]; + if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC) + return TRUE; + } + return TRUE; + } + return FALSE; +} + +/* global select optimisation. return 0 - success 1 - error @@ -1028,9 +1058,7 @@ JOIN::optimize() } } else if (join_tab[0].type == JT_REF_OR_NULL && join_tab[0].ref.items[0]->name == in_left_expr_name && - having->type() == Item::FUNC_ITEM && - ((Item_func *) having)->functype() == - Item_func::ISNOTNULLTEST_FUNC) + is_having_subq_predicates(having)) { join_tab[0].type= JT_INDEX_SUBQUERY; error= 0; @@ -1276,14 +1304,14 @@ JOIN::reinit() exec_tmp_table1->file->extra(HA_EXTRA_RESET_STATE); exec_tmp_table1->file->delete_all_rows(); free_io_cache(exec_tmp_table1); - filesort_free_buffers(exec_tmp_table1); + filesort_free_buffers(exec_tmp_table1,0); } if (exec_tmp_table2) { exec_tmp_table2->file->extra(HA_EXTRA_RESET_STATE); exec_tmp_table2->file->delete_all_rows(); free_io_cache(exec_tmp_table2); - filesort_free_buffers(exec_tmp_table2); + filesort_free_buffers(exec_tmp_table2,0); } if (items0) set_items_ref_array(items0); @@ -1446,6 +1474,7 @@ JOIN::exec() curr_join->examined_rows= 0; if ((curr_join->select_lex->options & OPTION_SCHEMA_TABLE) && + !thd->lex->describe && get_schema_tables_result(curr_join)) { DBUG_VOID_RETURN; @@ -2547,6 +2576,9 @@ typedef struct key_field_t { // Used when finding key fields when val IS NULL. */ bool null_rejecting; + + /* TRUE<=> This ref access is an outer subquery reference access */ + bool outer_ref; } KEY_FIELD; /* Values in optimize */ @@ -2848,6 +2880,7 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond, cond->functype() == Item_func::MULT_EQUAL_FUNC) && ((*value)->type() == Item::FIELD_ITEM) && ((Item_field*)*value)->field->maybe_null()); + (*key_fields)->outer_ref= FALSE; (*key_fields)++; } @@ -2906,7 +2939,7 @@ add_key_equal_fields(KEY_FIELD **key_fields, uint and_level, } static void -add_key_fields(KEY_FIELD **key_fields,uint *and_level, +add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level, COND *cond, table_map usable_tables, SARGABLE_PARAM **sargables) { @@ -2919,28 +2952,56 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level, { Item *item; while ((item=li++)) - add_key_fields(key_fields,and_level,item,usable_tables,sargables); + add_key_fields(join, key_fields, and_level, item, usable_tables, + sargables); for (; org_key_fields != *key_fields ; org_key_fields++) org_key_fields->level= *and_level; } else { (*and_level)++; - add_key_fields(key_fields,and_level,li++,usable_tables,sargables); + add_key_fields(join, key_fields, and_level, li++, usable_tables, + sargables); Item *item; while ((item=li++)) { KEY_FIELD *start_key_fields= *key_fields; (*and_level)++; - add_key_fields(key_fields,and_level,item,usable_tables,sargables); + add_key_fields(join, key_fields, and_level, item, usable_tables, + sargables); *key_fields=merge_key_fields(org_key_fields,start_key_fields, *key_fields,++(*and_level)); } } return; } - /* If item is of type 'field op field/constant' add it to key_fields */ + /* + Subquery optimization: check if the encountered condition is one + added by condition push down into subquery. + */ + { + if (cond->type() == Item::FUNC_ITEM && + ((Item_func*)cond)->functype() == Item_func::TRIG_COND_FUNC) + { + cond= ((Item_func*)cond)->arguments()[0]; + if (!join->group_list && !join->order && + join->unit->item && + join->unit->item->substype() == Item_subselect::IN_SUBS && + !join->unit->first_select()->next_select()) + { + KEY_FIELD *save= *key_fields; + add_key_fields(join, key_fields, and_level, cond, usable_tables, + sargables); + // Indicate that this ref access candidate is for subquery lookup: + for (; save != *key_fields; save++) + save->outer_ref= TRUE; + } + return; + } + } + + /* If item is of type 'field op field/constant' add it to key_fields */ if (cond->type() != Item::FUNC_ITEM) return; Item_func *cond_func= (Item_func*) cond; @@ -3114,6 +3175,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field) keyuse.used_tables=key_field->val->used_tables(); keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL; keyuse.null_rejecting= key_field->null_rejecting; + keyuse.outer_ref= key_field->outer_ref; VOID(insert_dynamic(keyuse_array,(gptr) &keyuse)); } } @@ -3236,7 +3298,7 @@ sort_keyuse(KEYUSE *a,KEYUSE *b) Here we can add 'ref' access candidates for t1 and t2, but not for t3. */ -static void add_key_fields_for_nj(TABLE_LIST *nested_join_table, +static void add_key_fields_for_nj(JOIN *join, TABLE_LIST *nested_join_table, KEY_FIELD **end, uint *and_level, SARGABLE_PARAM **sargables) { @@ -3248,12 +3310,13 @@ static void add_key_fields_for_nj(TABLE_LIST *nested_join_table, while ((table= li++)) { if (table->nested_join) - add_key_fields_for_nj(table, end, and_level, sargables); + add_key_fields_for_nj(join, table, end, and_level, sargables); else if (!table->on_expr) tables |= table->table->map; } - add_key_fields(end, and_level, nested_join_table->on_expr, tables, sargables); + add_key_fields(join, end, and_level, nested_join_table->on_expr, tables, + sargables); } @@ -3328,7 +3391,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, return TRUE; if (cond) { - add_key_fields(&end,&and_level,cond,normal_tables,sargables); + add_key_fields(join_tab->join, &end, &and_level, cond, normal_tables, + sargables); for (; field != end ; field++) { add_key_part(keyuse,field); @@ -3350,8 +3414,9 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, into account as well. */ if (*join_tab[i].on_expr_ref) - add_key_fields(&end,&and_level,*join_tab[i].on_expr_ref, - join_tab[i].table->map,sargables); + add_key_fields(join_tab->join, &end, &and_level, + *join_tab[i].on_expr_ref, + join_tab[i].table->map, sargables); } /* Process ON conditions for the nested joins */ @@ -3361,7 +3426,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, while ((table= li++)) { if (table->nested_join) - add_key_fields_for_nj(table, &end, &and_level, sargables); + add_key_fields_for_nj(join_tab->join, table, &end, &and_level, + sargables); } } @@ -6257,7 +6323,7 @@ void JOIN::cleanup(bool full) if (tables > const_tables) // Test for not-const tables { free_io_cache(table[const_tables]); - filesort_free_buffers(table[const_tables]); + filesort_free_buffers(table[const_tables],full); } if (full) @@ -9491,7 +9557,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, bool maybe_null=(*cur_group->item)->maybe_null; key_part_info->null_bit=0; key_part_info->field= field; - key_part_info->offset= field->offset(); + key_part_info->offset= field->offset(table->record[0]); key_part_info->length= (uint16) field->key_length(); key_part_info->type= (uint8) field->key_type(); key_part_info->key_type = @@ -9588,7 +9654,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, { key_part_info->null_bit=0; key_part_info->field= *reg_field; - key_part_info->offset= (*reg_field)->offset(); + key_part_info->offset= (*reg_field)->offset(table->record[0]); key_part_info->length= (uint16) (*reg_field)->pack_length(); key_part_info->type= (uint8) (*reg_field)->key_type(); key_part_info->key_type = @@ -10180,7 +10246,7 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) if (join->result->send_eof()) rc= 1; // Don't send error } - DBUG_PRINT("info",("%ld records output",join->send_records)); + DBUG_PRINT("info",("%ld records output", (long) join->send_records)); } else rc= -1; @@ -11012,6 +11078,13 @@ join_init_quick_read_record(JOIN_TAB *tab) } +int rr_sequential(READ_RECORD *info); +int init_read_record_seq(JOIN_TAB *tab) +{ + tab->read_record.read_record= rr_sequential; + return tab->read_record.file->ha_rnd_init(1); +} + static int test_if_quick_select(JOIN_TAB *tab) { @@ -11141,7 +11214,7 @@ join_ft_read_next(READ_RECORD *info) Reading of key with key reference and one part that may be NULL */ -static int +int join_read_always_key_or_null(JOIN_TAB *tab) { int res; @@ -11157,7 +11230,7 @@ join_read_always_key_or_null(JOIN_TAB *tab) } -static int +int join_read_next_same_or_null(READ_RECORD *info) { int error; @@ -12427,6 +12500,7 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, /* Fill schema tables with data before filesort if it's necessary */ if ((join->select_lex->options & OPTION_SCHEMA_TABLE) && + !thd->lex->describe && get_schema_tables_result(join)) goto err; @@ -12560,8 +12634,9 @@ remove_duplicates(JOIN *join, TABLE *entry,List<Item> &fields, Item *having) DBUG_RETURN(0); } Field **first_field=entry->field+entry->s->fields - field_count; - offset= field_count ? - entry->field[entry->s->fields - field_count]->offset() : 0; + offset= (field_count ? + entry->field[entry->s->fields - field_count]-> + offset(entry->record[0]) : 0); reclength=entry->s->reclength-offset; free_io_cache(entry); // Safety @@ -13761,9 +13836,16 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param, if (real_pos->type() == Item::FIELD_ITEM) { Item_field *item; - pos= real_pos; - if (!(item= new Item_field(thd, ((Item_field*) pos)))) + if (!(item= new Item_field(thd, ((Item_field*) real_pos)))) goto err; + if (pos->type() == Item::REF_ITEM) + { + /* preserve the names of the ref when dereferncing */ + Item_ref *ref= (Item_ref *) pos; + item->db_name= ref->db_name; + item->table_name= ref->table_name; + item->name= ref->name; + } pos= item; if (item->field->flags & BLOB_FLAG) { |