summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/ctype_latin1_de.result15
-rw-r--r--mysql-test/r/func_if.result3
-rw-r--r--mysql-test/r/func_str.result6
-rw-r--r--mysql-test/r/key_primary.result2
-rw-r--r--mysql-test/r/lock_multi.result12
-rw-r--r--mysql-test/r/multi_update.result1
-rw-r--r--mysql-test/r/null_key.result2
-rw-r--r--mysql-test/r/rpl_set_charset.result51
-rw-r--r--mysql-test/r/select.result16
-rw-r--r--mysql-test/r/type_timestamp.result60
-rw-r--r--mysql-test/r/union.result2
-rw-r--r--mysql-test/r/update.result37
-rw-r--r--mysql-test/t/ctype_latin1_de.test14
-rw-r--r--mysql-test/t/func_if.test4
-rw-r--r--mysql-test/t/func_str.test7
-rw-r--r--mysql-test/t/lock_multi.test24
-rw-r--r--mysql-test/t/multi_update.test2
-rw-r--r--mysql-test/t/rpl_set_charset.test40
-rw-r--r--mysql-test/t/select.test21
-rw-r--r--mysql-test/t/type_timestamp.test32
-rw-r--r--mysql-test/t/union.test2
-rw-r--r--mysql-test/t/update.test33
22 files changed, 370 insertions, 16 deletions
diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result
index 28394d9533a..c4bf6b5a3a9 100644
--- a/mysql-test/r/ctype_latin1_de.result
+++ b/mysql-test/r/ctype_latin1_de.result
@@ -267,3 +267,18 @@ select * from t1 where word like CAST(0xDF as CHAR);
word word2
drop table t1;
+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;
+autor
+Pozzi, S(amuel-Jean).
+Pozzi, Samuel-Jean.
+Pozzo, A.
+Pozzoli, Serge.
+DROP TABLE t1;
diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result
index aee54ede324..72226588de3 100644
--- a/mysql-test/r/func_if.result
+++ b/mysql-test/r/func_if.result
@@ -64,3 +64,6 @@ select if(1>2,a,avg(a)) from t1;
if(1>2,a,avg(a))
1.5000
drop table t1;
+SELECT NULLIF(5,5) IS NULL, NULLIF(5,5) IS NOT NULL;
+NULLIF(5,5) IS NULL NULLIF(5,5) IS NOT NULL
+1 0
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 12c1cf78f7c..b4d1be5bd54 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -282,3 +282,9 @@ NULL NULL 1 1 n
two 'two' 0 0 'two'
four 'four' 0 0 'four'
drop table t1;
+select trim(trailing 'foo' from 'foo');
+trim(trailing 'foo' from 'foo')
+
+select trim(leading 'foo' from 'foo');
+trim(leading 'foo' from 'foo')
+
diff --git a/mysql-test/r/key_primary.result b/mysql-test/r/key_primary.result
index 87289f1cf54..3216ead667b 100644
--- a/mysql-test/r/key_primary.result
+++ b/mysql-test/r/key_primary.result
@@ -13,7 +13,7 @@ t1
AB%
describe select * from t1 where t1="ABC";
table type possible_keys key key_len ref rows Extra
-t1 const PRIMARY PRIMARY 3 const 1
+t1 const PRIMARY PRIMARY 3 const 1 Using index
describe select * from t1 where t1="ABCD";
Comment
Impossible WHERE noticed after reading const tables
diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result
index b808fca0acf..b5672fe1791 100644
--- a/mysql-test/r/lock_multi.result
+++ b/mysql-test/r/lock_multi.result
@@ -17,6 +17,18 @@ unlock tables;
n
1
drop table t1;
+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;
+ update t1,t2 set c=a where b=d;
+select c from t2;
+c
+2
+drop table t1;
+drop table t2;
create table t1 (a int);
create table t2 (a int);
lock table t1 write, t2 write;
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 12cb965f045..fc414f2f46b 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -151,7 +151,6 @@ Table 't2' was locked with a READ lock and can't be updated
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
Table 't2' was locked with a READ lock and can't be updated
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
-Table 't2' was locked with a READ lock and can't be updated
unlock tables;
LOCK TABLES t1 write, t2 write;
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 009a3e06eb2..41b0ae26617 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -12,7 +12,7 @@ table type possible_keys key key_len ref rows Extra
t1 ref a,b a 9 const,const 1 Using where; Using index
explain select * from t1 where a=2 and b = 2;
table type possible_keys key key_len ref rows Extra
-t1 const a,b a 9 const,const 1
+t1 const a,b a 9 const,const 1 Using index
explain select * from t1 where a<=>b limit 2;
table type possible_keys key key_len ref rows Extra
t1 index NULL a 9 NULL 12 Using where; Using index
diff --git a/mysql-test/r/rpl_set_charset.result b/mysql-test/r/rpl_set_charset.result
new file mode 100644
index 00000000000..9c7ea73c741
--- /dev/null
+++ b/mysql-test/r/rpl_set_charset.result
@@ -0,0 +1,51 @@
+slave stop;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+slave start;
+drop database if exists mysqltest1;
+create database mysqltest1 /*!40100 character set latin2 */;
+use mysqltest1;
+drop table if exists t1;
+create table t1 (a varchar(255) character set latin2, b varchar(4));
+SET CHARACTER SET cp1250_latin2;
+INSERT INTO t1 VALUES ('','80');
+INSERT INTO t1 VALUES ('','90');
+INSERT INTO t1 VALUES ('','A0');
+INSERT INTO t1 VALUES ('','B0');
+INSERT INTO t1 VALUES ('','C0');
+INSERT INTO t1 VALUES ('','D0');
+INSERT INTO t1 VALUES ('','E0');
+INSERT INTO t1 VALUES ('','F0');
+select "--- on master ---";
+--- on master ---
+--- on master ---
+select hex(a),b from t1 order by b;
+hex(a) b
+A9A6ABAEAC 80
+B9B6BBBEBC 90
+A3A1AAAF A0
+B3B1BAA5B5BF B0
+C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF C0
+D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF D0
+E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF E0
+F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF F0
+show binlog events from 1979;
+Log_name Pos Event_type Server_id Orig_log_pos Info
+master-bin.001 1979 Query 1 1979 use `mysqltest1`; SET CHARACTER SET DEFAULT
+use mysqltest1;
+select "--- on slave ---";
+--- on slave ---
+--- on slave ---
+select hex(a),b from t1 order by b;
+hex(a) b
+A9A6ABAEAC 80
+B9B6BBBEBC 90
+A3A1AAAF A0
+B3B1BAA5B5BF B0
+C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF C0
+D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF D0
+E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF E0
+F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF F0
+drop database mysqltest1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 206fa507615..ce5ea94a08e 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2327,3 +2327,19 @@ select * from t2,t3 where t2.s = t3.s;
s s
two two
drop table t1, t2, t3;
+CREATE TABLE t1 (
+i int(11) NOT NULL default '0',
+c char(10) NOT NULL default '',
+PRIMARY KEY (i),
+UNIQUE KEY c (c)
+) TYPE=MyISAM;
+INSERT INTO t1 VALUES (1,'a');
+INSERT INTO t1 VALUES (2,'b');
+INSERT INTO t1 VALUES (3,'c');
+EXPLAIN SELECT i FROM t1 WHERE i=1;
+table type possible_keys key key_len ref rows Extra
+t1 const PRIMARY PRIMARY 4 const 1 Using index
+EXPLAIN SELECT i FROM t1 WHERE i=1;
+table type possible_keys key key_len ref rows Extra
+t1 const PRIMARY PRIMARY 4 const 1 Using index
+DROP TABLE t1;
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index 752a5045eb0..6253fa96ba8 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -122,40 +122,41 @@ t2 t4 t6 t8 t10 t12 t14
0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00
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
drop table t1;
+set new=0;
create table t1 (t1 timestamp default '2003-01-01 00:00:00',
t2 timestamp default '2003-01-01 00:00:00');
set TIMESTAMP=1000000000;
insert into t1 values();
select * from t1;
t1 t2
-2001-09-09 04:46:40 2003-01-01 00:00:00
+20010909044640 20030101000000
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`t1` timestamp(14) NOT NULL,
- `t2` timestamp(14) NOT NULL default '2003-01-01 00:00:00'
+ `t2` timestamp(14) NOT NULL default '20030101000000'
) TYPE=MyISAM
show columns from t1;
Field Type Null Key Default Extra
t1 timestamp(14) YES NULL
-t2 timestamp(14) YES 2003-01-01 00:00:00
+t2 timestamp(14) YES 20030101000000
show columns from t1 like 't2';
Field Type Null Key Default Extra
-t2 timestamp(14) YES 2003-01-01 00:00:00
+t2 timestamp(14) YES 20030101000000
create table t2 (select * from t1);
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`t1` timestamp(14) NOT NULL,
- `t2` timestamp(14) NOT NULL default '2003-01-01 00:00:00'
+ `t2` timestamp(14) NOT NULL default '20030101000000'
) TYPE=MyISAM
alter table t1 add column t0 timestamp first;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`t0` timestamp(14) NOT NULL,
- `t1` timestamp(14) NOT NULL default '2003-01-01 00:00:00',
- `t2` timestamp(14) NOT NULL default '2003-01-01 00:00:00'
+ `t1` timestamp(14) NOT NULL default '20030101000000',
+ `t2` timestamp(14) NOT NULL default '20030101000000'
) TYPE=MyISAM
drop table t1,t2;
create table t1 (ts1 timestamp, ts2 timestamp);
@@ -164,8 +165,8 @@ insert into t1 values ();
insert into t1 values (DEFAULT, DEFAULT);
select * from t1;
ts1 ts2
-2001-09-09 04:46:40 0000-00-00 00:00:00
-2001-09-09 04:46:40 0000-00-00 00:00:00
+20010909044640 00000000000000
+20010909044640 00000000000000
drop table t1;
create table t1 (ts timestamp(19));
show create table t1;
@@ -179,3 +180,44 @@ select * from t1;
ts
2001-09-09 04:46:40
drop table t1;
+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;
+max(t)
+2004-01-01 01:00:00
+2004-02-01 00:00:00
+drop table t1;
+create table t1 (ts1 timestamp);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `ts1` timestamp(19) NOT NULL
+) TYPE=MyISAM
+alter table t1 add ts2 timestamp;
+set new=0;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `ts1` timestamp(19) NOT NULL,
+ `ts2` timestamp(19) NOT NULL default '0000-00-00 00:00:00'
+) TYPE=MyISAM
+drop table t1;
+create table t1 (ts1 timestamp);
+insert into t1 values ('2004-01-01 00:00:00'), ('2004-01-01 01:00:00');
+select * from t1;
+ts1
+20040101000000
+20040101010000
+set new=1;
+select * from t1;
+ts1
+2004-01-01 00:00:00
+2004-01-01 01:00:00
+set new=0;
+select * from t1;
+ts1
+20040101000000
+20040101010000
+drop table t1;
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 8f33bc4c316..0db18b090bc 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -419,11 +419,11 @@ a
(SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;
Wrong usage/placement of 'SQL_CALC_FOUND_ROWS'
create temporary table t1 select a from t1 union select a from t2;
+drop table t1;
create table t1 select a from t1 union select a from t2;
INSERT TABLE 't1' isn't allowed in FROM table list
select a from t1 union select a from t2 order by t2.a;
Unknown column 't2.a' in 'ORDER BY'
-drop table t1;
drop table t1,t2;
select length(version()) > 1 as `*` UNION select 2;
*
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 11aff8fe50a..c9405d71237 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -166,3 +166,40 @@ F1 F2 F3 cnt groupid
2 0 1 2 4
2 2 0 1 7
drop table t1;
+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;
+colC colA colD colE colF
+3 4433 10005 495 500
+3 4433 10005 496 500
+3 4433 10009 495 0
+3 4433 10011 495 0
+3 4433 10005 498 0
+3 4433 10013 490 0
+3 4433 10005 494 500
+3 4433 10005 493 500
+3 4433 10005 492 500
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index 3a0f2658969..d6c12683d94 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -72,3 +72,17 @@ select * from t1 where word like 'AE';
select * from t1 where word like 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;
diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test
index 5e605dbe97b..f78cb5ade55 100644
--- a/mysql-test/t/func_if.test
+++ b/mysql-test/t/func_if.test
@@ -47,3 +47,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 9b0c076f23e..ba6a8b55236 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -172,3 +172,10 @@ create table t1(a char(4));
insert into t1 values ('one'),(NULL),('two'),('four');
select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') from t1;
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');
diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test
index 0295fca29e7..e20f8163751 100644
--- a/mysql-test/t/lock_multi.test
+++ b/mysql-test/t/lock_multi.test
@@ -51,6 +51,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 39ea136bde1..3494126f890 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -151,8 +151,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/rpl_set_charset.test b/mysql-test/t/rpl_set_charset.test
new file mode 100644
index 00000000000..bd68ce17bbc
--- /dev/null
+++ b/mysql-test/t/rpl_set_charset.test
@@ -0,0 +1,40 @@
+source include/master-slave.inc;
+--disable_warnings
+drop database if exists mysqltest1;
+# 4.1 bases its conversion on the db's charset,
+# while 4.0 uses the part of "SET CHARACTER SET" after "_".
+# So for 4.1 we add a clause to CREATE DATABASE.
+create database mysqltest1 /*!40100 character set latin2 */;
+use mysqltest1;
+drop table if exists t1;
+--enable_warnings
+create table t1 (a varchar(255) character set latin2, b varchar(4));
+SET CHARACTER SET cp1250_latin2;
+INSERT INTO t1 VALUES ('','80');
+INSERT INTO t1 VALUES ('','90');
+INSERT INTO t1 VALUES ('','A0');
+INSERT INTO t1 VALUES ('','B0');
+INSERT INTO t1 VALUES ('','C0');
+INSERT INTO t1 VALUES ('','D0');
+INSERT INTO t1 VALUES ('','E0');
+INSERT INTO t1 VALUES ('','F0');
+select "--- on master ---";
+select hex(a),b from t1 order by b;
+# It's complicated to verify that the charset is reset to default in
+# the binlog after each query, except by checking the binlog. When you
+# merge this into 4.1/5.0, the 1979 will have to be changed; all you have
+# to do is read the var/log/master-bin.0*01 with mysqlbinlog, verify
+# that a SET CHARACTER SET DEFAULT is just after the last INSERT, and
+# replace 1979 by its position (the "# at" line above the SET).
+show binlog events from 1979;
+save_master_pos;
+connection slave;
+sync_with_master;
+use mysqltest1;
+select "--- on slave ---";
+select hex(a),b from t1 order by b;
+connection master;
+drop database mysqltest1;
+save_master_pos;
+connection slave;
+sync_with_master;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 7cb157f194e..03490360b35 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1874,3 +1874,24 @@ select * from t3 where s = 'one';
select * from t1,t2 where t1.s = t2.s;
select * from t2,t3 where t2.s = t3.s;
drop table t1, t2, t3;
+
+#
+# Covering index is mentioned in EXPLAIN output for const tables (bug #5333)
+#
+
+CREATE TABLE t1 (
+ i int(11) NOT NULL default '0',
+ c char(10) NOT NULL default '',
+ PRIMARY KEY (i),
+ UNIQUE KEY c (c)
+) TYPE=MyISAM;
+
+INSERT INTO t1 VALUES (1,'a');
+INSERT INTO t1 VALUES (2,'b');
+INSERT INTO t1 VALUES (3,'c');
+
+EXPLAIN SELECT i FROM t1 WHERE i=1;
+
+EXPLAIN SELECT i FROM t1 WHERE i=1;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 92bd20e846e..464ee63c137 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -71,6 +71,7 @@ select * from t1;
set new=1;
select * from t1;
drop table t1;
+set new=0;
#
# Bug #1885, bug #2539.
@@ -116,3 +117,34 @@ 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;
+
+#
+# More --new mode tests
+# Both columns created before and during alter should have same length.
+#
+create table t1 (ts1 timestamp);
+show create table t1;
+alter table t1 add ts2 timestamp;
+set new=0;
+show create table t1;
+drop table t1;
+# Selecting from table in --new mode should not affect further selects.
+create table t1 (ts1 timestamp);
+insert into t1 values ('2004-01-01 00:00:00'), ('2004-01-01 01:00:00');
+select * from t1;
+set new=1;
+select * from t1;
+set new=0;
+select * from t1;
+drop table t1;
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 65b062d663f..eb2d8dd6efa 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -224,11 +224,11 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
(SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;
create temporary table t1 select a from t1 union select a from t2;
+drop table t1;
--error 1093
create table t1 select a from t1 union select a from t2;
--error 1054
select a from t1 union select a from t2 order by t2.a;
-drop table t1; # Drop temporary table
drop table t1,t2;
#
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 2e739dd927d..1850564418c 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -122,3 +122,36 @@ insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
delete from t1 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;
+
View Lorry Controller Status