- developers - lists.mariadb.org

[Maria-developers] Updated (by Guest): options for CREATE TABLE (43)
by worklog-noreply＠askmonty.org 23 Apr '10

23 Apr '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: options for CREATE TABLE CREATION DATE..: Tue, 11 Aug 2009, 17:02 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: Sanja COPIES TO......: Sergei, Monty CATEGORY.......: Server-BackLog TASK ID........: 43 (http://askmonty.org/worklog/?tid=43) VERSION........: Server-5.2 STATUS.........: Complete PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 32 (hours remain) ORIG. ESTIMATE.: 32 PROGRESS NOTES: -=-=(Guest - Fri, 23 Apr 2010, 11:02)=-=- Status updated. --- /tmp/wklog.43.old.24328 2010-04-23 11:02:07.000000000 +0000 +++ /tmp/wklog.43.new.24328 2010-04-23 11:02:07.000000000 +0000 @@ -1 +1 @@ -Assigned +Complete -=-=(Serg - Sun, 11 Apr 2010, 11:58)=-=- Low Level Design modified. --- /tmp/wklog.43.old.24176 2010-04-11 11:58:36.000000000 +0000 +++ /tmp/wklog.43.new.24176 2010-04-11 11:58:36.000000000 +0000 @@ -2,6 +2,5 @@ Handler have access to the options during creation and after opening frm. -It should has C interface. -=-=(Serg - Thu, 04 Mar 2010, 16:15)=-=- High-Level Specification modified. --- /tmp/wklog.43.old.32502 2010-03-04 16:15:12.000000000 +0000 +++ /tmp/wklog.43.new.32502 2010-03-04 16:15:12.000000000 +0000 @@ -1,13 +1,13 @@ -Table definition ca looks like following +Table definition will look like the following: CREATE TABLE table (field int ... field_opt1=fval1 field_opt2=fval2, key key1(field) key_opt1=kval1 key_opt2=kval2) table_option1=tval1, table_option2=tval2; -Exclusion should be made for old table and key options where -'=' was not obligatory. Behaviour and way of storage for existing options will -be left as is. +Exceptions should be made for old table and key options where +'=' was not obligatory. These options will behave as they did, +no changes from the user point of view. Old key options: KEY_BLOCK_SIZE <num> -> KEY_BLOCK_SIZE=num @@ -37,12 +37,11 @@ default charset default collation DATA DIRECTORY +INDEX DIRECTORY TABLESPACE STORAGE -For fields options can go with field attributes (NOT NULL, UNIQUE and so on) can -be separated from them by '=' sign. - -Incorrect option during creation/altering table lead only to worning. During -opening unrecognised options should be ignored. +Incorrect option during creation/altering table lead only to a warning. +Strict mode makes them errors, as expected. +When opening a table all unrecognised options should be ignored. -=-=(Sergei - Mon, 18 Jan 2010, 22:12)=-=- Observers changed: Monty,Sergei -=-=(Monty - Tue, 12 Jan 2010, 17:04)=-=- Version updated. --- /tmp/wklog.43.old.21802 2010-01-12 17:04:31.000000000 +0200 +++ /tmp/wklog.43.new.21802 2010-01-12 17:04:31.000000000 +0200 @@ -1 +1 @@ -Server-5.1 +Server-5.2 -=-=(Guest - Fri, 18 Sep 2009, 14:24)=-=- Low Level Design modified. --- /tmp/wklog.43.old.31654 2009-09-18 14:24:41.000000000 +0300 +++ /tmp/wklog.43.new.31654 2009-09-18 14:24:41.000000000 +0300 @@ -2,4 +2,6 @@ Handler have access to the options during creation and after opening frm. +It should has C interface. + -=-=(Sanja - Fri, 18 Sep 2009, 13:32)=-=- Low Level Design modified. --- /tmp/wklog.43.old.29567 2009-09-18 13:32:41.000000000 +0300 +++ /tmp/wklog.43.new.29567 2009-09-18 13:32:41.000000000 +0300 @@ -1 +1,5 @@ +Options stored in a list as pairs key, value which are LEX_STRING. + +Handler have access to the options during creation and after opening frm. + -=-=(Sanja - Fri, 18 Sep 2009, 13:21)=-=- High-Level Specification modified. --- /tmp/wklog.43.old.29107 2009-09-18 13:21:19.000000000 +0300 +++ /tmp/wklog.43.new.29107 2009-09-18 13:21:19.000000000 +0300 @@ -6,7 +6,8 @@ table_option1=tval1, table_option2=tval2; Exclusion should be made for old table and key options where -'=' was not obligatory. +'=' was not obligatory. Behaviour and way of storage for existing options will +be left as is. Old key options: KEY_BLOCK_SIZE <num> -> KEY_BLOCK_SIZE=num @@ -42,6 +43,6 @@ For fields options can go with field attributes (NOT NULL, UNIQUE and so on) can be separated from them by '=' sign. - - +Incorrect option during creation/altering table lead only to worning. During +opening unrecognised options should be ignored. -=-=(Guest - Tue, 11 Aug 2009, 19:57)=-=- High-Level Specification modified. --- /tmp/wklog.43.old.31856 2009-08-11 19:57:38.000000000 +0300 +++ /tmp/wklog.43.new.31856 2009-08-11 19:57:38.000000000 +0300 @@ -5,8 +5,43 @@ key key1(field) key_opt1=kval1 key_opt2=kval2) table_option1=tval1, table_option2=tval2; -Exclusion should be made for old table and key (KEY_BLOCK_SIZE) options where +Exclusion should be made for old table and key options where '=' was not obligatory. +Old key options: +KEY_BLOCK_SIZE <num> -> KEY_BLOCK_SIZE=num +WITH PARSER <name> -> PARSER=name + +Old table options: +ENGINE name -> ENGINE=name +TYPE name -> TYPE=name +MAX_ROWS num -> MAX_ROWS=num +MIX_ROWS num -> MIX_ROWS=num +AVG_ROW_LENGTH num -> AVG_ROW_LENGTH=num +PASSWORD string -> PASSWORD=string +COMMENT string -> COMMENT=string +AUTO_INCREMENT num -> AUTO_INCREMENT=num +PACK_KEYS num/default -> PACK_KEYS=num/default +CHECKSUM num -> CHECKSUM=num +TABLE_CHECKSUM num -> TABLE_CHECKSUM=num +PAGE_CHECKSUM num -> PAGE_CHECKSUM=num +DELAY_KEY_WRITE num -> DELAY_KEY_WRITE=num +ROW_FORMAT name -> ROW_FORMAT=name +INSERT_METHOD name -> INSERT_METHOD=name +KEY_BLOCK_SIZE num -> KEY_BLOCK_SIZE=num +TRANSACTIONAL num -> TRANSACTIONAL=num + +Table options which will be left hardcoded +UNION +default charset +default collation +DATA DIRECTORY +TABLESPACE +STORAGE + For fields options can go with field attributes (NOT NULL, UNIQUE and so on) can be separated from them by '=' sign. + + + + -=-=(Guest - Tue, 11 Aug 2009, 19:36)=-=- High-Level Specification modified. --- /tmp/wklog.43.old.30883 2009-08-11 19:36:45.000000000 +0300 +++ /tmp/wklog.43.new.30883 2009-08-11 19:36:45.000000000 +0300 @@ -1 +1,12 @@ +Table definition ca looks like following +CREATE TABLE table + (field int ... field_opt1=fval1 field_opt2=fval2, + key key1(field) key_opt1=kval1 key_opt2=kval2) + table_option1=tval1, table_option2=tval2; + +Exclusion should be made for old table and key (KEY_BLOCK_SIZE) options where +'=' was not obligatory. + +For fields options can go with field attributes (NOT NULL, UNIQUE and so on) can +be separated from them by '=' sign. DESCRIPTION: Add ability to create table with additional option which can be passed to engine. Also make current options such as TRANSACTIONAL working via this mechanism. HIGH-LEVEL SPECIFICATION: Table definition will look like the following: CREATE TABLE table (field int ... field_opt1=fval1 field_opt2=fval2, key key1(field) key_opt1=kval1 key_opt2=kval2) table_option1=tval1, table_option2=tval2; Exceptions should be made for old table and key options where '=' was not obligatory. These options will behave as they did, no changes from the user point of view. Old key options: KEY_BLOCK_SIZE <num> -> KEY_BLOCK_SIZE=num WITH PARSER <name> -> PARSER=name Old table options: ENGINE name -> ENGINE=name TYPE name -> TYPE=name MAX_ROWS num -> MAX_ROWS=num MIX_ROWS num -> MIX_ROWS=num AVG_ROW_LENGTH num -> AVG_ROW_LENGTH=num PASSWORD string -> PASSWORD=string COMMENT string -> COMMENT=string AUTO_INCREMENT num -> AUTO_INCREMENT=num PACK_KEYS num/default -> PACK_KEYS=num/default CHECKSUM num -> CHECKSUM=num TABLE_CHECKSUM num -> TABLE_CHECKSUM=num PAGE_CHECKSUM num -> PAGE_CHECKSUM=num DELAY_KEY_WRITE num -> DELAY_KEY_WRITE=num ROW_FORMAT name -> ROW_FORMAT=name INSERT_METHOD name -> INSERT_METHOD=name KEY_BLOCK_SIZE num -> KEY_BLOCK_SIZE=num TRANSACTIONAL num -> TRANSACTIONAL=num Table options which will be left hardcoded UNION default charset default collation DATA DIRECTORY INDEX DIRECTORY TABLESPACE STORAGE Incorrect option during creation/altering table lead only to a warning. Strict mode makes them errors, as expected. When opening a table all unrecognised options should be ignored. LOW-LEVEL DESIGN: Options stored in a list as pairs key, value which are LEX_STRING. Handler have access to the options during creation and after opening frm. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): options for CREATE TABLE (43)
by worklog-noreply＠askmonty.org 23 Apr '10

