From 0e9cba1817d082d134deca50bf063e11b3feee4d Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Wed, 3 Sep 2008 12:32:43 +0500 Subject: Bug #39002: The server crashes on the query: INSERT .. SELECT .. ON DUPLICATE KEY UPDATE col=DEFAULT In order to get correct values from update fields that belongs to the SELECT part in the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE statement, the server adds referenced fields to the select list. Part of the code that does this transformation is shared between implementations of the DEFAULT(col) function and the DEFAULT keyword (in the col=DEFAULT expression), and an implementation of the DEFAULT keyword is incomplete. mysql-test/r/default.result: Added test case for bug #39002. mysql-test/t/default.test: Added test case for bug #39002. sql/item.cc: The Item_default_value::transform() function has been modified to take into account the fact that the DEFAULT keyword has no arguments unlike the DEFAULT(col) function that always has an argument. --- mysql-test/r/default.result | 15 +++++++++++++++ mysql-test/t/default.test | 19 +++++++++++++++++++ sql/item.cc | 7 +++++++ 3 files changed, 41 insertions(+) diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result index e2eda69c2e7..5f27af882f2 100644 --- a/mysql-test/r/default.result +++ b/mysql-test/r/default.result @@ -205,4 +205,19 @@ Warnings: Warning 1364 Field 'id' doesn't have a default value drop view v1; drop table t1; +create table t1 (a int unique); +create table t2 (b int default 10); +insert into t1 (a) values (1); +insert into t2 (b) values (1); +insert into t1 (a) select b from t2 on duplicate key update a=default; +select * from t1; +a +NULL +insert into t1 (a) values (1); +insert into t1 (a) select b from t2 on duplicate key update a=default(b); +select * from t1; +a +NULL +10 +drop table t1, t2; End of 5.0 tests. diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test index 14aa4b02cfe..b719cb83448 100644 --- a/mysql-test/t/default.test +++ b/mysql-test/t/default.test @@ -145,5 +145,24 @@ insert into t1 values(default); drop view v1; drop table t1; +# +# Bug #39002: crash with +# INSERT ... SELECT ... ON DUPLICATE KEY UPDATE col=DEFAULT +# + +create table t1 (a int unique); +create table t2 (b int default 10); +insert into t1 (a) values (1); +insert into t2 (b) values (1); + +insert into t1 (a) select b from t2 on duplicate key update a=default; +select * from t1; + +insert into t1 (a) values (1); +insert into t1 (a) select b from t2 on duplicate key update a=default(b); +select * from t1; + +drop table t1, t2; + --echo End of 5.0 tests. diff --git a/sql/item.cc b/sql/item.cc index bf447581afa..2874c51e22f 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6039,6 +6039,13 @@ Item *Item_default_value::transform(Item_transformer transformer, byte *args) { DBUG_ASSERT(!current_thd->is_stmt_prepare()); + /* + If the value of arg is NULL, then this object represents a constant, + so further transformation is unnecessary (and impossible). + */ + if (!arg) + return 0; + Item *new_item= arg->transform(transformer, args); if (!new_item) return 0; -- cgit v1.2.1 From 644471d5c9a1cc7e9fb23327c853db5f744f6794 Mon Sep 17 00:00:00 2001 From: Ramil Kalimullin Date: Wed, 3 Sep 2008 15:17:19 +0500 Subject: Fix for bug#38821: Assert table->auto_increment_field_not_null failed in open_table() Problem: repeating "CREATE... ( AUTOINCREMENT) ... SELECT" may lead to an assertion failure. Fix: reset table->auto_increment_field_not_null after each record writing. mysql-test/r/create.result: Fix for bug#38821: Assert table->auto_increment_field_not_null failed in open_table() - test result. mysql-test/t/create.test: Fix for bug#38821: Assert table->auto_increment_field_not_null failed in open_table() - test case. sql/sql_insert.cc: Fix for bug#38821: Assert table->auto_increment_field_not_null failed in open_table() - reset table->auto_increment_field_not_null after writing a record for "{CREATE, INSERT}..SELECT". --- mysql-test/r/create.result | 11 +++++++++++ mysql-test/t/create.test | 18 ++++++++++++++++++ sql/sql_insert.cc | 6 +++++- 3 files changed, 34 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 53c2058f3ec..2d668499df3 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1546,4 +1546,15 @@ SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 c1 1 c1 A NULL NULL NULL YES BTREE DROP TABLE t1; +CREATE TABLE t1 (a INTEGER AUTO_INCREMENT PRIMARY KEY, b INTEGER NOT NULL); +INSERT IGNORE INTO t1 (b) VALUES (5); +CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) +SELECT a FROM t1; +CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) +SELECT a FROM t1; +ERROR 23000: Duplicate entry '1' for key 1 +CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) +SELECT a FROM t1; +ERROR 23000: Duplicate entry '1' for key 1 +DROP TABLE t1, t2; End of 5.0 tests diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 97a7ea71b29..61ee40477ee 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -1172,4 +1172,22 @@ SHOW INDEX FROM t1; DROP TABLE t1; +# +# Bug#38821: Assert table->auto_increment_field_not_null failed in open_table() +# +CREATE TABLE t1 (a INTEGER AUTO_INCREMENT PRIMARY KEY, b INTEGER NOT NULL); +INSERT IGNORE INTO t1 (b) VALUES (5); + +CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) + SELECT a FROM t1; +--error 1062 +CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) + SELECT a FROM t1; +--error 1062 +CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) + SELECT a FROM t1; + +DROP TABLE t1, t2; + + --echo End of 5.0 tests diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 746a04e03f3..3f43c902faa 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2897,7 +2897,11 @@ bool select_insert::send_data(List &values) DBUG_RETURN(1); } } - if (!(error= write_record(thd, table, &info))) + + error= write_record(thd, table, &info); + table->auto_increment_field_not_null= FALSE; + + if (!error) { if (table->triggers || info.handle_duplicates == DUP_UPDATE) { -- cgit v1.2.1 From f024a8b66521dd6d0313b3e4f3d6bbb9228ca30f Mon Sep 17 00:00:00 2001 From: Ramil Kalimullin Date: Fri, 5 Sep 2008 13:30:01 +0500 Subject: Fix for bug #39021: SELECT REGEXP BINARY NULL never returns Problem: SELECT ... REGEXP BINARY NULL may lead to server crash/hang. Fix: properly handle NULL regular expressions. mysql-test/r/func_regexp.result: Fix for bug #39021: SELECT REGEXP BINARY NULL never returns - test result. mysql-test/t/func_regexp.test: Fix for bug #39021: SELECT REGEXP BINARY NULL never returns - test case. sql/item_cmpfunc.cc: Fix for bug #39021: SELECT REGEXP BINARY NULL never returns - checking regular expressions' null_value we tested it without a val_xxx() call before, which is wrong. Now Item_func_regex::regcomp() returns -1 in the case and allows to handle NULL expessions properly. sql/item_cmpfunc.h: Fix for bug #39021: SELECT REGEXP BINARY NULL never returns - checking regular expressions' null_value we tested it without a val_xxx() call before, which is wrong. Now Item_func_regex::regcomp() returns -1 in the case and allows to handle NULL expessions properly. --- mysql-test/r/func_regexp.result | 12 ++++++++++++ mysql-test/t/func_regexp.test | 10 ++++++++++ sql/item_cmpfunc.cc | 31 ++++++++++++++++++++++--------- sql/item_cmpfunc.h | 2 +- 4 files changed, 45 insertions(+), 10 deletions(-) diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result index c2dbfc3c331..f3a1c625a09 100644 --- a/mysql-test/r/func_regexp.result +++ b/mysql-test/r/func_regexp.result @@ -114,4 +114,16 @@ End of 4.1 tests SELECT 1 REGEXP NULL; 1 REGEXP NULL NULL +SELECT '' REGEXP BINARY NULL; +'' REGEXP BINARY NULL +NULL +SELECT NULL REGEXP BINARY NULL; +NULL REGEXP BINARY NULL +NULL +SELECT 'A' REGEXP BINARY NULL; +'A' REGEXP BINARY NULL +NULL +SELECT "ABC" REGEXP BINARY NULL; +"ABC" REGEXP BINARY NULL +NULL End of 5.0 tests diff --git a/mysql-test/t/func_regexp.test b/mysql-test/t/func_regexp.test index 1b35fab9d54..43fda0d11a8 100644 --- a/mysql-test/t/func_regexp.test +++ b/mysql-test/t/func_regexp.test @@ -64,4 +64,14 @@ drop table t1; SELECT 1 REGEXP NULL; + +# +# Bug #39021: SELECT REGEXP BINARY NULL never returns +# + +SELECT '' REGEXP BINARY NULL; +SELECT NULL REGEXP BINARY NULL; +SELECT 'A' REGEXP BINARY NULL; +SELECT "ABC" REGEXP BINARY NULL; + --echo End of 5.0 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 1994f6bf1a5..bec6c315c5e 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4341,8 +4341,20 @@ void Item_func_like::cleanup() #ifdef USE_REGEX -bool -Item_func_regex::regcomp(bool send_error) +/** + @brief Compile regular expression. + + @param[in] send_error send error message if any. + + @details Make necessary character set conversion then + compile regular expression passed in the args[1]. + + @retval 0 success. + @retval 1 error occurred. + @retval -1 given null regular expression. + */ + +int Item_func_regex::regcomp(bool send_error) { char buff[MAX_FIELD_WIDTH]; String tmp(buff,sizeof(buff),&my_charset_bin); @@ -4350,12 +4362,12 @@ Item_func_regex::regcomp(bool send_error) int error; if (args[1]->null_value) - return TRUE; + return -1; if (regex_compiled) { if (!stringcmp(res, &prev_regexp)) - return FALSE; + return 0; prev_regexp.copy(*res); my_regfree(&preg); regex_compiled= 0; @@ -4367,7 +4379,7 @@ Item_func_regex::regcomp(bool send_error) uint dummy_errors; if (conv.copy(res->ptr(), res->length(), res->charset(), regex_lib_charset, &dummy_errors)) - return TRUE; + return 1; res= &conv; } @@ -4379,10 +4391,10 @@ Item_func_regex::regcomp(bool send_error) (void) my_regerror(error, &preg, buff, sizeof(buff)); my_error(ER_REGEXP_ERROR, MYF(0), buff); } - return TRUE; + return 1; } regex_compiled= 1; - return FALSE; + return 0; } @@ -4420,13 +4432,14 @@ Item_func_regex::fix_fields(THD *thd, Item **ref) const_item_cache=args[0]->const_item() && args[1]->const_item(); if (!regex_compiled && args[1]->const_item()) { - if (args[1]->null_value) + int comp_res= regcomp(TRUE); + if (comp_res == -1) { // Will always return NULL maybe_null=1; fixed= 1; return FALSE; } - if (regcomp(TRUE)) + else if (comp_res) return TRUE; regex_is_const= 1; maybe_null= args[0]->maybe_null; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index d250e1b366a..1bd60ff37d9 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1323,7 +1323,7 @@ class Item_func_regex :public Item_bool_func CHARSET_INFO *regex_lib_charset; int regex_lib_flags; String conv; - bool regcomp(bool send_error); + int regcomp(bool send_error); public: Item_func_regex(Item *a,Item *b) :Item_bool_func(a,b), regex_compiled(0),regex_is_const(0) {} -- cgit v1.2.1 From 835f3528b860fe0fbb52145d23e36068f5d43d47 Mon Sep 17 00:00:00 2001 From: Ramil Kalimullin Date: Tue, 9 Sep 2008 20:05:27 +0500 Subject: Fix for bug#37526: asymertic operator <=> in trigger Problem: <=> operator may return wrong results comparing NULL and a DATE/DATETIME/TIME value. Fix: properly check NULLs. mysql-test/r/type_datetime.result: Fix for bug#37526: asymertic operator <=> in trigger - test result. mysql-test/t/type_datetime.test: Fix for bug#37526: asymertic operator <=> in trigger - test case. sql/item_cmpfunc.cc: Fix for bug#37526: asymertic operator <=> in trigger - if is_nulls_eq is TRUE Arg_comparator::compare_datetime() should return 1 only if both arguments are NULL. --- mysql-test/r/type_datetime.result | 23 +++++++++++++++++++++++ mysql-test/t/type_datetime.test | 16 ++++++++++++++++ sql/item_cmpfunc.cc | 20 +++++++++++++------- 3 files changed, 52 insertions(+), 7 deletions(-) diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 3814e2f8348..9c358c41215 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -560,4 +560,27 @@ select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date drop table t1,t2; +SELECT +CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) n1, +CAST('2008-01-01' AS DATE) <=> CAST('NULL' AS DATE) n2, +CAST('NULL' AS DATE) <=> CAST('NULL' AS DATE) n3, +CAST('NULL' AS DATE) <> CAST('2008-01-01' AS DATE) n4, +CAST('2008-01-01' AS DATE) <> CAST('NULL' AS DATE) n5, +CAST('NULL' AS DATE) <> CAST('NULL' AS DATE) n6, +CAST('NULL' AS DATE) < CAST('2008-01-01' AS DATE) n7, +CAST('2008-01-01' AS DATE) < CAST('NULL' AS DATE) n8, +CAST('NULL' AS DATE) < CAST('NULL' AS DATE) n9; +n1 n2 n3 n4 n5 n6 n7 n8 n9 +0 0 1 NULL NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' End of 5.0 tests diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 722e4c082a7..16a7bb66755 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -388,4 +388,20 @@ where id in (select id from t2 as x1 where (t2.cur_date is null)); drop table t1,t2; + +# +# Bug #37526: asymertic operator <=> in trigger +# +SELECT + CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) n1, + CAST('2008-01-01' AS DATE) <=> CAST('NULL' AS DATE) n2, + CAST('NULL' AS DATE) <=> CAST('NULL' AS DATE) n3, + CAST('NULL' AS DATE) <> CAST('2008-01-01' AS DATE) n4, + CAST('2008-01-01' AS DATE) <> CAST('NULL' AS DATE) n5, + CAST('NULL' AS DATE) <> CAST('NULL' AS DATE) n6, + CAST('NULL' AS DATE) < CAST('2008-01-01' AS DATE) n7, + CAST('2008-01-01' AS DATE) < CAST('NULL' AS DATE) n8, + CAST('NULL' AS DATE) < CAST('NULL' AS DATE) n9; + + --echo End of 5.0 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index bec6c315c5e..0410c781590 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -966,19 +966,24 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, 1 if items are equal or both are null 0 otherwise If is_nulls_eq is FALSE: - -1 a < b or one of items is null + -1 a < b or at least one item is null 0 a == b 1 a > b + See the table: + is_nulls_eq | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | + a_is_null | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | + b_is_null | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | + result | 1 | 0 | 0 |0/1|-1 |-1 |-1 |-1/0/1| */ int Arg_comparator::compare_datetime() { - bool is_null= FALSE; + bool a_is_null, b_is_null; ulonglong a_value, b_value; /* Get DATE/DATETIME/TIME value of the 'a' item. */ - a_value= (*get_value_func)(thd, &a, &a_cache, *b, &is_null); - if (!is_nulls_eq && is_null) + a_value= (*get_value_func)(thd, &a, &a_cache, *b, &a_is_null); + if (!is_nulls_eq && a_is_null) { if (owner) owner->null_value= 1; @@ -986,14 +991,15 @@ int Arg_comparator::compare_datetime() } /* Get DATE/DATETIME/TIME value of the 'b' item. */ - b_value= (*get_value_func)(thd, &b, &b_cache, *a, &is_null); - if (is_null) + b_value= (*get_value_func)(thd, &b, &b_cache, *a, &b_is_null); + if (a_is_null || b_is_null) { if (owner) owner->null_value= is_nulls_eq ? 0 : 1; - return is_nulls_eq ? 1 : -1; + return is_nulls_eq ? (a_is_null == b_is_null) : -1; } + /* Here we have two not-NULL values. */ if (owner) owner->null_value= 0; -- cgit v1.2.1