Merge with 4.0 for 4.1 release

Noteworthy:
- New HANDLER code
- New multi-update-grant-check code
- Table lock code in ha_innodb.cc was not applied

10 files changed, 337 insertions, 22 deletions

diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index e29e43496af..472a3db648e 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -86,6 +86,24 @@ select * from t1 where word like binary 0xDF;
 select * from t1 where word like CAST(0xDF as CHAR);
 drop table t1;
 
+#
+# Bug #5447 Select does not find records
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+  autor varchar(80) NOT NULL default '',
+  PRIMARY KEY  (autor)
+);
+INSERT INTO t1 VALUES ('Powell, B.'),('Powell, Bud.'),('Powell, L. H.'),('Power, H.'),
+('Poynter, M. A. L. Lane'),('Poynting, J. H. und J. J. Thomson.'),('Pozzi, S(amuel-Jean).'),
+('Pozzi, Samuel-Jean.'),('Pozzo, A.'),('Pozzoli, Serge.');
+SELECT * FROM t1 WHERE autor LIKE 'Poz%' ORDER BY autor;
+DROP TABLE t1;
+
+#
+# Test of special character in german collation
+#
+
 CREATE TABLE t1 (
 s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci
 );
diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test
index 56c8ce77627..5f60445d765 100644
--- a/mysql-test/t/delete.test
+++ b/mysql-test/t/delete.test
@@ -71,6 +71,16 @@ delete from t1 where 3 > 2;
 select count(*) from t1;
 
 drop table t1;
+#
+# Bug #5733: Table handler error with self-join multi-table DELETE
+#
+
+create table t1 (a int not null auto_increment primary key, b char(32));
+insert into t1 (b) values ('apple'), ('apple');
+select * from t1;
+delete t1 from t1, t1 as t2 where t1.b = t2.b and t1.a > t2.a;
+select * from t1;
+drop table t1;
 
 #
 # IGNORE option
diff --git a/mysql-test/t/flush_table.test b/mysql-test/t/flush_table.test
index 8bee94cf21f..afb30d21da7 100644
--- a/mysql-test/t/flush_table.test
+++ b/mysql-test/t/flush_table.test
@@ -16,7 +16,8 @@ drop table t1;
 
 #
 # In the following test FLUSH TABLES produces a deadlock
-# (hang forever) if the fix for bug#3565 is missing.
+# (hang forever) if the fix for BUG #3565 is missing.
+# And it shows that handler tables are re-opened after flush (BUG #4286).
 #
 create table t1(table_id char(20) primary key);
 create table t2(table_id char(20) primary key);
@@ -31,28 +32,43 @@ handler a1 read first limit 9;
 handler a2 read first limit 9;
 handler t2 read first limit 9;
 flush tables;
---error 1109;
 handler a1 read first limit 9;
---error 1109;
 handler a2 read first limit 9;
---error 1109;
 handler t2 read first limit 9;
 #
+--error 1066
 handler t1 open as a1;
+--error 1066
 handler t1 open as a2;
+--error 1066
 handler t2 open;
 handler a1 read first limit 9;
 handler a2 read first limit 9;
 handler t2 read first limit 9;
 flush table t1;
---error 1109;
 handler a1 read first limit 9;
---error 1109;
 handler a2 read first limit 9;
 handler t2 read first limit 9;
 flush table t2;
---error 1109;
 handler t2 close;
 drop table t1;
 drop table t2;
 
+#
+# The fix for BUG #4286 cannot restore the position after a flush.
+#
+create table t1(table_id char(20) primary key);
+insert into t1 values ('Record-01');
+insert into t1 values ('Record-02');
+insert into t1 values ('Record-03');
+insert into t1 values ('Record-04');
+insert into t1 values ('Record-05');
+handler t1 open;
+handler t1 read first limit 1;
+handler t1 read next limit 1;
+handler t1 read next limit 1;
+flush table t1;
+handler t1 read next limit 1;
+handler t1 read next limit 1;
+handler t1 close;
+drop table t1;
diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test
index 7048d188604..693773b18c8 100644
--- a/mysql-test/t/func_if.test
+++ b/mysql-test/t/func_if.test
@@ -56,3 +56,7 @@ insert t1 values (1),(2);
 select if(1>2,a,avg(a)) from t1;
 drop table t1;
 
+#
+# Bug #5595  NULLIF() IS NULL returns false if NULLIF() returns NULL
+#
+SELECT NULLIF(5,5) IS NULL, NULLIF(5,5) IS NOT NULL;
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index e7852df40b3..3eab694ee05 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -182,6 +182,13 @@ select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') fr
 drop table t1;
 
 #
+# Bug #5498: TRIM fails with LEADING or TRAILING if remstr = str
+#
+
+select trim(trailing 'foo' from 'foo');
+select trim(leading 'foo' from 'foo');
+
+#
 # Test collation and coercibility
 #
 
diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test
index 685f438712c..b7fe5d10297 100644
--- a/mysql-test/t/handler.test
+++ b/mysql-test/t/handler.test
@@ -138,3 +138,207 @@ handler t1 read a=(1) where b=1;
 handler t1 close;
 drop table t1;
 