23 Apr '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: options for CREATE TABLE CREATION DATE..: Tue, 11 Aug 2009, 17:02 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: Sanja COPIES TO......: Sergei, Monty CATEGORY.......: Server-BackLog TASK ID........: 43 (http://askmonty.org/worklog/?tid=43) VERSION........: Server-5.2 STATUS.........: Complete PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 32 (hours remain) ORIG. ESTIMATE.: 32 PROGRESS NOTES: -=-=(Guest - Fri, 23 Apr 2010, 11:02)=-=- Status updated. --- /tmp/wklog.43.old.24328 2010-04-23 11:02:07.000000000 +0000 +++ /tmp/wklog.43.new.24328 2010-04-23 11:02:07.000000000 +0000 @@ -1 +1 @@ -Assigned +Complete -=-=(Serg - Sun, 11 Apr 2010, 11:58)=-=- Low Level Design modified. --- /tmp/wklog.43.old.24176 2010-04-11 11:58:36.000000000 +0000 +++ /tmp/wklog.43.new.24176 2010-04-11 11:58:36.000000000 +0000 @@ -2,6 +2,5 @@ Handler have access to the options during creation and after opening frm. -It should has C interface. -=-=(Serg - Thu, 04 Mar 2010, 16:15)=-=- High-Level Specification modified. --- /tmp/wklog.43.old.32502 2010-03-04 16:15:12.000000000 +0000 +++ /tmp/wklog.43.new.32502 2010-03-04 16:15:12.000000000 +0000 @@ -1,13 +1,13 @@ -Table definition ca looks like following +Table definition will look like the following: CREATE TABLE table (field int ... field_opt1=fval1 field_opt2=fval2, key key1(field) key_opt1=kval1 key_opt2=kval2) table_option1=tval1, table_option2=tval2; -Exclusion should be made for old table and key options where -'=' was not obligatory. Behaviour and way of storage for existing options will -be left as is. +Exceptions should be made for old table and key options where +'=' was not obligatory. These options will behave as they did, +no changes from the user point of view. Old key options: KEY_BLOCK_SIZE <num> -> KEY_BLOCK_SIZE=num @@ -37,12 +37,11 @@ default charset default collation DATA DIRECTORY +INDEX DIRECTORY TABLESPACE STORAGE -For fields options can go with field attributes (NOT NULL, UNIQUE and so on) can -be separated from them by '=' sign. - -Incorrect option during creation/altering table lead only to worning. During -opening unrecognised options should be ignored. +Incorrect option during creation/altering table lead only to a warning. +Strict mode makes them errors, as expected. +When opening a table all unrecognised options should be ignored. -=-=(Sergei - Mon, 18 Jan 2010, 22:12)=-=- Observers changed: Monty,Sergei -=-=(Monty - Tue, 12 Jan 2010, 17:04)=-=- Version updated. --- /tmp/wklog.43.old.21802 2010-01-12 17:04:31.000000000 +0200 +++ /tmp/wklog.43.new.21802 2010-01-12 17:04:31.000000000 +0200 @@ -1 +1 @@ -Server-5.1 +Server-5.2 -=-=(Guest - Fri, 18 Sep 2009, 14:24)=-=- Low Level Design modified. --- /tmp/wklog.43.old.31654 2009-09-18 14:24:41.000000000 +0300 +++ /tmp/wklog.43.new.31654 2009-09-18 14:24:41.000000000 +0300 @@ -2,4 +2,6 @@ Handler have access to the options during creation and after opening frm. +It should has C interface. + -=-=(Sanja - Fri, 18 Sep 2009, 13:32)=-=- Low Level Design modified. --- /tmp/wklog.43.old.29567 2009-09-18 13:32:41.000000000 +0300 +++ /tmp/wklog.43.new.29567 2009-09-18 13:32:41.000000000 +0300 @@ -1 +1,5 @@ +Options stored in a list as pairs key, value which are LEX_STRING. + +Handler have access to the options during creation and after opening frm. + -=-=(Sanja - Fri, 18 Sep 2009, 13:21)=-=- High-Level Specification modified. --- /tmp/wklog.43.old.29107 2009-09-18 13:21:19.000000000 +0300 +++ /tmp/wklog.43.new.29107 2009-09-18 13:21:19.000000000 +0300 @@ -6,7 +6,8 @@ table_option1=tval1, table_option2=tval2; Exclusion should be made for old table and key options where -'=' was not obligatory. +'=' was not obligatory. Behaviour and way of storage for existing options will +be left as is. Old key options: KEY_BLOCK_SIZE <num> -> KEY_BLOCK_SIZE=num @@ -42,6 +43,6 @@ For fields options can go with field attributes (NOT NULL, UNIQUE and so on) can be separated from them by '=' sign. - - +Incorrect option during creation/altering table lead only to worning. During +opening unrecognised options should be ignored. -=-=(Guest - Tue, 11 Aug 2009, 19:57)=-=- High-Level Specification modified. --- /tmp/wklog.43.old.31856 2009-08-11 19:57:38.000000000 +0300 +++ /tmp/wklog.43.new.31856 2009-08-11 19:57:38.000000000 +0300 @@ -5,8 +5,43 @@ key key1(field) key_opt1=kval1 key_opt2=kval2) table_option1=tval1, table_option2=tval2; -Exclusion should be made for old table and key (KEY_BLOCK_SIZE) options where +Exclusion should be made for old table and key options where '=' was not obligatory. +Old key options: +KEY_BLOCK_SIZE <num> -> KEY_BLOCK_SIZE=num +WITH PARSER <name> -> PARSER=name + +Old table options: +ENGINE name -> ENGINE=name +TYPE name -> TYPE=name +MAX_ROWS num -> MAX_ROWS=num +MIX_ROWS num -> MIX_ROWS=num +AVG_ROW_LENGTH num -> AVG_ROW_LENGTH=num +PASSWORD string -> PASSWORD=string +COMMENT string -> COMMENT=string +AUTO_INCREMENT num -> AUTO_INCREMENT=num +PACK_KEYS num/default -> PACK_KEYS=num/default +CHECKSUM num -> CHECKSUM=num +TABLE_CHECKSUM num -> TABLE_CHECKSUM=num +PAGE_CHECKSUM num -> PAGE_CHECKSUM=num +DELAY_KEY_WRITE num -> DELAY_KEY_WRITE=num +ROW_FORMAT name -> ROW_FORMAT=name +INSERT_METHOD name -> INSERT_METHOD=name +KEY_BLOCK_SIZE num -> KEY_BLOCK_SIZE=num +TRANSACTIONAL num -> TRANSACTIONAL=num + +Table options which will be left hardcoded +UNION +default charset +default collation +DATA DIRECTORY +TABLESPACE +STORAGE + For fields options can go with field attributes (NOT NULL, UNIQUE and so on) can be separated from them by '=' sign. + + + + -=-=(Guest - Tue, 11 Aug 2009, 19:36)=-=- High-Level Specification modified. --- /tmp/wklog.43.old.30883 2009-08-11 19:36:45.000000000 +0300 +++ /tmp/wklog.43.new.30883 2009-08-11 19:36:45.000000000 +0300 @@ -1 +1,12 @@ +Table definition ca looks like following +CREATE TABLE table + (field int ... field_opt1=fval1 field_opt2=fval2, + key key1(field) key_opt1=kval1 key_opt2=kval2) + table_option1=tval1, table_option2=tval2; + +Exclusion should be made for old table and key (KEY_BLOCK_SIZE) options where +'=' was not obligatory. + +For fields options can go with field attributes (NOT NULL, UNIQUE and so on) can +be separated from them by '=' sign. DESCRIPTION: Add ability to create table with additional option which can be passed to engine. Also make current options such as TRANSACTIONAL working via this mechanism. HIGH-LEVEL SPECIFICATION: Table definition will look like the following: CREATE TABLE table (field int ... field_opt1=fval1 field_opt2=fval2, key key1(field) key_opt1=kval1 key_opt2=kval2) table_option1=tval1, table_option2=tval2; Exceptions should be made for old table and key options where '=' was not obligatory. These options will behave as they did, no changes from the user point of view. Old key options: KEY_BLOCK_SIZE <num> -> KEY_BLOCK_SIZE=num WITH PARSER <name> -> PARSER=name Old table options: ENGINE name -> ENGINE=name TYPE name -> TYPE=name MAX_ROWS num -> MAX_ROWS=num MIX_ROWS num -> MIX_ROWS=num AVG_ROW_LENGTH num -> AVG_ROW_LENGTH=num PASSWORD string -> PASSWORD=string COMMENT string -> COMMENT=string AUTO_INCREMENT num -> AUTO_INCREMENT=num PACK_KEYS num/default -> PACK_KEYS=num/default CHECKSUM num -> CHECKSUM=num TABLE_CHECKSUM num -> TABLE_CHECKSUM=num PAGE_CHECKSUM num -> PAGE_CHECKSUM=num DELAY_KEY_WRITE num -> DELAY_KEY_WRITE=num ROW_FORMAT name -> ROW_FORMAT=name INSERT_METHOD name -> INSERT_METHOD=name KEY_BLOCK_SIZE num -> KEY_BLOCK_SIZE=num TRANSACTIONAL num -> TRANSACTIONAL=num Table options which will be left hardcoded UNION default charset default collation DATA DIRECTORY INDEX DIRECTORY TABLESPACE STORAGE Incorrect option during creation/altering table lead only to a warning. Strict mode makes them errors, as expected. When opening a table all unrecognised options should be ignored. LOW-LEVEL DESIGN: Options stored in a list as pairs key, value which are LEX_STRING. Handler have access to the options during creation and after opening frm. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Rev 2842: A test implementation of having SQL layer to supply the storage engine with in file:///home/psergey/dev/maria-5.1-minmax/
by Sergey Petrunya 21 Apr '10

21 Apr '10

