From: Dmitry Shulga Date: August 24 2012 9:26am Subject: bzr push into mysql-trunk branch (Dmitry.Shulga:4197 to 4198) WL#6030 List-Archive: http://lists.mysql.com/commits/144608 Message-Id: <201208240925.q7O9PZFC009271@acsmt358.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4198 Dmitry Shulga 2012-08-24 Follow-up for WL#6030. It's added support for handling NULL fields in trigger BEFORE-insert during execution of statement LOAD DATA INFILE. Refactoring: it's added wrapper for function check_that_all_fields_are_given_values(). modified: mysql-test/r/wl6030.result mysql-test/std_data/wl6030.dat mysql-test/t/wl6030.test sql/field.cc sql/field.h sql/sql_insert.cc sql/sql_load.cc 4197 Dmitry Shulga 2012-08-23 Added datafile for LOAD DATA INFILE that is used in the test wl6030. added: mysql-test/std_data/wl6030.dat === modified file 'mysql-test/r/wl6030.result' --- a/mysql-test/r/wl6030.result 2012-08-23 05:54:28 +0000 +++ b/mysql-test/r/wl6030.result 2012-08-24 05:55:53 +0000 @@ -160,11 +160,12 @@ IF NEW.b IS NULL THEN SET NEW.b='123'; END IF; END | -LOAD DATA INFILE '../../std_data/wl6030.dat' INTO TABLE t1 FIELDS TERMINATED BY ','; +LOAD DATA INFILE '../../std_data/wl6030.dat' INTO TABLE t1 FIELDS +TERMINATED BY ',' ENCLOSED BY '"'; SELECT * FROM t1; a b -457 NULL -321 text -579 NULL +457 123 +321 text +579 123 DROP TRIGGER t1_bi; DROP TABLE t1; === modified file 'mysql-test/std_data/wl6030.dat' --- a/mysql-test/std_data/wl6030.dat 2012-08-23 07:13:59 +0000 +++ b/mysql-test/std_data/wl6030.dat 2012-08-24 05:55:53 +0000 @@ -1,3 +1,3 @@ -457, NULL -321, text -579, NULL +457,NULL +321,text +579,NULL === modified file 'mysql-test/t/wl6030.test' --- a/mysql-test/t/wl6030.test 2012-08-23 05:54:28 +0000 +++ b/mysql-test/t/wl6030.test 2012-08-24 05:55:53 +0000 @@ -178,7 +178,9 @@ END | delimiter ;| -LOAD DATA INFILE '../../std_data/wl6030.dat' INTO TABLE t1 FIELDS TERMINATED BY ','; +LOAD DATA INFILE '../../std_data/wl6030.dat' INTO TABLE t1 FIELDS +TERMINATED BY ',' ENCLOSED BY '"'; + SELECT * FROM t1; DROP TRIGGER t1_bi; DROP TABLE t1; === modified file 'sql/field.cc' --- a/sql/field.cc 2012-08-10 06:34:02 +0000 +++ b/sql/field.cc 2012-08-24 05:55:53 +0000 @@ -1375,7 +1375,8 @@ Field::Field(uchar *ptr_arg,uint32 lengt @return TYPE_OK if the value is Ok, or corresponding error code from the type_conversion_status enum. */ -type_conversion_status Field::check_constraints() const +type_conversion_status +Field::check_constraints(bool report_as_warn_null_to_notnull) const { /* Ensure that Field::check_constraints() is called only when temporary @@ -1410,7 +1411,11 @@ type_conversion_status Field::check_cons switch (m_count_cuted_fields_saved) { case CHECK_FIELD_WARN: - set_warning(Sql_condition::WARN_LEVEL_WARN, ER_BAD_NULL_ERROR, 1); + if (report_as_warn_null_to_notnull) + set_warning(Sql_condition::WARN_LEVEL_WARN, + ER_WARN_NULL_TO_NOTNULL, 1); + else + set_warning(Sql_condition::WARN_LEVEL_WARN, ER_BAD_NULL_ERROR, 1); /* fall through */ case CHECK_FIELD_IGNORE: return TYPE_OK; === modified file 'sql/field.h' --- a/sql/field.h 2012-08-10 06:34:02 +0000 +++ b/sql/field.h 2012-08-24 05:55:53 +0000 @@ -982,7 +982,8 @@ public: void set_notnull(my_ptrdiff_t row_offset= 0); - type_conversion_status check_constraints() const; + type_conversion_status + check_constraints(bool report_as_warn_null_to_notnull=false) const; /** Remember the value of THD::count_cuted_fields to handle possible === modified file 'sql/sql_insert.cc' --- a/sql/sql_insert.cc 2012-08-21 09:48:05 +0000 +++ b/sql/sql_insert.cc 2012-08-24 05:55:53 +0000 @@ -610,6 +610,38 @@ create_insert_stmt_from_insert_delayed(T /** + Wrapper for invocation of function check_that_all_fields_are_given_value. + + @param[in] thd Thread handler + @param[in] table Table to insert into (can be NULL if table + should be taken from table_list->table) + @param[in] table_list Table list + @param[in] abort_on_warning whether to report if some INSERT field is not + assigned as an error (TRUE) or + as a warning (FALSE). + + @return Operation status. + @retval false Success + @retval true Failure + */ +static bool +safely_check_that_all_fields_are_given_values(THD* thd, TABLE* table, + TABLE_LIST* table_list, + bool abort_on_warning) +{ + bool saved_abort_on_warning= thd->abort_on_warning; + thd->abort_on_warning= abort_on_warning; + bool res= check_that_all_fields_are_given_values(thd, + table ? table : + table_list->table, + table_list); + thd->abort_on_warning= saved_abort_on_warning; + + return res; +} + + +/** INSERT statement implementation @note Like implementations of other DDL/DML in MySQL, this function @@ -949,15 +981,10 @@ bool mysql_insert(THD *thd,TABLE_LIST *t table_list->view != 0) && (table ? table->triggers != NULL : context->table_list->table->triggers != NULL)) - { - bool saved_abort_on_warning= thd->abort_on_warning; - thd->abort_on_warning= !ignore && thd->is_strict_mode(); - res= check_that_all_fields_are_given_values(thd, - table ? table : - context->table_list->table, - context->table_list); - thd->abort_on_warning= saved_abort_on_warning; - } + res= safely_check_that_all_fields_are_given_values(thd, table, + context->table_list, + !ignore && + thd->is_strict_mode()); if (table_list->prepare_where(thd, 0, TRUE) || table_list->prepare_check_option(thd)) @@ -1511,15 +1538,9 @@ bool mysql_prepare_insert(THD *thd, TABL if (!res && check_fields && (table ? table->triggers == NULL : context->table_list->table->triggers == NULL)) - { - bool saved_abort_on_warning= thd->abort_on_warning; - thd->abort_on_warning= abort_on_warning; - res= check_that_all_fields_are_given_values(thd, - table ? table : - context->table_list->table, - context->table_list); - thd->abort_on_warning= saved_abort_on_warning; - } + res= safely_check_that_all_fields_are_given_values(thd, table, + context->table_list, + abort_on_warning); if (!res) res= setup_fields(thd, Ref_ptr_array(), @@ -3550,15 +3571,10 @@ select_insert::prepare(List &value prepare_triggers_for_insert_stmt(table); if (fields->elements) - { - bool saved_abort_on_warning= thd->abort_on_warning; - - thd->abort_on_warning= !ignore_errors && thd->is_strict_mode(); - - res= check_that_all_fields_are_given_values(thd, table_list->table, - table_list); - thd->abort_on_warning= saved_abort_on_warning; - } + res= safely_check_that_all_fields_are_given_values(thd, table_list->table, + table_list, + !ignore_errors && + thd->is_strict_mode()); } DBUG_RETURN(res); } === modified file 'sql/sql_load.cc' --- a/sql/sql_load.cc 2012-04-10 20:17:48 +0000 +++ b/sql/sql_load.cc 2012-08-24 05:55:53 +0000 @@ -964,6 +964,13 @@ read_sep_field(THD *thd, COPY_INFO &info real_item= item->real_item(); + /* + Enable temporary nullability for items that corresponds + to table fields. + */ + if (real_item->type() == Item::FIELD_ITEM) + ((Item_field *)real_item)->field->set_tmp_nullable(true); + if ((!read_info.enclosed && (enclosed_length && length == 4 && !memcmp(pos, STRING_WITH_LEN("NULL")))) || @@ -979,18 +986,19 @@ read_sep_field(THD *thd, COPY_INFO &info thd->get_stmt_da()->current_row_for_warning()); DBUG_RETURN(1); } - // Try to set to NULL; if it fails, field remains at 0. - field->set_null(); - if (!field->maybe_null()) + if (!field->real_maybe_null() && + field->type() == FIELD_TYPE_TIMESTAMP) { - if (field->type() == FIELD_TYPE_TIMESTAMP) - { - // Specific of TIMESTAMP NOT NULL: set to CURRENT_TIMESTAMP. - Item_func_now_local::store_in(field); - } - else if (field != table->next_number_field) - field->set_warning(Sql_condition::WARN_LEVEL_WARN, - ER_WARN_NULL_TO_NOTNULL, 1); + // Specific of TIMESTAMP NOT NULL: set to CURRENT_TIMESTAMP. + Item_func_now_local::store_in(field); + } + else + { + /* + Set field to NULL. Later we will clear temporary nullability flag + and check NOT NULL constraint. + */ + field->set_null(); } } else if (item->type() == Item::STRING_ITEM) @@ -1087,6 +1095,19 @@ read_sep_field(THD *thd, COPY_INFO &info } } + /* + Bypass all fields in the table and clear + temporary nullability flags for each one. + */ + Item *real_item; + it.rewind(); + while ((item= it++)) + { + real_item= item->real_item(); + if (real_item->type() == Item::FIELD_ITEM) + ((Item_field *)real_item)->field->set_tmp_nullable(false); + } + if (thd->killed || fill_record_n_invoke_before_triggers(thd, set_fields, set_values, ignore_check_option_errors, @@ -1094,6 +1115,26 @@ read_sep_field(THD *thd, COPY_INFO &info TRG_EVENT_INSERT)) DBUG_RETURN(1); + if (!table->triggers) + { + /* + If there isn't triggers for the table then bypass all fields + in the table and check for NOT NULL constraint for each one. + */ + it.rewind(); + + while ((item= it++)) + { + real_item= item->real_item(); + if (real_item->type() == Item::FIELD_ITEM) + /* + Pass true as argument value to push warning + ER_WARN_NULL_TO_NOTNULL instead of ER_BAD_NULL_ERROR + */ + ((Item_field *)real_item)->field->check_constraints(true); + } + } + switch (table_list->view_check_option(thd, ignore_check_option_errors)) { case VIEW_CHECK_SKIP: No bundle (reason: useless for push emails).