diff options
Diffstat (limited to 'sql/sql_select.cc')
| -rw-r--r-- | sql/sql_select.cc | 123 |
1 files changed, 106 insertions, 17 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f224f333a98..52f09b13311 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -689,6 +689,24 @@ JOIN::optimize() DBUG_PRINT("info",("Select tables optimized away")); zero_result_cause= "Select tables optimized away"; tables_list= 0; // All tables resolved + /* + Extract all table-independent conditions and replace the WHERE + clause with them. All other conditions were computed by opt_sum_query + and the MIN/MAX/COUNT function(s) have been replaced by constants, + so there is no need to compute the whole WHERE clause again. + Notice that make_cond_for_table() will always succeed to remove all + computed conditions, because opt_sum_query() is applicable only to + conjunctions. + */ + if (conds) + { + COND *table_independent_conds= + make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0); + DBUG_EXECUTE("where", + print_where(table_independent_conds, + "where after opt_sum_query()");); + conds= table_independent_conds; + } } } if (!tables_list) @@ -1067,6 +1085,23 @@ JOIN::optimize() { need_tmp=1; simple_order=simple_group=0; // Force tmp table without sort } + if (order) + { + /* + Force using of tmp table if sorting by a SP or UDF function due to + their expensive and probably non-deterministic nature. + */ + for (ORDER *tmp_order= order; tmp_order ; tmp_order=tmp_order->next) + { + Item *item= *tmp_order->item; + if (item->walk(&Item::is_expensive_processor,(byte*)0)) + { + /* Force tmp table without sort */ + need_tmp=1; simple_order=simple_group=0; + break; + } + } + } } tmp_having= having; @@ -1212,6 +1247,11 @@ int JOIN::reinit() { DBUG_ENTER("JOIN::reinit"); + + unit->offset_limit_cnt= (ha_rows)(select_lex->offset_limit ? + select_lex->offset_limit->val_uint() : + ULL(0)); + first_record= 0; if (exec_tmp_table1) @@ -2492,8 +2532,11 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end, /* field = expression OR field IS NULL */ old->level= and_level; old->optimize= KEY_OPTIMIZE_REF_OR_NULL; - /* Remember the NOT NULL value */ - if (old->val->is_null()) + /* + Remember the NOT NULL value unless the value does not depend + on other tables. + */ + if (!old->val->used_tables() && old->val->is_null()) old->val= new_fields->val; /* The referred expression can be NULL: */ old->null_rejecting= 0; @@ -5520,6 +5563,7 @@ make_join_readinfo(JOIN *join, uint options) { uint i; bool statistics= test(!(join->select_options & SELECT_DESCRIBE)); + bool ordered_set= 0; bool sorted= 1; DBUG_ENTER("make_join_readinfo"); @@ -5530,6 +5574,22 @@ make_join_readinfo(JOIN *join, uint options) tab->read_record.table= table; tab->read_record.file=table->file; tab->next_select=sub_select; /* normal select */ + + /* + Determine if the set is already ordered for ORDER BY, so it can + disable join cache because it will change the ordering of the results. + Code handles sort table that is at any location (not only first after + the const tables) despite the fact that it's currently prohibited. + */ + if (!ordered_set && + (table == join->sort_by_table && + (!join->order || join->skip_sort_order || + test_if_skip_sort_order(tab, join->order, join->select_limit, + 1)) + ) || + (join->sort_by_table == (TABLE *) 1 && i != join->const_tables)) + ordered_set= 1; + tab->sorted= sorted; sorted= 0; // only first must be sorted switch (tab->type) { @@ -5602,10 +5662,11 @@ make_join_readinfo(JOIN *join, uint options) case JT_ALL: /* If previous table use cache + If the incoming data set is already sorted don't use cache. */ table->status=STATUS_NO_RECORD; if (i != join->const_tables && !(options & SELECT_NO_JOIN_CACHE) && - tab->use_quick != 2 && !tab->first_inner) + tab->use_quick != 2 && !tab->first_inner && !ordered_set) { if ((options & SELECT_DESCRIBE) || !join_init_cache(join->thd,join->join_tab+join->const_tables, @@ -6192,10 +6253,16 @@ return_zero_rows(JOIN *join, select_result *result,TABLE_LIST *tables, DBUG_RETURN(0); } - +/* + used only in JOIN::clear +*/ static void clear_tables(JOIN *join) { - for (uint i=0 ; i < join->tables ; i++) + /* + must clear only the non-const tables, as const tables + are not re-calculated. + */ + for (uint i=join->const_tables ; i < join->tables ; i++) mark_as_null_row(join->table[i]); // All fields are NULL } @@ -8056,7 +8123,12 @@ Field *create_tmp_field_from_field(THD *thd, Field *org_field, { Field *new_field; - if (convert_blob_length && (org_field->flags & BLOB_FLAG)) + /* + Make sure that the blob fits into a Field_varstring which has + 2-byte lenght. + */ + if (convert_blob_length && convert_blob_length < UINT_MAX16 && + (org_field->flags & BLOB_FLAG)) new_field= new Field_varstring(convert_blob_length, org_field->maybe_null(), org_field->field_name, table->s, @@ -8121,8 +8193,13 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table, item->name, item->decimals); break; case INT_RESULT: - new_field= new Field_longlong(item->max_length, maybe_null, - item->name, item->unsigned_flag); + /* Select an integer type with the minimal fit precision */ + if (item->max_length > 11) + new_field=new Field_longlong(item->max_length, maybe_null, + item->name, item->unsigned_flag); + else + new_field=new Field_long(item->max_length, maybe_null, + item->name, item->unsigned_flag); break; case STRING_RESULT: DBUG_ASSERT(item->collation.collation); @@ -8135,8 +8212,13 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table, if ((type= item->field_type()) == MYSQL_TYPE_DATETIME || type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE) new_field= item->tmp_table_field_from_field_type(table, 1); + /* + Make sure that the blob fits into a Field_varstring which has + 2-byte lenght. + */ else if (item->max_length/item->collation.collation->mbmaxlen > 255 && - convert_blob_length) + item->max_length/item->collation.collation->mbmaxlen < UINT_MAX16 + && convert_blob_length) new_field= new Field_varstring(convert_blob_length, maybe_null, item->name, table->s, item->collation.collation); @@ -10710,8 +10792,13 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), { if (!join->first_record) { + List_iterator_fast<Item> it(*join->fields); + Item *item; /* No matching rows for group function */ join->clear(); + + while ((item= it++)) + item->no_rows_in_result(); } if (join->having && join->having->val_int() == 0) error= -1; // Didn't satisfy having @@ -12881,7 +12968,7 @@ count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields, { if (! field->const_item()) { - Item_sum *sum_item=(Item_sum*) field; + Item_sum *sum_item=(Item_sum*) field->real_item(); if (!sum_item->quick_group) param->quick_group=0; // UDF SUM function param->sum_func_count++; @@ -13141,10 +13228,11 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param, param->copy_funcs.empty(); for (i= 0; (pos= li++); i++) { - if (pos->real_item()->type() == Item::FIELD_ITEM) + Item *real_pos= pos->real_item(); + if (real_pos->type() == Item::FIELD_ITEM) { Item_field *item; - pos= pos->real_item(); + pos= real_pos; if (!(item= new Item_field(thd, ((Item_field*) pos)))) goto err; pos= item; @@ -13183,12 +13271,13 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param, } } } - else if ((pos->type() == Item::FUNC_ITEM || - pos->type() == Item::SUBSELECT_ITEM || - pos->type() == Item::CACHE_ITEM || - pos->type() == Item::COND_ITEM) && - !pos->with_sum_func) + else if ((real_pos->type() == Item::FUNC_ITEM || + real_pos->type() == Item::SUBSELECT_ITEM || + real_pos->type() == Item::CACHE_ITEM || + real_pos->type() == Item::COND_ITEM) && + !real_pos->with_sum_func) { // Save for send fields + pos= real_pos; /* TODO: In most cases this result will be sent to the user. This should be changed to use copy_int or copy_real depending |