Merge with 4.0.13

21 files changed, 304 insertions, 21 deletions

diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh
index 81e3f866709..137e99f48b3 100644
--- a/mysql-test/mysql-test-run.sh
+++ b/mysql-test/mysql-test-run.sh
@@ -13,12 +13,15 @@ DB=test
 DBPASSWD=
 VERBOSE=""
 USE_MANAGER=0
-TZ=GMT-3; export TZ # for UNIX_TIMESTAMP tests to work
+MY_TZ=GMT-3
+TZ=$MY_TZ; export TZ # for UNIX_TIMESTAMP tests to work
 
 #++
 # Program Definitions
 #--
 
+LC_COLLATE=C
+export LC_COLLATE
 PATH=/bin:/usr/bin:/usr/local/bin:/usr/bsd:/usr/X11R6/bin:/usr/openwin/bin:/usr/bin/X11:$PATH
 MASTER_40_ARGS="--rpl-recovery-rank=1 --init-rpl-role=master"
 
@@ -1163,9 +1166,18 @@ run_testcase ()
    if [ -f $master_opt_file ] ;
    then
      EXTRA_MASTER_OPT=`$CAT $master_opt_file | $SED -e "s;\\$MYSQL_TEST_DIR;$MYSQL_TEST_DIR;"`
+     case "$EXTRA_MASTER_OPT" in
+       --timezone=*)
+	 TZ=`$ECHO "$EXTRA_MASTER_OPT" | $SED -e "s;--timezone=;;"`
+	 export TZ
+	 # Note that this must be set to space, not "" for test-reset to work
+	 EXTRA_MASTER_OPT=" "
+	 ;;
+     esac
      stop_master
      echo "CURRENT_TEST: $tname" >> $MASTER_MYERR
      start_master
+     TZ=$MY_TZ; export TZ
    else
      if [ ! -z "$EXTRA_MASTER_OPT" ] || [ x$MASTER_RUNNING != x1 ] || [ -f $master_init_script ]
      then
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 7bd836acefd..02c392c964f 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -276,3 +276,8 @@ t1	0	a	1	a	A	3	NULL	NULL	YES	BTREE
 t1	0	a	2	b	A	300	NULL	NULL	YES	BTREE	
 t1	1	b	1	b	A	100	NULL	NULL	YES	BTREE	
 drop table t1;
+CREATE TABLE t1 (i int(10), index(i) );
+ALTER TABLE t1 DISABLE KEYS;
+INSERT DELAYED INTO t1 VALUES(1),(2),(3);
+ALTER TABLE t1 ENABLE KEYS;
+drop table t1;
diff --git a/mysql-test/r/err000001.result b/mysql-test/r/errors.result
index d0011c8deb6..d0011c8deb6 100644
--- a/mysql-test/r/err000001.result
+++ b/mysql-test/r/errors.result
diff --git a/mysql-test/r/have_mest_timezone.require b/mysql-test/r/have_mest_timezone.require
new file mode 100644
index 00000000000..2a219f39b7e
--- /dev/null
+++ b/mysql-test/r/have_mest_timezone.require
@@ -0,0 +1,2 @@
+Variable_name	Value
+timezone	MEST
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 3325995f2bd..4f6349f8f8b 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -65,3 +65,15 @@ INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
 SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2
 WHERE numeropost=9 ORDER BY numreponse ASC;
 DROP TABLE IF EXISTS t1,t2;
+create table t1(a int, unique(a));
+insert into t1 values(2);
+create table t2(a int);
+insert into t2 values(1),(2);
+reset master;
+insert into t1 select * from t2;
+Duplicate entry '2' for key 1
+show binlog events;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	4	Start	1	4	Server ver: VERSION, Binlog ver: 3
+master-bin.001	79	Query	1	79	use test; insert into t1 select * from t2
+drop table t1, t2;
diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result
index 7b4802136f3..560c5b2567e 100644
--- a/mysql-test/r/loaddata.result
+++ b/mysql-test/r/loaddata.result
@@ -15,4 +15,11 @@ a	b	c	d
 0000-00-00	0000-00-00	0000-00-00	0000-00-00
 2003-03-03	2003-03-03	2003-03-03	NULL
 2003-03-03	2003-03-03	2003-03-03	NULL
+truncate table t1;
+load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
+SELECT * from t1;
+a	b	c	d
+NULL	NULL	0000-00-00	0000-00-00
+NULL	0000-00-00	0000-00-00	0000-00-00
+NULL	2003-03-03	2003-03-03	NULL
 drop table t1;