At file:///home/psergey/dev/maria-5.1-minmax/ ------------------------------------------------------------ revno: 2842 revision-id: psergey(a)askmonty.org-20100421141933-707cy6l3iha89lm5 parent: monty(a)askmonty.org-20100406224708-i62wvtw3nl14fhv4 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.1-minmax timestamp: Wed 2010-04-21 10:19:33 -0400 message: A test implementation of having SQL layer to supply the storage engine with interval lists for each unindexed column. This patch consists of - SQL layer providing service of taking a condition and extracting list<column, disjoint_ordered_list<interval>> from it (see opt_field_minmax.h) - An storage engine's "Implementation" that prints passed intervals to server stderr. Things need to be done: - Comments, better names - Make the interface compatible with Query Fragment Pushdown ideas. - Invent something that would allow to have test coverage for this. === added file 'sql/opt_field_minmax.h' --- a/sql/opt_field_minmax.h 1970-01-01 00:00:00 +0000 +++ b/sql/opt_field_minmax.h 2010-04-21 14:19:33 +0000 @@ -0,0 +1,35 @@ +//#include "../sql/opt_field_minmax.h" + +class RANGE_OPT_PARAM; +class SEL_TREE; +class SEL_ARG; + +typedef bool (*take_int_return_bool)(void* param, uint index); + +class Condition_analyzer +{ + TABLE *table; + MEM_ROOT alloc; + RANGE_OPT_PARAM *range_par; + SEL_TREE *tree; + my_bitmap_map *old_sets[2]; + uchar min_val[MAX_KEY_LENGTH+MAX_FIELD_WIDTH]; + uchar max_val[MAX_KEY_LENGTH+MAX_FIELD_WIDTH]; + + uint n_keys; + + int cur_key; + SEL_ARG *cur_interval; +public: + Condition_analyzer(TABLE *table_arg) : + table(table_arg), range_par(NULL), cur_key(-1) {} + + bool analyze(Item *cond, take_int_return_bool field_filter_func, + void *field_filter_param); + + bool start_intervals_walk_for_field(uint *fieldno); + bool get_next_interval(KEY_MULTI_RANGE *mrange); + + void dispose(); +}; + === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2010-01-15 15:27:55 +0000 +++ b/sql/opt_range.cc 2010-04-21 14:19:33 +0000 @@ -2788,6 +2788,248 @@ DBUG_RETURN(retval); } +// psergey-minmax: start + +#include "opt_field_minmax.h" + +/* + Create one-column pseudo-indexes for each column. +*/ + +static +bool create_minmax_indexes_descriptions(RANGE_OPT_PARAM *range_par, + take_int_return_bool field_filter_func, + void *func_param, + uint *n_keys) +{ + TABLE *table= range_par->table; + MEM_ROOT *alloc= range_par->mem_root; + *n_keys= 0; + uint fieldno; + for (fieldno= 0; fieldno < range_par->table->s->fields; fieldno++) + { + enum_field_types type= table->field[fieldno]->type(); + if (!(type == MYSQL_TYPE_TINY_BLOB || + type == MYSQL_TYPE_MEDIUM_BLOB || + type == MYSQL_TYPE_LONG_BLOB || + type == MYSQL_TYPE_BLOB || + type == MYSQL_TYPE_GEOMETRY) && + field_filter_func(func_param, fieldno) && + *n_keys < MAX_INDEXES) + { + (*n_keys)++; + } + } + + if (!*n_keys) + return TRUE; + + KEY_PART *key_part; + key_part= (KEY_PART*)alloc_root(alloc, sizeof(KEY_PART)* (*n_keys)); + range_par->key_parts= key_part; + range_par->key_parts_end= key_part + *n_keys; + + uint idx= 0; + for (fieldno= 0; fieldno < range_par->table->s->fields; fieldno++) + { + enum_field_types type= table->field[fieldno]->type(); + if (!(type == MYSQL_TYPE_TINY_BLOB || + type == MYSQL_TYPE_MEDIUM_BLOB || + type == MYSQL_TYPE_LONG_BLOB || + type == MYSQL_TYPE_BLOB || + type == MYSQL_TYPE_GEOMETRY) && + field_filter_func(func_param, fieldno) && + idx < MAX_INDEXES) + { + idx++; + Field **field= table->field + fieldno; + key_part->key= 0; + key_part->part= 0; + key_part->store_length= key_part->length= (uint16) (*field)->key_length(); + if ((*field)->real_maybe_null()) + key_part->store_length+= HA_KEY_NULL_LENGTH; + if ((*field)->type() == MYSQL_TYPE_BLOB || + (*field)->real_type() == MYSQL_TYPE_VARCHAR) + key_part->store_length+= HA_KEY_BLOB_LENGTH; + + key_part->field= (*field); + key_part->image_type = Field::itRAW; + /* + We set keypart flag to 0 here as the only HA_PART_KEY_SEG is checked + in the RangeAnalysisModule. + */ + key_part->flag= 0; + /* We don't set key_parts->null_bit as it will not be used */ + + key_part++; + } + } + return FALSE; +} + + + + +bool Condition_analyzer::analyze(Item *cond, + take_int_return_bool func, + void *func_param) +{ + bool retval= FALSE; + THD *thd= current_thd; + uint i; + DBUG_ENTER("find_min_max_bounds"); + + init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0); + range_par= (RANGE_OPT_PARAM*)alloc_root(&alloc, sizeof(RANGE_OPT_PARAM)); + range_par->mem_root= &alloc; + range_par->old_root= thd->mem_root; + range_par->thd= thd; + range_par->table= table; + /* range_par->cond doesn't need initialization */ + range_par->prev_tables= range_par->read_tables= 0; + range_par->current_table= table->map; + + if (create_minmax_indexes_descriptions(range_par, func, func_param, &n_keys)) + { + free_root(&alloc,MYF(0)); // Return memory & allocator + DBUG_RETURN(FALSE); + } + range_par->keys= n_keys; + + dbug_tmp_use_all_columns(table, old_sets, + table->read_set, table->write_set); + range_par->using_real_indexes= FALSE; + range_par->remove_jump_scans= TRUE; + for (i=0; i < n_keys; i++) + range_par->real_keynr[i]= i; + range_par->alloced_sel_args= 0; + + + thd->no_errors=1; // Don't warn about NULL + thd->mem_root=&alloc; + + tree= get_mm_tree(range_par, cond); + if (!tree) + { + retval= TRUE; + goto end; + } + + if (tree->type == SEL_TREE::IMPOSSIBLE) + { + retval= TRUE; + goto end; + } + + if ((tree->type != SEL_TREE::KEY && tree->type != SEL_TREE::KEY_SMALLER) || + !tree->merges.is_empty()) + { + retval= TRUE; + goto end; + } + +end: + DBUG_RETURN(retval); +} + + +bool Condition_analyzer::start_intervals_walk_for_field(uint *fieldno) +{ + if (!tree) + return FALSE; + for (cur_key++; cur_key < (int)n_keys; cur_key++) + { + if (tree->keys[cur_key]) + { + *fieldno= range_par->key_parts[cur_key].field->field_index; + cur_interval= tree->keys[0]->first(); + return TRUE; + } + } + return FALSE; +} + + +bool Condition_analyzer::get_next_interval(KEY_MULTI_RANGE *mrange) +{ + uchar *key_ptr; + key_range *min_range= &mrange->start_key; + key_range *max_range= &mrange->end_key; + + if (!cur_interval) + return FALSE; + +// uchar min_val[MAX_KEY_LENGTH+MAX_FIELD_WIDTH]; +// uchar max_val[MAX_KEY_LENGTH+MAX_FIELD_WIDTH]; + min_range->key= this->min_val; + max_range->key= this->max_val; + + key_ptr= (uchar*)min_range->key; + cur_interval->store_min(range_par->key_parts[cur_key].store_length, + &key_ptr, /*cur_interval->min_flag*/ 0); + min_range->keypart_map= key_part_map(1); + min_range->length= key_ptr - min_range->key; + min_range->flag= (cur_interval->min_flag & NEAR_MIN ? HA_READ_AFTER_KEY : + HA_READ_KEY_EXACT); + + key_ptr= (uchar*)max_range->key; + cur_interval->store_max(range_par->key_parts[cur_key].store_length, + &key_ptr, /* cur_interval->max_flag */ 0); + max_range->keypart_map= key_part_map(1); + max_range->length= key_ptr - max_range->key; + max_range->flag= (cur_interval->max_flag & NEAR_MAX ? + HA_READ_BEFORE_KEY : HA_READ_AFTER_KEY); + + mrange->range_flag= cur_interval->min_flag | cur_interval->max_flag; + + cur_interval= cur_interval->next; + return TRUE; +} + + +void Condition_analyzer::dispose() +{ + THD *thd= current_thd; + dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets); + thd->no_errors=0; + thd->mem_root= range_par->old_root; + free_root(&alloc,MYF(0)); // Return memory & allocator +} + + +#if 0 +static +int search_min_max_bounds(RANGE_OPT_PARAM *ppar, SEL_ARG *key_tree) +{ + int res, left_res=0, right_res=0; + + if (key_tree->left != &null_element) + { + if (-1 == (left_res= search_min_max_bounds(ppar,key_tree->left))) + return -1; + } + + if (key_tree->type == SEL_ARG::KEY_RANGE) + { + /* + Partitioning is done by RANGE|INTERVAL(monotonic_expr(fieldX)), and + we got "const1 CMP fieldX CMP const2" interval <-- psergey-todo: change + */ + DBUG_EXECUTE("info", dbug_print_segment_range(key_tree, + ppar->key_parts);); + // key_tree->min_value, key_tree->max_value, key_tree->min_flag | key_tree->max_flag, + } + + if (key_tree->right != &null_element) + { + if (-1 == (right_res= search_min_max_bounds(ppar,key_tree->right))) + return -1; + } + return (left_res || right_res || res); +} +#endif +// psergey-minmax: end; + /* Store field key image to table record === modified file 'sql/sql_parse.cc' --- a/sql/sql_parse.cc 2010-03-04 08:03:07 +0000 +++ b/sql/sql_parse.cc 2010-04-21 14:19:33 +0000 @@ -1237,6 +1237,7 @@ general_log_write(thd, command, thd->query(), thd->query_length()); DBUG_PRINT("query",("%-.4096s",thd->query())); + fprintf(stderr, "query: %-.4096s\n",thd->query()); #if defined(ENABLED_PROFILING) && defined(COMMUNITY_SERVER) thd->profiling.set_query_source(thd->query(), thd->query_length()); #endif === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-03-10 09:12:23 +0000 +++ b/sql/sql_select.cc 2010-04-21 14:19:33 +0000 @@ -6643,6 +6643,8 @@ } } +//psergey-minmax: +bool find_min_max_bounds(THD *thd, TABLE *table, Item *pprune_cond); static void make_join_readinfo(JOIN *join, ulonglong options) @@ -6681,6 +6683,7 @@ table->status=STATUS_NO_RECORD; pick_table_access_method (tab); + //find_min_max_bounds(join->thd, table, tab->select_cond); switch (tab->type) { case JT_EQ_REF: tab->read_record.unlock_row= join_read_key_unlock_row; === modified file 'storage/myisam/ha_myisam.cc' --- a/storage/myisam/ha_myisam.cc 2009-12-03 11:34:11 +0000 +++ b/storage/myisam/ha_myisam.cc 2010-04-21 14:19:33 +0000 @@ -2269,3 +2269,88 @@ DBUG_RETURN(TRUE); } #endif + +static bool do_min_max_for_index(void* param, uint index) +{ + /* param is what was to Condition_analyzer::analyze */ + return TRUE; +} + +#include "../sql/opt_field_minmax.h" + +void store_key_image_to_rec(Field *field, uchar *ptr, uint len); +static void print_segment_range(Field *field, KEY_MULTI_RANGE *mrange); + +static void print_field(Field *field) +{ + if (field->is_real_null()) + fprintf(stderr, "NULL"); + else + { + char buf[256]; + String str(buf, sizeof(buf), &my_charset_bin); + str.length(0); + String *pstr; + pstr= field->val_str(&str); + fprintf(stderr, "'%s'", pstr->c_ptr_safe()); + } +} + +/* Print a "c1 < keypartX < c2" - type interval into debug trace. */ +static void print_segment_range(Field *field, KEY_MULTI_RANGE *mrange) +{ + if (!(mrange->range_flag & NO_MIN_RANGE)) + { + //DBUG_ASSERT(field->key_length() == mrange->start_key.length); + store_key_image_to_rec(field, (uchar*)mrange->start_key.key, mrange->start_key.length); + print_field(field); + if (mrange->range_flag & NEAR_MIN) + fputs(" < ", stderr); + else + fputs(" <= ", stderr); + } + + fprintf(stderr, "%s", field->field_name); + + if (!(mrange->range_flag & NO_MAX_RANGE)) + { + if (mrange->range_flag & NEAR_MAX) + fputs(" < ", stderr); + else + fputs(" <= ", stderr); + //DBUG_ASSERT(field->key_length() == mrange->end_key.length); + store_key_image_to_rec(field, (uchar*)mrange->end_key.key, mrange->end_key.length); + print_field(field); + } + fputs("\n", stderr); +} + +//931 202 76 04 natalya + + + +const COND * ha_myisam::cond_push(const COND *cond) +{ + fprintf(stderr, "table %s: set min-max bounds { \n", table->alias); + Condition_analyzer analyzer(table); + analyzer.analyze((Item*)cond, do_min_max_for_index, (void*)0x123456); + uint fieldno; + while (analyzer.start_intervals_walk_for_field(&fieldno)) + { + KEY_MULTI_RANGE mrange; + while (analyzer.get_next_interval(&mrange)) + { + // print the range; + print_segment_range(table->field[fieldno], &mrange); + } + } + fprintf(stderr, "}\n"); + analyzer.dispose(); + return cond; +} + +void ha_myisam::cond_pop() +{ + fprintf(stderr, "table %s: Remove min-max bounds\n", table->alias); +} + === modified file 'storage/myisam/ha_myisam.h' --- a/storage/myisam/ha_myisam.h 2009-09-07 20:50:10 +0000 +++ b/storage/myisam/ha_myisam.h 2010-04-21 14:19:33 +0000 @@ -148,4 +148,10 @@ { return file; } +//psergey-minmax: + /* + Condition pushdown + */ + const COND *cond_push(const COND *cond); + void cond_pop(); };

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2848: Fix a bunch of Windows warnings
by noreply＠launchpad.net 21 Apr '10

