From ad5c763e74821465cc8237aa39e2391239a17206 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 16 Jun 2003 23:05:45 +0200 Subject: fulltext and left join bug fixed mysql-test/t/fulltext.test: explain select fulltext test mysql-test/t/fulltext_left_join.test: explain select fulltext test --- sql/sql_select.cc | 3 +++ 1 file changed, 3 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ff6fde1ca0c..0e8b191e4ef 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1688,6 +1688,9 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array, if (!cond_func || cond_func->key == NO_SUCH_KEY) return; + if (!(usable_tables & cond_func->table->map)) + return; + KEYUSE keyuse; keyuse.table= cond_func->table; -- cgit v1.2.1 From 1688ada0e2ce3bf5e62f1539811f0cfb0d8bc4b6 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 21 Jun 2003 16:59:40 +0500 Subject: Proposed fix for #674 This crash happens in rather exotic case when we try to run SELECT DISTINCT some_func(SUM(some_field)) GROUP BY another_field; on a table with single row. Optimizer marks this table as const, sets group=NULL (with remove_const) thus, create_tmp_table makes mistake collecting columns for temporary table and then crashes because the field_count gets less than hidden_columns_count. sql/sql_select.cc: There's several ways to fix this bug. This one looks easy and correct to me --- sql/sql_select.cc | 3 +++ 1 file changed, 3 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0e8b191e4ef..cb056100e2a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -453,6 +453,9 @@ mysql_select(THD *thd,TABLE_LIST *tables,List &fields,COND *conds, goto err; thd->proc_info="preparing"; + + select_distinct= select_distinct && (join.const_tables != join.tables); + if (result->initialize_tables(&join)) goto err; if (join.const_table_map != join.found_const_table_map && -- cgit v1.2.1 From 68524c81feac77d7d0e8dca716738b5b59d98e3f Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 23 Jun 2003 20:05:54 +0300 Subject: Simple code cleanup mysql-test/r/innodb.result: Fixed test case after innodb optimize->analyze fix mysys/mf_pack.c: Added comments sql/log_event.cc: Fixed usage of fn_format() sql/slave.cc: Indentation fixes and comments cleanup sql/sql_repl.cc: Comment cleanup --- sql/sql_select.cc | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0e8b191e4ef..67bec77e245 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1627,6 +1627,9 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field) key_field->field->table->reginfo.not_exists_optimize=1; } + +#define FT_KEYPART (MAX_REF_PARTS+10) + static void add_ft_keys(DYNAMIC_ARRAY *keyuse_array, JOIN_TAB *stat,COND *cond,table_map usable_tables) @@ -1685,23 +1688,20 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array, } } - if (!cond_func || cond_func->key == NO_SUCH_KEY) - return; - - if (!(usable_tables & cond_func->table->map)) + if ((!cond_func || cond_func->key == NO_SUCH_KEY) || + (!(usable_tables & cond_func->table->map))) return; KEYUSE keyuse; - keyuse.table= cond_func->table; keyuse.val = cond_func; keyuse.key = cond_func->key; -#define FT_KEYPART (MAX_REF_PARTS+10) - keyuse.keypart=FT_KEYPART; + keyuse.keypart= FT_KEYPART; keyuse.used_tables=cond_func->key_item()->used_tables(); VOID(insert_dynamic(keyuse_array,(gptr) &keyuse)); } + static int sort_keyuse(KEYUSE *a,KEYUSE *b) { -- cgit v1.2.1 From 2cc81ba3d32015ae89ef43cb88adbf542ba0eb49 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 25 Jun 2003 18:36:52 +0300 Subject: Fix for a bug which results in "Unknown Error". Query should actually return empty result set, but currently can't, as it attempts to store NULL in NOT NULL field, because that is a match made by self join conditions. --- sql/sql_select.cc | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0e8b191e4ef..c2c4070956c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -969,7 +969,14 @@ mysql_select(THD *thd,TABLE_LIST *tables,List &fields,COND *conds, group ? group : order, select_limit, thd->select_limit)) - goto err; /* purecov: inspected */ + { + if (!join.join_tab[join.const_tables].select->quick) + error= return_zero_rows(&join, result, tables, fields, + 0, select_options, + "Impossible WHERE noticed after reading const tables", + having,procedure); + goto err; + } } join.having=having; // Actually a parameter thd->proc_info="Sending data"; -- cgit v1.2.1 From c2cb9b97be721d07e9cb2a27076c8beaf22f304b Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 27 Jun 2003 03:04:54 +0300 Subject: Added thread variable max_seeks_for_key Change optimizer to prefer key lookups before table scan Change table scans to be done after tables with constrains on scanned table mysql-test/r/distinct.result: Update results mysql-test/r/group_by.result: Update results mysql-test/r/heap.result: Update results mysql-test/r/join.result: Update results mysql-test/r/key_diff.result: Update results mysql-test/r/myisam.result: Update results mysql-test/r/order_by.result: Update results mysql-test/r/select_safe.result: Update results mysql-test/t/distinct.test: Change test to be repeatable mysql-test/t/join.test: Change test to be repeatable mysql-test/t/select_safe.test: Add tests for MAX_SEEKS_FOR_KEY sql/handler.h: Optimize structure for 64 bit machines (and to avoid problems with gdb) sql/item_cmpfunc.cc: Initialize not_null_tables_cache properly sql/mysqld.cc: Added max_seeks_for_key sql/set_var.cc: Added max_seeks_for_key sql/sql_class.h: Added max_seeks_for_key sql/sql_select.cc: Added max_seeks_for_key Change optimizer to prefer key lookups before table scan. Change table scans to be done after tables with constrains on scanned table --- sql/sql_select.cc | 41 +++++++++++++++++++++++++++++------------ 1 file changed, 29 insertions(+), 12 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 67bec77e245..f4c7a2ab031 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1271,14 +1271,14 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, /* Set a max range of how many seeks we can expect when using keys - This was (s->read_time*5), but this was too low with small rows + This is can't be to high as otherwise we are likely to use + table scan. */ - s->worst_seeks= (double) s->found_records / 5; + s->worst_seeks= min((double) s->found_records / 10, + (double) s->read_time*3); if (s->worst_seeks < 2.0) // Fix for small tables s->worst_seeks=2.0; - /* if (s->type == JT_EQ_REF) - continue; */ if (s->const_keys) { ha_rows records; @@ -1884,6 +1884,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, best=best_time=records=DBL_MAX; KEYUSE *best_key=0; uint best_max_key_part=0; + my_bool found_constrain= 0; if (s->keyuse) { /* Use key if possible */ @@ -1964,6 +1965,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, } else { + found_constrain= 1; /* Check if we found full key */ @@ -2000,16 +2002,18 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, records=2.0; // Can't be as good as a unique } } + /* Limit the number of matched rows */ + tmp= records; + set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key); if (table->used_keys & ((key_map) 1 << key)) { /* we can use only index tree */ uint keys_per_block= table->file->block_size/2/ (keyinfo->key_length+table->file->ref_length)+1; - tmp=(record_count*(records+keys_per_block-1)/ - keys_per_block); + tmp=record_count*(tmp+keys_per_block-1)/keys_per_block; } else - tmp=record_count*min(records,s->worst_seeks); + tmp=record_count*min(tmp,s->worst_seeks); } } else @@ -2039,7 +2043,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, { /* Assume that the first key part matches 1% of the file - and that the hole key matches 10 (dupplicates) or 1 + and that the hole key matches 10 (duplicates) or 1 (unique) records. Assume also that more key matches proportionally more records @@ -2071,6 +2075,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, records=(ulong) tmp; } } + /* Limit the number of matched rows */ + set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key); if (table->used_keys & ((key_map) 1 << key)) { /* we can use only index tree */ @@ -2113,20 +2119,31 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, s->table->used_keys && best_key) && !(s->table->force_index && best_key)) { // Check full join + ha_rows rnd_records= s->found_records; if (s->on_expr) { - tmp=rows2double(s->found_records); // Can't use read cache + tmp=rows2double(rnd_records); // Can't use read cache } else { tmp=(double) s->read_time; - /* Calculate time to read through cache */ + /* Calculate time to read previous rows through cache */ tmp*=(1.0+floor((double) cache_record_length(join,idx)* record_count / (double) thd->variables.join_buff_size)); } + + /* + If there is a restriction on the table, assume that 25% of the + rows can be skipped on next part. + This is to force tables that this table depends on before this + table + */ + if (found_constrain) + rnd_records-= rnd_records/4; + if (best == DBL_MAX || - (tmp + record_count/(double) TIME_FOR_COMPARE*s->found_records < + (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records < best + record_count/(double) TIME_FOR_COMPARE*records)) { /* @@ -2134,7 +2151,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, will ensure that this will be used */ best=tmp; - records= rows2double(s->found_records); + records= rows2double(rnd_records); best_key=0; } } -- cgit v1.2.1 From caddb5ea53cbde18bf0a915bc4867fecd93c55d5 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 29 Jun 2003 21:15:51 +0300 Subject: Fixed 'Unknown error' when doing ORDER BY on reference table which was used with NULL value on NOT NULL column. (Bug #479) mysql-test/r/order_by.result: Update of results mysql-test/t/order_by.test: Test of new code sql/sql_select.cc: Fixed problem with lookup on NULL --- sql/sql_select.cc | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cafafee243e..b9e9c0a14fa 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5326,11 +5326,23 @@ create_sort_index(JOIN_TAB *tab,ORDER *order,ha_rows select_limit) can use. */ if (!(select->quick=get_ft_or_quick_select_for_ref(table, tab))) - goto err; + { + if (current_thd->fatal_error) + goto err; // End of memory + /* + Impossible range (for example lookup on NULL on not null field) + Create empty result set + */ + if (!(table->record_pointers= my_malloc(1, MYF(MY_WME)))) + goto err; + table->found_records= 0; + goto end; + } } } table->found_records=filesort(&table,sortorder,length, select, 0L, select_limit, &examined_rows); +end: delete select; // filesort did select tab->select=0; tab->select_cond=0; -- cgit v1.2.1 From 12d879c912543fffa77cecfda799bc20ddd35df5 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 00:10:47 +0300 Subject: Lot's of clean-ups and fixes for 4.0.14. include/my_global.h: CONFIG_SMP mysql-test/r/grant.result: Test cases for bug fixes mysql-test/r/insert_select.result: Test cases for bug fixes mysql-test/r/union.result: Test cases for bug fixes mysql-test/t/grant.test: Test cases for bug fixes mysql-test/t/insert_select.test: Test cases for bug fixes mysql-test/t/union.test: Test cases for bug fixes sql/sql_acl.cc: Fix for granting global privileges on db level sql/sql_parse.cc: Fix for INSERT... SELECT sql/sql_select.cc: removing a fix sql/sql_union.cc: P --- sql/sql_select.cc | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6c2d06051d4..eb29946a3d7 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -974,11 +974,7 @@ mysql_select(THD *thd,TABLE_LIST *tables,List &fields,COND *conds, thd->select_limit)) { if (!join.join_tab[join.const_tables].select->quick) - error= return_zero_rows(&join, result, tables, fields, - 0, select_options, - "Impossible WHERE noticed after reading const tables", - having,procedure); - goto err; + goto err; } } join.having=having; // Actually a parameter -- cgit v1.2.1 From d433fe5580dafbc1fba0ebe9dc76217d1b0e5144 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 10:42:13 +0500 Subject: Proposed bugfix for #717 Current table is placed into read_tables set of the current join_tab->select so get_mm_parts function thinks that current table's record is read and tries to calculate WHERE condition with the fields of the record. Result of these calculations is unpredictable. Looks funny - outcome of the SELECT depends on the queries executed before. Anyway i think we should have testcase on this part of the code. mysql-test/r/sel000001.result: appropriate result added mysql-test/t/sel000001.test: testcase added sql/sql_select.cc: I think we should count current table out of read_tables set --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5b5972be384..bde5392d299 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2683,7 +2683,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) (select->quick && (select->quick->records >= 100L)))) ? 2 : 1; - sel->read_tables= used_tables; + sel->read_tables= used_tables & ~current_map; } if (i != join->const_tables && tab->use_quick != 2) { /* Read with cache */ -- cgit v1.2.1 From 2848b0bed9998378b10ac181cd7223c5853549d9 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 4 Jul 2003 14:42:57 +0200 Subject: merge + order by (bug #515) temporary fix properly fixed in 4.1 --- sql/sql_select.cc | 2 ++ 1 file changed, 2 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index bde5392d299..7176337dbfd 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5843,6 +5843,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, */ if (!select->quick->reverse_sorted()) { + if (table->file->index_flags(ref_key) & HA_NOT_READ_PREFIX_LAST) + DBUG_RETURN(0); // Use filesort // ORDER BY range_key DESC QUICK_SELECT_DESC *tmp=new QUICK_SELECT_DESC(select->quick, used_key_parts); -- cgit v1.2.1 From 7ff9378a342d6ad11da953de4d92d0e151ff243c Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 7 Jul 2003 16:45:19 +0200 Subject: correct (finally) condition for MATCH >0, >=0 etc in add_ft_keys (SW#1647) still no rec. descent --- sql/sql_select.cc | 18 +++++++++--------- 1 file changed, 9 insertions(+), 9 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7176337dbfd..55f10ab930b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1655,17 +1655,17 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array, { Item_func *arg0=(Item_func *)(func->arguments()[0]), *arg1=(Item_func *)(func->arguments()[1]); - if ((functype == Item_func::GE_FUNC || - functype == Item_func::GT_FUNC) && - arg0->type() == Item::FUNC_ITEM && - arg0->functype() == Item_func::FT_FUNC && - arg1->const_item() && arg1->val()>0) + if (arg1->const_item() && + ((functype == Item_func::GE_FUNC && arg1->val()> 0) || + (functype == Item_func::GT_FUNC && arg1->val()>=0)) && + arg0->type() == Item::FUNC_ITEM && + arg0->functype() == Item_func::FT_FUNC) cond_func=(Item_func_match *) arg0; - else if ((functype == Item_func::LE_FUNC || - functype == Item_func::LT_FUNC) && + else if (arg0->const_item() && + ((functype == Item_func::LE_FUNC && arg0->val()> 0) || + (functype == Item_func::LT_FUNC && arg0->val()>=0)) && arg1->type() == Item::FUNC_ITEM && - arg1->functype() == Item_func::FT_FUNC && - arg0->const_item() && arg0->val()>0) + arg1->functype() == Item_func::FT_FUNC) cond_func=(Item_func_match *) arg1; } } -- cgit v1.2.1 From a4c3f0d7271c6d93b13d2b03b640adb624fd494d Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 7 Jul 2003 19:02:58 +0200 Subject: finally proper recursive descent for parsing expressions with MATCH ... AGAINST in add_ft_keys() --- sql/sql_select.cc | 20 +++----------------- 1 file changed, 3 insertions(+), 17 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 55f10ab930b..80e329a7f03 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1676,26 +1676,13 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array, if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) { Item *item; - /* - I'm (Sergei) too lazy to implement proper recursive descent here, - and anyway, nobody will use such a stupid queries - that will require it :-) - May be later... - */ while ((item=li++)) - { - if (item->type() == Item::FUNC_ITEM && - ((Item_func *)item)->functype() == Item_func::FT_FUNC) - { - cond_func=(Item_func_match *)item; - break; - } - } + add_ft_keys(keyuse_array,stat,item,usable_tables); } } - if ((!cond_func || cond_func->key == NO_SUCH_KEY) || - (!(usable_tables & cond_func->table->map))) + if (!cond_func || cond_func->key == NO_SUCH_KEY || + !(usable_tables & cond_func->table->map)) return; KEYUSE keyuse; @@ -1707,7 +1694,6 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array, VOID(insert_dynamic(keyuse_array,(gptr) &keyuse)); } - static int sort_keyuse(KEYUSE *a,KEYUSE *b) { -- cgit v1.2.1 From 1a45c8397258d992bdc526d3463b92ad448a3754 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 11 Jul 2003 14:17:18 +0500 Subject: Fix for the bug #801: create table t1 select x'41'; doesn't work --- sql/sql_select.cc | 1 + 1 file changed, 1 insertion(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 80e329a7f03..4008bf75f01 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3664,6 +3664,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, case Item::STRING_ITEM: case Item::REF_ITEM: case Item::NULL_ITEM: + case Item::VARBIN_ITEM: { bool maybe_null=item->maybe_null; Field *new_field; -- cgit v1.2.1 From 2763b52d0a0ad85818f5c8259b4dd1d060e568e7 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 25 Jul 2003 14:32:03 +0200 Subject: outer join, impossible on condition, where, and usable key for range bug#926 --- sql/sql_select.cc | 4 ---- 1 file changed, 4 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4008bf75f01..6675a310464 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2641,9 +2641,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) join->thd->select_limit < join->best_positions[i].records_read && !(join->select_options & OPTION_FOUND_ROWS))) { - /* Join with outer join condition */ - COND *orig_cond=sel->cond; - sel->cond=and_conds(sel->cond,tab->on_expr); if (sel->test_quick_select(tab->keys, used_tables & ~ current_map, (join->select_options & @@ -2651,7 +2648,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) HA_POS_ERROR : join->thd->select_limit)) < 0) DBUG_RETURN(1); // Impossible range - sel->cond=orig_cond; /* Fix for EXPLAIN */ if (sel->quick) join->best_positions[i].records_read= sel->quick->records; -- cgit v1.2.1