- developers - lists.mariadb.org

[Maria-developers] Rev 2769: Bug#49198 Wrong result for second call of of procedure with view in subselect. in file:///home/psergey/dev/maria-5.3-subqueries-r7/
by Sergey Petrunya 24 Feb '10

24 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r7/ ------------------------------------------------------------ revno: 2769 revision-id: psergey(a)askmonty.org-20100224113342-k558ogyr3cik1767 parent: psergey(a)askmonty.org-20100223092202-sc3huivw9yymnj5d committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r7 timestamp: Wed 2010-02-24 12:33:42 +0100 message: Bug#49198 Wrong result for second call of of procedure with view in subselect. Re-worked fix of Tor Didriksen: The problem was that fix_after_pullout() after semijoin conversion wasn't propagated from the view to the underlying table. On subesequent executions of the prepared statement, we would mark the underlying table as 'dependent' and the predicate anlysis would lead to a different (and illegal) execution plan. === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-02-21 07:53:12 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-02-24 11:33:42 +0000 @@ -824,3 +824,50 @@ 3 2 drop table t1, t2, t3; +# +# Bug#49198 Wrong result for second call of procedure +# with view in subselect. +# +CREATE TABLE t1 (t1field integer, primary key (t1field)); +CREATE TABLE t2 (t2field integer, primary key (t2field)); +CREATE TABLE t3 (t3field integer, primary key (t3field)); +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t2 VALUES(1),(2); +INSERT INTO t3 VALUES(1),(2); +PREPARE stmt FROM +" +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2); +"; +EXECUTE stmt; +t1field +1 +2 +EXECUTE stmt; +t1field +1 +2 +PREPARE stmt FROM +" +EXPLAIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2) + AND t1field IN (SELECT * FROM v3) +"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +DROP TABLE t1, t2, t3; +DROP VIEW v2, v3; +# End of Bug#49198 === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-02-21 07:53:12 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-02-24 11:33:42 +0000 @@ -828,6 +828,53 @@ 3 2 drop table t1, t2, t3; +# +# Bug#49198 Wrong result for second call of procedure +# with view in subselect. +# +CREATE TABLE t1 (t1field integer, primary key (t1field)); +CREATE TABLE t2 (t2field integer, primary key (t2field)); +CREATE TABLE t3 (t3field integer, primary key (t3field)); +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t2 VALUES(1),(2); +INSERT INTO t3 VALUES(1),(2); +PREPARE stmt FROM +" +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2); +"; +EXECUTE stmt; +t1field +1 +2 +EXECUTE stmt; +t1field +1 +2 +PREPARE stmt FROM +" +EXPLAIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2) + AND t1field IN (SELECT * FROM v3) +"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +DROP TABLE t1, t2, t3; +DROP VIEW v2, v3; +# End of Bug#49198 set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2010-02-21 07:53:12 +0000 +++ b/mysql-test/t/subselect_sj.test 2010-02-24 11:33:42 +0000 @@ -728,3 +728,45 @@ drop table t1, t2, t3; +--echo # +--echo # Bug#49198 Wrong result for second call of procedure +--echo # with view in subselect. +--echo # + +CREATE TABLE t1 (t1field integer, primary key (t1field)); +CREATE TABLE t2 (t2field integer, primary key (t2field)); +CREATE TABLE t3 (t3field integer, primary key (t3field)); + +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v3 AS SELECT * FROM t3; + +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t2 VALUES(1),(2); +INSERT INTO t3 VALUES(1),(2); + +PREPARE stmt FROM +" +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2); +"; + +EXECUTE stmt; +EXECUTE stmt; + +PREPARE stmt FROM +" +EXPLAIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2) + AND t1field IN (SELECT * FROM v3) +"; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1, t2, t3; +DROP VIEW v2, v3; + +--echo # End of Bug#49198 === modified file 'sql/item.cc' --- a/sql/item.cc 2010-02-21 06:32:23 +0000 +++ b/sql/item.cc 2010-02-24 11:33:42 +0000 @@ -6491,11 +6491,9 @@ void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr) { + (*ref)->fix_after_pullout(new_parent, ref); if (depended_from == new_parent) - { - (*ref)->fix_after_pullout(new_parent, ref); depended_from= NULL; - } }