diff --git a/mysql-test/r/lowercase_table.result b/mysql-test/r/lowercase_table.result
index 1caaf317c96..d32228216b8 100644
--- a/mysql-test/r/lowercase_table.result
+++ b/mysql-test/r/lowercase_table.result
@@ -13,3 +13,15 @@ show tables like 't_';
 Tables_in_test (t_)
 t3
 drop table t3;
+create table t1 (a int);
+select count(*) from T1;
+count(*)
+0
+select count(*) from t1;
+count(*)
+0
+select count(T1.a) from t1;
+Unknown table 'T1' in field list
+select count(bags.a) from t1 as Bags;
+Unknown table 'bags' in field list
+drop table t1;
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 4622bb231a2..9d0c105b6c0 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -502,6 +502,7 @@ drop table t1;
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	0
+create table t1 (a int);
 show global variables like "query_cache_min_res_unit";
 Variable_name	Value
 query_cache_min_res_unit	4096
@@ -565,18 +566,57 @@ set GLOBAL query_cache_size=1000;
 show global variables like "query_cache_size";
 Variable_name	Value
 query_cache_size	0
-set GLOBAL query_cache_size=1100;
-set GLOBAL query_cache_size=1200;
-set GLOBAL query_cache_size=1300;
-set GLOBAL query_cache_size=1400;
-set GLOBAL query_cache_size=1500;
-set GLOBAL query_cache_size=1600;
-set GLOBAL query_cache_size=1700;
-set GLOBAL query_cache_size=1800;
-set GLOBAL query_cache_size=1900;
+select * from t1;
+a
+set GLOBAL query_cache_size=1024;
+show global variables like "query_cache_size";
+Variable_name	Value
+query_cache_size	0
+select * from t1;
+a
+set GLOBAL query_cache_size=10240;
+show global variables like "query_cache_size";
+Variable_name	Value
+query_cache_size	0
+select * from t1;
+a
+set GLOBAL query_cache_size=20480;
+show global variables like "query_cache_size";
+Variable_name	Value
+query_cache_size	0
+select * from t1;
+a
+set GLOBAL query_cache_size=40960;
+show global variables like "query_cache_size";
+Variable_name	Value
+query_cache_size	0
+select * from t1;
+a
+set GLOBAL query_cache_size=51200;
+show global variables like "query_cache_size";
+Variable_name	Value
+query_cache_size	51200
+select * from t1;
+a
+set GLOBAL query_cache_size=61440;
 show global variables like "query_cache_size";
 Variable_name	Value
-query_cache_size	1024
+query_cache_size	61440
+select * from t1;
+a
+set GLOBAL query_cache_size=81920;
+show global variables like "query_cache_size";
+Variable_name	Value
+query_cache_size	81920
+select * from t1;
+a
+set GLOBAL query_cache_size=102400;
+show global variables like "query_cache_size";
+Variable_name	Value
+query_cache_size	102400
+select * from t1;
+a
+drop table t1;
 set GLOBAL query_cache_size=1048576;
 create table t1 (i int not null);
 create table t2 (i int not null);
diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result
index 82015bdf538..3d7a2a0dd75 100644
--- a/mysql-test/r/rpl_insert_id.result
+++ b/mysql-test/r/rpl_insert_id.result
@@ -22,10 +22,12 @@ drop table t1;
 drop table t2;
 create table t1(a int auto_increment, key(a));
 create table t2(b int auto_increment, c int, key(b));
+SET FOREIGN_KEY_CHECKS=0;
 insert into t1 values (10);
 insert into t1 values (null),(null),(null);
 insert into t2 values (5,0);
 insert into t2 values (null,last_insert_id());
+SET FOREIGN_KEY_CHECKS=1;
 select * from t1;
 a
 10
@@ -38,3 +40,31 @@ b	c
 6	11
 drop table t1;
 drop table t2;
+create table t1(a int auto_increment, key(a));
+create table t2(b int auto_increment, c int, key(b));
+insert into t1 values (10);
+insert into t1 values (null),(null),(null);
+insert into t2 values (5,0);
+insert into t2 (c) select * from t1;
+select * from t2;
+b	c
+5	0
+6	10
+7	11
+8	12
+9	13
+select * from t1;
+a
+10
+11
+12
+13
+select * from t2;
+b	c
+5	0
+6	10
+7	11
+8	12
+9	13
+drop table t1;
+drop table t2;
diff --git a/mysql-test/r/timezone.result b/mysql-test/r/timezone.result
new file mode 100644
index 00000000000..b82b39da262
--- /dev/null
+++ b/mysql-test/r/timezone.result
@@ -0,0 +1,25 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (ts int);
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 03:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 02:59:59'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 03:00:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 03:59:59'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 04:00:01'));
+SELECT ts,from_unixtime(ts) FROM t1;
+ts	from_unixtime(ts)
+1035673200	2002-10-27 01:00:00
+1035680400	2002-10-27 02:00:00
+1035684000	2002-10-27 03:00:00
+1035680400	2002-10-27 02:00:00
+1035673200	2002-10-27 01:00:00
+1035680400	2002-10-27 02:00:00
+1048986000	2003-03-30 03:00:00
+1048986000	2003-03-30 03:00:00
+1048989599	2003-03-30 03:59:59
+1048989601	2003-03-30 04:00:01
+DROP TABLE t1;
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index fc22bc85aee..04229bd1882 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -135,3 +135,12 @@ analyze table t1;
 show keys from t1;
 drop table t1;
 