21 Apr '10

------------------------------------------------------------ revno: 2848 committer: Bo Thorsen <bo(a)askmonty.org> branch nick: trunk-winfix timestamp: Wed 2010-04-21 02:25:59 +0200 message: Fix a bunch of Windows warnings modified: extra/yassl/taocrypt/src/algebra.cpp sql/hash_filo.cc sql/mf_iocache.cc sql/repl_failsafe.cc sql/sql_repl.cc storage/pbxt/src/locklist_xt.cc storage/pbxt/src/pbms_enabled.cc storage/xtradb/include/univ.i storage/xtradb/sync/sync0sync.c -- 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] Review request: Remove a bunch of Windows warnings
by Bo Thorsen 20 Apr '10

20 Apr '10

Hi everyone, This patch removes most of the Windows build warnings on Windows Vista 32 bit with Visual Studio 2005. I have only built with debug info using the configuration in win\configure-mariadb.sh. I added "namespace { char dummy; };" to empty files, because the VC++ linker gives a warning when it finds empty object files. These changes are all guarded by #ifdef __WIN__. There are probably more of those when compiling with different options and other things disabled. In extra/yassl/taocrypt/src/algebra.cpp there is a re-definition of DEBUG, when building with debug info. I removed it completely, because it shouldn't be necessary at all. In storage/xtradb/include/univ.i I changed the type of UNIV_PAGE_SIZE to uint to get rid of a bunch of uint/int comparison warnings. The xtradb sync0sync.c removes a warning about an unused variable. Can I push this to lp:maria? Bo.

2 1

[Maria-developers] Learning Storage Engine API
by Igor K 20 Apr '10

20 Apr '10

Dear all, This question is directed to Sergei Golubchik at Monty Program, although input from anyone's experience would be valuable. What is the best approach, in your opinion, to start learning storage engine API? So that one can write a dummy storage engine in no time. Igor Kozachenko University of California, Berkeley

3 2

[Maria-developers] --with-embedded-privilege-control
by Kristian Nielsen 20 Apr '10

20 Apr '10

Hi Serg, I added --with-embedded-privilege-control to the -fulltest builds to avoid new occurences of MBug#563187 in the future. - Kristian.

1 1

[Maria-developers] Patch to fix the dbug printout crash in 5.3-subqueries
by Sergey Petrunya 20 Apr '10

20 Apr '10

