summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/insert_update.result11
-rw-r--r--mysql-test/t/insert_update.test21
-rw-r--r--sql/mysql_priv.h3
-rw-r--r--sql/sql_insert.cc68
-rw-r--r--sql/sql_prepare.cc2
5 files changed, 80 insertions, 25 deletions
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index f658ff06624..2403de5f7a9 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -236,3 +236,14 @@ INSERT INTO t2 VALUES (1), (3);
INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
ERROR 42S22: Unknown column 'a' in 'field list'
DROP TABLE t1,t2;
+SET SQL_MODE = 'TRADITIONAL';
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
+INSERT INTO t1 (a) VALUES (1);
+ERROR HY000: Field 'b' doesn't have a default value
+INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
+ERROR HY000: Field 'b' doesn't have a default value
+INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
+ERROR HY000: Field 'b' doesn't have a default value
+SELECT * FROM t1;
+a b
+DROP TABLE t1;
diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index 4581cc7a875..3ebcf7d8ff3 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -162,3 +162,24 @@ INSERT INTO t2 VALUES (1), (3);
--error ER_BAD_FIELD_ERROR
INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
DROP TABLE t1,t2;
+
+#
+# Bug #26261: Missing default value isn't noticed in
+# insert ... on duplicate key update
+#
+SET SQL_MODE = 'TRADITIONAL';
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
+
+--error 1364
+INSERT INTO t1 (a) VALUES (1);
+
+--error 1364
+INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
+
+--error 1364
+INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index f90ea5587fa..e5877e8ed1f 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -823,7 +823,8 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, TABLE *table,
List<Item> &fields, List_item *values,
List<Item> &update_fields,
List<Item> &update_values, enum_duplicates duplic,
- COND **where, bool select_insert);
+ COND **where, bool select_insert,
+ bool check_fields, bool abort_on_warning);
bool mysql_insert(THD *thd,TABLE_LIST *table,List<Item> &fields,
List<List_item> &values, List<Item> &update_fields,
List<Item> &update_values, enum_duplicates flag,
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 332c4c82ba1..dfff70e858e 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -451,10 +451,15 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
thd->proc_info="init";
thd->used_tables=0;
values= its++;
+ value_count= values->elements;
if (mysql_prepare_insert(thd, table_list, table, fields, values,
update_fields, update_values, duplic, &unused_conds,
- FALSE))
+ FALSE,
+ (fields.elements || !value_count),
+ !ignore && (thd->variables.sql_mode &
+ (MODE_STRICT_TRANS_TABLES |
+ MODE_STRICT_ALL_TABLES))))
goto abort;
/* mysql_prepare_insert set table_list->table if it was not set */
@@ -480,7 +485,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
table_list->next_local= 0;
context->resolve_in_table_list_only(table_list);
- value_count= values->elements;
while ((values= its++))
{
counter++;
@@ -551,17 +555,9 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
table->file->start_bulk_insert(values_list.elements);
thd->no_trans_update= 0;
- thd->abort_on_warning= (!ignore &&
- (thd->variables.sql_mode &
- (MODE_STRICT_TRANS_TABLES |
- MODE_STRICT_ALL_TABLES)));
-
- if ((fields.elements || !value_count) &&
- check_that_all_fields_are_given_values(thd, table, table_list))
- {
- /* thd->net.report_error is now set, which will abort the next loop */
- error= 1;
- }
+ thd->abort_on_warning= (!ignore && (thd->variables.sql_mode &
+ (MODE_STRICT_TRANS_TABLES |
+ MODE_STRICT_ALL_TABLES)));
mark_fields_used_by_triggers_for_insert_stmt(thd, table, duplic);
@@ -934,6 +930,10 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list,
be taken from table_list->table)
where Where clause (for insert ... select)
select_insert TRUE if INSERT ... SELECT statement
+ check_fields TRUE if need to check that all INSERT fields are
+ given values.
+ abort_on_warning whether to report if some INSERT field is not
+ assigned as an error (TRUE) or as a warning (FALSE).
TODO (in far future)
In cases of:
@@ -954,7 +954,8 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
TABLE *table, List<Item> &fields, List_item *values,
List<Item> &update_fields, List<Item> &update_values,
enum_duplicates duplic,
- COND **where, bool select_insert)
+ COND **where, bool select_insert,
+ bool check_fields, bool abort_on_warning)
{
SELECT_LEX *select_lex= &thd->lex->select_lex;
Name_resolution_context *context= &select_lex->context;
@@ -1017,10 +1018,22 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
table_list->next_local= 0;
context->resolve_in_table_list_only(table_list);
- if (!(res= check_insert_fields(thd, context->table_list, fields, *values,
- !insert_into_view, &map) ||
- setup_fields(thd, 0, *values, 0, 0, 0))
- && duplic == DUP_UPDATE)
+ res= check_insert_fields(thd, context->table_list, fields, *values,
+ !insert_into_view, &map) ||
+ setup_fields(thd, 0, *values, 0, 0, 0);
+
+ if (!res && check_fields)
+ {
+ 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;
+ }
+
+ if (!res && duplic == DUP_UPDATE)
{
select_lex->no_wrap_view_item= TRUE;
res= check_update_fields(thd, context->table_list, update_fields, &map);
@@ -2295,7 +2308,7 @@ bool mysql_insert_select_prepare(THD *thd)
lex->query_tables->table, lex->field_list, 0,
lex->update_list, lex->value_list,
lex->duplicates,
- &select_lex->where, TRUE))
+ &select_lex->where, TRUE, FALSE, FALSE))
DBUG_RETURN(TRUE);
/*
@@ -2357,7 +2370,18 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
!insert_into_view, &map) ||
setup_fields(thd, 0, values, 0, 0, 0);
- if (info.handle_duplicates == DUP_UPDATE)
+ if (!res && fields->elements)
+ {
+ bool saved_abort_on_warning= thd->abort_on_warning;
+ thd->abort_on_warning= !info.ignore && (thd->variables.sql_mode &
+ (MODE_STRICT_TRANS_TABLES |
+ MODE_STRICT_ALL_TABLES));
+ res= check_that_all_fields_are_given_values(thd, table_list->table,
+ table_list);
+ thd->abort_on_warning= saved_abort_on_warning;
+ }
+
+ if (info.handle_duplicates == DUP_UPDATE && !res)
{
Name_resolution_context *context= &lex->select_lex.context;
Name_resolution_context_state ctx_state;
@@ -2459,9 +2483,7 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
MODE_STRICT_ALL_TABLES)));
- res= ((fields->elements &&
- check_that_all_fields_are_given_values(thd, table, table_list)) ||
- table_list->prepare_where(thd, 0, TRUE) ||
+ res= (table_list->prepare_where(thd, 0, TRUE) ||
table_list->prepare_check_option(thd));
if (!res)
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index ad2b0be4eb8..8e807ca0ada 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1058,7 +1058,7 @@ static bool mysql_test_insert(Prepared_statement *stmt,
if (mysql_prepare_insert(thd, table_list, table_list->table,
fields, values, update_fields, update_values,
- duplic, &unused_conds, FALSE))
+ duplic, &unused_conds, FALSE, FALSE, FALSE))
goto error;
value_count= values->elements;
View Lorry Controller Status