1 0

Re: [Maria-developers] [Merge] lp:~mydb08/maria/maria-5.1 into lp:maria
by Venu Kalyan 24 Feb '10

24 Feb '10

Looks like the diff is in-complete.. but you should get the updated one from here.. https://code.launchpad.net/~mydb08/maria/maria-5.1/+merge/20003 On Tue, Feb 23, 2010 at 1:17 PM, Venu Anuganti <mydb08(a)gmail.com> wrote: > Venu Anuganti has proposed merging lp:~mydb08/maria/maria-5.1 into lp:maria. > > Requested reviews: > Maria-captains (maria-captains) > > > Initial SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN db] implementation along with associated Information Schema tables. > > Its been a while (probably 6 yrs+) I pushed any code to MySQL, so review is a must even though am working actively on MySQL code for last few years for developing storage engines. > > Give me the feedback in case if I missed anything. > > Todo: > > - ACL check on global temp tables, need an advice on what priv to use > - InnoDB has some issues on stat(tmp_file) as its not generating the full path, will work on a fix. > > Questions: > > Do we need to expose internal temp tables to this implementation ? I don't see any benefit doing so as it should be really part of slow query implementation. > > -- > https://code.launchpad.net/~mydb08/maria/maria-5.1/+merge/20003 > You are the owner of lp:~mydb08/maria/maria-5.1. > > === modified file 'sql/mysqld.cc' > --- sql/mysqld.cc 2010-02-11 19:15:24 +0000 > +++ sql/mysqld.cc 2010-02-23 21:17:16 +0000 > @@ -3272,6 +3272,7 @@ > {"show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS}, > {"show_table_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]), SHOW_LONG_STATUS}, > {"show_tables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]), SHOW_LONG_STATUS}, > + {"show_temporary_tables",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TEMPORARY_TABLES]), SHOW_LONG_STATUS}, > {"show_triggers", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]), SHOW_LONG_STATUS}, > {"show_variables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]), SHOW_LONG_STATUS}, > {"show_warnings", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_WARNS]), SHOW_LONG_STATUS}, > > === modified file 'sql/sql_lex.h' > --- sql/sql_lex.h 2009-12-03 11:19:05 +0000 > +++ sql/sql_lex.h 2010-02-23 21:17:16 +0000 > @@ -67,7 +67,7 @@ > SQLCOM_UPDATE, SQLCOM_INSERT, SQLCOM_INSERT_SELECT, > SQLCOM_DELETE, SQLCOM_TRUNCATE, SQLCOM_DROP_TABLE, SQLCOM_DROP_INDEX, > > - SQLCOM_SHOW_DATABASES, SQLCOM_SHOW_TABLES, SQLCOM_SHOW_FIELDS, > + SQLCOM_SHOW_DATABASES, SQLCOM_SHOW_TABLES, SQLCOM_SHOW_TEMPORARY_TABLES, SQLCOM_SHOW_FIELDS, > SQLCOM_SHOW_KEYS, SQLCOM_SHOW_VARIABLES, SQLCOM_SHOW_STATUS, > SQLCOM_SHOW_ENGINE_LOGS, SQLCOM_SHOW_ENGINE_STATUS, SQLCOM_SHOW_ENGINE_MUTEX, > SQLCOM_SHOW_PROCESSLIST, SQLCOM_SHOW_MASTER_STAT, SQLCOM_SHOW_SLAVE_STAT, > > === modified file 'sql/sql_parse.cc' > --- sql/sql_parse.cc 2010-01-29 10:42:31 +0000 > +++ sql/sql_parse.cc 2010-02-23 21:17:16 +0000 > @@ -342,6 +342,9 @@ > sql_command_flags[SQLCOM_SHOW_TABLES]= (CF_STATUS_COMMAND | > CF_SHOW_TABLE_COMMAND | > CF_REEXECUTION_FRAGILE); > + sql_command_flags[SQLCOM_SHOW_TEMPORARY_TABLES]= (CF_STATUS_COMMAND | > + CF_SHOW_TABLE_COMMAND | > + CF_REEXECUTION_FRAGILE); > sql_command_flags[SQLCOM_SHOW_TABLE_STATUS]= (CF_STATUS_COMMAND | > CF_SHOW_TABLE_COMMAND | > CF_REEXECUTION_FRAGILE); > @@ -1774,6 +1777,8 @@ > > case SCH_TABLE_NAMES: > case SCH_TABLES: > + case SCH_TEMPORARY_TABLES: > + case SCH_GLOBAL_TEMPORARY_TABLES: > case SCH_VIEWS: > case SCH_TRIGGERS: > case SCH_EVENTS: > @@ -2262,6 +2267,7 @@ > } > case SQLCOM_SHOW_DATABASES: > case SQLCOM_SHOW_TABLES: > + case SQLCOM_SHOW_TEMPORARY_TABLES: > case SQLCOM_SHOW_TRIGGERS: > case SQLCOM_SHOW_TABLE_STATUS: > case SQLCOM_SHOW_OPEN_TABLES: > @@ -5354,6 +5360,8 @@ > > case SCH_TABLE_NAMES: > case SCH_TABLES: > + case SCH_TEMPORARY_TABLES: > + case SCH_GLOBAL_TEMPORARY_TABLES: > case SCH_VIEWS: > case SCH_TRIGGERS: > case SCH_EVENTS: > > === modified file 'sql/sql_show.cc' > --- sql/sql_show.cc 2010-01-15 15:27:55 +0000 > +++ sql/sql_show.cc 2010-02-23 21:17:16 +0000 > @@ -2716,6 +2716,7 @@ > return 0; > case SQLCOM_SHOW_TABLES: > case SQLCOM_SHOW_TABLE_STATUS: > + case SQLCOM_SHOW_TEMPORARY_TABLES: > case SQLCOM_SHOW_TRIGGERS: > case SQLCOM_SHOW_EVENTS: > lookup_field_values->db_value.str= lex->select_lex.db; > @@ -3171,6 +3172,212 @@ > return (uint) OPEN_FULL_TABLE; > } > > +/** > + @brief Change I_S table item list for SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN db] > + > + @param[in] thd thread handler > + @param[in] schema_table I_S table > + > + @return Operation status > + @retval 0 success > + @retval 1 error > +*/ > +int make_temporary_tables_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table) > +{ > + char tmp[128]; > + String buffer(tmp,sizeof(tmp), thd->charset()); > + LEX *lex= thd->lex; > + Name_resolution_context *context= &lex->select_lex.context; > + > + if (thd->lex->option_type == OPT_GLOBAL) { > + ST_FIELD_INFO *field_info= &schema_table->fields_info[0]; > + Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name); > + if (add_item_to_list(thd, field)) > + return 1; > + field->set_name(field_info->old_name, strlen(field_info->old_name), system_charset_info); > + } > + > + ST_FIELD_INFO *field_info= &schema_table->fields_info[2]; > + buffer.length(0); > + buffer.append(field_info->old_name); > + buffer.append(lex->select_lex.db); > + > + if (lex->wild && lex->wild->ptr()) > + { > + buffer.append(STRING_WITH_LEN(" (")); > + buffer.append(lex->wild->ptr()); > + buffer.append(')'); > + } > + > + Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name); > + if (add_item_to_list(thd, field)) > + return 1; > + > + field->set_name(buffer.ptr(), buffer.length(), system_charset_info); > + return 0; > +} > + > +/** > + @brief Fill records for temporary tables by reading info from table object > + > + @param[in] thd thread handler > + @param[in] table I_S table > + @param[in] tmp_table temporary table > + @param[in] db database name > + > + @return Operation status > + @retval 0 success > + @retval 1 error > +*/ > + > +static int store_temporary_table_record(THD *thd, TABLE *table, TABLE *tmp_table, const char *db, bool table_name_only) > +{ > + CHARSET_INFO *cs= system_charset_info; > + DBUG_ENTER("store_temporary_table_record"); > + > + if (db && my_strcasecmp(cs, db, tmp_table->s->db.str)) > + DBUG_RETURN(0); > + > + restore_record(table, s->default_values); > + > + //session_id > + table->field[0]->store((longlong) thd->thread_id, TRUE); > + > + //database > + table->field[1]->store(tmp_table->s->db.str, tmp_table->s->db.length, cs); > + > + //table > + table->field[2]->store(tmp_table->s->table_name.str, tmp_table->s->table_name.length, cs); > + > + if (table_name_only) > + DBUG_RETURN(schema_table_store_record(thd, table)); > + > + //engine > + handler *handle= tmp_table->file; > + char *engineType = (char *)(handle ? handle->table_type() : "UNKNOWN"); > + table->field[3]->store(engineType, strlen(engineType), cs); > + > + //name > + if (tmp_table->s->path.str) { > + char *p=strstr(tmp_table->s->path.str, "#sql"); > + int len=tmp_table->s->path.length-(p-tmp_table->s->path.str); > + table->field[4]->store(p, min(FN_REFLEN, len), cs); > + } > + > + // file stats > + handler *file= tmp_table->file; > + > + if (file) { > + > + MYSQL_TIME time; > + > + /** > + TODO: InnoDB stat(file) checks file on short names within data dictionary > + rather than using full path, because of that, temp files created in > + TMPDIR will not have access/create time as it will not find the file > + > + The fix is to patch InnoDB to use full path > + */ > + file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK); > + > + table->field[5]->store((longlong) file->stats.records, TRUE); > + table->field[5]->set_notnull(); > + > + table->field[6]->store((longlong) file->stats.mean_rec_length, TRUE); > + table->field[7]->store((longlong) file->stats.data_file_length, TRUE); > + table->field[8]->store((longlong) file->stats.index_file_length, TRUE); > + if (file->stats.create_time) > + { > + thd->variables.time_zone->gmt_sec_to_TIME(&time, > + (my_time_t) file->stats.create_time); > + table->field[9]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); > + table->field[9]->set_notnull(); > + } > + if (file->stats.update_time) > + { > + thd->variables.time_zone->gmt_sec_to_TIME(&time, > + (my_time_t) file->stats.update_time); > + table->field[10]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); > + table->field[10]->set_notnull(); > + } > + } > + > + DBUG_RETURN(schema_table_store_record(thd, table)); > +} > + > +/** > + @brief Fill I_S tables with global temporary tables > + > + @param[in] thd thread handler > + @param[in] tables I_S table > + @param[in] cond 'WHERE' condition > + > + @return Operation status > + @retval 0 success > + @retval 1 error > +*/ > + > +static int fill_global_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond) > +{ > + DBUG_ENTER("fill_global_temporary_tables"); > + > + pthread_mutex_lock(&LOCK_thread_count); > + > + bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0; > + I_List_iterator<THD> it(threads); > + THD *thd_item; > + TABLE *tmp; > + > + //TODO: ACL check when I_S used directly on global temp tables > + while ((thd_item=it++)) { > + for (tmp=thd_item->temporary_tables; tmp; tmp=tmp->next) { > + > + THD *t= tmp->in_use; > + tmp->in_use= thd; > + > + if (store_temporary_table_record(thd_item, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) { > + tmp->in_use= t; > + pthread_mutex_unlock(&LOCK_thread_count); > + DBUG_RETURN(1); > + } > + > + tmp->in_use= t; > + } > + } > + > + pthread_mutex_unlock(&LOCK_thread_count); > + DBUG_RETURN(0); > +} > + > +/** > + @brief Fill I_S tables with session temporary tables > + > + @param[in] thd thread handler > + @param[in] tables I_S table > + @param[in] cond 'WHERE' condition > + > + @return Operation status > + @retval 0 success > + @retval 1 error > +*/ > + > +int fill_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond) > +{ > + DBUG_ENTER("fill_temporary_tables"); > + > + if (thd->lex->option_type == OPT_GLOBAL) > + DBUG_RETURN(fill_global_temporary_tables(thd, tables, cond)); > + > + bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0; > + TABLE *tmp; > + > + for (tmp=thd->temporary_tables; tmp; tmp=tmp->next) { > + if (store_temporary_table_record(thd, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) { > + DBUG_RETURN(1); > + } > + } > + DBUG_RETURN(0); > +} > > /** > @brief Fill I_S table with data from FRM file only > @@ -6259,6 +6466,25 @@ > {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE} > }; > > +ST_FIELD_INFO temporary_table_fields_info[]= > +{ > + {"SESSION_ID", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Session", SKIP_OPEN_TABLE}, > + {"TABLE_SCHEMA", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Db", SKIP_OPEN_TABLE}, > + {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Temp_tables_in_", SKIP_OPEN_TABLE}, > + {"ENGINE", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Engine", OPEN_FRM_ONLY}, > + {"NAME", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, "Name", SKIP_OPEN_TABLE}, > + {"TABLE_ROWS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, > + MY_I_S_UNSIGNED, "Rows", OPEN_FULL_TABLE}, > + {"AVG_ROW_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, > + MY_I_S_UNSIGNED, "Avg Row", OPEN_FULL_TABLE}, > + {"DATA_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, > + MY_I_S_UNSIGNED, "Data Length", OPEN_FULL_TABLE}, > + {"INDEX_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, > + MY_I_S_UNSIGNED, "Index Size", OPEN_FULL_TABLE}, > + {"CREATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Create Time", OPEN_FULL_TABLE}, > + {"UPDATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Update Time", OPEN_FULL_TABLE}, > + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE} > +}; > > ST_FIELD_INFO columns_fields_info[]= > { > @@ -6817,6 +7043,9 @@ > fill_schema_files, 0, 0, -1, -1, 0, 0}, > {"GLOBAL_STATUS", variables_fields_info, create_schema_table, > fill_status, make_old_format, 0, 0, -1, 0, 0}, > + {"GLOBAL_TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table, > + fill_global_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0, > + OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE}, > {"GLOBAL_VARIABLES", variables_fields_info, create_schema_table, > fill_variables, make_old_format, 0, 0, -1, 0, 0}, > {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table, > @@ -6860,6 +7089,9 @@ > get_all_tables, make_table_names_old_format, 0, 1, 2, 1, 0}, > {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table, > fill_schema_table_privileges, 0, 0, -1, -1, 0, 0}, > + {"TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table, > + fill_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0, > + OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE}, > {"TRIGGERS", triggers_fields_info, create_schema_table, > get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0, > OPEN_TABLE_ONLY}, > > === modified file 'sql/sql_yacc.yy' > --- sql/sql_yacc.yy 2010-01-17 17:22:46 +0000 > +++ sql/sql_yacc.yy 2010-02-23 21:17:16 +0000 > @@ -10035,6 +10035,15 @@ > if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_NAMES)) > MYSQL_YYABORT; > } > + | opt_var_type TEMPORARY TABLES opt_db > + { > + LEX *lex= Lex; > + lex->sql_command= SQLCOM_SHOW_TEMPORARY_TABLES; > + lex->option_type= $1; > + lex->select_lex.db= $4; > + if (prepare_schema_table(YYTHD, lex, 0, SCH_TEMPORARY_TABLES)) > + MYSQL_YYABORT; > + } > | opt_full TRIGGERS_SYM opt_db wild_and_where > { > LEX *lex= Lex; > > === modified file 'sql/table.h' > --- sql/table.h 2010-02-10 19:06:24 +0000 > +++ sql/table.h 2010-02-23 21:17:16 +0000 > @@ -942,6 +942,7 @@ > SCH_FILES, > SCH_GLOBAL_STATUS, > SCH_GLOBAL_VARIABLES, > + SCH_GLOBAL_TEMPORARY_TABLES, > SCH_KEY_COLUMN_USAGE, > SCH_OPEN_TABLES, > SCH_PARTITIONS, > @@ -960,6 +961,7 @@ > SCH_TABLE_CONSTRAINTS, > SCH_TABLE_NAMES, > SCH_TABLE_PRIVILEGES, > + SCH_TEMPORARY_TABLES, > SCH_TRIGGERS, > SCH_USER_PRIVILEGES, > SCH_VARIABLES, > > >

