From 709a0a131021135e9fb7a2095fcfcbc223dfb126 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 26 May 2010 13:18:18 -0700 Subject: MWL#106: Backport optimizations for derived tables and views. The main consolidated patch. --- sql/sql_insert.cc | 88 +++++++++++++++++++++++++++++++++++++++++++------------ 1 file changed, 70 insertions(+), 18 deletions(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index afc86ef6d4f..16d74eb5b39 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -124,7 +124,7 @@ bool check_view_single_update(List &fields, List *values, { it.init(*values); while ((item= it++)) - tables|= item->used_tables(); + tables|= item->view_used_tables(view); } /* Convert to real table bits */ @@ -140,6 +140,11 @@ bool check_view_single_update(List &fields, List *values, if (view->check_single_table(&tbl, tables, view) || tbl == 0) goto error; + /* + A buffer for the insert values was allocated for the merged view. + Use it. + */ + //tbl->table->insert_values= view->table->insert_values; view->table= tbl->table; *map= tables; @@ -243,6 +248,10 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list, */ table_list->next_local= 0; context->resolve_in_table_list_only(table_list); + /* 'Unfix' fields to allow correct marking by the setup_fields function. */ + if (table_list->is_view()) + unfix_fields(fields); + res= setup_fields(thd, 0, fields, MARK_COLUMNS_WRITE, 0, 0); /* Restore the current context. */ @@ -252,7 +261,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list, if (res) return -1; - if (table_list->effective_algorithm == VIEW_ALGORITHM_MERGE) + if (table_list->is_view() && table_list->is_merged_derived()) { if (check_view_single_update(fields, fields_and_values_from_different_maps ? @@ -341,7 +350,8 @@ static int check_update_fields(THD *thd, TABLE_LIST *insert_table_list, if (setup_fields(thd, 0, update_fields, MARK_COLUMNS_WRITE, 0, 0)) return -1; - if (insert_table_list->effective_algorithm == VIEW_ALGORITHM_MERGE && + if (insert_table_list->is_view() && + insert_table_list->is_merged_derived() && check_view_single_update(update_fields, &update_values, insert_table_list, map)) return -1; @@ -641,6 +651,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, table_list->table_name); DBUG_RETURN(TRUE); } + /* + mark the table_list as a target for insert, to skip the DT/view prepare phase + for correct access rights checks + TODO: remove this hack + */ + table_list->skip_prepare_derived= TRUE; if (table_list->lock_type == TL_WRITE_DELAYED) { @@ -652,6 +668,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, if (open_and_lock_tables(thd, table_list)) DBUG_RETURN(TRUE); } + lock_type= table_list->lock_type; thd_proc_info(thd, "init"); @@ -1010,6 +1027,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, ::my_ok(thd, (ulong) thd->row_count_func, id, buff); } thd->abort_on_warning= 0; + if (thd->lex->current_select->first_cond_optimization) + { + thd->lex->current_select->save_leaf_tables(thd); + thd->lex->current_select->first_cond_optimization= 0; + } + DBUG_RETURN(FALSE); abort: @@ -1138,6 +1161,11 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, bool insert_into_view= (table_list->view != 0); DBUG_ENTER("mysql_prepare_insert_check_table"); + if (!table_list->updatable) + { + my_error(ER_NON_INSERTABLE_TABLE, MYF(0), table_list->alias, "INSERT"); + DBUG_RETURN(TRUE); + } /* first table in list is the one we'll INSERT into, requires INSERT_ACL. all others require SELECT_ACL only. the ACL requirement below is for @@ -1148,14 +1176,16 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context, &thd->lex->select_lex.top_join_list, table_list, - &thd->lex->select_lex.leaf_tables, - select_insert, INSERT_ACL, SELECT_ACL)) + thd->lex->select_lex.leaf_tables, + select_insert, INSERT_ACL, SELECT_ACL, + TRUE)) DBUG_RETURN(TRUE); if (insert_into_view && !fields.elements) { thd->lex->empty_field_list_on_rset= 1; - if (!table_list->table) + if (!thd->lex->select_lex.leaf_tables.head()->table || + table_list->is_multitable()) { my_error(ER_VIEW_NO_INSERT_FIELD_LIST, MYF(0), table_list->view_db.str, table_list->view_name.str); @@ -1246,6 +1276,12 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, /* INSERT should have a SELECT or VALUES clause */ DBUG_ASSERT (!select_insert || !values); + if (mysql_handle_derived(thd->lex, DT_INIT)) + DBUG_RETURN(TRUE); + if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(TRUE); + if (mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE)) + DBUG_RETURN(TRUE); /* For subqueries in VALUES() we should not see the table in which we are inserting (for INSERT ... SELECT this is done by changing table_list, @@ -2913,9 +2949,9 @@ bool mysql_insert_select_prepare(THD *thd) { LEX *lex= thd->lex; SELECT_LEX *select_lex= &lex->select_lex; - TABLE_LIST *first_select_leaf_table; DBUG_ENTER("mysql_insert_select_prepare"); + /* Statement-based replication of INSERT ... SELECT ... LIMIT is not safe as order of rows is not defined, so in mixed mode we go to row-based. @@ -2941,21 +2977,37 @@ bool mysql_insert_select_prepare(THD *thd) &select_lex->where, TRUE, FALSE, FALSE)) DBUG_RETURN(TRUE); + DBUG_ASSERT(select_lex->leaf_tables.elements != 0); + List_iterator ti(select_lex->leaf_tables); + TABLE_LIST *table; + uint insert_tables; + + if (select_lex->first_cond_optimization) + { + /* Back up leaf_tables list. */ + Query_arena *arena= thd->stmt_arena, backup; + arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test + + insert_tables= select_lex->insert_tables; + while ((table= ti++) && insert_tables--) + { + select_lex->leaf_tables_exec.push_back(table); + table->tablenr_exec= table->table->tablenr; + table->map_exec= table->table->map; + } + if (arena) + thd->restore_active_arena(arena, &backup); + } + ti.rewind(); /* exclude first table from leaf tables list, because it belong to INSERT */ - DBUG_ASSERT(select_lex->leaf_tables != 0); - lex->leaf_tables_insert= select_lex->leaf_tables; /* skip all leaf tables belonged to view where we are insert */ - for (first_select_leaf_table= select_lex->leaf_tables->next_leaf; - first_select_leaf_table && - first_select_leaf_table->belong_to_view && - first_select_leaf_table->belong_to_view == - lex->leaf_tables_insert->belong_to_view; - first_select_leaf_table= first_select_leaf_table->next_leaf) - {} - select_lex->leaf_tables= first_select_leaf_table; + insert_tables= select_lex->insert_tables; + while ((table= ti++) && insert_tables--) + ti.remove(); + DBUG_RETURN(FALSE); } @@ -3169,7 +3221,7 @@ void select_insert::cleanup() select_insert::~select_insert() { DBUG_ENTER("~select_insert"); - if (table) + if (table && table->created) { table->next_number_field=0; table->auto_increment_field_not_null= FALSE; -- cgit v1.2.1 From 52090a443493f5a8410094b43bfa5cdde8c32f5d Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Wed, 24 Nov 2010 00:08:48 +0200 Subject: Code cleanup to get fewer reallocs() during execution. - Changed TABLE->alias to String to get fewer reallocs when alias are used. - Preallocate some buffers Changed some String->c_ptr() -> String->ptr() when \0 is not needed. Fixed wrong usage of String->ptr() when we need a \0 terminated string. Use my_strtod() instead of my_atof() to avoid having to add \0 to string. c_ptr() -> c_ptr_safe() to avoid warnings from valgrind. zr sql/event_db_repository.cc: Update usage of TABLE->alias sql/event_scheduler.cc: c_ptr() -> c_ptr_safe() sql/events.cc: c_ptr() -> ptr() as \0 was not needed sql/field.cc: Update usage of TABLE->alias sql/field.h: Update usage of TABLE->alias sql/ha_partition.cc: Update usage of TABLE->alias sql/handler.cc: Update usage of TABLE->alias Fixed wrong usage of str.ptr() sql/item.cc: Fixed error where code wrongly assumed string was \0 terminated. sql/item_func.cc: c_ptr() -> c_ptr_safe() Update usage of TABLE->alias sql/item_sum.h: Use my_strtod() instead of my_atof() to avoid having to add \0 to string sql/lock.cc: Update usage of TABLE->alias sql/log.cc: c_ptr() -> ptr() as \0 was not needed sql/log_event.cc: c_ptr_quick() -> ptr() as \0 was not needed sql/opt_range.cc: ptr() -> c_ptr() as \0 is needed sql/opt_subselect.cc: Update usage of TABLE->alias sql/opt_table_elimination.cc: Update usage of TABLE->alias sql/set_var.cc: ptr() -> c_ptr() as \0 is needed c_ptr() -> c_ptr_safe() sql/sp.cc: c_ptr() -> ptr() as \0 was not needed sql/sp_rcontext.cc: Update usage of TABLE->alias sql/sql_base.cc: Preallocate buffers Update usage of TABLE->alias sql/sql_class.cc: Fix arguments to sprintf() to work even if string is not \0 terminated sql/sql_insert.cc: Update usage of TABLE->alias c_ptr() -> ptr() as \0 was not needed sql/sql_load.cc: Preallocate buffers Trivial optimizations sql/sql_parse.cc: Trivial optimization sql/sql_plugin.cc: c_ptr() -> ptr() as \0 was not needed sql/sql_select.cc: Update usage of TABLE->alias sql/sql_show.cc: Update usage of TABLE->alias sql/sql_string.h: Added move() function to move allocated memory from one object to another. sql/sql_table.cc: Update usage of TABLE->alias c_ptr() -> c_ptr_safe() sql/sql_test.cc: ptr() -> c_ptr_safe() sql/sql_trigger.cc: Update usage of TABLE->alias c_ptr() -> c_ptr_safe() sql/sql_update.cc: Update usage of TABLE->alias sql/sql_view.cc: ptr() -> c_ptr_safe() sql/sql_yacc.yy: ptr() -> c_ptr() sql/table.cc: Update usage of TABLE->alias sql/table.h: Changed TABLE->alias to String to get fewer reallocs when alias are used. storage/federatedx/ha_federatedx.cc: Use c_ptr_safe() to ensure strings are \0 terminated. storage/maria/ha_maria.cc: Update usage of TABLE->alias storage/myisam/ha_myisam.cc: Update usage of TABLE->alias storage/xtradb/row/row0sel.c: Ensure that null bits in record are properly reset. (Old code didn't work as row_search_for_mysql() can be called twice while reading fields from one row. --- sql/sql_insert.cc | 14 ++++++++------ 1 file changed, 8 insertions(+), 6 deletions(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 3c2f90e8080..9477d2ca971 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2074,6 +2074,7 @@ TABLE *Delayed_insert::get_local_table(THD* client_thd) TABLE *copy; TABLE_SHARE *share; uchar *bitmap; + char *copy_tmp; DBUG_ENTER("Delayed_insert::get_local_table"); /* First request insert thread to get a lock */ @@ -2106,14 +2107,15 @@ TABLE *Delayed_insert::get_local_table(THD* client_thd) the other record buffers and alignment are unnecessary. */ thd_proc_info(client_thd, "allocating local table"); - copy= (TABLE*) client_thd->alloc(sizeof(*copy)+ - (share->fields+1)*sizeof(Field**)+ - share->reclength + - share->column_bitmap_size*3); - if (!copy) + copy_tmp= (char*) client_thd->alloc(sizeof(*copy)+ + (share->fields+1)*sizeof(Field**)+ + share->reclength + + share->column_bitmap_size*3); + if (!copy_tmp) goto error; /* Copy the TABLE object. */ + copy= new (copy_tmp) TABLE; *copy= *table; /* We don't need to change the file handler here */ /* Assign the pointers for the field pointers array and the record. */ @@ -3502,7 +3504,7 @@ int select_create::write_to_binlog(bool is_trans, int errcode) Avoid to use thd->binlog_query() twice, otherwise it will print the unsafe warning twice. */ - Query_log_event ev(thd, query.c_ptr_safe(), query.length(), is_trans, + Query_log_event ev(thd, query.ptr(), query.length(), is_trans, FALSE, errcode); return mysql_bin_log.write(&ev); } -- cgit v1.2.1 From 7c56b08216d5ff709d10e4ca662d0215cd823c09 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Sat, 27 Nov 2010 17:29:52 +0200 Subject: Added TRASH() to table->record[0] to find out if we access not initialzed data. - Changed Cached_item_field not copy data for fields with NULL value - In key_copy() and key_restore() don't copy data for fields with NULL value Fixed code to avoid valgrind warnings - Use c_ptr_safe instead of c_ptr() Removed "QQ" from comments (QQ was ment to be used for internal comments that should be removed before pushing) Fixed wrong alias used (from previous patch) sql/event_db_repository.cc: Update testing if event table is valid (to avoid valgrind errors) sql/ha_partition.cc: m_ordered_scan_ongoing was not initialized Reset null bits in record to avoid valgrind errors sql/handler.h: Added flag if storage engine will write row verbatim and the row contains varchar or null fields (in which case we must clear the row to avoid valgrind warnings) sql/item_buff.cc: Changed Cached_item_field not copy data for fields with NULL value (Optimization and avoids valgrind warnings) sql/item_func.cc: c_ptr() -> c_ptr_safe() sql/key.cc: In key_copy() and key_restore() don't copy data for fields with NULL value sql/opt_range.cc: c_ptr() -> c_ptr_safe() sql/sql_base.cc: Added TRASH() to table->record[0] to find out if we access not initialzed data. Initialize null_bytes to: - Get consistent tests - Ensure we don't get valgrind warnings for null fields (as we may only update a couple of bits in a byte) sql/sql_class.cc: Removed "QQ" from comments sql/sql_insert.cc: Initialize row to default values if we are using valgrind and row will be copied verbatim to disk in storage engine. sql/sql_load.cc: QQ -> TODO sql/sql_parse.cc: Removed old not used code marked QQ and withing "#ifdef REMOVED" sql/sql_select.cc: QQ -> TODO Initialize some variables that was used uninitialized Added DBUG_ASSERT() to find out if thd was not properly initialized for sub queries sql/sql_test.cc: Fixed format for printing to DBUG file Fixed wrong alias used (from previous patch) sql/sql_trigger.h: QQ -> TODO sql/table.cc: QQ -> TODO storage/maria/ha_maria.cc: Mark table with HA_RECORD_MUST_BE_CLEAN_ON_WRITE, if row is written verbatim to disk and contains varchar or null fields. storage/maria/ma_open.c: Added flags if table has varchar or null fields storage/maria/maria_def.h: Added flags if table has varchar or null fields storage/myisam/ha_myisam.cc: Mark table with HA_RECORD_MUST_BE_CLEAN_ON_WRITE, if row is written verbatim to disk and contains varchar or null fields. storage/myisam/mi_open.c: Fixed memory overrun bug when using fulltext keys storage/xtradb/row/row0sel.c: Removed initialization of null bits. (not needed anymore) --- sql/sql_insert.cc | 10 +++++++--- 1 file changed, 7 insertions(+), 3 deletions(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 9477d2ca971..7267d73a29c 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -622,8 +622,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, /* We can't write-delayed into a table locked with LOCK TABLES: this will lead to a deadlock, since the delayed thread will - never be able to get a lock on the table. QQQ: why not - upgrade the lock here instead? + never be able to get a lock on the table. */ if (table_list->lock_type == TL_WRITE_DELAYED && thd->locked_tables && find_locked_table(thd, table_list->db, table_list->table_name)) @@ -808,7 +807,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, be overwritten by fill_record() anyway (and fill_record() does not use default values in this case). */ - table->record[0][0]= share->default_values[0]; +#ifdef HAVE_valgrind + if (table->file->ha_table_flags() && HA_RECORD_MUST_BE_CLEAN_ON_WRITE) + restore_record(table,s->default_values); // Get empty record + else +#endif + table->record[0][0]= share->default_values[0]; /* Fix undefined null_bits. */ if (share->null_bytes > 1 && share->last_null_bit_pos) -- cgit v1.2.1 From 32379aeba9222d255f151bdea4954932fe784021 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 10 Jan 2011 14:53:09 +0100 Subject: Merge Percona patch MWL#47 into mariadb 5.2-percona. This patch adds options to annotate the binlog (and the mysqlbinlog output) with the original SQL query for queries that are logged using row-based replication. --- sql/sql_insert.cc | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 72ca7310ee1..48105cf9f3c 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1966,6 +1966,11 @@ bool delayed_get_table(THD *thd, TABLE_LIST *table_list) pthread_mutex_lock(&LOCK_thread_count); thread_count++; pthread_mutex_unlock(&LOCK_thread_count); + /* + Annotating delayed inserts is not supported. + */ + di->thd.variables.binlog_annotate_rows_events= 0; + di->thd.set_db(table_list->db, (uint) strlen(table_list->db)); di->thd.set_query(my_strdup(table_list->table_name, MYF(MY_WME)), 0); if (di->thd.db == NULL || di->thd.query() == NULL) -- cgit v1.2.1 From ff3da0f963b28d128005da8ca5d0c93a72d34b27 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Mon, 28 Feb 2011 12:48:50 +0200 Subject: Change TABLE->alias to String for less memory reallocation Changed some String.ptr() -> String.c_ptr() for String that are not guaranteed to end with \0 Removed some c_ptr() usage from parameters to functions that takes ptr & length Use preallocate buffers to avoid calling malloc() for most operations. sql/event_db_repository.cc: alias is now a String sql/event_scheduler.cc: c_ptr -> c_ptr_safe() to avoid warnings from valgrind. sql/events.cc: c_ptr -> c_ptr_safe() to avoid warnings from valgrind. c_ptr -> ptr() as function takes ptr & length sql/field.cc: alias is now a String sql/field.h: alias is now a String sql/ha_partition.cc: alias is now a String sql/handler.cc: alias is now a String ptr() -> c_ptr() as string is not guaranteed to be \0 terminated sql/item.cc: Store error parameter in separarte buffer to ensure correct error message sql/item_func.cc: ptr() -> c_ptr_safe() as string is not guaranteed to be \0 terminated sql/item_sum.h: Use my_strtod() instead of my_atof() to not have to make string \0 terminated sql/lock.cc: alias is now a String sql/log.cc: c_ptr() -> ptr() as function takes ptr & length sql/log_event.cc: c_ptr_quick() -> ptr() as we only want to get the pointer to String buffer sql/opt_range.cc: ptr() -> c_ptr() as string is not guaranteed to be \0 terminated sql/opt_table_elimination.cc: alias is now a String sql/set_var.cc: ptr() -> c_ptr() as string is not guaranteed to be \0 terminated c_ptr() -> c_ptr_safe() to avoid warnings from valgrind. c_ptr() -> ptr() as function takes ptr & length Simplify some code. sql/sp.cc: c_ptr() -> ptr() as function takes ptr & length sql/sp_rcontext.cc: alias is now a String sql/sql_base.cc: alias is now a String. Here we win a realloc() for most alias usage. sql/sql_class.cc: Use size descriptor for printf() to avoid accessing bytes outside of buffer sql/sql_insert.cc: Change allocation of TABLE as it's now contains a String _ptr() -> ptr() as function takes ptr & length sql/sql_load.cc: Use preallocate buffers to avoid calling malloc() for most operations. sql/sql_parse.cc: Use c_ptr_safe() to ensure string is \0 terminated. sql/sql_plugin.cc: c_ptr_quick() -> ptr() as function takes ptr & length sql/sql_select.cc: alias is now a String sql/sql_show.cc: alias is now a String sql/sql_string.h: Added move() function to change who owns the string (owner does the free) sql/sql_table.cc: alias is now a String c_ptr() -> c_ptr_safe() to avoid warnings from valgrind. sql/sql_test.cc: c_ptr() -> c_ptr_safe() to avoid warnings from valgrind. alias is now a String sql/sql_trigger.cc: c_ptr() -> c_ptr_safe() to avoid warnings from valgrind. Use field->init() to setup pointers to alias. sql/sql_update.cc: alias is now a String sql/sql_view.cc: ptr() -> c_ptr_safe() as string is not guaranteed to be \0 terminated sql/sql_yacc.yy: r() -> c_ptr() as string is not guaranteed to be \0 terminated sql/table.cc: alias is now a String sql/table.h: alias is now a String storage/federatedx/ha_federatedx.cc: Remove extra 1 byte alloc that is automaticly done by strmake() Ensure that error message ends with \0 storage/maria/ha_maria.cc: alias is now a String storage/myisam/ha_myisam.cc: alias is now a String --- sql/sql_insert.cc | 14 ++++++++------ 1 file changed, 8 insertions(+), 6 deletions(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 3c2f90e8080..9477d2ca971 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2074,6 +2074,7 @@ TABLE *Delayed_insert::get_local_table(THD* client_thd) TABLE *copy; TABLE_SHARE *share; uchar *bitmap; + char *copy_tmp; DBUG_ENTER("Delayed_insert::get_local_table"); /* First request insert thread to get a lock */ @@ -2106,14 +2107,15 @@ TABLE *Delayed_insert::get_local_table(THD* client_thd) the other record buffers and alignment are unnecessary. */ thd_proc_info(client_thd, "allocating local table"); - copy= (TABLE*) client_thd->alloc(sizeof(*copy)+ - (share->fields+1)*sizeof(Field**)+ - share->reclength + - share->column_bitmap_size*3); - if (!copy) + copy_tmp= (char*) client_thd->alloc(sizeof(*copy)+ + (share->fields+1)*sizeof(Field**)+ + share->reclength + + share->column_bitmap_size*3); + if (!copy_tmp) goto error; /* Copy the TABLE object. */ + copy= new (copy_tmp) TABLE; *copy= *table; /* We don't need to change the file handler here */ /* Assign the pointers for the field pointers array and the record. */ @@ -3502,7 +3504,7 @@ int select_create::write_to_binlog(bool is_trans, int errcode) Avoid to use thd->binlog_query() twice, otherwise it will print the unsafe warning twice. */ - Query_log_event ev(thd, query.c_ptr_safe(), query.length(), is_trans, + Query_log_event ev(thd, query.ptr(), query.length(), is_trans, FALSE, errcode); return mysql_bin_log.write(&ev); } -- cgit v1.2.1 From a8a757c6bb32bbf291afdf33df861127489889ab Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 1 Mar 2011 13:24:36 +0100 Subject: wl#173 - temporal types with sub-second resolution and collateral changes. * introduce my_hrtime_t, my_timediff_t, and conversion macros * inroduce TIME_RESULT, but it can only be returned from Item::cmp_type(), never from Item::result_type() * pack_time/unpack_time function for "packed" representation of MYSQL_TIME in a longlong that can be compared * ADDTIME()/SUBTIME()/+- INTERVAL now work with TIME values * numbers aren't quoted in EXPLAIN EXTENDED * new column I_S.COLUMNS.DATETIME_PRECISION * date/time values are compares to anything as date/time, not as strings or numbers. * old timestamp(X) is no longer supported * MYSQL_TIME to string conversion functions take precision as an argument * unified the warnings from Field_timestamp/datetime/time/date/newdate store methods * Field_timestamp_hires, Field_datetime_hires, Field_time_hires * Field_temporal * Lazy_string class to pass a value (string, number, time) polymorphically down the stack * make_truncated_value_warning and Field::set_datetime_warning use Lazy_string as an argument, removed char*/int/double variants * removed Field::can_be_compared_as_longlong(). Use Field::cmp_type() == INT_RESULT instead * introduced Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() * in many cases date/time types are treated like other types, not as special cases * greatly simplified Arg_comparator (regarding date/time/year code) * SEC_TO_TIME is real function, not integer. * microsecond precision in NOW, CURTIME, etc * Item_temporal. All items derived from it only provide get_date, but no val* methods * replication of NOW(6) * Protocol::store(time) now takes the precision as an argument * @@TIMESTAMP is a double client/mysqlbinlog.cc: remove unneded casts include/my_sys.h: introduce my_hrtime_t, my_timediff_t, and conversion macros include/my_time.h: pack_time/unpack_time, etc. convenience functions to work with MYSQL_TIME::second_part libmysql/libmysql.c: str_to_time() is gone. str_to_datetime() does it now. my_TIME_to_str() takes the precision as an argument mysql-test/include/ps_conv.inc: time is not equal to datetime anymore mysql-test/r/distinct.result: a test for an old MySQL bug mysql-test/r/explain.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/func_default.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/func_sapdb.result: when decimals=NOT_FIXED_DEC it means "not fixed" indeed mysql-test/r/func_test.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/func_time.result: ADDTIME()/SUBTIME()/+- INTERVAL now work with TIME values mysql-test/r/having.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/information_schema.result: new column I_S.COLUMNS.DATETIME_PRECISION mysql-test/r/join_outer.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/metadata.result: TIMESTAMP no longer has zerofill flag mysql-test/r/range.result: invalid datetime is not compared with as a string mysql-test/r/select.result: NO_ZERO_IN_DATE, etc only affect storage - according to the manual numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/subselect.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/sysdate_is_now.result: when decimals=NOT_FIXED_DEC it means "not fixed" indeed mysql-test/r/type_blob.result: TIMESTAMP(N) is not deprecated mysql-test/r/type_timestamp.result: old TIMESTAMP(X) semantics is not supported anymore mysql-test/r/union.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/varbinary.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/t/distinct.test: test for an old MySQL bug mysql-test/t/func_time.test: +- INTERVAL now works with TIME values mysql-test/t/select.test: typo mysql-test/t/subselect.test: only one error per statement, please mysql-test/t/system_mysql_db_fix40123.test: old timestamp(X) is no longer supported mysql-test/t/system_mysql_db_fix50030.test: old timestamp(X) is no longer supported mysql-test/t/system_mysql_db_fix50117.test: old timestamp(X) is no longer supported mysql-test/t/type_blob.test: old timestamp(X) is no longer supported mysql-test/t/type_timestamp.test: old timestamp(X) is no longer supported mysys/my_getsystime.c: functions to get the time with microsecond precision mysys/my_init.c: move the my_getsystime.c initialization code to my_getsystime.c mysys/my_static.c: no need to make these variables extern mysys/my_static.h: no need to make these variables extern scripts/mysql_system_tables.sql: old timestamp(X) is no longer supported scripts/mysql_system_tables_fix.sql: old timestamp(X) is no longer supported scripts/mysqlhotcopy.sh: old timestamp(X) is no longer supported sql-common/my_time.c: * call str_to_time from str_to_datetime, as appropriate * date/time to string conversions take precision as an argument * number_to_time() * TIME_to_double() * pack_time() and unpack_time() sql/event_data_objects.cc: cast is not needed my_datetime_to_str() takes precision as an argument sql/event_db_repository.cc: avoid dangerous downcast (because the pointer is not always Field_timestamp, see events_1.test) sql/event_queue.cc: avoid silly double-work for cond_wait (having an endpoint of wait, subtract the current time to get the timeout, and use set_timespec() macro to fill in struct timespec, by adding the current time to the timeout) sql/field.cc: * remove virtual Field::get_time(), everyone should use only Field::get_date() * remove lots of #ifdef WORDS_BIGENDIAN * unified the warnings from Field_timestamp/datetime/time/date/newdate store methods * Field_timestamp_hires, Field_datetime_hires, Field_time_hires * Field_temporal * make_truncated_value_warning and Field::set_datetime_warning use Lazy_string as an argument, removed char*/int/double variants sql/field.h: * remove virtual Field::get_time(), everyone should use only Field::get_date() * remove lots of #ifdef WORDS_BIGENDIAN * unified the warnings from Field_timestamp/datetime/time/date/newdate store methods * Field_timestamp_hires, Field_datetime_hires, Field_time_hires * Field_temporal * make_truncated_value_warning and Field::set_datetime_warning use Lazy_string as an argument, removed char*/int/double variants * removed Field::can_be_compared_as_longlong(). Use Field::cmp_type() == INT_RESULT instead sql/filesort.cc: TIME_RESULT, cmp_time() sql/item.cc: * numbers aren't quoted in EXPLAIN EXTENDED * Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() * virtual Item::get_time() is gone * Item_param::field_type() is set correctly * Item_datetime, for a datetime constant * time to anything is compared as a time * Item_cache::print() prints the value is available * bug fixed in Item_cache_int::val_str() sql/item.h: * Item::print_value(), to be used from Item_xxx::print() when needed * Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() * virtual Item::get_time() is gone * Item_datetime, for a datetime constant * better default for cast_to_int_type() * Item_cache objects now *always* have the field_type() set sql/item_cmpfunc.cc: * get_year_value, get_time_value are gone. get_datetime_value does it all * get_value_a_func, get_value_b_func are gone * can_compare_as_dates() is gone too, TIME_RESULT is used instead * cmp_type() instead or result_type() when doing a comparison * compare_datetime and compate_e_datetime in the comparator_matrix, is_nulls_eq is gone * Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() sql/item_cmpfunc.h: greatly simplified Arg_comparator sql/item_create.cc: * fix a bug in error messages in CAST sql/item_func.cc: Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() mention all possibitiles in switch over Item_result values, or use default: sql/item_row.h: overwrite the default cmp_type() for Item_row, as no MYSQL_TYPE_xxx value corresponds to ROW_RESULT sql/item_timefunc.cc: rewrite make_datetime to support precision argument SEC_TO_TIME is real function, not integer. many functions that returned temporal values had duplicate code in val_* methods, some of them did not have get_date() which resulted in unnecessary date->str->date conversions. Now they all are derived from Item_temporal_func and *only* provide get_date, not val* methods. many fixes to set decimals (datetime precision) correctly. sql/item_timefunc.h: SEC_TO_TIME is real function, not integer. many functions that returned temporal values had duplicate code in val_* methods, some of them did not have get_date() which resulted in unnecessary date->str->date conversions. Now they all are derived from Item_temporal_func and *only* provide get_date, not val* methods. many fixes to set decimals (datetime precision) correctly. sql/log_event.cc: replication of NOW(6) sql/log_event.h: replication of NOW(6) sql/mysql_priv.h: Lazy_string class to pass a value (string, number, time) polymorphically down the stack. make_truncated_value_warning() that uses it. sql/mysqld.cc: datetime in Arg_comparator::comparator_matrix sql/opt_range.cc: cleanup: don't disable warnings before calling save_in_field_no_warnings() sql/protocol.cc: Protocol::store(time) now takes the precision as an argument sql/protocol.h: Protocol::store(time) now takes the precision as an argument sql/rpl_rli.cc: small cleanup sql/set_var.cc: SET TIMESTAMP=double sql/set_var.h: @@TIMESTAMP is a double sql/share/errmsg.txt: precision and scale are unsigned sql/slave.cc: replication of NOW(6) sql/sp_head.cc: cleanup sql/sql_class.cc: support for NOW(6) sql/sql_class.h: support for NOW(6) sql/sql_insert.cc: support for NOW(6) sql/sql_select.cc: use item->cmp_type(). move a comment where it belongs sql/sql_show.cc: new column I_S.COLUMNS.DATETIME_PRECISION sql/sql_yacc.yy: TIME(X), DATETIME(X), cast, NOW(X), CURTIME(X), etc sql/time.cc: fix date_add_interval() to support MYSQL_TIMESTAMP_TIME argument storage/myisam/ha_myisam.cc: TIMESTAMP no longer carries ZEROFIELD flag, still we keep MYI file compatible. strings/my_vsnprintf.c: warnings tests/mysql_client_test.c: old timestamp(X) does not work anymore datetime is no longer equal to time --- sql/sql_insert.cc | 13 +++++++++---- 1 file changed, 9 insertions(+), 4 deletions(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index f0735a9e093..12c5478b0ae 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1694,10 +1694,11 @@ class delayed_row :public ilink { public: char *record; enum_duplicates dup; - time_t start_time; + my_time_t start_time; + ulong start_time_sec_part; ulong sql_mode; bool auto_increment_field_not_null; - bool query_start_used, ignore, log_query; + bool query_start_used, ignore, log_query, query_start_sec_part_used; bool stmt_depends_on_first_successful_insert_id_in_prev_stmt; ulonglong first_successful_insert_id_in_prev_stmt; ulonglong forced_insert_id; @@ -2185,8 +2186,10 @@ int write_delayed(THD *thd, TABLE *table, enum_duplicates duplic, if (!(row->record= (char*) my_malloc(table->s->reclength, MYF(MY_WME)))) goto err; memcpy(row->record, table->record[0], table->s->reclength); - row->start_time= thd->start_time; - row->query_start_used= thd->query_start_used; + row->start_time= thd->start_time; + row->query_start_used= thd->query_start_used; + row->start_time_sec_part= thd->start_time_sec_part; + row->query_start_sec_part_used= thd->query_start_sec_part_used; /* those are for the binlog: LAST_INSERT_ID() has been evaluated at this time, so record does not need it, but statement-based binlogging of the @@ -2640,6 +2643,8 @@ bool Delayed_insert::handle_inserts(void) thd.start_time=row->start_time; thd.query_start_used=row->query_start_used; + thd.start_time_sec_part=row->start_time_sec_part; + thd.query_start_sec_part_used=row->query_start_sec_part_used; /* To get the exact auto_inc interval to store in the binlog we must not use values from the previous interval (of the previous rows). -- cgit v1.2.1 From e6b0be38f9ea03b4f657b210755f6891ce2a30f0 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Wed, 9 Mar 2011 19:45:48 +0200 Subject: Bug fix for lp:732124 union + limit returns wrong result mysql-test/r/union.result: Added test for lp:732124 mysql-test/t/union.test: Added test for lp:732124 sql/sp_rcontext.cc: Updated function definition for ::send_data() sql/sp_rcontext.h: Updated function definition for ::send_data() sql/sql_analyse.cc: Test if send_data() returned an error sql/sql_class.cc: Updated function definition for ::send_data() sql/sql_class.h: Changed select_result::send_data(List &items) to return -1 in case of duplicate row that should not be counted as part of LIMIT sql/sql_cursor.cc: Check if send_data returned error sql/sql_delete.cc: Updated function definition for ::send_data() sql/sql_insert.cc: Updated function definition for ::send_data() sql/sql_select.cc: Don't count rows which send_data() tells you to ignore sql/sql_union.cc: Inform caller that the row should be ignored. This is the real bug fix for lp:732124 sql/sql_update.cc: Updated function definition for ::send_data() --- sql/sql_insert.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 83735fd2f41..dd13f36a432 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -3178,7 +3178,7 @@ select_insert::~select_insert() } -bool select_insert::send_data(List &values) +int select_insert::send_data(List &values) { DBUG_ENTER("select_insert::send_data"); bool error=0; -- cgit v1.2.1 From 7c459960ece917dcdc4dedc9f3dd0c9f5d07c3b8 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 20 May 2011 23:56:13 +0200 Subject: db_low_byte_first is gone --- sql/sql_insert.cc | 3 --- 1 file changed, 3 deletions(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 12c5478b0ae..1aa1d50fc49 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -3542,9 +3542,6 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info, tmp_table.s->db_create_options=0; tmp_table.s->blob_ptr_size= portable_sizeof_char_ptr; - tmp_table.s->db_low_byte_first= - test(create_info->db_type == myisam_hton || - create_info->db_type == heap_hton); tmp_table.null_row=tmp_table.maybe_null=0; while ((item=it++)) -- cgit v1.2.1 From d37465a9cc458ab215105de22875ce0a64c0efc2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 16 Jul 2011 23:57:43 -0700 Subject: Fixed LP bug #794901. Also: 1. simplified the code of the function mysql_derived_merge_for_insert. 2. moved merge of views/dt for multi-update/delete to the prepare stage. 3. the list of the references to the candidates for semi-join now is allocated in the statement memory. --- sql/sql_insert.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 72514d72dc1..05a30ab49a4 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -135,7 +135,7 @@ bool check_view_single_update(List &fields, List *values, A buffer for the insert values was allocated for the merged view. Use it. */ - //tbl->table->insert_values= view->table->insert_values; + tbl->table->insert_values= view->table->insert_values; view->table= tbl->table; *map= tables; -- cgit v1.2.1 From 249a10c7dd879b26665a7d14d9ce4a5c90ca8c77 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 16 Aug 2011 22:48:35 -0700 Subject: Fixed LP bug #825035. The value of maybe_null flag should be saved for the second execution of a prepared statement from SELECT that uses an outer join. --- sql/sql_insert.cc | 1 + 1 file changed, 1 insertion(+) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 05a30ab49a4..386650ff99c 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -3015,6 +3015,7 @@ bool mysql_insert_select_prepare(THD *thd) select_lex->leaf_tables_exec.push_back(table); table->tablenr_exec= table->table->tablenr; table->map_exec= table->table->map; + table->maybe_null_exec= table->table->maybe_null; } if (arena) thd->restore_active_arena(arena, &backup); -- cgit v1.2.1