Test case for bug when updating on key

Fixed bug in CONCAT_WS()
Print the default ISOLATION level.
Change lock type for CREATE ... SELECT and INSERT/REPLACE ... SELECT

4 files changed, 85 insertions, 0 deletions

diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index c1a0c288562..ec421e9f31b 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -34,6 +34,14 @@ concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*')
 :my:sql:
 TRIM("foo" FROM "foo")	TRIM("foo" FROM "foook")	TRIM("foo" FROM "okfoo")
 	ok	ok
+concat_ws(', ','monty','was here','again')
+monty, was here, again
+concat_ws(NULL,'a')	concat_ws(',',NULL,'')
+NULL	
+concat_ws(',','',NULL,'a')
+a
+CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"')
+"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb";"cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc";"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"
 insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
 this	is a	test
 replace('aaaa','a','b')	replace('aaaa','aa','b')	replace('aaaa','a','bb')	replace('aaaa','','b')	replace('bbbb','a','c')
@@ -96,3 +104,9 @@ domain
 hello.de
 domain
 test.de
+CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter), '"')
+"Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4"
+CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"')
+"Link";"1";"1";"1";"0";"4"
+CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter)
+Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index d0cca3bdb6f..bd34700e5ec 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -37,3 +37,5 @@ a
 146
 place_id	shows
 1	1
+status
+1
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index d4211868790..b7200b2d144 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -4,6 +4,8 @@
 # -----------
 # Testing string functions
 
+drop table if exists t1;
+
 select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';
 select 'hello' 'monty';
 select length('\n\t\r\b\0\_\%\\');
@@ -24,6 +26,11 @@ select concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***s
 select concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':');
 select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
 
+select concat_ws(', ','monty','was here','again');
+select concat_ws(NULL,'a'),concat_ws(',',NULL,'');
+select concat_ws(',','',NULL,'a');
+SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"');
+
 select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
 select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');
 select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ;
@@ -70,3 +77,26 @@ insert into t1 VALUES ("hello.de" ), ("test.de" );
 select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@hello.de';
 select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@test.de';
 drop table t1;
+
+#
+# Test bug in concat_ws
+#
+
+CREATE TABLE t1 (
+  id int(10) unsigned NOT NULL,
+  title varchar(255) default NULL,
+  prio int(10) unsigned default NULL,
+  category int(10) unsigned default NULL,
+  program int(10) unsigned default NULL,
+  bugdesc text,
+  created datetime default NULL,
+  modified timestamp(14) NOT NULL,
+  bugstatus int(10) unsigned default NULL,
+  submitter int(10) unsigned default NULL
+) TYPE=MyISAM;
+
+INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);
+SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter), '"') FROM t1;
+SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
+SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter) FROM t1;
+drop table t1;
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index a51bce8aebe..6f446a11521 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -37,3 +37,42 @@ VALUES (1,0,0,0,0,0,0,20000928174434);
 UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
 select place_id,shows from t1;
 drop table t1;
+
+#
+# Test bug with update reported by Jan Legenhausen
+#
+
+CREATE TABLE t1 (
+  lfdnr int(10) unsigned NOT NULL default '0',
+  ticket int(10) unsigned NOT NULL default '0',
+  client varchar(255) NOT NULL default '',
+  replyto varchar(255) NOT NULL default '',
+  subject varchar(100) NOT NULL default '',
+  timestamp int(10) unsigned NOT NULL default '0',
+  tstamp timestamp(14) NOT NULL,
+  status int(3) NOT NULL default '0',
+  type varchar(15) NOT NULL default '',
+  assignment int(10) unsigned NOT NULL default '0',
+  fupcount int(4) unsigned NOT NULL default '0',
+  parent int(10) unsigned NOT NULL default '0',
+  activity int(10) unsigned NOT NULL default '0',
+  priority tinyint(1) unsigned NOT NULL default '1',
+  cc varchar(255) NOT NULL default '',
+  bcc varchar(255) NOT NULL default '',
+  body text NOT NULL,
+  comment text,
+  header text,
+  PRIMARY KEY  (lfdnr),
+  KEY k1 (timestamp),
+  KEY k2 (type),
+  KEY k3 (parent),
+  KEY k4 (assignment),
+  KEY ticket (ticket)
+) TYPE=MyISAM;
+
+INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
+
+alter table t1 change lfdnr lfdnr int(10) unsigned default 0 not null auto_increment;
+update t1 set status=1 where type='Open';
+select status from t1;
+drop table t1;