+#
+# Check if two database names beginning the same are seen as different.
+#
+# This database begins like the usual 'test' database.
+#
+--disable_warnings
+drop database if exists test_test;
+--enable_warnings
+create database test_test;
+use test_test;
+create table t1(table_id char(20) primary key);
+insert into t1 values ('test_test.t1');
+insert into t1 values ('');
+handler t1 open;
+handler t1 read first limit 9;
+create table t2(table_id char(20) primary key);
+insert into t2 values ('test_test.t2');
+insert into t2 values ('');
+handler t2 open;
+handler t2 read first limit 9;
+#
+# This is the usual 'test' database.
+#
+use test;
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1(table_id char(20) primary key);
+insert into t1 values ('test.t1');
+insert into t1 values ('');
+--error 1066
+handler t1 open;
+#
+# Check accesibility of all the tables.
+#
+use test;
+--error 1109;
+handler test.t1 read first limit 9;
+handler test_test.t1 read first limit 9;
+handler t1 read first limit 9;
+handler test_test.t2 read first limit 9;
+handler t2 read first limit 9;
+#
+# Cleanup.
+#
+
+handler test_test.t1 close;
+--error 1109;
+handler t1 close;
+drop table test_test.t1;
+handler test_test.t2 close;
+--error 1109;
+handler t2 close;
+drop table test_test.t2;
+drop database test_test;
+#
+use test;
+--error 1109;
+handler test.t1 close;
+--error 1109;
+handler t1 close;
+drop table test.t1;
+
+#
+# BUG#4335
+#
+--disable_warnings
+drop database if exists test_test;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+--enable_warnings
+create database test_test;
+use test_test;
+create table t1 (c1 char(20));
+insert into t1 values ('test_test.t1');
+create table t3 (c1 char(20));
+insert into t3 values ('test_test.t3');
+handler t1 open;
+handler t1 read first limit 9;
+handler t1 open h1;
+handler h1 read first limit 9;
+use test;
+create table t1 (c1 char(20));
+create table t2 (c1 char(20));
+create table t3 (c1 char(20));
+insert into t1 values ('t1');
+insert into t2 values ('t2');
+insert into t3 values ('t3');
+--error 1066
+handler t1 open;
+--error 1066
+handler t2 open t1;
+--error 1066
+handler t3 open t1;
+handler t1 read first limit 9;
+--error 1109
+handler test.t1 close;
+--error 1066
+handler test.t1 open h1;
+--error 1066
+handler test_test.t1 open h1;
+handler test_test.t3 open h3;
+handler test.t1 open h2;
+handler t1 read first limit 9;
+handler h1 read first limit 9;
+handler h2 read first limit 9;
+handler h3 read first limit 9;
+handler test.h2 read first limit 9;
+--error 1109
+handler test.h1 close;
+handler test_test.t1 close;
+handler test_test.h1 close;
+handler h2 close;
+--error 1109
+handler t1 read first limit 9;
+--error 1109
+handler h1 read first limit 9;
+--error 1109
+handler h2 read first limit 9;
+handler h3 read first limit 9;
+handler test_test.h3 read first limit 9;
+use test_test;
+handler h3 read first limit 9;
+--error 1109
+handler test.h3 read first limit 9;
+handler test_test.h3 close;
+use test;
+drop table t3;
+drop table t2;
+drop table t1;
+drop database test_test;
+
+#
+# Test if fix for BUG#4286 correctly closes handler tables.
+#
+create table t1 (c1 char(20));
+insert into t1 values ("t1");
+handler t1 open as h1;
+handler h1 read first limit 9;
+create table t2 (c1 char(20));
+insert into t2 values ("t2");
+handler t2 open as h2;
+handler h2 read first limit 9;
+create table t3 (c1 char(20));
+insert into t3 values ("t3");
+handler t3 open as h3;
+handler h3 read first limit 9;
+create table t4 (c1 char(20));
+insert into t4 values ("t4");
+handler t4 open as h4;
+handler h4 read first limit 9;
+create table t5 (c1 char(20));
+insert into t5 values ("t5");
+handler t5 open as h5;
+handler h5 read first limit 9;
+# close first
+alter table t1 engine=MyISAM;
+--error 1109;
+handler h1 read first limit 9;
+handler h2 read first limit 9;
+handler h3 read first limit 9;
+handler h4 read first limit 9;
+handler h5 read first limit 9;
+# close last
+alter table t5 engine=MyISAM;
+--error 1109;
+handler h1 read first limit 9;
+handler h2 read first limit 9;
+handler h3 read first limit 9;
+handler h4 read first limit 9;
+--error 1109;
+handler h5 read first limit 9;
+# close middle
+alter table t3 engine=MyISAM;
+--error 1109;
+handler h1 read first limit 9;
+handler h2 read first limit 9;
+--error 1109;
+handler h3 read first limit 9;
+handler h4 read first limit 9;
+--error 1109;
+handler h5 read first limit 9;
+handler h2 close;
+handler h4 close;
+# close all depending handler tables
+handler t1 open as h1_1;
+handler t1 open as h1_2;
+handler t1 open as h1_3;
+handler h1_1 read first limit 9;
+handler h1_2 read first limit 9;
+handler h1_3 read first limit 9;
+alter table t1 engine=MyISAM;
+--error 1109;
+handler h1_1 read first limit 9;
+--error 1109;
+handler h1_2 read first limit 9;
+--error 1109;
+handler h1_3 read first limit 9;
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
+drop table t5;
diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test
index fa095275182..cbda47ac864 100644
--- a/mysql-test/t/lock_multi.test
+++ b/mysql-test/t/lock_multi.test
@@ -53,6 +53,30 @@ reap;
 drop table t1;
 
 #