+#
+# Test of ALTER TABLE DELAYED
+#
+
+CREATE TABLE t1 (i int(10), index(i) );
+ALTER TABLE t1 DISABLE KEYS;
+INSERT DELAYED INTO t1 VALUES(1),(2),(3);
+ALTER TABLE t1 ENABLE KEYS;
+drop table t1;
diff --git a/mysql-test/t/err000001.test b/mysql-test/t/errors.test
index b558c90b0c6..b558c90b0c6 100644
--- a/mysql-test/t/err000001.test
+++ b/mysql-test/t/errors.test
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 8fcb22e4684..5bd7b95f560 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -71,3 +71,20 @@ WHERE numeropost=9 ORDER BY numreponse ASC;
 
 DROP TABLE IF EXISTS t1,t2;
 
+# Addendum by Guilhem:
+# Check if a partly-completed INSERT SELECT in a MyISAM table goes
+# into the binlog
+
+create table t1(a int, unique(a));
+insert into t1 values(2);
+create table t2(a int);
+insert into t2 values(1),(2);
+reset master;
+--error 1062
+insert into t1 select * from t2;
+# The above should produce an error, but still be in the binlog;
+# verify the binlog :
+let $VERSION=`select version()`;
+--replace_result $VERSION VERSION
+show binlog events;
+drop table t1, t2;
diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test
index d7e84590dc0..2f484d30ff7 100644
--- a/mysql-test/t/loaddata.test
+++ b/mysql-test/t/loaddata.test
@@ -10,4 +10,12 @@ create table t1 (a date, b date, c date not null, d date);
 load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',';
 load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
 SELECT * from t1;
+truncate table t1;
+
+load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
+SELECT * from t1;
 drop table t1;
+
+
+
+
diff --git a/mysql-test/t/lowercase_table.test b/mysql-test/t/lowercase_table.test
index 348116b02f2..cb30c14b70c 100644
--- a/mysql-test/t/lowercase_table.test
+++ b/mysql-test/t/lowercase_table.test
@@ -14,3 +14,14 @@ ALTER TABLE T2 ADD new_col int not null;
 ALTER TABLE T2 RENAME T3;
 show tables like 't_';
 drop table t3;
+#
+# Test alias
+#
+create table t1 (a int);
+select count(*) from T1;
+select count(*) from t1;
+--error 1109
+select count(T1.a) from t1;
+--error 1109
+select count(bags.a) from t1 as Bags;
+drop table t1;
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 1078d19533c..929146ba97a 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -371,18 +371,35 @@ show global variables like "query_cache_min_res_unit";
 #
 # Test of query cache resizing
 #
+create table t1 (a int);
 set GLOBAL query_cache_size=1000;
 show global variables like "query_cache_size";
-set GLOBAL query_cache_size=1100;
-set GLOBAL query_cache_size=1200;
-set GLOBAL query_cache_size=1300;
-set GLOBAL query_cache_size=1400;
-set GLOBAL query_cache_size=1500;
-set GLOBAL query_cache_size=1600;
-set GLOBAL query_cache_size=1700;
-set GLOBAL query_cache_size=1800;
-set GLOBAL query_cache_size=1900;
+select * from t1;
+set GLOBAL query_cache_size=1024;
+show global variables like "query_cache_size";
+select * from t1;
+set GLOBAL query_cache_size=10240;
+show global variables like "query_cache_size";
+select * from t1;
+set GLOBAL query_cache_size=20480;
+show global variables like "query_cache_size";
+select * from t1;
+set GLOBAL query_cache_size=40960;
+show global variables like "query_cache_size";
+select * from t1;
+set GLOBAL query_cache_size=51200;
+show global variables like "query_cache_size";
+select * from t1;
+set GLOBAL query_cache_size=61440;
+show global variables like "query_cache_size";
+select * from t1;
+set GLOBAL query_cache_size=81920;
 show global variables like "query_cache_size";
