From ce563480b774f912ab698efe0a71e77b9360893d Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Fri, 30 Jan 2009 17:12:24 +0100 Subject: Bug#41370: TIMESTAMP field does not accepts NULL from FROM_UNIXTIME() When storing a NULL to a TIMESTAMP NOT NULL DEFAULT ..., NULL returned from some functions threw a 'cannot be NULL error.' NULL-returns now correctly result in the timestamp-field being assigned its default value. mysql-test/r/type_timestamp.result: Show that for TIMESTAMP NOT NULL DEFAULT, NULL function returns set default now. mysql-test/t/type_timestamp.test: Show that for TIMESTAMP NOT NULL DEFAULT, NULL function returns set default now. sql/item.cc: When storing a returned NULL (from a time- or date-related function), make sure to apply NULL-means-DEFAULT magic where applicable. --- mysql-test/r/type_timestamp.result | 19 +++++++++++++++++++ mysql-test/t/type_timestamp.test | 20 +++++++++++++++++++- sql/item.cc | 4 ++-- 3 files changed, 40 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 445ada578d0..59900fe4a77 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -492,6 +492,7 @@ a b c 5 NULL 2001-09-09 04:46:59 6 NULL 2006-06-06 06:06:06 drop table t1; +End of 4.1 tests set time_zone= @@global.time_zone; CREATE TABLE t1 ( `id` int(11) NOT NULL auto_increment, @@ -508,3 +509,21 @@ select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COL is_nullable NO drop table t1; +CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, +f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, +f3 TIMESTAMP); +INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00"); +INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL); +INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL)); +INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999')); +INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL)); +UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1; +SELECT f1,f2-f3 FROM t1; +f1 f2-f3 +1 0 +2 0 +3 0 +4 0 +5 0 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index 7b4af9e0c69..ce1d3f21ff5 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -324,7 +324,7 @@ insert into t1 (a, c) values (4, '2004-04-04 00:00:00'), select * from t1; drop table t1; -# End of 4.1 tests +--echo End of 4.1 tests # Restore timezone to default set time_zone= @@global.time_zone; @@ -339,3 +339,21 @@ PRIMARY KEY (`id`) show fields from t1; select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COLUMN_NAME='posted_on'; drop table t1; + +# +# Bug#41370: TIMESTAMP field does not accepts NULL from FROM_UNIXTIME() +# + +CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, + f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + f3 TIMESTAMP); +INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00"); +INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL); +INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL)); +INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999')); +INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL)); +UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1; +SELECT f1,f2-f3 FROM t1; +DROP TABLE t1; + +--echo End of 5.0 tests diff --git a/sql/item.cc b/sql/item.cc index 2a89c86cd88..977c5f0b7c0 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -321,7 +321,7 @@ int Item::save_time_in_field(Field *field) { MYSQL_TIME ltime; if (get_time(<ime)) - return set_field_to_null(field); + return set_field_to_null_with_conversions(field, 0); field->set_notnull(); return field->store_time(<ime, MYSQL_TIMESTAMP_TIME); } @@ -331,7 +331,7 @@ int Item::save_date_in_field(Field *field) { MYSQL_TIME ltime; if (get_date(<ime, TIME_FUZZY_DATE)) - return set_field_to_null(field); + return set_field_to_null_with_conversions(field, 0); field->set_notnull(); return field->store_time(<ime, MYSQL_TIMESTAMP_DATETIME); } -- cgit v1.2.1 From 9a3afd1a121fe3ffdc7a58c45341ce4b2e9fb144 Mon Sep 17 00:00:00 2001 From: Patrick Crews Date: Mon, 2 Feb 2009 17:30:02 -0500 Subject: Bug#39369: execution of "variables.test" with "check-testcases" show differences. The original symptoms of this bug have been fixed as a consequence of other bug fixes. Taking this time to correct some formatting, such as replacing error numbers with names. Beginning this with 5.0 --- mysql-test/t/variables.test | 92 ++++++++++++++++++++++----------------------- 1 file changed, 46 insertions(+), 46 deletions(-) diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 1748b63ceb9..60254ad9a1d 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -6,7 +6,7 @@ drop table if exists t1,t2; --enable_warnings # -# Bug #19263: variables.test doesn't clean up after itself (I/II -- save) +# Bug#19263: variables.test doesn't clean up after itself (I/II -- save) # set @my_binlog_cache_size =@@global.binlog_cache_size; set @my_connect_timeout =@@global.connect_timeout; @@ -172,46 +172,46 @@ SELECT @@version_compile_os LIKE 'non-existent'; # The following should give errors ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set big_tables=OFFF; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set big_tables="OFFF"; ---error 1193 +--error ER_UNKNOWN_SYSTEM_VARIABLE set unknown_variable=1; ---error 1232 +--error ER_WRONG_TYPE_FOR_VAR set max_join_size="hello"; ---error 1286 +--error ER_UNKNOWN_STORAGE_ENGINE set storage_engine=UNKNOWN_TABLE_TYPE; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set storage_engine=MERGE, big_tables=2; show local variables like 'storage_engine'; ---error 1229 +--error ER_GLOBAL_VARIABLE set SESSION query_cache_size=10000; ---error 1230 +--error ER_NO_DEFAULT set GLOBAL storage_engine=DEFAULT; ---error 1115 +--error ER_UNKNOWN_CHARACTER_SET set character_set_client=UNKNOWN_CHARACTER_SET; ---error 1273 +--error ER_UNKNOWN_COLLATION set collation_connection=UNKNOWN_COLLATION; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set character_set_client=NULL; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set collation_connection=NULL; ---error 1228 +--error ER_LOCAL_VARIABLE set global autocommit=1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR select @@global.timestamp; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@version=''; ---error 1229 +--error ER_GLOBAL_VARIABLE set @@concurrent_insert=1; ---error 1228 +--error ER_LOCAL_VARIABLE set @@global.sql_auto_is_null=1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR select @@global.sql_auto_is_null; ---error 1229 +--error ER_GLOBAL_VARIABLE set myisam_max_sort_file_size=100; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set @@SQL_WARNINGS=NULL; # Test setting all variables @@ -338,23 +338,23 @@ drop table t1,t2; # error conditions # ---error 1193 +--error ER_UNKNOWN_SYSTEM_VARIABLE select @@xxxxxxxxxx; select 1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR select @@session.key_buffer_size; ---error 1229 +--error ER_GLOBAL_VARIABLE set ft_boolean_syntax = @@init_connect; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global ft_boolean_syntax = @@init_connect; ---error 1229 +--error ER_GLOBAL_VARIABLE set init_connect = NULL; set global init_connect = NULL; ---error 1229 +--error ER_GLOBAL_VARIABLE set ft_boolean_syntax = @@init_connect; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global ft_boolean_syntax = @@init_connect; # Bug#3754 SET GLOBAL myisam_max_sort_file_size doesn't work as @@ -385,15 +385,15 @@ select @a, @b; # # Bug#2586:Disallow global/session/local as structured var. instance names # ---error 1064 +--error ER_PARSE_ERROR set @@global.global.key_buffer_size= 1; ---error 1064 +--error ER_PARSE_ERROR set GLOBAL global.key_buffer_size= 1; ---error 1064 +--error ER_PARSE_ERROR SELECT @@global.global.key_buffer_size; ---error 1064 +--error ER_PARSE_ERROR SELECT @@global.session.key_buffer_size; ---error 1064 +--error ER_PARSE_ERROR SELECT @@global.local.key_buffer_size; # BUG#5135: cannot turn on log_warnings with SET in 4.1 (and 4.0) @@ -478,27 +478,27 @@ select @@lc_time_names; --echo *** LC_TIME_NAMES: testing with string expressions set lc_time_names=concat('de','_','DE'); select @@lc_time_names; ---error 1105 +--error ER_UNKNOWN_ERROR set lc_time_names=concat('de','+','DE'); select @@lc_time_names; --echo LC_TIME_NAMES: testing with numeric expressions set @@lc_time_names=1+2; select @@lc_time_names; ---error 1232 +--error ER_WRONG_TYPE_FOR_VAR set @@lc_time_names=1/0; select @@lc_time_names; set lc_time_names=en_US; --echo LC_TIME_NAMES: testing NULL and a negative number: ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set lc_time_names=NULL; ---error 1105 +--error ER_UNKNOWN_ERROR set lc_time_names=-1; select @@lc_time_names; --echo LC_TIME_NAMES: testing locale with the last ID: set lc_time_names=108; select @@lc_time_names; --echo LC_TIME_NAMES: testing a number beyond the valid ID range: ---error 1105 +--error ER_UNKNOWN_ERROR set lc_time_names=109; select @@lc_time_names; --echo LC_TIME_NAMES: testing that 0 is en_US: @@ -540,7 +540,7 @@ select @@query_prealloc_size = @test; # Bug#31588 buffer overrun when setting variables # # Buffer-size Off By One. Should throw valgrind-warning without fix #31588. ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global sql_mode=repeat('a',80); --echo End of 4.1 tests @@ -558,9 +558,9 @@ drop table t1; # Bug #10339: read only variables. # ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@warning_count=1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@global.error_count=1; # @@ -578,9 +578,9 @@ select @@max_heap_table_size > 0; # Bug #11775 Variable character_set_system does not exist (sometimes) # select @@character_set_system; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set global character_set_system = latin1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@global.version_compile_os='234'; # @@ -677,7 +677,7 @@ select @@@; # Don't actually output, since it depends on the system --replace_column 1 # select @@hostname; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@hostname= "anothername"; --replace_column 2 # show variables like 'hostname'; @@ -688,12 +688,12 @@ show variables like 'hostname'; SHOW VARIABLES LIKE 'log'; SELECT @@log; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR SET GLOBAL log=0; SHOW VARIABLES LIKE 'log_slow_queries'; SELECT @@log_slow_queries; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR SET GLOBAL log_slow_queries=0; --echo End of 5.0 tests -- cgit v1.2.1 From 97bd763544bbaf882a083b1952b47901bc9a335b Mon Sep 17 00:00:00 2001 From: Sergey Vojtovich Date: Wed, 4 Feb 2009 15:46:23 +0400 Subject: BUG#32047 - 'Spurious' errors while opening MERGE tables Accessing well defined MERGE table may return an error stating that the merge table is incorrectly defined. This happens if MERGE child tables were accessed before and we failed to open another incorrectly defined MERGE table in this connection. myrg_open() internally used my_errno as a variable for determining failure, and thus could be tricked into a wrong decision by other uses of my_errno. With this fix we use function local boolean flag instead of my_errno to determine failure. myisammrg/myrg_open.c: There are two requirement for accessing/setting my_errno variable, which were not followed by myrg_open(): - it must be checked immediately after a function returned an error. There must be no calls to other functions that may change it's value between. - my_errno value must be set right before a function is going to return an error. There must be no calls to other functions that may change it's value between (that's why we have these tricks with save_errno at the bottom of myrg_open()). myrg_open() internally used my_errno as a variable for determining failure, and thus could be tricked into a wrong decision by other uses of my_errno. mysql-test/r/merge.result: A test case for BUG#32047. mysql-test/t/merge.test: A test case for BUG#32047. --- myisammrg/myrg_open.c | 17 ++++++++++------- mysql-test/r/merge.result | 11 +++++++++++ mysql-test/t/merge.test | 13 +++++++++++++ 3 files changed, 34 insertions(+), 7 deletions(-) diff --git a/myisammrg/myrg_open.c b/myisammrg/myrg_open.c index 0e82e429afd..4e61f42efce 100644 --- a/myisammrg/myrg_open.c +++ b/myisammrg/myrg_open.c @@ -40,6 +40,7 @@ MYRG_INFO *myrg_open(const char *name, int mode, int handle_locking) IO_CACHE file; MI_INFO *isam=0; uint found_merge_insert_method= 0; + my_bool bad_children= FALSE; DBUG_ENTER("myrg_open"); LINT_INIT(key_parts); @@ -89,13 +90,13 @@ MYRG_INFO *myrg_open(const char *name, int mode, int handle_locking) fn_format(buff, buff, "", "", 0); if (!(isam=mi_open(buff,mode,(handle_locking?HA_OPEN_WAIT_IF_LOCKED:0)))) { - my_errno= HA_ERR_WRONG_MRG_TABLE_DEF; if (handle_locking & HA_OPEN_FOR_REPAIR) { myrg_print_wrong_table(buff); + bad_children= TRUE; continue; } - goto err; + goto bad_children; } if (!m_info) /* First file */ { @@ -122,13 +123,13 @@ MYRG_INFO *myrg_open(const char *name, int mode, int handle_locking) files++; if (m_info->reclength != isam->s->base.reclength) { - my_errno=HA_ERR_WRONG_MRG_TABLE_DEF; if (handle_locking & HA_OPEN_FOR_REPAIR) { myrg_print_wrong_table(buff); + bad_children= TRUE; continue; } - goto err; + goto bad_children; } m_info->options|= isam->s->options; m_info->records+= isam->state->records; @@ -141,8 +142,8 @@ MYRG_INFO *myrg_open(const char *name, int mode, int handle_locking) m_info->tables); } - if (my_errno == HA_ERR_WRONG_MRG_TABLE_DEF) - goto err; + if (bad_children) + goto bad_children; if (!m_info && !(m_info= (MYRG_INFO*) my_malloc(sizeof(MYRG_INFO), MYF(MY_WME | MY_ZEROFILL)))) goto err; @@ -170,12 +171,14 @@ MYRG_INFO *myrg_open(const char *name, int mode, int handle_locking) pthread_mutex_unlock(&THR_LOCK_open); DBUG_RETURN(m_info); +bad_children: + my_errno= HA_ERR_WRONG_MRG_TABLE_DEF; err: save_errno=my_errno; switch (errpos) { case 3: while (files) - mi_close(m_info->open_tables[--files].table); + (void) mi_close(m_info->open_tables[--files].table); my_free((char*) m_info,MYF(0)); /* Fall through */ case 2: diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index f8ef4f23180..3f2ac3e08ec 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -940,4 +940,15 @@ m1 CREATE TABLE `m1` ( `a` int(11) default NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1, m1; +CREATE TABLE t1(a INT); +CREATE TABLE t2(a VARCHAR(10)); +CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2); +CREATE TABLE m2(a INT) ENGINE=MERGE UNION=(t1); +SELECT * FROM t1; +a +SELECT * FROM m1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +SELECT * FROM m2; +a +DROP TABLE t1, t2, m1, m2; End of 5.0 tests diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index e5cc4d703f2..341296dbe8e 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -556,4 +556,17 @@ ALTER TABLE m1 UNION=(); SHOW CREATE TABLE m1; DROP TABLE t1, m1; +# +# BUG#32047 - 'Spurious' errors while opening MERGE tables +# +CREATE TABLE t1(a INT); +CREATE TABLE t2(a VARCHAR(10)); +CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2); +CREATE TABLE m2(a INT) ENGINE=MERGE UNION=(t1); +SELECT * FROM t1; +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE t1, t2, m1, m2; + --echo End of 5.0 tests -- cgit v1.2.1