4195 Dmitry Shulga 2012-08-21
Follow-up for WL#6030.
Extended interface of class Table_triggers_list by two additional
methods enable_fields_temporary_nullability() and
disable_fields_temporary_nullability(). These methods are used in the
function fill_record_n_invoke_before_triggers() to enable/disable
temporary nullability for table's fields during execution of
BEFORE trigger.
When there is a trigger for the table being updated then call
check_that_all_fields_are_given_values() after fields
that is set in trigger has been prepared (by call of
prepare_triggers_for_insert_stmt())
Result file for the test warnings.test was changed since the original
order of statement'a warning was incorrect.
For the following test environment:
create table t1 (b tinyint unsigned, c char(5));
create table t2(a tinyint NOT NULL, b char(3));
insert into t1 values (null, 'mysq');
when we execute the statemet
insert into t2 select b,c from t1
the mysql server calls fill_record_n_invoke_before_triggers()
that calls fill_record(). Inside fill_record()
we iterate over fields as it is specified in the statement
and calls Item_field::save_in_field for each one. The call
pf Item_field::save_in_field can generate warning.
It means that warnings should follow in the order of fields
occurences in the statement INSERT being executed.
So the warning.result should be changed.
modified:
mysql-test/r/warnings.result
mysql-test/r/wl6030.result
mysql-test/t/wl6030.test
sql/sql_base.cc
sql/sql_insert.cc
sql/sql_trigger.cc
sql/sql_trigger.h
4194 Dmitry Shulga 2012-08-13
Reverted incorrect changes in Item_func_group_concat::add.
modified:
sql/item_sum.cc
=== modified file 'mysql-test/r/warnings.result'
--- a/mysql-test/r/warnings.result 2012-08-08 14:49:40 +0000
+++ b/mysql-test/r/warnings.result 2012-08-21 09:48:05 +0000
@@ -115,8 +115,8 @@ Warnings:
Warning 1265 Data truncated for column 'b' at row 1
Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'b' at row 3
-Warning 1265 Data truncated for column 'b' at row 4
Warning 1048 Column 'a' cannot be null
+Warning 1265 Data truncated for column 'b' at row 4
insert into t2(b) values('mysqlab');
Warnings:
Warning 1364 Field 'a' doesn't have a default value
=== modified file 'mysql-test/r/wl6030.result'
--- a/mysql-test/r/wl6030.result 2012-08-08 07:03:04 +0000
+++ b/mysql-test/r/wl6030.result 2012-08-21 09:48:05 +0000
@@ -124,8 +124,18 @@ IF new.b IS NULL THEN SET new.b = 1;
END IF;
END|
INSERT INTO t1(a) VALUES(NULL);
-ERROR 23000: Column 'b' cannot be null
SELECT * FROM t1;
a b
+NULL 1
DROP TRIGGER t1_bi;
DROP TABLE t1;
+# Test 7: Nullability of column being copied as result of INSERT SELECT
+CREATE TABLE t1 (a INT NOT NULL);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (NULL);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1;
+INSERT INTO t1 SELECT * FROM t2;
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1,t2;
=== modified file 'mysql-test/t/wl6030.test'
--- a/mysql-test/t/wl6030.test 2012-08-08 07:03:04 +0000
+++ b/mysql-test/t/wl6030.test 2012-08-21 09:48:05 +0000
@@ -12,8 +12,6 @@ DROP TRIGGER IF EXISTS t1_bu;
CREATE TABLE t1(a INT NOT NULL);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1;
-# FIXME: This should work
-# --error ER_BAD_NULL_ERROR
INSERT INTO t1 VALUES(NULL);
SELECT * FROM t1;
DROP TRIGGER t1_bi;
@@ -59,8 +57,6 @@ BEGIN
SET new.a = 1;
END|
delimiter ;|
-# FIXME: This should work
-# --error ER_BAD_NULL_ERROR
INSERT INTO t1(a) VALUES(NULL);
SELECT * FROM t1;
DROP TRIGGER t1_bi;
@@ -73,8 +69,6 @@ BEGIN
SET new.b = new.a;
END|
delimiter ;|
-# FIXME: This should work
-# --error ER_BAD_NULL_ERROR
INSERT INTO t1(a) VALUES(NULL);
SELECT * FROM t1;
DROP TRIGGER t1_bi;
@@ -90,8 +84,6 @@ BEGIN
SET new.a = 2;
END|
delimiter ;|
-# FIXME: This should work
-# --error ER_BAD_NULL_ERROR
INSERT INTO t1 VALUES(1);
SELECT * FROM t1;
DROP TRIGGER t1_bi;
@@ -107,8 +99,6 @@ BEGIN
SET new.a = 1;
END|
delimiter ;|
-# FIXME: This should work
-# --error ER_BAD_NULL_ERROR
INSERT INTO t1 VALUES(NULL);
SELECT @a;
SELECT a FROM t1;
@@ -150,9 +140,16 @@ BEGIN
END IF;
END|
delimiter ;|
-# FIXME: This should work
---error ER_BAD_NULL_ERROR
INSERT INTO t1(a) VALUES(NULL);
SELECT * FROM t1;
DROP TRIGGER t1_bi;
DROP TABLE t1;
+
+--echo # Test 7: Nullability of column being copied as result of INSERT SELECT
+CREATE TABLE t1 (a INT NOT NULL);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (NULL);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1;
+INSERT INTO t1 SELECT * FROM t2;
+SELECT * FROM t1;
+DROP TABLE t1,t2;
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2012-08-10 06:34:02 +0000
+++ b/sql/sql_base.cc 2012-08-21 09:48:05 +0000
@@ -8966,73 +8966,21 @@ fill_record_n_invoke_before_triggers(THD
Table_triggers_list *triggers,
enum trg_event_type event)
{
- {
- List_iterator_fast<Item> it(fields);
-
- while (true)
- {
- Item *item= it++;
-
- if (!item)
- break;
-
- Item_field *item_field= item->field_for_view_update();
- if (!item_field)
- {
- /**
- Rewind iterator for table's fields, iterate along
- all fields and reset temporary NULLability flag for
- every fields before this one that fired an error.
- */
- it.rewind();
- Item *item_to_restore;
- while ((item_to_restore= it++) && item_to_restore != item)
- {
- Item_field* item_field_to_restore=
- item_to_restore->field_for_view_update();
- item_field_to_restore->field->set_tmp_nullable(false);
- }
-
- my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), item->item_name.ptr());
- return true;
- }
- Field *field= item_field->field;
-
- field->set_tmp_nullable(true);
- field->set_count_cuted_fields(thd->count_cuted_fields);
- }
- }
+ if (triggers)
+ triggers->enable_fields_temporary_nullability(thd);
bool fill_error=
fill_record(thd, fields, values, ignore_errors, NULL) ||
(triggers && triggers->process_triggers(thd, event, TRG_ACTION_BEFORE, true));
+ bool check_error;
+ if (triggers)
{
- List_iterator_fast<Item> it(fields);
-
- while (true)
- {
- Item *item= it++;
-
- if (!item)
- break;
-
- Item_field *item_field= item->field_for_view_update();
-
- /*
- It's been checked that field_for_view_update() returns valid pointer
- in the loop above. Here we just have an assert for sanity check.
- */
- DBUG_ASSERT(item_field);
- {
- Field *field= item_field->field;
-
- field->set_tmp_nullable(false);
- }
- }
+ triggers->disable_fields_temporary_nullability();
+ check_error= check_record(thd, triggers->trigger_table->field);
}
-
- bool check_error= check_record(thd, fields, ignore_errors);
+ else
+ check_error= check_record(thd, fields, ignore_errors);
return fill_error || check_error;
}
@@ -9130,18 +9078,15 @@ fill_record_n_invoke_before_triggers(THD
Table_triggers_list *triggers,
enum trg_event_type event)
{
- for (int i= 0; ptr[i]; ++i)
- {
- ptr[i]->set_tmp_nullable(true);
- ptr[i]->set_count_cuted_fields(thd->count_cuted_fields);
- }
+ if (triggers)
+ triggers->enable_fields_temporary_nullability(thd);
bool fill_error=
fill_record(thd, ptr, values, ignore_errors, NULL) ||
(triggers && triggers->process_triggers(thd, event, TRG_ACTION_BEFORE, true));
- for (int i= 0; ptr[i]; ++i)
- ptr[i]->set_tmp_nullable(false);
+ if (triggers)
+ triggers->disable_fields_temporary_nullability();
bool check_error= check_record(thd, ptr);
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc 2012-08-03 11:31:19 +0000
+++ b/sql/sql_insert.cc 2012-08-21 09:48:05 +0000
@@ -940,6 +940,24 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
prepare_triggers_for_insert_stmt(table);
+ /**
+ If there is trigger for the table being updated then
+ checks that all fields either is assigned in clause VALUES
+ or is set in trigger's body or has DEFAULT value.
+ */
+ if ((fields.elements || !value_count ||
+ 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;
+ }
if (table_list->prepare_where(thd, 0, TRUE) ||
table_list->prepare_check_option(thd))
@@ -1485,12 +1503,19 @@ bool mysql_prepare_insert(THD *thd, TABL
check_insert_fields(thd, context->table_list, fields, *values,
!insert_into_view, 0, &map));
- if (!res && check_fields)
+ /**
+ If there isn't trigger for the table being updated then
+ checks that all fields either is assigned in clause VALUES
+ or has DEFAULT value.
+ */
+ 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 :
+ 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;
@@ -3407,17 +3432,6 @@ select_insert::prepare(List<Item> &value
check_insert_fields(thd, table_list, *fields, values,
!insert_into_view, 1, &map));
- if (!res && 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;
- }
-
if (duplicate_handling == DUP_UPDATE && !res)
{
Name_resolution_context *context= &lex->select_lex.context;
@@ -3532,8 +3546,20 @@ select_insert::prepare(List<Item> &value
table_list->prepare_check_option(thd));
if (!res)
+ {
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;
+ }
+ }
DBUG_RETURN(res);
}
=== modified file 'sql/sql_trigger.cc'
--- a/sql/sql_trigger.cc 2012-07-24 07:09:42 +0000
+++ b/sql/sql_trigger.cc 2012-08-21 09:48:05 +0000
@@ -2242,6 +2242,31 @@ bool Table_triggers_list::is_fields_upda
/**
+ Bypass every trigger's field and mark it as temporary nullable.
+
+ @param thd Thread context.
+*/
+void Table_triggers_list::enable_fields_temporary_nullability(THD* thd)
+{
+ for (Field** next_field= trigger_table->field; *next_field; ++next_field)
+ {
+ (*next_field)->set_tmp_nullable(true);
+ (*next_field)->set_count_cuted_fields(thd->count_cuted_fields);
+ }
+}
+
+
+/**
+ Bypass every trigger's field and reset its temporary nullability flag.
+*/
+void Table_triggers_list::disable_fields_temporary_nullability()
+{
+ for (Field** next_field= trigger_table->field; *next_field; ++next_field)
+ (*next_field)->set_tmp_nullable(false);
+}
+
+
+/**
Mark fields of subject table which we read/set in its triggers
as such.
=== modified file 'sql/sql_trigger.h'
--- a/sql/sql_trigger.h 2012-05-29 22:14:39 +0000
+++ b/sql/sql_trigger.h 2012-08-21 09:48:05 +0000
@@ -213,6 +213,10 @@ public:
bool is_fields_updated_in_trigger(MY_BITMAP *used_fields,
trg_event_type event_type,
trg_action_time_type action_time);
+
+ void enable_fields_temporary_nullability(THD* thd);
+ void disable_fields_temporary_nullability();
+
private:
bool prepare_record1_accessors();
LEX_STRING* change_table_name_in_trignames(const char *old_db_name,
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (Dmitry.Shulga:4194 to 4195) WL#6030 | Dmitry Shulga | 21 Aug |