+# Test problem when using locks with multi-updates
+# It should not block when multi-update is reading on a read-locked table
+#
+
+connection locker;
+create table t1 (a int, b int);
+create table t2 (c int, d int);
+insert into t1 values(1,1);
+insert into t1 values(2,2);
+insert into t2 values(1,2);
+lock table t1 read;
+connection writer;
+--sleep 2
+send update t1,t2 set c=a where b=d;
+connection reader;
+--sleep 2
+select c from t2;
+connection writer;
+reap;
+connection locker;
+drop table t1;
+drop table t2;
+
+#
 # Test problem when using locks on many tables and droping a table that
 # is to-be-locked by another thread
 #
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 40e742679f8..e56636bd2ba 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -154,8 +154,6 @@ LOCK TABLES t1 write, t2 read;
 DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
 --error 1099
 UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
-# The following should be fixed to not give an error
---error 1099
 UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
 unlock tables;
 LOCK TABLES t1 write, t2 write;
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 3f0b41d7221..d3bda858b8b 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -62,19 +62,6 @@ INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
 SELECT * FROM t1;
 drop table t1;
 
-show variables like 'new';
-create table t1 (t2 timestamp(2), t4 timestamp(4), t6 timestamp(6),
-                 t8 timestamp(8), t10 timestamp(10), t12 timestamp(12),
-                 t14 timestamp(14));
-insert t1 values (0,0,0,0,0,0,0),
-("1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59",
-"1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59",
-"1997-12-31 23:47:59");
-select * from t1;
-set new=1;
-select * from t1;
-drop table t1;
-
 #
 # Let us check if we properly treat wrong datetimes and produce proper warnings
 # (for both strings and numbers)
@@ -258,6 +245,9 @@ select * from t1;
 drop table t1;
 
 # It is also OK to specify NULL as default explicitly for such fields.
+# This is also a test for bug #2464, DEFAULT keyword in INSERT statement
+# should return default value for column.
+
 create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
 show create table t1;
 insert into t1 values (NULL, NULL);
@@ -275,3 +265,14 @@ set TIMESTAMP=1000000000;
 insert into t1 values ();
 select * from t1;
 drop table t1;
+
+#
+# Test for bug #4131, TIMESTAMP columns missing minutes and seconds when
+# using GROUP BY in @@new=1 mode.
+#
+set new=1;
+create table t1 (a char(2), t timestamp);
+insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'),
+                      ('b', '2004-02-01 00:00:00');
+select max(t) from t1 group by a;
+drop table t1;
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 6ca75cf0c26..aeefa3c33f5 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -128,3 +128,36 @@ insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
 delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
 select * from t1;
 drop table t1;
+
+#
+# Bug#5553 - Multi table UPDATE IGNORE fails on duplicate keys 
+#
+
+CREATE TABLE t1 ( 
+   `colA` int(10) unsigned NOT NULL auto_increment,
+   `colB` int(11) NOT NULL default '0',
+   PRIMARY KEY  (`colA`)
+);
+INSERT INTO t1 VALUES (4433,5424);
+CREATE TABLE t2 (
+  `colC` int(10) unsigned NOT NULL default '0',
+  `colA` int(10) unsigned NOT NULL default '0',
+  `colD` int(10) unsigned NOT NULL default '0',
+  `colE` int(10) unsigned NOT NULL default '0',
+  `colF` int(10) unsigned NOT NULL default '0',
+  PRIMARY KEY  (`colC`,`colA`,`colD`,`colE`)
+);
+INSERT INTO t2 VALUES (3,4433,10005,495,500);
+INSERT INTO t2 VALUES (3,4433,10005,496,500);
+INSERT INTO t2 VALUES (3,4433,10009,494,500);
+INSERT INTO t2 VALUES (3,4433,10011,494,500);
+INSERT INTO t2 VALUES (3,4433,10005,497,500);
+INSERT INTO t2 VALUES (3,4433,10013,489,500);
+INSERT INTO t2 VALUES (3,4433,10005,494,500);
+INSERT INTO t2 VALUES (3,4433,10005,493,500);
+INSERT INTO t2 VALUES (3,4433,10005,492,500);
+UPDATE IGNORE t2,t1 set t2.colE = t2.colE + 1,colF=0 WHERE t1.colA = t2.colA AND (t1.colB & 4096) > 0 AND (colE + 1) < colF;
+SELECT * FROM t2;
+DROP TABLE t1;
+DROP TABLE t2;
+