+select * from t1;
+set GLOBAL query_cache_size=102400;
+show global variables like "query_cache_size";
+select * from t1;
+drop table t1;
 
 #
 # Temporary tables
diff --git a/mysql-test/t/raid.test b/mysql-test/t/raid.test
index 235add63c22..395a04615cb 100644
--- a/mysql-test/t/raid.test
+++ b/mysql-test/t/raid.test
@@ -1,5 +1,7 @@
 -- require r/have_raid.require
+disable_query_log;
 show variables like "have_raid";
+enable_query_log;
 
 #
 # Test of raided tables
diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test
index 93062f39c20..3e84f86c092 100644
--- a/mysql-test/t/rpl_insert_id.test
+++ b/mysql-test/t/rpl_insert_id.test
@@ -1,5 +1,8 @@
 # See if queries that use both auto_increment and LAST_INSERT_ID()
 # are replicated well
+
+# We also check how the foreign_key_check variable is replicated
+
 source include/master-slave.inc;
 connection master;
 create table t1(a int auto_increment, key(a));
@@ -20,10 +23,30 @@ drop table t1;
 drop table t2;
 create table t1(a int auto_increment, key(a));
 create table t2(b int auto_increment, c int, key(b));
+SET FOREIGN_KEY_CHECKS=0;
 insert into t1 values (10);
 insert into t1 values (null),(null),(null);
 insert into t2 values (5,0);
 insert into t2 values (null,last_insert_id());
+SET FOREIGN_KEY_CHECKS=1;
+save_master_pos;
+connection slave;
+sync_with_master;
+select * from t1;
+select * from t2;
+connection master;
+
+# check if INSERT SELECT in auto_increment is well replicated (bug #490)
+
+drop table t1;
+drop table t2;
+create table t1(a int auto_increment, key(a));
+create table t2(b int auto_increment, c int, key(b));
+insert into t1 values (10);
+insert into t1 values (null),(null),(null);
+insert into t2 values (5,0);
+insert into t2 (c) select * from t1;
+select * from t2;
 save_master_pos;
 connection slave;
 sync_with_master;
diff --git a/mysql-test/t/rpl_log.test b/mysql-test/t/rpl_log.test
index faca1a6c339..369c62848f9 100644
--- a/mysql-test/t/rpl_log.test
+++ b/mysql-test/t/rpl_log.test
@@ -32,11 +32,33 @@ show binlog events from 79 limit 2;
 show binlog events from 79 limit 2,1;
 flush logs;
 
+# We need an extra update before doing save_master_pos.
+# Otherwise, an unlikely scenario may occur:
+# * When the master's binlog_dump thread reads the end of master-bin.001,
+# it send the rotate event which is at this end, plus a fake rotate event
+# because it's starting to read a new binlog.
+# save_master_pos will record the position of the first of the two rotate
+# (because the fake one is not in the master's binlog anyway).
+# * Later the slave waits for the position of the first rotate event,
+# and it may quickly stop (in 'slave stop') without having received the fake
+# one.
+# So, depending on a few milliseconds, we end up with 2 rotate events in the
+# relay log or one, which influences the output of SHOW SLAVE STATUS, making
+# it not predictable and causing random test failures.
+# To make it predictable, we do a useless update now, but which has the interest
+# of making the slave catch both rotate events.
+
+create table t5 (a int);
+drop table t5;
+
 # Sync slave and force it to start on another binary log
 
 save_master_pos;
 connection slave;
-start slave;
+# Note that the above 'slave start' will cause a 3rd rotate event (a fake one)
+# to go into the relay log (the master always sends a fake one when replication
+# starts). 
+slave start;
 sync_with_master;
 flush logs;
 stop slave;
diff --git a/mysql-test/t/timezone-master.opt b/mysql-test/t/timezone-master.opt
new file mode 100644
index 00000000000..0477f941e9d
--- /dev/null
+++ b/mysql-test/t/timezone-master.opt
@@ -0,0 +1 @@
+--timezone=MET
diff --git a/mysql-test/t/timezone.test b/mysql-test/t/timezone.test
new file mode 100644
index 00000000000..14facc0374a
--- /dev/null
+++ b/mysql-test/t/timezone.test
@@ -0,0 +1,28 @@
+#
+# Test of timezone handling.  This script must be run with TZ=MEST
+
+-- require r/have_mest_timezone.require
+disable_query_log;
+show variables like "timezone";
+enable_query_log;
+
+# Initialization
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+
+CREATE TABLE t1 (ts int);
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 03:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 02:59:59'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 03:00:00'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 03:59:59'));
+INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 04:00:01'));
+
+SELECT ts,from_unixtime(ts) FROM t1;
+DROP TABLE t1;