Hi Sanja, Here is the fix: === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2010-03-29 20:09:40 +0000 +++ sql/sql_select.cc 2010-04-19 17:59:46 +0000 @@ -5214,7 +5214,7 @@ greedy_search(JOIN *join, 'join->best_positions' contains a complete optimal extension of the current partial QEP. */ - DBUG_EXECUTE("opt", print_plan(join, join->tables, + DBUG_EXECUTE("opt", print_plan(join, n_tables, record_count, read_time, read_time, "optimal");); DBUG_RETURN(FALSE); feel free to apply/push or I'll do it today night. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Getting a stable Windows host into Buildbot
by Kristian Nielsen 15 Apr '10

15 Apr '10

[Cc:ing maria-developers@, in case anyone can help with suggestions or is just generally interested. For background see http://askmonty.org/wiki/BuildBot::package] Hi Bo, Just a quick summary of our discussions about setting up a Windows Buildbot host in Buildbot using KVM to get things running stably. As our discussions in the booth at the MySQL conference did tend to get a bit fragmented due to constant interuptions :-) We need to set up a Windows installation (version to be determined, windows 7 or XP I guess). I guess having both 32 and 64 bit would be good. We need the following images: 1) Base image. A plain installation of Windows with no compilers etc. Setup to get internet access from dhcp. It needs to be running in some kind of 'server mode', we will be using it headless without any interactive desktop login. Don't know if anything special is required to set this up. It needs an account `buildbot' with a passwordless login via ssh using this public key: ssh-dss AAAAB3NzaC1kc3MAAACBAPYQjJkZ/R+77yr8snKPgndVCdJub3DDYYc3HP8lNlI1hOHlbsDbkJCe62HkXxhHKpRbrCdmb4U5Zr0gLT4IN2jq+CdQr9tDZ7bxhJBWAopq8MBMvgDgeAAzXxtQkXHnR9D4vq3Bo2q/KT1bihWKc71B5q6hvhrCSx+OyRdyiZPxAAAAFQDqRhD5nEWjABWl+wVDZVjHBRjkiQAAAIEA8umFvbGb5g/k0SlrG39OXIxzG/uy/+igIKuqSDaF90fFA/ynI9PwHKoGr2+T/aoMeJ/bsbxdR+dj5ifTX7hIelhmRWcp4933hEu5Ownz3mzQA7mDjKOFiPwtMJuWClafxlORDg29gRBmuaRlciV7a6VrVbIsQpsiV/Oseqkg2ekAAACAdMfzaIWO4a1V/zX0eIZSDZgozVZ0xkKugAonJ7UP/EJz3R9plAclnMgnHH74P8d1EbtQN4GeeEa6iIRQyBaVEHHr25DsOIdIfgz8RVm9aqU+AAuZGhMzTTGRlaG9kj6ECga74fD8evvTRxaKWWq9SHK1qZt3L9IRaVxBJ82bhSM= buildbot@thor Any ssh implementation ought to work, I know cygwin is a possibility, but if there is something better that should be ok as well. 2) Build image, to run the build. This is a copy-on-write clone of the base image, with the addition of all compilers and tools needed to build MariaDB. Just like the existing native Windows machine we have in Buildbot currently. 3) Install image, to test installing the binaries. This is again a copy-on-write clone of the base image, with addition of any extra stuff needed to install MariaDB. I am guessing that there might not be anything extra needed at all (in particular it should have no compiler tools installed). As we discussed, the actual installation should be done using a recent KVM (our Buildbot is using the one from Ubuntu 9.10/karmic). There is a lot of details about how the existing (Linux) images were installed here: http://askmonty.org/wiki/BuildBot:vm-setup#Detail_of_each_vm_image The basic idea should be something like this: qemu-img create -f qcow2 vm-win-i386-base.qcow2 20G kvm -m 2047 -hda vm-win-amd64-base.qcow2 -cdrom <install ISO> -redir tcp:2222::22 -boot d -cpu qemu32 and then install the base system. For the Build vm image, something like this to make the copy-on-write clone: qemu-img create -b vm-win-i386-base.qcow2 -f qcow2 vm-win-i386-build.qcow2 kvm -m 2047 -hda vm-win-i386-build.qcow2 -cdrom <install ISO> -redir tcp:2222::22 -boot d -cpu qemu32 and then install compilers etc. Similarly for the install vm image qemu-img create -b vm-win-i386-base.qcow2 -f qcow2 vm-win-i386-install.qcow2 kvm -m 2047 -hda vm-win-i386-install.qcow2 -cdrom <install ISO> -redir tcp:2222::22 -boot d -cpu qemu32 Hope you/we will be able to work things out from here. - Kristian.

1 0

[Maria-developers] Rev 2790: MWL#66 Subquery cache (IN & single rsult) in file:///Users/bell/maria/bzr/work-maria-5.3-subqueries-cache/
by sanja＠askmonty.org 14 Apr '10

14 Apr '10

At file:///Users/bell/maria/bzr/work-maria-5.3-subqueries-cache/ ------------------------------------------------------------ revno: 2790 revision-id: sanja(a)askmonty.org-20100414203404-8bw8wicortsfmga5 parent: timour(a)askmonty.org-20100405211515-istsgehaz7zafg0l committer: sanja(a)askmonty.org branch nick: work-maria-5.3-subqueries-cache timestamp: Wed 2010-04-14 23:34:04 +0300 message: MWL#66 Subquery cache (IN & single rsult) === modified file 'libmysqld/Makefile.am' --- a/libmysqld/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/libmysqld/Makefile.am 2010-04-14 20:34:04 +0000 @@ -80,7 +80,8 @@ sqlsources = derror.cc field.cc field_co sql_tablespace.cc \ rpl_injector.cc my_user.c partition_info.cc \ sql_servers.cc event_parse_data.cc opt_table_elimination.cc \ - multi_range_read.cc opt_index_cond_pushdown.cc + multi_range_read.cc opt_index_cond_pushdown.cc \ + sql_subquery_cache.cc libmysqld_int_a_SOURCES= $(libmysqld_sources) nodist_libmysqld_int_a_SOURCES= $(libmysqlsources) $(sqlsources) === added file 'mysql-test/r/subquery_cache.result' --- a/mysql-test/r/subquery_cache.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/subquery_cache.result 2010-04-14 20:34:04 +0000 @@ -0,0 +1,369 @@ +set optimizer_switch='subquery_cache=on'; +create table t1 (a int, b int); +insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5); +create table t2 (c int, d int); +insert into t2 values (2,3),(3,4),(5,6); +#single value subquery test +select a, (select d from t2 where b=c) + 1 from t1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +#single value subquery test (PS) +prepare stmt1 from 'select a, (select d from t2 where b=c) + 1 from t1'; +execute stmt1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 12 +Subquery_cache_miss 8 +execute stmt1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 18 +Subquery_cache_miss 12 +deallocate prepare stmt1; +#single value subquery test (SP) +CREATE PROCEDURE p1() select a, (select d from t2 where b=c) + 1 from t1; +call p1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +call p1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +drop procedure p1; +#IN subquery test +flush status; +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +select a, b , b in (select d from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +insert into t1 values (7,8),(9,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +7 8 0 +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 12 +Subquery_cache_miss 10 +insert into t2 values (8,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +7 8 NULL +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 18 +Subquery_cache_miss 16 +#IN subquery tesy (PS) +delete from t1 where a > 6; +delete from t2 where c > 6; +prepare stmt1 from 'select a, b , b in (select d from t2) as SUBS from t1'; +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 24 +Subquery_cache_miss 20 +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 30 +Subquery_cache_miss 24 +insert into t1 values (7,8),(9,NULL); +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 36 +Subquery_cache_miss 30 +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 42 +Subquery_cache_miss 36 +insert into t2 values (8,NULL); +execute stmt1; +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 48 +Subquery_cache_miss 42 +execute stmt1; +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 54 +Subquery_cache_miss 48 +deallocate prepare stmt1; +#IN subquery tesy (SP) +delete from t1 where a > 6; +delete from t2 where c > 6; +CREATE PROCEDURE p1() select a, b , b in (select d from t2) as SUBS from t1; +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 60 +Subquery_cache_miss 52 +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 66 +Subquery_cache_miss 56 +insert into t1 values (7,8),(9,NULL); +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 72 +Subquery_cache_miss 62 +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 78 +Subquery_cache_miss 68 +insert into t2 values (8,NULL); +call p1(); +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 84 +Subquery_cache_miss 74 +call p1(); +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 90 +Subquery_cache_miss 80 +drop procedure p1; +#clean up +drop table t1,t2; +set optimizer_switch='subquery_cache=default'; === added file 'mysql-test/t/subquery_cache.test' --- a/mysql-test/t/subquery_cache.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/subquery_cache.test 2010-04-14 20:34:04 +0000 @@ -0,0 +1,98 @@ + +set optimizer_switch='subquery_cache=on'; + +create table t1 (a int, b int); +insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5); +create table t2 (c int, d int); +insert into t2 values (2,3),(3,4),(5,6); + +--echo #single value subquery test +select a, (select d from t2 where b=c) + 1 from t1; + +show status like "subquery_cache%"; + +--echo #single value subquery test (PS) +prepare stmt1 from 'select a, (select d from t2 where b=c) + 1 from t1'; +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; +deallocate prepare stmt1; + +--echo #single value subquery test (SP) +CREATE PROCEDURE p1() select a, (select d from t2 where b=c) + 1 from t1; + +call p1; +call p1; + +drop procedure p1; + +--echo #IN subquery test +flush status; + +show status like "subquery_cache%"; +select a, b , b in (select d from t2) as SUBS from t1; +show status like "subquery_cache%"; + +insert into t1 values (7,8),(9,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +show status like "subquery_cache%"; + +insert into t2 values (8,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +show status like "subquery_cache%"; + +--echo #IN subquery tesy (PS) +delete from t1 where a > 6; +delete from t2 where c > 6; + +prepare stmt1 from 'select a, b , b in (select d from t2) as SUBS from t1'; +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; + +insert into t1 values (7,8),(9,NULL); +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; + +insert into t2 values (8,NULL); +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; + +deallocate prepare stmt1; + + +--echo #IN subquery tesy (SP) +delete from t1 where a > 6; +delete from t2 where c > 6; + +CREATE PROCEDURE p1() select a, b , b in (select d from t2) as SUBS from t1; + +call p1(); +show status like "subquery_cache%"; +call p1(); +show status like "subquery_cache%"; + +insert into t1 values (7,8),(9,NULL); +call p1(); +show status like "subquery_cache%"; +call p1(); +show status like "subquery_cache%"; + +insert into t2 values (8,NULL); +call p1(); +show status like "subquery_cache%"; +call p1(); +show status like "subquery_cache%"; + +drop procedure p1; + +--echo #clean up +drop table t1,t2; + +set optimizer_switch='subquery_cache=default'; === modified file 'sql/CMakeLists.txt' --- a/sql/CMakeLists.txt 2010-03-20 12:01:47 +0000 +++ b/sql/CMakeLists.txt 2010-04-14 20:34:04 +0000 @@ -78,7 +78,7 @@ SET (SQL_SOURCE rpl_rli.cc rpl_mi.cc sql_servers.cc sql_connect.cc scheduler.cc sql_profile.cc event_parse_data.cc opt_table_elimination.cc - ds_mrr.cc + ds_mrr.cc sql_subquery_cache.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.h ${PROJECT_SOURCE_DIR}/include/mysqld_error.h === modified file 'sql/Makefile.am' --- a/sql/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/sql/Makefile.am 2010-04-14 20:34:04 +0000 @@ -80,7 +80,7 @@ noinst_HEADERS = item.h item_func.h item event_data_objects.h event_scheduler.h \ sql_partition.h partition_info.h partition_element.h \ contributors.h sql_servers.h \ - multi_range_read.h + multi_range_read.h sql_subquery_cache.h mysqld_SOURCES = sql_lex.cc sql_handler.cc sql_partition.cc \ item.cc item_sum.cc item_buff.cc item_func.cc \ @@ -130,7 +130,7 @@ mysqld_SOURCES = sql_lex.cc sql_handler. sql_servers.cc event_parse_data.cc \ opt_table_elimination.cc \ multi_range_read.cc \ - opt_index_cond_pushdown.cc + opt_index_cond_pushdown.cc sql_subquery_cache.cc nodist_mysqld_SOURCES = mini_client_errors.c pack.c client.c my_time.c my_user.c === modified file 'sql/item.cc' --- a/sql/item.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item.cc 2010-04-14 20:34:04 +0000 @@ -2273,6 +2273,13 @@ void Item_int::print(String *str, enum_q str->append(str_value); } +void Item_bool_cache::print(String *str, enum_query_type query_type) +{ + if (null_value) + str->append("NULL", 4); + else + Item_int::print(str, query_type); +} Item_uint::Item_uint(const char *str_arg, uint length): Item_int(str_arg, length) @@ -3646,12 +3653,17 @@ static bool mark_as_dependent(THD *thd, resolved_item->db_name : ""); const char *table_name= (resolved_item->table_name ? resolved_item->table_name : ""); + DBUG_ENTER("mark_as_dependent"); + DBUG_PRINT("enter", ("Field '%s.%s.%s in select %d resolved in %d", + db_name, table_name, + resolved_item->field_name, current->select_number, + last->select_number)); /* store pointer on SELECT_LEX from which item is dependent */ if (mark_item) mark_item->depended_from= last; if (current->mark_as_dependent(thd, last, /** resolved_item psergey-thu **/mark_item)) - return TRUE; + DBUG_RETURN(TRUE); if (thd->lex->describe & DESCRIBE_EXTENDED) { push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, @@ -3661,7 +3673,7 @@ static bool mark_as_dependent(THD *thd, resolved_item->field_name, current->select_number, last->select_number); } - return FALSE; + DBUG_RETURN(FALSE); } @@ -3726,6 +3739,7 @@ void mark_select_range_as_dependent(THD mark_as_dependent(thd, last_select, current_sel, resolved_item, dependent); } + return; } === modified file 'sql/item.h' --- a/sql/item.h 2010-03-20 12:01:47 +0000 +++ b/sql/item.h 2010-04-14 20:34:04 +0000 @@ -1922,8 +1922,31 @@ public: virtual void print(String *str, enum_query_type query_type); Item_num *neg (); uint decimal_precision() const { return max_length; } - bool check_partition_func_processor(uchar *bool_arg) { return FALSE;} - bool check_vcol_func_processor(uchar *arg) { return FALSE;} +}; + + +/** + Item represent TRUE/FALSE/NULL for subquery values +*/ + +class Item_bool_cache: public Item_int +{ +public: + Item_bool_cache(): Item_int(0, 1) + { + unsigned_flag= maybe_null= null_value= TRUE; + name= (char *)"bool chache"; + } + Item_bool_cache(my_bool val, my_bool null): Item_int(val, 1) + { + unsigned_flag= maybe_null= TRUE; + null_value= null; + name= (char *)"bool chache"; + } + Item *clone_item() { return new Item_bool_cache(value, null_value); } + uint decimal_precision() const { return 1; } + virtual void print(String *str, enum_query_type query_type); + void set(my_bool val, my_bool null) {value= test(val); null_value= null;} }; @@ -3146,7 +3169,8 @@ public: example(0), used_table_map(0), cached_field(0), cached_field_type(MYSQL_TYPE_STRING), value_cached(0) { - fixed= 1; + fixed= 1; + maybe_null= 1; null_value= 1; } Item_cache(enum_field_types field_type_arg): @@ -3154,6 +3178,7 @@ public: value_cached(0) { fixed= 1; + maybe_null= 1; null_value= 1; } === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.cc 2010-04-14 20:34:04 +0000 @@ -1736,6 +1736,12 @@ bool Item_in_optimizer::fix_fields(THD * used_tables_cache|= args[1]->used_tables(); not_null_tables_cache|= args[1]->not_null_tables(); const_item_cache&= args[1]->const_item(); + DBUG_ASSERT(scache == NULL); + if (thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE) + { + sub->depends_on.push_front(cache); + scache= new Subquery_cache_tmptable(thd, sub->depends_on, &result); + } fixed= 1; return FALSE; } @@ -1744,10 +1750,26 @@ bool Item_in_optimizer::fix_fields(THD * longlong Item_in_optimizer::val_int() { bool tmp; + DBUG_ENTER("Item_in_optimizer::val_int"); + DBUG_ASSERT(fixed == 1); cache->store(args[0]); cache->cache_value(); - + + /* check if result is in the cache */ + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + { + tmp= cached_value->val_int(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + } + if (cache->null_value) { /* @@ -1818,11 +1840,18 @@ longlong Item_in_optimizer::val_int() for (uint i= 0; i < ncols; i++) item_subs->set_cond_guard_var(i, TRUE); } - return 0; + DBUG_RETURN(0); } tmp= args[1]->val_bool_result(); null_value= args[1]->null_value; - return tmp; + + /* put result in the cache */ + if (scache) + { + result.set(tmp, null_value); + scache->put_value(&result); + } + DBUG_RETURN(tmp); } @@ -1839,6 +1868,11 @@ void Item_in_optimizer::cleanup() Item_bool_func::cleanup(); if (!save_cache) cache= 0; + if (scache) + { + delete scache; + scache= 0; + } DBUG_VOID_RETURN; } === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.h 2010-04-14 20:34:04 +0000 @@ -215,6 +215,7 @@ public: class Item_cache; +class Subquery_cache; #define UNKNOWN ((my_bool)-1) @@ -237,6 +238,10 @@ class Item_in_optimizer: public Item_boo { protected: Item_cache *cache; + /* Subquery cache */ + Subquery_cache *scache; + /* result representation for the subquery cache */ + Item_bool_cache result; bool save_cache; /* Stores the value of "NULL IN (SELECT ...)" for uncorrelated subqueries: @@ -247,7 +252,7 @@ protected: my_bool result_for_null_param; public: Item_in_optimizer(Item *a, Item_in_subselect *b): - Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), + Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), scache(NULL), save_cache(0), result_for_null_param(UNKNOWN) {} bool fix_fields(THD *, Item **); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-04-02 14:27:06 +0000 +++ b/sql/item_subselect.cc 2010-04-14 20:34:04 +0000 @@ -1,4 +1,4 @@ -/* Copyright (C) 2000 MySQL AB +/* Copyrigh (C) 2000 MySQL AB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -34,11 +34,10 @@ Item_subselect::Item_subselect(): Item_result_field(), value_assigned(0), thd(0), substitution(0), - engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0), - const_item_cache(1), - inside_first_fix_fields(0), done_first_fix_fields(FALSE), - eliminated(FALSE), - engine_changed(0), changed(0), is_correlated(FALSE) + engine(0), old_engine(0), scache(0), used_tables_cache(0), + have_to_be_excluded(0), const_item_cache(1), inside_first_fix_fields(0), + done_first_fix_fields(FALSE), eliminated(FALSE), engine_changed(0), + changed(0), is_correlated(FALSE) { with_subselect= 1; reset(); @@ -116,6 +115,12 @@ void Item_subselect::cleanup() } if (engine) engine->cleanup(); + depends_on.empty(); + if (scache) + { + delete scache; + scache= 0; + } reset(); value_assigned= 0; DBUG_VOID_RETURN; @@ -148,6 +153,8 @@ void Item_in_subselect::cleanup() Item_subselect::~Item_subselect() { delete engine; + if (scache) + delete scache; } Item_subselect::trans_res @@ -746,9 +753,19 @@ enum_field_types Item_singlerow_subselec void Item_singlerow_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_singlerow_subselect::fix_length_and_dec"); if ((max_columns= engine->cols()) == 1) { + DBUG_PRINT("info", ("one, elements: %u flag %u", + (uint)depends_on.elements, + (uint)test(thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE))); engine->fix_length_and_dec(row= &value); + if (depends_on.elements && optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE)) + { + DBUG_ASSERT(scache == NULL); + scache= new Subquery_cache_tmptable(thd, depends_on, value); + DBUG_PRINT("info", ("cache: 0x%lx", (ulong) scache)); + } } else { @@ -765,6 +782,7 @@ void Item_singlerow_subselect::fix_lengt */ if (engine->no_tables()) maybe_null= engine->may_be_null(); + DBUG_VOID_RETURN; } uint Item_singlerow_subselect::cols() @@ -797,46 +815,117 @@ void Item_singlerow_subselect::bring_val exec(); } + +Item *Item_singlerow_subselect::check_cache() +{ + DBUG_ENTER("Item_singlerow_subselect::check_cache"); + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + DBUG_RETURN(cached_value); + } + DBUG_RETURN(NULL); +} + double Item_singlerow_subselect::val_real() { + Item *cached_value; DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + double res= cached_value->val_real(); + if ((null_value= cached_value->null_value)) + { + reset(); + return 0; + } + else + return res; + } + if (!exec() && !value->null_value) { null_value= 0; + if (scache) + scache->put_value(value); return value->val_real(); } else { reset(); + if (scache) + scache->put_value(&const_null_value); return 0; } } longlong Item_singlerow_subselect::val_int() { + Item *cached_value; + DBUG_ENTER("Item_singlerow_subselect::val_int"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + longlong res= cached_value->val_int(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + if (!exec() && !value->null_value) { null_value= 0; - return value->val_int(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_int()); } else { reset(); - return 0; + if (scache) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } String *Item_singlerow_subselect::val_str(String *str) { + Item *cached_value; + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + String *res= cached_value->val_str(str); + if ((null_value= cached_value->null_value)) + { + reset(); + return 0; + } + else + return res; + } + if (!exec() && !value->null_value) { null_value= 0; + if (scache) + scache->put_value(value); return value->val_str(str); } else { reset(); + if (scache) + scache->put_value(&const_null_value); return 0; } } @@ -844,14 +933,33 @@ String *Item_singlerow_subselect::val_st my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) { + Item *cached_value; + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_decimal *res= cached_value->val_decimal(decimal_value); + if ((null_value= cached_value->null_value)) + { + reset(); + return 0; + } + else + return res; + } + if (!exec() && !value->null_value) { null_value= 0; + if (scache) + scache->put_value(value); return value->val_decimal(decimal_value); } else { reset(); + if (scache) + scache->put_value(&const_null_value); return 0; } } @@ -859,14 +967,33 @@ my_decimal *Item_singlerow_subselect::va bool Item_singlerow_subselect::val_bool() { + Item *cached_value; + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + bool res= cached_value->val_bool(); + if ((null_value= cached_value->null_value)) + { + reset(); + return 0; + } + else + return res; + } + if (!exec() && !value->null_value) { null_value= 0; + if (scache) + scache->put_value(value); return value->val_bool(); } else { reset(); + if (scache) + scache->put_value(&const_null_value); return 0; } } === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-03-29 14:04:35 +0000 +++ b/sql/item_subselect.h 2010-04-14 20:34:04 +0000 @@ -27,6 +27,7 @@ class subselect_engine; class subselect_hash_sj_engine; class Item_bool_func2; class Cached_item; +class Subquery_cache; /* base class for subselects */ @@ -57,6 +58,10 @@ protected: subselect_engine *engine; /* old engine if engine was changed */ subselect_engine *old_engine; + /* subquery cache */ + Subquery_cache *scache; + /* null consrtant for caching */ + Item_null const_null_value; /* cache of used external tables */ table_map used_tables_cache; /* allowed number of columns (1 for single value subqueries) */ @@ -67,7 +72,7 @@ protected: bool have_to_be_excluded; /* cache of constant state */ bool const_item_cache; - + bool inside_first_fix_fields; bool done_first_fix_fields; public: @@ -88,13 +93,18 @@ public: */ List<Ref_to_outside> upper_refs; st_select_lex *parent_select; - - /* + + /** + List of items subquery depends on (externally resolved); + */ + List<Item> depends_on; + + /* TRUE<=>Table Elimination has made it redundant to evaluate this select (and so it is not part of QEP, etc) - */ + */ bool eliminated; - + /* changed engine indicator */ bool engine_changed; /* subquery is transformed */ @@ -202,6 +212,8 @@ class Item_singlerow_subselect :public I { protected: Item_cache *value, **row; + + Item *check_cache(); public: Item_singlerow_subselect(st_select_lex *select_lex); Item_singlerow_subselect() :Item_subselect(), value(0), row (0) {} === modified file 'sql/mysql_priv.h' --- a/sql/mysql_priv.h 2010-03-20 12:01:47 +0000 +++ b/sql/mysql_priv.h 2010-04-14 20:34:04 +0000 @@ -568,12 +568,13 @@ protected: #define OPTIMIZER_SWITCH_SEMIJOIN 256 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE 512 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN 1024 +#define OPTIMIZER_SWITCH_SUBQUERY_CACHE (1<<11) #ifdef DBUG_OFF -# define OPTIMIZER_SWITCH_LAST 2048 +# define OPTIMIZER_SWITCH_LAST (1<<12) #else -# define OPTIMIZER_SWITCH_TABLE_ELIMINATION 2048 -# define OPTIMIZER_SWITCH_LAST 4096 +# define OPTIMIZER_SWITCH_TABLE_ELIMINATION (1<<12) +# define OPTIMIZER_SWITCH_LAST (1<<13) #endif #ifdef DBUG_OFF @@ -588,7 +589,8 @@ protected: OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #else # define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -601,7 +603,8 @@ protected: OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #endif /* @@ -936,6 +939,7 @@ bool general_log_write(THD *thd, enum en #ifdef MYSQL_SERVER #include "sql_servers.h" #include "opt_range.h" +#include "sql_subquery_cache.h" #ifdef HAVE_QUERY_CACHE struct Query_cache_query_flags @@ -1269,6 +1273,10 @@ bool mysql_select(THD *thd, Item ***rref Item *having, ORDER *proc_param, ulonglong select_type, select_result *result, SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex); + +struct st_join_table *create_index_lookup_join_tab(TABLE *table); +int join_read_key2(THD *thd, struct st_join_table *tab, TABLE *table, + struct st_table_ref *table_ref); void free_underlaid_joins(THD *thd, SELECT_LEX *select); bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result); @@ -1288,6 +1296,7 @@ Field *create_tmp_field(THD *thd, TABLE bool table_cant_handle_bit_fields, bool make_copy_field, uint convert_blob_length); +bool open_tmp_table(TABLE *table); void sp_prepare_create_field(THD *thd, Create_field *sql_field); int prepare_create_field(Create_field *sql_field, uint *blob_columns, === modified file 'sql/mysqld.cc' --- a/sql/mysqld.cc 2010-03-20 12:01:47 +0000 +++ b/sql/mysqld.cc 2010-04-14 20:34:04 +0000 @@ -305,6 +305,7 @@ static const char *optimizer_switch_name "firstmatch","loosescan","materialization", "semijoin", "partial_match_rowid_merge", "partial_match_table_scan", + "subquery_cache", #ifndef DBUG_OFF "table_elimination", #endif @@ -325,6 +326,7 @@ static const unsigned int optimizer_swit sizeof("semijoin") - 1, sizeof("partial_match_rowid_merge") - 1, sizeof("partial_match_table_scan") - 1, + sizeof("subquery_cache") - 1, #ifndef DBUG_OFF sizeof("table_elimination") - 1, #endif @@ -404,8 +406,9 @@ static const char *sql_mode_str= "OFF"; static const char *optimizer_switch_str="index_merge=on,index_merge_union=on," "index_merge_sort_union=on," "index_merge_intersection=on," - "index_condition_pushdown=on" -#ifndef DBUG_OFF + "index_condition_pushdown=on," + "subquery_cache=on" +#ifndef DBUG_OFF ",table_elimination=on"; #else ; @@ -5872,7 +5875,9 @@ enum options_mysqld OPT_RECORD_RND_BUFFER, OPT_DIV_PRECINCREMENT, OPT_RELAY_LOG_SPACE_LIMIT, OPT_RELAY_LOG_PURGE, OPT_SLAVE_NET_TIMEOUT, OPT_SLAVE_COMPRESSED_PROTOCOL, OPT_SLOW_LAUNCH_TIME, - OPT_SLAVE_TRANS_RETRIES, OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, + OPT_SLAVE_TRANS_RETRIES, + OPT_SUBQUERY_CACHE, + OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, OPT_DEBUGGING, OPT_DEBUG_FLUSH, OPT_SORT_BUFFER, OPT_TABLE_OPEN_CACHE, OPT_TABLE_DEF_CACHE, OPT_THREAD_CONCURRENCY, OPT_THREAD_CACHE_SIZE, @@ -7164,7 +7169,7 @@ The minimum value for this variable is 4 {"optimizer_switch", OPT_OPTIMIZER_SWITCH, "optimizer_switch=option=val[,option=val...], where option={index_merge, " "index_merge_union, index_merge_sort_union, index_merge_intersection, " - "index_condition_pushdown" + "index_condition_pushdown, subquery_cache" #ifndef DBUG_OFF ", table_elimination" #endif @@ -7868,6 +7873,8 @@ SHOW_VAR status_vars[]= { {"Ssl_version", (char*) &show_ssl_get_version, SHOW_FUNC}, #endif /* HAVE_OPENSSL */ {"Syncs", (char*) &my_sync_count, SHOW_LONG_NOFLUSH}, + {"Subquery_cache_hit", (char*) &subquery_cache_hit, SHOW_LONG}, + {"Subquery_cache_miss", (char*) &subquery_cache_miss, SHOW_LONG}, {"Table_locks_immediate", (char*) &locks_immediate, SHOW_LONG}, {"Table_locks_waited", (char*) &locks_waited, SHOW_LONG}, #ifdef HAVE_MMAP @@ -8006,6 +8013,7 @@ static int mysql_init_variables(void) abort_loop= select_thread_in_use= signal_thread_in_use= 0; ready_to_exit= shutdown_in_progress= grant_option= 0; aborted_threads= aborted_connects= 0; + subquery_cache_miss= subquery_cache_hit= 0; delayed_insert_threads= delayed_insert_writes= delayed_rows_in_use= 0; delayed_insert_errors= thread_created= 0; specialflag= 0; === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_base.cc 2010-04-14 20:34:04 +0000 @@ -8062,6 +8062,10 @@ int setup_conds(THD *thd, TABLE_LIST *ta if (*conds) { thd->where="where clause"; + DBUG_EXECUTE("where", + print_where(*conds, + "WHERE in setup_conds", + QT_ORDINARY);); if ((!(*conds)->fixed && (*conds)->fix_fields(thd, conds)) || (*conds)->check_cols(1)) goto err_no_arena; === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2010-04-05 21:15:15 +0000 +++ b/sql/sql_class.cc 2010-04-14 20:34:04 +0000 @@ -3034,6 +3034,7 @@ void TMP_TABLE_PARAM::init() table_charset= 0; precomputed_group_by= 0; bit_fields_as_long= 0; + skip_create_table= 0; DBUG_VOID_RETURN; } === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2010-04-05 21:15:15 +0000 +++ b/sql/sql_class.h 2010-04-14 20:34:04 +0000 @@ -2786,6 +2786,11 @@ public: that MEMORY tables cannot index BIT columns. */ bool bit_fields_as_long; + /* + Whether to create or postpone actual creation of this temporary table. + TRUE <=> create_tmp_table will create only the TABLE structure. + */ + bool skip_create_table; TMP_TABLE_PARAM() :copy_field(0), group_parts(0), === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_lex.cc 2010-04-14 20:34:04 +0000 @@ -1843,7 +1843,7 @@ void st_select_lex_unit::exclude_tree() bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency) { - + DBUG_ENTER("st_select_lex::mark_as_dependent"); DBUG_ASSERT(this != last); /* @@ -1868,15 +1868,22 @@ bool st_select_lex::mark_as_dependent(TH sl->uncacheable|= UNCACHEABLE_UNITED; } } + if (dependency && dependency->fixed) + { + s->master_unit()->item->depends_on.push_back(dependency); + DBUG_PRINT("info", ("depends_on: Select: %d added: %s", + s->select_number, + (dependency->name ? dependency->name : "<no name>"))); + } Item_subselect *subquery_expr= s->master_unit()->item; if (subquery_expr && subquery_expr->mark_as_dependent(thd, last, dependency)) - return TRUE; + DBUG_RETURN(TRUE); } while ((s= s->outer_select()) != last && s != 0); is_correlated= TRUE; this->master_unit()->item->is_correlated= TRUE; - return FALSE; + DBUG_RETURN(FALSE); } bool st_select_lex_node::set_braces(bool value) { return 1; } === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-03-29 20:09:40 +0000 +++ b/sql/sql_select.cc 2010-04-14 20:34:04 +0000 @@ -156,7 +156,6 @@ static int join_read_const_table(JOIN_TA static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); -static int join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref); static void join_read_key_unlock_row(st_join_table *tab); static int join_read_always_key(JOIN_TAB *tab); static int join_read_last_key(JOIN_TAB *tab); @@ -7630,6 +7629,40 @@ make_join_readinfo(JOIN *join, ulonglong /** + Creates and fills JOIN_TAB for index look up in temporary table + + @param table The table where to look up + + @return JOIN_TAB object or NULL in case of error +*/ + +JOIN_TAB *create_index_lookup_join_tab(TABLE *table) +{ + JOIN_TAB *tab; + DBUG_ENTER("create_index_lookup_join_tab"); + + if (!((tab= new JOIN_TAB))) + DBUG_RETURN(NULL); + tab->read_record.table= table; + tab->read_record.file=table->file; + /*tab->read_record.unlock_row= rr_unlock_row;*/ + tab->next_select=0; + tab->sorted= 1; + + table->status= STATUS_NO_RECORD; + tab->read_first_record= join_read_key; + /*tab->read_record.unlock_row= join_read_key_unlock_row;*/ + tab->read_record.read_record= join_no_more_records; + if (table->covering_keys.is_set(tab->ref.key) && + !table->no_keyread) + { + table->key_read=1; + table->file->extra(HA_EXTRA_KEYREAD); + } + DBUG_RETURN(tab); +} + +/** Give error if we some tables are done with a full join. This is used by multi_table_update and multi_table_delete when running @@ -10783,6 +10816,7 @@ Field *create_tmp_field(THD *thd, TABLE case Item::REF_ITEM: case Item::NULL_ITEM: case Item::VARBIN_ITEM: + case Item::CACHE_ITEM: if (make_copy_field) { DBUG_ASSERT(((Item_result_field*)item)->result_field); @@ -11557,7 +11591,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA &param->recinfo, select_options)) goto err; } - if (open_tmp_table(table)) + DBUG_PRINT("info", ("skip_create_table: %d", (int)param->skip_create_table)); + if (!param->skip_create_table && open_tmp_table(table)) goto err; thd->mem_root= mem_root_save; @@ -11705,16 +11740,17 @@ error: bool open_tmp_table(TABLE *table) { int error; + DBUG_ENTER("open_tmp_table"); if ((error= table->file->ha_open(table, table->s->table_name.str, O_RDWR, HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE))) { table->file->print_error(error,MYF(0)); /* purecov: inspected */ table->db_stat=0; - return(1); + DBUG_RETURN(1); } (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ - return(0); + DBUG_RETURN(0); } @@ -12543,7 +12579,8 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi else { /* Do index lookup in the materialized table */ - if ((res= join_read_key2(join_tab, sjm->table, sjm->tab_ref)) == 1) + if ((res= join_read_key2(join_tab->join->thd, join_tab, + sjm->table, sjm->tab_ref)) == 1) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if (res || !sjm->in_equality->val_int()) DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); @@ -13326,61 +13363,61 @@ join_read_const(JOIN_TAB *tab) static int join_read_key(JOIN_TAB *tab) { - return join_read_key2(tab, tab->table, &tab->ref); + return join_read_key2(tab->join->thd, tab, tab->table, &tab->ref); } -/* +/* eq_ref access handler but generalized a bit to support TABLE and TABLE_REF not from the join_tab. See join_read_key for detailed synopsis. */ -static int -join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) +int join_read_key2(THD *thd, JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) { int error; + DBUG_ENTER("join_read_key2"); if (!table->file->inited) { table->file->ha_index_init(table_ref->key, tab->sorted); } /* TODO: Why don't we do "Late NULLs Filtering" here? */ - if (cmp_buffer_with_ref(tab->join->thd, table, table_ref) || + if (cmp_buffer_with_ref(thd, table, table_ref) || (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) { if (table_ref->key_err) { table->status=STATUS_NOT_FOUND; - return -1; + DBUG_RETURN(-1); } /* Moving away from the current record. Unlock the row in the handler if it did not match the partial WHERE. */ - if (tab->ref.has_record && tab->ref.use_count == 0) + if (table_ref->has_record && table_ref->use_count == 0) { tab->read_record.file->unlock_row(); - tab->ref.has_record= FALSE; + table_ref->has_record= FALSE; } error=table->file->ha_index_read_map(table->record[0], table_ref->key_buff, make_prev_keypart_map(table_ref->key_parts), HA_READ_KEY_EXACT); if (error && error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE) - return report_error(table, error); + DBUG_RETURN(report_error(table, error)); if (! error) { - tab->ref.has_record= TRUE; - tab->ref.use_count= 1; + table_ref->has_record= TRUE; + table_ref->use_count= 1; } } else if (table->status == 0) { - DBUG_ASSERT(tab->ref.has_record); - tab->ref.use_count++; + DBUG_ASSERT(table_ref->has_record); + table_ref->use_count++; } table->null_row=0; - return table->status ? -1 : 0; + DBUG_RETURN(table->status ? -1 : 0); } === added file 'sql/sql_subquery_cache.cc' --- a/sql/sql_subquery_cache.cc 1970-01-01 00:00:00 +0000 +++ b/sql/sql_subquery_cache.cc 2010-04-14 20:34:04 +0000 @@ -0,0 +1,236 @@ + +#include "mysql_priv.h" +#include "sql_select.h" + +ulonglong subquery_cache_miss, subquery_cache_hit; + +/** + Creates structures which we need for index look up + + @retval FALSE OK + @retval TRUE Error +*/ + +static my_bool createtmp_table_search_structures(THD *thd, + TABLE *table, + List_iterator_fast<Item> &li, + TABLE_REF **ref) +{ + /* + Create/initialize everything we will need to index lookups into the + temptable. + */ + TABLE_REF *tab_ref; + KEY *tmp_key; /* The only index on the temporary table. */ + Item *item; + uint tmp_key_parts; /* Number of keyparts in tmp_key. */ + uint i; + + DBUG_ENTER("createtmp_table_search_structures"); + + tmp_key= table->key_info; + tmp_key_parts= tmp_key->key_parts; + + if (!(tab_ref= (TABLE_REF*) thd->alloc(sizeof(TABLE_REF)))) + DBUG_RETURN(TRUE); + + tab_ref->key= 0; /* The only temp table index. */ + tab_ref->key_length= tmp_key->key_length; + if (!(tab_ref->key_buff= + (uchar*) thd->calloc(ALIGN_SIZE(tmp_key->key_length) * 2)) || + !(tab_ref->key_copy= + (store_key**) thd->alloc((sizeof(store_key*) * + (tmp_key_parts + 1)))) || + !(tab_ref->items= + (Item**) thd->alloc(sizeof(Item*) * tmp_key_parts))) + DBUG_RETURN(TRUE); /* purecov: inspected */ + + tab_ref->key_buff2=tab_ref->key_buff+ALIGN_SIZE(tmp_key->key_length); + tab_ref->key_err=1; + tab_ref->null_rejecting= 1; + tab_ref->disable_cache= FALSE; + tab_ref->has_record= 0; + + KEY_PART_INFO *cur_key_part= tmp_key->key_part; + store_key **ref_key= tab_ref->key_copy; + uchar *cur_ref_buff= tab_ref->key_buff; + + for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) + { + item= li++; + DBUG_ASSERT(item); + tab_ref->items[i]= item; + int null_count= test(cur_key_part->field->real_maybe_null()); + *ref_key= new store_key_item(thd, cur_key_part->field, + /* TODO: + the NULL byte is taken into account in + cur_key_part->store_length, so instead of + cur_ref_buff + test(maybe_null), we could + use that information instead. + */ + cur_ref_buff + null_count, + null_count ? tab_ref->key_buff : 0, + cur_key_part->length, tab_ref->items[i]); + cur_ref_buff+= cur_key_part->store_length; + } + *ref_key= NULL; /* End marker. */ + tab_ref->key_err= 1; + tab_ref->key_parts= tmp_key_parts; + *ref= tab_ref; + + DBUG_RETURN(FALSE); +} + + +Subquery_cache_tmptable::Subquery_cache_tmptable(THD *thd, + List<Item> &dependance, + Item *value) + :cache_table(NULL), table_thd(thd) +{ + ulonglong keymap; + List_iterator_fast<Item> li(dependance); + DBUG_ENTER("Subquery_cache_tmptable::Subquery_cache_tmptable"); + + if (!(ULONGLONG_MAX >> (dependance.elements + 1))) + { + DBUG_PRINT("info", ("Too many dependencies")); + DBUG_VOID_RETURN; + } + + cache_table= NULL; + list= &dependance; + + cache_table_param.init(); + /* dependance items and result */ + cache_table_param.field_count= dependance.elements + 1; + /* postpone table creation to index description */ + cache_table_param.skip_create_table= 1; + + + dependance.push_front(value); + if (!(cache_table= create_tmp_table(thd, &cache_table_param, + dependance, (ORDER*) NULL, + FALSE, FALSE, + thd->options | TMP_TABLE_ALL_COLUMNS, + HA_POS_ERROR, + (char *)"subquery-cache-table"))) + { + DBUG_PRINT("error", ("create_tmp_table failed, caching switched off")); + DBUG_VOID_RETURN; + } + + /* makes all bits set for keys */ + keymap= 1 << (dependance.elements); /* + 1 - 1 */ + if (!keymap) + keymap= ULONGLONG_MAX; + else + keymap--; + keymap&=~1; + + li++; + if (cache_table->alloc_keys(1) || + (cache_table->add_tmp_key(keymap, "cache-table-key") < 0) || + createtmp_table_search_structures(thd, cache_table, li, &tab_ref) || + !(tab= create_index_lookup_join_tab(cache_table))) + { + DBUG_PRINT("error", ("creating index failed")); + goto error; + } + cache_table->s->keys= 1; + cache_table->s->uniques= 1; + + if (open_tmp_table(cache_table)) + { + DBUG_PRINT("error", ("Opening (creating) temporary table failed")); + goto error; + } + + if (!(chached_result= new Item_field(cache_table->field[0]))) + { + DBUG_PRINT("error", ("Creating Item_field failed")); + goto error; + } + + DBUG_VOID_RETURN; + +error: + /* switch off cache */ + free_tmp_table(thd, cache_table); + cache_table= NULL; + DBUG_VOID_RETURN; +} + + +Subquery_cache_tmptable::~Subquery_cache_tmptable() +{ + if (cache_table) + free_tmp_table(table_thd, cache_table); +} + + +Subquery_cache::result Subquery_cache_tmptable::check_value(Item **value) +{ + int res; + DBUG_ENTER("Subquery_cache_tmptable::check_value"); + if (cache_table) + { + DBUG_PRINT("info", ("status: %u has_record %u", + (uint)cache_table->status, (uint)tab_ref->has_record)); + if ((res= join_read_key2(table_thd, tab, cache_table, tab_ref)) == 1) + DBUG_RETURN(ERROR); /* purecov: inspected */ + if (res) + { + subquery_cache_miss++; + DBUG_RETURN(MISS); + } + + subquery_cache_hit++; + *value= chached_result; + DBUG_RETURN(Subquery_cache::HIT); + } + DBUG_RETURN(Subquery_cache::MISS); +} + + +my_bool Subquery_cache_tmptable::put_value(Item *value) +{ + int error; + DBUG_ENTER("Subquery_cache_tmptable::put_value"); + if (!cache_table) + { + DBUG_PRINT("info", ("No table so behave as we successfully put value")); + DBUG_RETURN(FALSE); + } + + *(list->head_ref())= value; + fill_record(table_thd, cache_table->field, *list, 1); + if (table_thd->is_error()) + goto err;; + + if ((error= cache_table->file->ha_write_row(cache_table->record[0]))) + { + /* create_myisam_from_heap will generate error if needed */ + if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) && + create_internal_tmp_table_from_heap(table_thd, cache_table, + cache_table_param.start_recinfo, + &cache_table_param.recinfo, + error, 1)) + goto err; + } + cache_table->status= 0; /* cache_table->record contains an existed record */ + tab_ref->has_record= TRUE; /* the same as above */ + DBUG_PRINT("info", ("has_record: TRUE status: 0")); + + DBUG_RETURN(FALSE); + +err: + free_tmp_table(table_thd, cache_table); + cache_table= NULL; + DBUG_RETURN(TRUE); +} + + +void Subquery_cache_tmptable::cleanup() +{ + cache_table->file->ha_delete_all_rows(); +} === added file 'sql/sql_subquery_cache.h' --- a/sql/sql_subquery_cache.h 1970-01-01 00:00:00 +0000 +++ b/sql/sql_subquery_cache.h 2010-04-14 20:34:04 +0000 @@ -0,0 +1,68 @@ +#ifndef _SQL_SUBQUERY_CACHE_H_ +#define _SQL_SUBQUERY_CACHE_H_ + +/** + Interface for subquery cache +*/ + +extern ulonglong subquery_cache_miss, subquery_cache_hit; + +class Subquery_cache :public Sql_alloc +{ +public: + enum result {ERROR, HIT, MISS}; + + Subquery_cache(){}; + virtual ~Subquery_cache() {}; + /** + Checks presence of the key (taken from cache owner) and if found return + it via value parameter + */ + virtual result check_value(Item **value)= 0; + /** + Puts value into this cache (key should be taken from cache owner) + */ + virtual my_bool put_value(Item *value)= 0; + /** + Cleans up and reset cache before reusing + */ + virtual void cleanup()= 0; +}; + +struct st_table_ref; +struct st_join_table; +//class Item_cache; +class Item_field; + +/** + Implementation of subquery cache over temporary table +*/ + +class Subquery_cache_tmptable :public Subquery_cache +{ +public: + Subquery_cache_tmptable(THD *thd, List<Item> &dependance, Item *value); + virtual ~Subquery_cache_tmptable(); + virtual result check_value(Item **value); + virtual my_bool put_value(Item *value); + virtual void cleanup(); + +private: + /* tmp table parameters */ + TMP_TABLE_PARAM cache_table_param; + /* temporary table to store this cache */ + TABLE *cache_table; + /* Thread handler for the temporary table */ + THD *table_thd; + /* tab_ref for index search */ + struct st_table_ref *tab_ref; + /* cache of subquery value to avoid evaluating it twice */ + //Item_cache *value_cache; + /* JOIN_TAB for index lookup */ + st_join_table *tab; + /* Chached result */ + Item_field *chached_result; + /* List of items */ + List<Item> *list; +}; +#endif === modified file 'sql/table.cc' --- a/sql/table.cc 2010-03-20 12:01:47 +0000 +++ b/sql/table.cc 2010-04-14 20:34:04 +0000 @@ -20,6 +20,7 @@ #include "sql_trigger.h" #include <m_ctype.h> #include "my_md5.h" +#include "my_bit.h" /* INFORMATION_SCHEMA name */ LEX_STRING INFORMATION_SCHEMA_NAME= {C_STRING_WITH_LEN("information_schema")}; @@ -5096,6 +5097,115 @@ void st_table::mark_virtual_columns_for_ file->column_bitmaps_signal(); } + +/** + @brief + Allocate space for keys + + @param key_count number of keys to allocate. + + @details + Allocate space enough to fit 'key_count' keys for this table. + + @return FALSE space was successfully allocated. + @return TRUE an error occur. +*/ + +bool TABLE::alloc_keys(uint key_count) +{ + DBUG_ASSERT(!s->keys); + key_info= s->key_info= (KEY*) my_malloc(sizeof(KEY)*key_count, MYF(0)); + max_keys= key_count; + return !(key_info); +} + + +/** + @brief Adds one key to a temporary table. + + @param key_parts bitmap of fields that take a part in the key. + @param key_name name of the key + + @details + Creates a key for this table from fields which corresponds the bits set to 1 + in the 'key_parts' bitmap. The 'key_name' name is given to the newly created + key. + + @return <0 an error occur. + @return >=0 number of newly added key. +*/ + +int TABLE::add_tmp_key(ulonglong key_parts, const char *key_name) +{ + DBUG_ASSERT(s->keys< max_keys); + + KEY* keyinfo; + Field **reg_field; + uint i; + bool key_start= TRUE; + uint key_part_count= my_count_bits(key_parts); + KEY_PART_INFO* key_part_info= + (KEY_PART_INFO*) my_malloc(sizeof(KEY_PART_INFO)* key_part_count, MYF(0)); + if (!key_part_info) + return -1; + keyinfo= key_info + s->keys; + keyinfo->key_part=key_part_info; + keyinfo->usable_key_parts=keyinfo->key_parts= key_part_count; + keyinfo->key_length=0; + keyinfo->algorithm= HA_KEY_ALG_UNDEF; + keyinfo->name= (char *)key_name; + keyinfo->flags= HA_GENERATED_KEY; + keyinfo->rec_per_key= (ulong*)my_malloc(sizeof(ulong)*key_part_count, MYF(0)); + if (!keyinfo->rec_per_key) + return -1; + bzero(keyinfo->rec_per_key, sizeof(ulong)*key_part_count); + for (i= 0, reg_field=field ; + *reg_field; + i++, reg_field++) + { + if (!(key_parts & (1 << i))) + continue; + if (key_start) + (*reg_field)->key_start.set_bit(s->keys); + key_start= FALSE; + (*reg_field)->part_of_key.set_bit(s->keys); + (*reg_field)->flags|= PART_KEY_FLAG; + key_part_info->null_bit= (*reg_field)->null_bit; + key_part_info->null_offset= (uint) ((*reg_field)->null_ptr - + (uchar*) record[0]); + key_part_info->field= *reg_field; + key_part_info->offset= (*reg_field)->offset(record[0]); + key_part_info->length= (uint16) (*reg_field)->pack_length(); + keyinfo->key_length+= key_part_info->length; + /* TODO: + The below method of computing the key format length of the + key part is a copy/paste from opt_range.cc, and table.cc. + This should be factored out, e.g. as a method of Field. + In addition it is not clear if any of the Field::*_length + methods is supposed to compute the same length. If so, it + might be reused. + */ + key_part_info->store_length= key_part_info->length; + + if ((*reg_field)->real_maybe_null()) + key_part_info->store_length+= HA_KEY_NULL_LENGTH; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB || + (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR) + key_part_info->store_length+= HA_KEY_BLOB_LENGTH; + + key_part_info->type= (uint8) (*reg_field)->key_type(); + key_part_info->key_type = + ((ha_base_keytype) key_part_info->type == HA_KEYTYPE_TEXT || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ? + 0 : FIELDFLAG_BINARY; + key_part_info++; + } + set_if_bigger(s->max_key_length, keyinfo->key_length); + return ++s->keys - 1; +} + + /** @brief Check if this is part of a MERGE table with attached children. === modified file 'sql/table.h' --- a/sql/table.h 2010-03-20 12:01:47 +0000 +++ b/sql/table.h 2010-04-14 20:34:04 +0000 @@ -781,6 +781,7 @@ struct st_table { uint temp_pool_slot; /* Used by intern temp tables */ uint status; /* What's in record[0] */ uint db_stat; /* mode of file as in handler.h */ + uint max_keys; /* Size of allocated key_info array. */ /* number of select if it is derived table */ uint derived_select_number; int current_lock; /* Type of lock on table */ @@ -914,6 +915,8 @@ struct st_table { inline bool needs_reopen_or_name_lock() { return s->version != refresh_version; } bool is_children_attached(void); + bool alloc_keys(uint key_count); + int add_tmp_key(ulonglong key_parts, const char *key_name); }; enum enum_schema_table_state

1 0