Merge with 4.0

17 files changed, 249 insertions, 25 deletions

diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test
index 117ca40ce54..6aca345b282 100644
--- a/mysql-test/t/analyse.test
+++ b/mysql-test/t/analyse.test
@@ -5,10 +5,11 @@
 --disable_warnings
 drop table if exists t1,t2;
 --enable_warnings
-create table t1 (i int, j int);
-insert into t1 values (1,2), (3,4), (5,6), (7,8);
+create table t1 (i int, j int, empty_string char(10), bool char(1), d date);
+insert into t1 values (1,2,"","Y","2002-03-03"), (3,4,"","N","2002-03-04"), (5,6,"","Y","2002-03-04"), (7,8,"","N","2002-03-05");
 select count(*) from t1 procedure analyse();
 select * from t1 procedure analyse();
+select * from t1 procedure analyse(2);
 create table t2 select * from t1 procedure analyse();
 select * from t2;
 drop table t1,t2;
diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test
index 30979202bd7..d7f67fe80d4 100644
--- a/mysql-test/t/auto_increment.test
+++ b/mysql-test/t/auto_increment.test
@@ -54,6 +54,13 @@ insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,
 select * from t1;
 drop table t1;
 
+create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid,  id));
+create table t2 (sid char(20), id int(2));
+insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
+insert into t1 select * from t2;
+select * from t1;
+drop table t1,t2;
+
 #
 # Test of auto_increment columns when they are set to 0
 #
@@ -64,3 +71,4 @@ update t1 set a=0;
 select * from t1;
 check table t1;
 drop table t1;
