From c47c56452680e1bbdaa9d8ed4203470cf7f02c56 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 31 May 2003 18:31:57 +0300 Subject: Fix for the optimiser problem caused by the fact that with derived tables one (or more tables) is opened / closed twice. --- mysql-test/r/derived.result | 2 +- sql/sql_derived.cc | 2 ++ 2 files changed, 3 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index bfd4c544131..52a54ac7773 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -137,7 +137,7 @@ a t explain select count(*) from t1 as tt1, (select * from t1) as tt2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -2 DERIVED tt1 index NULL a 4 NULL 10000 Using index +2 DERIVED tt1 ALL NULL NULL NULL NULL 10000 drop table t1; SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b; (SELECT * FROM (SELECT 1 as a) as a ) diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 7f555f37d40..66b8048996f 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -219,6 +219,8 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, /* Add new temporary table to list of open derived tables */ table->next= thd->derived_tables; thd->derived_tables= table; + thd->query_id++; + query_id++; } exit: -- cgit v1.2.1 From 4ac20ad4a925fe9efa4f0937ddeb026cf112857f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 12 Jun 2003 16:52:36 +0300 Subject: Derived tables bug fix ....... sql/ha_myisam.cc: DISABLE KEYS warnings sql/sql_table.cc: DISABLE KEYS warnings --- mysql-test/r/innodb.result | 10 +++++----- sql/ha_myisam.cc | 12 ++++++++++++ sql/sql_base.cc | 4 +++- sql/sql_derived.cc | 12 ++++++++++-- sql/sql_table.cc | 17 +++++++++++++---- sql/table.h | 1 + 6 files changed, 44 insertions(+), 12 deletions(-) diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 4fb6efe6a14..8409ca59784 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1322,10 +1322,10 @@ SELECT t2.id, t1.label FROM t2 INNER JOIN (SELECT t1.id_object as id_object FROM t1 WHERE t1.label LIKE '%test%') AS lbl ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); id label -3382 Fournisseur Test -102 Fournisseur Test -1794 Fournisseur Test -1822 Fournisseur Test -3524 Fournisseur Test +3382 Test +102 Le Pekin (Test) +1794 Test de resto +1822 Test 3 +3524 Societe Test 3525 Fournisseur Test drop table t1,t2; diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc index a9c3ddaef60..3f660abea80 100644 --- a/sql/ha_myisam.cc +++ b/sql/ha_myisam.cc @@ -701,12 +701,20 @@ int ha_myisam::repair(THD *thd, MI_CHECK ¶m, bool optimize) void ha_myisam::deactivate_non_unique_index(ha_rows rows) { MYISAM_SHARE* share = file->s; + bool do_warning=0; if (share->state.key_map == ((ulonglong) 1L << share->base.keys)-1) { if (!(specialflag & SPECIAL_SAFE_MODE)) { if (rows == HA_POS_ERROR) + { + uint orig_update= file->update; + file->update ^= HA_STATE_CHANGED; + uint check_update= file->update; mi_extra(file, HA_EXTRA_NO_KEYS, 0); + do_warning= (file->update == check_update) && file->state->records; + file->update= orig_update; + } else { /* @@ -731,6 +739,10 @@ void ha_myisam::deactivate_non_unique_index(ha_rows rows) } else enable_activate_all_index=0; + if (do_warning) + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA, + ER(ER_ILLEGAL_HA), table->table_name); } diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 1ef4e9df020..6ee423d4ed7 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -789,6 +789,7 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name, DBUG_RETURN(0); } table->query_id=thd->query_id; + table->clear_query_id=1; thd->tmp_table_used= 1; goto reset; } @@ -2050,8 +2051,9 @@ bool setup_tables(TABLE_LIST *tables) table->keys_in_use_for_query &= ~map; } table->used_keys &= table->keys_in_use_for_query; - if (table_list->shared) + if (table_list->shared || table->clear_query_id) { + table->clear_query_id= 0; /* Clear query_id that may have been set by previous select */ for (Field **ptr=table->field ; *ptr ; ptr++) (*ptr)->query_id=0; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index b2a055b3ee5..1cb0ea2ea5f 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -122,6 +122,16 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, res= -1; goto exit; } + + /* + This is done in order to redo all field optimisations when any of the + involved tables is used in the outer query + */ + if (tables) + { + for (TABLE_LIST *cursor= tables; cursor; cursor= cursor->next) + cursor->table->clear_query_id= 1; + } item_list= select_cursor->item_list; select_cursor->with_wild= 0; @@ -223,8 +233,6 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, /* Add new temporary table to list of open derived tables */ table->next= thd->derived_tables; thd->derived_tables= table; - thd->query_id++; - query_id++; } exit: diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 2b329aac305..60e034302a0 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -1835,10 +1835,19 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, /* COND_refresh will be signaled in close_thread_tables() */ break; case DISABLE: - VOID(pthread_mutex_lock(&LOCK_open)); - wait_while_table_is_used(thd, table); - VOID(pthread_mutex_unlock(&LOCK_open)); - table->file->deactivate_non_unique_index(HA_POS_ERROR); + if (table->db_type == DB_TYPE_MYISAM) + { + VOID(pthread_mutex_lock(&LOCK_open)); + wait_while_table_is_used(thd, table); + VOID(pthread_mutex_unlock(&LOCK_open)); + table->file->deactivate_non_unique_index(HA_POS_ERROR); + } + else + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_ILLEGAL_HA, + ER(ER_ILLEGAL_HA), table->table_name); + break; + /* COND_refresh will be signaled in close_thread_tables() */ break; } diff --git a/sql/table.h b/sql/table.h index 2aefe23cb2f..c9f35887a27 100644 --- a/sql/table.h +++ b/sql/table.h @@ -116,6 +116,7 @@ struct st_table { my_bool crashed; my_bool is_view; my_bool no_keyread; + my_bool clear_query_id; /* To reset query_id for tables and cols */ Field *next_number_field, /* Set if next_number is activated */ *found_next_number_field, /* Set on open */ *rowid_field; -- cgit v1.2.1 From 8f5bdb0000342c0b5380a4b754413a364f42a433 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 25 Jun 2003 18:34:46 +0300 Subject: code cleanup --- sql/sql_union.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/sql_union.cc b/sql/sql_union.cc index f47900d8276..35d133789d4 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -136,7 +136,7 @@ int st_select_lex_unit::prepare(THD *thd, select_result *sel_result, found_rows_for_union= first_select()->options & OPTION_FOUND_ROWS && global_parameters->select_limit; if (found_rows_for_union) - first_select()->options ^= OPTION_FOUND_ROWS; + first_select()->options&= ~OPTION_FOUND_ROWS; } if (t_and_f) { -- cgit v1.2.1 From b5e85696741e02ab33432bdec254edd802839fa1 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 6 Aug 2003 18:48:34 +0300 Subject: sql_union.cc, sql_select.cc: Code clean-up sql_union.cc, union.test, union.result: A fix for a bug #978. This enables that NULL's can be entered into UNION's result set, although first SELECT columns are NOT NULL. This is also a start of fixing UNION's properly regarding type acceptance. sql_select.cc: A commit for my second July SPRINT task mysql-test/r/union.result: A fix for a bug #978. This enables that NULL's can be entered into UNION's result set, although first SELECT columns are NOT NULL. This is also a start of fixing UNION's properly regarding type acceptance. mysql-test/t/union.test: A fix for a bug #978. This enables that NULL's can be entered into UNION's result set, although first SELECT columns are NOT NULL. This is also a start of fixing UNION's properly regarding type acceptance. sql/sql_select.cc: Code clean-up sql/sql_union.cc: Code clean-up --- mysql-test/r/union.result | 13 +++++++++++++ mysql-test/t/union.test | 9 +++++++++ sql/sql_union.cc | 16 ++++++---------- 3 files changed, 28 insertions(+), 10 deletions(-) diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 5b7b26bc1bb..6d8bd263546 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -328,3 +328,16 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 UNION t1 ref b b 5 const 1 Using where drop table t1,t2; +create table t1 ( id int not null auto_increment, primary key (id) ,user_name text ); +create table t2 ( id int not null auto_increment, primary key (id) ,group_name text ); +create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references users(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references groups(id) ); +insert into t1 (user_name) values ('Tester'); +insert into t2 (group_name) values ('Group A'); +insert into t2 (group_name) values ('Group B'); +insert into t3 (user_id, group_id) values (1,1); +select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c; +is_in_group user_name group_name id +1 Tester Group A 1 +0 Tester Group A NULL +0 Tester Group B NULL +drop table t1, t2, t3; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 6d857a4b40f..aeea27ade0f 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -209,3 +209,12 @@ explain (select * from t1 where a=1) union (select * from t2 where a=1); explain (select * from t1 where a=1 and b=10) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a); explain (select * from t1 where a=1) union (select * from t1 where b=1); drop table t1,t2; +create table t1 ( id int not null auto_increment, primary key (id) ,user_name text ); +create table t2 ( id int not null auto_increment, primary key (id) ,group_name text ); +create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references users(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references groups(id) ); +insert into t1 (user_name) values ('Tester'); +insert into t2 (group_name) values ('Group A'); +insert into t2 (group_name) values ('Group B'); +insert into t3 (user_id, group_id) values (1,1); +select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c; +drop table t1, t2, t3; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index bd97a68e2d3..fae6810bab0 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -154,8 +154,6 @@ int st_select_lex_unit::prepare(THD *thd, select_result *sel_result, goto err; List_iterator it(select_cursor->item_list); Item *item; - while((item=it++)) - item->maybe_null=1; item_list= select_cursor->item_list; select_cursor->with_wild= 0; if (setup_ref_array(thd, &select_cursor->ref_pointer_array, @@ -166,6 +164,12 @@ int st_select_lex_unit::prepare(THD *thd, select_result *sel_result, item_list, 0, 0, 1)) goto err; t_and_f= 1; + while((item=it++)) + { + item->maybe_null=1; + if (item->type() == Item::FIELD_ITEM) + ((class Item_field *)item)->field->table->maybe_null=1; + } } tmp_table_param.field_count=item_list.elements; @@ -249,7 +253,6 @@ err: int st_select_lex_unit::exec() { - int do_print_slow= 0; SELECT_LEX_NODE *lex_select_save= thd->lex.current_select; SELECT_LEX *select_cursor=first_select_in_union(); DBUG_ENTER("st_select_lex_unit::exec"); @@ -317,7 +320,6 @@ int st_select_lex_unit::exec() thd->lex.current_select= lex_select_save; DBUG_RETURN(res); } - do_print_slow|= select_cursor->options; } } optimized= 1; @@ -360,12 +362,6 @@ int st_select_lex_unit::exec() Mark for slow query log if any of the union parts didn't use indexes efficiently */ - select_cursor->options= ((select_cursor->options & - ~(QUERY_NO_INDEX_USED | - QUERY_NO_GOOD_INDEX_USED)) | - do_print_slow & - (QUERY_NO_INDEX_USED | - QUERY_NO_GOOD_INDEX_USED)); } } thd->lex.current_select= lex_select_save; -- cgit v1.2.1