1 0

[Maria-developers] Rev 2752: Subquery optimization: Duplicate Elimination: process in file:///home/psergey/dev/maria-5.3-subqueries-r3/
by Sergey Petrunya 23 Feb '10

23 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r3/ ------------------------------------------------------------ revno: 2752 revision-id: psergey(a)askmonty.org-20100208125530-l0o01n6lxk95cn78 parent: psergey(a)askmonty.org-20100208095616-ar502xdhoqqhe3nk committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r3 timestamp: Mon 2010-02-08 14:55:30 +0200 message: Subquery optimization: Duplicate Elimination: process temporary table overflow correctly. === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-01-28 13:48:33 +0000 +++ b/sql/sql_select.cc 2010-02-08 12:55:30 +0000 @@ -16250,12 +16250,12 @@ if (error) { /* create_internal_tmp_table_from_heap will generate error if needed */ - if (sjtbl->tmp_table->file->is_fatal_error(error, HA_CHECK_DUP) && - create_internal_tmp_table_from_heap(thd, sjtbl->tmp_table, + if (!sjtbl->tmp_table->file->is_fatal_error(error, HA_CHECK_DUP)) + DBUG_RETURN(1); /* Duplicate */ + if (create_internal_tmp_table_from_heap(thd, sjtbl->tmp_table, sjtbl->start_recinfo, &sjtbl->recinfo, error, 1)) DBUG_RETURN(-1); - DBUG_RETURN(1); } DBUG_RETURN(0); }

2 1

[Maria-developers] Rev 2751: Fix for previous: pass correct parameters to mark_as_dependent. in file:///home/psergey/dev/maria-5.3-subqueries-r3/
by Sergey Petrunya 23 Feb '10

23 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r3/ ------------------------------------------------------------ revno: 2751 revision-id: psergey(a)askmonty.org-20100208095616-ar502xdhoqqhe3nk parent: psergey(a)askmonty.org-20100202200045-13q0nb5dwzm739j6 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r3 timestamp: Mon 2010-02-08 11:56:16 +0200 message: Fix for previous: pass correct parameters to mark_as_dependent. === modified file 'sql/item.cc' --- a/sql/item.cc 2010-02-02 20:00:45 +0000 +++ b/sql/item.cc 2010-02-08 09:56:16 +0000 @@ -4220,7 +4220,7 @@ return -1; mark_as_dependent(thd, last_checked_context->select_lex, - context->select_lex, this, + context->select_lex, rf /*this*/ , //psergey-fix: s/this/rf/ rf); return 0; } @@ -6000,7 +6000,8 @@ goto error; thd->change_item_tree(reference, fld); mark_as_dependent(thd, last_checked_context->select_lex, - thd->lex->current_select, this, fld); + thd->lex->current_select, fld, fld); //psergey-fix-todo: use reference instead? + // was: this, fld) /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of

2 1

Re: [Maria-developers] Rev 2740: Group commit for maria storage engine. in file:///Users/bell/maria/bzr/work-maria-5.2-groupcommit/
by Michael Widenius 23 Feb '10

23 Feb '10

Hi! >>>>> "Sergei" == Sergei Golubchik <serg(a)askmonty.org> writes: Sergei> Hi, Michael! Sergei> On Feb 11, Michael Widenius wrote: >> >> Yes, but if you are only looking at one value, without regard of the >> other, then you don't need to use my_atomic_load32() Sergei> if 32-bit reads are always atomic - yes. Sergei> I suppose we can assume that they are. >> Another problem is that if the variables are related in any way, >> atomic_load32 is not good enough as you can get 'a' and 'b' from >> different instances in time. >> >> for example: >> >> a=b= 0; >> >> T1: my_atomic_store32(&a, 1); >> T1: my_atomic_store32(&b, 2); >> >> T2: my_atomic_store32(&a, 3); >> T2: my_atomic_store32(&b, 4); >> >> T3: a1= my_atomic_load32(&a); >> T3: b1= my_atomic_load32(&b); >> >> this means that you can get the following values for a1 and b1: >> >> a1= 0 b1= 0 >> a1= 0 b1= 2 >> a1= 1 b1= 2 >> a1= 1 b1= 4 >> a1= 3 b1= 2 >> a1= 3 b1= 4 Sergei> and also Sergei> a1=0 b1=4 Sergei> a1=1 b1=0 ok, I missed the above one. Sergei> a1=3 b1=0 Ok, that is right. However, my intention was actually to have T2 to be T1 (ie same process), in which case this option would be impossible. Sergei> all the 9 variants are possible. Sanja, when considering the above, are you sure your usage of my_atomic_load/my_atmoic_store is correct? For example: + my_atomic_rwlock_rdlock(&soft_sync_rwl); + min= my_atomic_load32(&soft_sync_min); + max= my_atomic_load32(&soft_sync_max); + sync_request= my_atomic_load32(&soft_need_sync); + my_atomic_store32(&soft_sync_min, max); + my_atomic_store32(&soft_need_sync, 0); + my_atomic_rwlock_rdunlock(&soft_sync_rwl); In the above code, there is no gurantee that you read or write related soft_sync_min and soft_sync_max values. (See above table). Also, as we concluded with Sergei, any code like: + my_atomic_rwlock_rdlock(&soft_sync_rwl); + min= my_atomic_load32(&soft_sync_min); + my_atomic_rwlock_rdunlock(&soft_sync_rwl); There is no reason to have an my_atomic_rwlock instruction. Regards, Monty

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2821: fix for a possible DoS in the my_net_skip_rest()
by noreply＠launchpad.net 23 Feb '10

23 Feb '10

------------------------------------------------------------ revno: 2821 committer: Sergei Golubchik <sergii(a)pisem.net> branch nick: maria-5.1 timestamp: Tue 2010-02-23 13:04:58 +0100 message: fix for a possible DoS in the my_net_skip_rest() modified: include/mysql.h.pp include/mysql_com.h sql/net_serv.cc sql/sql_connect.cc -- lp:maria https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2820: Fixed LP#524679: make test ORDER BY date_ord ASC
by noreply＠launchpad.net 23 Feb '10

23 Feb '10

------------------------------------------------------------ revno: 2820 committer: Michael Widenius <monty(a)askmonty.org> branch nick: maria-5.1 timestamp: Tue 2010-02-23 14:06:41 +0200 message: Fixed LP#524679: make test ORDER BY date_ord ASC (Problem was missing time_zone setting) modified: mysql-test/t/func_if.test -- lp:maria https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2820)
by Michael Widenius 23 Feb '10

23 Feb '10

#At lp:maria based on revid:psergey@askmonty.org-20100219141349-bvecf7xaj7fzodw0 2820 Michael Widenius 2010-02-23 Fixed LP#524679: make test ORDER BY date_ord ASC (Problem was missing time_zone setting) modified: mysql-test/t/func_if.test === modified file 'mysql-test/t/func_if.test' --- a/mysql-test/t/func_if.test 2008-12-12 13:16:25 +0000 +++ b/mysql-test/t/func_if.test 2010-02-23 12:06:41 +0000 @@ -5,6 +5,11 @@ drop table if exists t1; --enable_warnings +--disable_query_log +# Set timezone to something specific, to make it possible to use unix_timestamp +set time_zone="+03:00"; +--enable_query_log + # # Simple IF tests # @@ -163,5 +168,9 @@ SELECT * FROM (SELECT MAX(IFNULL(CAST(c DROP TABLE t1; - --echo End of 5.0 tests + +--disable_query_log +# Restore timezone to default +set time_zone= @@global.time_zone; +--enable_query_log

1 0

[Maria-developers] Rev 2768: Subquery backport: update pbxt suite test results (checked). in file:///home/psergey/dev/maria-5.3-subqueries-r7/
by Sergey Petrunya 23 Feb '10

23 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r7/ ------------------------------------------------------------ revno: 2768 revision-id: psergey(a)askmonty.org-20100223092202-sc3huivw9yymnj5d parent: psergey(a)askmonty.org-20100221075312-fc08qgn72dnbudd5 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r7 timestamp: Tue 2010-02-23 11:22:02 +0200 message: Subquery backport: update pbxt suite test results (checked). === modified file 'mysql-test/suite/pbxt/r/group_min_max.result' --- a/mysql-test/suite/pbxt/r/group_min_max.result 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/r/group_min_max.result 2010-02-23 09:22:02 +0000 @@ -2257,7 +2257,7 @@ a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra === modified file 'mysql-test/suite/pbxt/r/subselect.result' --- a/mysql-test/suite/pbxt/r/subselect.result 2009-12-16 09:28:51 +0000 +++ b/mysql-test/suite/pbxt/r/subselect.result 2010-02-23 09:22:02 +0000 @@ -1293,31 +1293,31 @@ 4 explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1332,31 +1332,31 @@ 4 explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1 100.00 Using index -2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.b 1 100.00 Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index +1 PRIMARY t3 ref a a 5 test.t1.b 1 100.00 Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1369,10 +1369,10 @@ 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) drop table t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -2823,10 +2823,10 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1) Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N')) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 @@ -3412,7 +3412,7 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort ALTER TABLE t1 ADD INDEX(a); SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); a b @@ -3423,7 +3423,7 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort DROP TABLE t1; create table t1( f1 int,f2 int); insert into t1 values (1,1),(2,2); @@ -4213,8 +4213,8 @@ CREATE INDEX I2 ON t1 (b); EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where +1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan +1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using where SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); a b CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); @@ -4223,15 +4223,15 @@ CREATE INDEX I2 ON t2 (b); EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 1 Using index; Using where +1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan +1 PRIMARY t2 ref I2 I2 13 test.t2.a 1 Using where SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); a b EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where +1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan +1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using where SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); a b DROP TABLE t1,t2;

1 0

[Maria-developers] New (by Monty): Add exit_connect, start_server, stop_server hooks (87)
by worklog-noreply＠askmonty.org 22 Feb '10

22 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add exit_connect, start_server, stop_server hooks CREATION DATE..: Mon, 22 Feb 2010, 19:51 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 87 (http://askmonty.org/worklog/?tid=87) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 20 (hours remain) ORIG. ESTIMATE.: 20 PROGRESS NOTES: DESCRIPTION: Add exit_connect, start_server, stop_server hooks. These would be similar to the init_connect command that is executed when a connection is created. The exit_connect command would be executed as the connection user. The start_server and stop_server should be run as user 'root' with SUPER_ACL privileges) (Note that this is troublesome as the server may not have a 'root' user, so we may want to allow the user to specify that name of the super user on the command line) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0