+
diff --git a/mysql-test/t/backup-master.sh b/mysql-test/t/backup-master.sh
new file mode 100755
index 00000000000..99da5857afe
--- /dev/null
+++ b/mysql-test/t/backup-master.sh
@@ -0,0 +1,5 @@
+#!/bin/sh
+if [ "$MYSQL_TEST_DIR" ]
+then
+  rm -f $MYSQL_TEST_DIR/var/tmp/*.frm $MYSQL_TEST_DIR/var/tmp/*.MY?
+fi
diff --git a/mysql-test/t/backup.test b/mysql-test/t/backup.test
index 02ef72ef66d..a66c07fd27f 100644
--- a/mysql-test/t/backup.test
+++ b/mysql-test/t/backup.test
@@ -1,3 +1,7 @@
+#
+# This test is a bit tricky as we can't use backup table to overwrite an old
+# table
+#
 connect (con1,localhost,root,,);
 connect (con2,localhost,root,,);
 connection con1;
@@ -5,13 +9,17 @@ set SQL_LOG_BIN=0;
 --disable_warnings
 drop table if exists t1, t2, t3;
 --enable_warnings
+create table t4(n int);
+--replace_result "errno: 2" "errno: X" "errno: 22" "errno: X" "errno: 23" "errno: X"
+backup table t4 to '../bogus';
+backup table t4 to '../tmp';
+--replace_result "errno: 17" "errno: X"
+backup table t4 to '../tmp';
+drop table t4;
+restore table t4 from '../tmp';
+select count(*) from t4;
+
 create table t1(n int);
---replace_result "errno = 1" "errno = X" "errno = 2" "errno = X" "errno = 22" "errno = X" "errno = 23" "errno = X"
-backup table t1 to '../bogus';
-backup table t1 to '../tmp';
-drop table t1;
-restore table t1 from '../tmp';
-select count(*) from t1;
 insert into t1 values (23),(45),(67);
 backup table t1 to '../tmp';
 drop table t1;
@@ -22,23 +30,24 @@ create table t2(m int not null primary key);
 create table t3(k int not null primary key);
 insert into t2 values (123),(145),(167);
 insert into t3 values (223),(245),(267);
-backup table t1,t2,t3 to '../tmp';
+backup table t2,t3 to '../tmp';
 drop table t1,t2,t3;
 restore table t1,t2,t3 from '../tmp';
 select n from t1;
 select m from t2;
 select k from t3;
-drop table t1,t2,t3;
+drop table t1,t2,t3,t4;
 restore table t1 from '../tmp';
 connection con2;
+rename table t1 to t5;
 --send
-lock tables t1 write;
+lock tables t5 write;
 connection con1;
 --send
-backup table t1 to '../tmp';
+backup table t5 to '../tmp';
 connection con2;
 reap;
 unlock tables;
 connection con1;
 reap;
-drop table t1;
+drop table t5;
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 70a589c4be6..98d76bf2883 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -58,6 +58,14 @@ create table test_$1.test2$ (a int);
 drop table test_$1.test2$;
 drop database test_$1;
 
+--error 1103
+create table `` (a int);
+--error 1103
+drop table if exists ``;
+--error 1166
+create table t1 (`` int);
+drop table if exists t1;
+
 #
 # Test of CREATE ... SELECT with indexes
 #
diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index a4b4b816ec4..e0591913f68 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -4,13 +4,13 @@
 --disable_warnings
 drop table if exists t1;
 --enable_warnings
-create table t1 (a char (20) not null, b int not null auto_increment, index (a,b),index(b));
+create table t1 (a char (20) not null, b int not null auto_increment, index (a,b));
 insert into t1 (a) values ('ä'),('ac'),('ae'),('ad'),('Äc'),('aeb');
 insert into t1 (a) values ('üc'),('uc'),('ue'),('ud'),('Ü'),('ueb'),('uf');
 insert into t1 (a) values ('ö'),('oc'),('Öa'),('oe'),('od'),('Öc'),('oeb');
 insert into t1 (a) values ('s'),('ss'),('ß'),('ßb'),('ssa'),('ssc'),('ßa');
 insert into t1 (a) values ('eä'),('uü'),('öo'),('ää'),('ääa'),('aeae');
-insert into t1 (a) values ('q'),('a'),('u'),('o'),('é'),('É');
+insert into t1 (a) values ('q'),('a'),('u'),('o'),('é'),('É'),('a');
 select a,b from t1 order by a,b;
 select a,b from t1 order by upper(a),b;
 select a from t1 order by a desc;
diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test
index 57321739bfb..af047db04bd 100644
--- a/mysql-test/t/delete.test
+++ b/mysql-test/t/delete.test
@@ -46,3 +46,22 @@ CREATE TABLE `t` (
 -- error 1054
 DELETE FROM t USING t WHERE post='1';
 drop table if exists t;
+
+#
+# CHAR(0) bug - not actually DELETE bug, but anyway...
+#
+
+CREATE TABLE t1 (
+  bool     char(0) default NULL,
+  not_null varchar(20) binary NOT NULL default '',
+  misc     integer not null,
+  PRIMARY KEY  (not_null)
+) TYPE=MyISAM;
+
+INSERT INTO t1 VALUES (NULL,'a',4), (NULL,'b',5), (NULL,'c',6), (NULL,'d',7);
+
+select * from t1 where misc > 5 and bool is null;
+delete   from t1 where misc > 5 and bool is null;
+select * from t1 where misc > 5 and bool is null;
+
+drop table t1;
diff --git a/mysql-test/t/func_like.test b/mysql-test/t/func_like.test
index 47590ae7559..90b376e34df 100644
--- a/mysql-test/t/func_like.test
+++ b/mysql-test/t/func_like.test
@@ -8,10 +8,13 @@ drop table if exists t1;
 
 create table t1 (a varchar(10), key(a));
 insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
-select * from t1 where a like "abc%"; 
-select * from t1 where a like "ABC%"; 
-select * from t1 where a like "test%"; 
-select * from t1 where a like "te_t"; 
+explain select * from t1 where a like 'abc%';
+explain select * from t1 where a like concat('abc','%');
+select * from t1 where a like "abc%";
+select * from t1 where a like concat("abc","%");
+select * from t1 where a like "ABC%";
+select * from t1 where a like "test%";
+select * from t1 where a like "te_t";
 
 #
 # The following will test the Turbo Boyer-Moore code
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index dfcf72eb2c3..85ec680b3a2 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -78,7 +78,8 @@ CREATE TABLE t1 (
 
 INSERT INTO t1 VALUES (1,'1970-01-01','1997-10-17 00:00:00',2529,1,21000,11886,'check',0,'F',16200,6);
 
-!$1056 SELECT COUNT(P.URID),SUM(P.amount),P.method, MIN(PP.recdate+0) > 19980501000000   AS IsNew FROM t1 AS P JOIN t1 as PP WHERE P.URID = PP.URID GROUP BY method,IsNew;
+--error 1056
+SELECT COUNT(P.URID),SUM(P.amount),P.method, MIN(PP.recdate+0) > 19980501000000   AS IsNew FROM t1 AS P JOIN t1 as PP WHERE P.URID = PP.URID GROUP BY method,IsNew;
 
 drop table t1;
 
@@ -265,6 +266,14 @@ select sql_big_result score,count(*) from t1 group by score desc;
 drop table t1;
 
 #
+
+# not purely group_by bug, but group_by is involved...
+
+create table t1 (a date default null, b date default null);
+insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');
+select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;
+drop table t1;
+
 # Compare with hash keys
 #
 
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index b8ba55d9230..95a690e56b7 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -717,3 +717,14 @@ delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
 select * from t1;
 select * from t2;
 drop table t1,t2;
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1(id INT NOT NULL,  PRIMARY KEY (id)) TYPE=INNODB;
+CREATE TABLE t2(id  INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id)  ) TYPE=INNODB;
+INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES(1, 1);
+SELECT * from t1;
+UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
+SELECT * from t1;
+UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
+SELECT * from t1;
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 59c0c37e40d..7840e128ac7 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -248,3 +248,27 @@ CREATE TABLE t2 (
 INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');
 select t1.*, t2.*  from t1, t2 where t2.id=t1.t2_id limit 2;
 drop table t1,t2;
+
+#
+# Bug in range optimiser with MAYBE_KEY
+#
+
+CREATE TABLE t1 (
+  siteid varchar(25) NOT NULL default '',
+  emp_id varchar(30) NOT NULL default '',
+  rate_code varchar(10) default NULL,
+  UNIQUE KEY site_emp (siteid,emp_id),
+  KEY siteid (siteid)
+) TYPE=MyISAM;
+INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust');
+CREATE TABLE t2 (
+  siteid varchar(25) NOT NULL default '',
+  rate_code varchar(10) NOT NULL default '',
+  base_rate float NOT NULL default '0',
+  PRIMARY KEY  (siteid,rate_code),
+  FULLTEXT KEY rate_code (rate_code)
+) TYPE=MyISAM;
+INSERT INTO t2 VALUES ('rivercats','cust',20);
+SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
+SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
+drop table t1,t2;
diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test
index 0c6c59d0444..fa095275182 100644
--- a/mysql-test/t/lock_multi.test
+++ b/mysql-test/t/lock_multi.test
@@ -7,7 +7,7 @@
 -- source include/not_embedded.inc
 
 --disable_warnings
-drop table if exists t1;
+drop table if exists t1,t2;
 --enable_warnings
 
 # Test to see if select will get the lock ahead of low priority update
@@ -51,3 +51,22 @@ reap;
 connection reader;
 reap;
 drop table t1;
+
+#
+# Test problem when using locks on many tables and droping a table that
+# is to-be-locked by another thread
+#
+
+connection locker;
+create table t1 (a int);
+create table t2 (a int);
+lock table t1 write, t2 write;
+connection reader;
+send insert t1 select * from t2;
+connection locker;
+drop table t2;
+connection reader;
+--error 1146
+reap;
+connection locker;
+drop table t1;
diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test
index 8dfd0d8c2f9..3af7406cef3 100644
--- a/mysql-test/t/openssl_1.test
+++ b/mysql-test/t/openssl_1.test
@@ -1,6 +1,43 @@
-# We test openssl. Result set is optimized to be compiled with --with-openssl but 
-# SSL is swithced off in some reason
--- source include/have_openssl_2.inc
+# We test openssl. Result set is optimized to be compiled with --with-openssl.
+# Use mysql-test-run with --with-openssl option.
+-- source include/have_openssl_1.inc
 
-SHOW STATUS LIKE 'SSL%';
+drop table if exists t1;
+create table t1(f1 int);
+insert into t1 values (5);
 
+grant select on test.* to ssl_user1@localhost require SSL;
+grant select on test.* to ssl_user2@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
+grant select on test.* to ssl_user3@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "/C=RU/L=orenburg/O=MySQL AB/OU=client/CN=walrus/Email=walrus@mysql.com";
+grant select on test.* to ssl_user4@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "/C=RU/L=orenburg/O=MySQL AB/OU=client/CN=walrus/Email=walrus@mysql.com" ISSUER "/C=RU/ST=Some-State/L=Orenburg/O=MySQL AB/CN=Walrus/Email=walrus@mysql.com";
+flush privileges;
+connect (con1,localhost,ssl_user1,,);
+connect (con2,localhost,ssl_user2,,);
+connect (con3,localhost,ssl_user3,,);
+connect (con4,localhost,ssl_user4,,);
+
+connection con1;
+select * from t1;
+--error 1044;
+delete from t1;
+
+connection con2;
+select * from t1;
+--error 1044;
+delete from t1;
+
+connection con3;
+select * from t1;
+--error 1044;
+delete from t1;
+
+connection con4;
+select * from t1;
+--error 1044;
+delete from t1;
+
+connection default;
+delete from mysql.user where user='ssl_user%';
+delete from mysql.db where user='ssl_user%';
+flush privileges;
+drop table t1;
diff --git a/mysql-test/t/rpl_loaddatalocal.test b/mysql-test/t/rpl_loaddatalocal.test
new file mode 100644
index 00000000000..70f4ab96b6a
--- /dev/null
+++ b/mysql-test/t/rpl_loaddatalocal.test
@@ -0,0 +1,36 @@
+# See if "LOAD DATA LOCAL INFILE" is well replicated
+# (LOAD DATA LOCAL INFILE is not written to the binlog
+# the same way as LOAD DATA INFILE : Append_blocks are smaller).
+# In MySQL 4.0 <4.0.12 there were 2 bugs with LOAD DATA LOCAL INFILE :
+# - the loaded file was not written entirely to the master's binlog,
+# only the first 4KB, 8KB or 16KB usually.
+# - the loaded file's first line was not written entirely to the
+# master's binlog (1st char was absent)
+source include/master-slave.inc;
+
+create table t1(a int);
+let $1=10000;
+disable_query_log;
+set SQL_LOG_BIN=0;
+while ($1)
+{
+#eval means expand $ expressions
+ eval insert into t1 values(1);
+ dec $1;
+}
+set SQL_LOG_BIN=1;
+enable_query_log;
+select * into outfile '../../var/master-data/rpl_loaddatalocal.select_outfile' from t1;
+#This will generate a 20KB file, now test LOAD DATA LOCAL
+truncate table t1;
+load data local infile './var/master-data/rpl_loaddatalocal.select_outfile' into table t1;
+system rm ./var/master-data/rpl_loaddatalocal.select_outfile ;
+save_master_pos;
+connection slave;
+sync_with_master;
+select a,count(*) from t1 group by a;
+connection master;
+drop table t1;
+save_master_pos;
+connection slave;
+sync_with_master;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index d4effd2026c..3c26cf1903b 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1722,11 +1722,27 @@ drop table t4, t3, t2, t1;
 
 #
 # Test of DO
+#
 
 DO 1;
 DO benchmark(100,1+1),1,1;
 
 #
+# random in WHERE clause
+#
+
+CREATE TABLE t1 (
+  id mediumint(8) unsigned NOT NULL auto_increment,
+  pseudo varchar(35) NOT NULL default '',
+  PRIMARY KEY  (id),
+  UNIQUE KEY pseudo (pseudo)
+);
+INSERT INTO t1 (pseudo) VALUES ('test');
+INSERT INTO t1 (pseudo) VALUES ('test1');
+SELECT 1 as rnd1 from t1 where rand() > 2;
+DROP TABLE t1;
+
+#
 # Test of bug with SUM(CASE...)
 #
 
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index f791cd76d34..e9c45b2908f 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -62,3 +62,8 @@ INSERT INTO t1 (numfacture,expedition) VALUES ('1212','0001-00-00 00:00:00');
 SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';
 EXPLAIN SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';
 drop table t1;
+create table t1 (a datetime not null, b datetime not null);
+insert into t1 values (now(), now());
+insert into t1 values (now(), now());
+select * from t1 where a is null or b is null;
+drop table t1;
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 1c9275ecd0a..cd76dbe6ab0 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -58,3 +58,17 @@ INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
 #INSERT INTO t1 VALUES ("2050-01-01","2050-01-01 00:00:00",20500101000000);
 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;
+