From cda336b9289be4368998986eaed2c7439e59a82e Mon Sep 17 00:00:00 2001
From: "bar@mysql.com" <>
Date: Thu, 9 Sep 2004 12:23:10 +0500
Subject: Bug #5447 Select does not find records Note, there is no need to
 propagate this change into 4.1.

---
 mysql-test/t/ctype_latin1_de.test | 14 ++++++++++++++
 1 file changed, 14 insertions(+)

(limited to 'mysql-test/t')

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;
-- 
cgit v1.2.1


From 2488b827de39fd3dbad5aa6b6fa04e6fd07981f1 Mon Sep 17 00:00:00 2001
From: "ram@gw.mysql.r18.ru" <>
Date: Mon, 13 Sep 2004 14:25:43 +0500
Subject: A fix (bug #5498 TRIM fails with LEADING or TRAILING if remstr =
 str).

---
 mysql-test/t/func_str.test | 7 +++++++
 1 file changed, 7 insertions(+)

(limited to 'mysql-test/t')

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');
-- 
cgit v1.2.1


From 0b6dc4938838c32c51b4c3633551711110229dc4 Mon Sep 17 00:00:00 2001
From: "antony@ltantony.rdg.cyberkinetica.homeunix.net" <>
Date: Fri, 17 Sep 2004 12:07:59 +0100
Subject: Bug#5553 - Multi table UPDATE IGNORE fails on dup key   We don't want
 the update to abort when IGNORE is specified

---
 mysql-test/t/update.test | 33 +++++++++++++++++++++++++++++++++
 1 file changed, 33 insertions(+)

(limited to 'mysql-test/t')

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;
+
-- 
cgit v1.2.1


From 276622c92d6ff6c43383019b2eeb94c84018cd3a Mon Sep 17 00:00:00 2001
From: "gluh@gluh.mysql.r18.ru" <>
Date: Sat, 18 Sep 2004 13:06:44 +0400
Subject: Fix for bug #5595: NULLIF() IS NULL returns false if NULLIF() returns
 NULL

---
 mysql-test/t/func_if.test | 4 ++++
 1 file changed, 4 insertions(+)

(limited to 'mysql-test/t')

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;
-- 
cgit v1.2.1


From e47decfa7f2e984558d3dc48d19dd94d8ec0b956 Mon Sep 17 00:00:00 2001
From: "ingo@mysql.com" <>
Date: Fri, 24 Sep 2004 18:39:25 +0200
Subject: BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S). BUG#4335 -
 one name can be handler open'ed many times. Reworked the HANDLER functions
 and interface. Using a HASH to store information on open tables that survives
 FLUSH TABLE. HANDLER tables alias names must now be unique, though it is
 allowed in 4.0 to qualify them with the database name of the base table.

---
 mysql-test/t/flush_table.test |  84 +++++------------
 mysql-test/t/handler.test     | 204 ++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 227 insertions(+), 61 deletions(-)

(limited to 'mysql-test/t')

diff --git a/mysql-test/t/flush_table.test b/mysql-test/t/flush_table.test
index ad81f266afc..58c12bad3fa 100644
--- a/mysql-test/t/flush_table.test
+++ b/mysql-test/t/flush_table.test
@@ -12,63 +12,10 @@ flush table t1;
 check table t1;
 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 ('');
-handler t1 open;
-handler t1 read first limit 9;
-#
-# Check accesibility of all the tables.
-#
-use test;
-handler test.t1 read first limit 9;
---error 1109;
-handler test.t2 read first limit 9;
-handler test_test.t1 read first limit 9;
-handler test_test.t2 read first limit 9;
-#
-# Cleanup.
-#
-handler test_test.t1 close;
-drop table test_test.t1;
-handler test_test.t2 close;
-drop table test_test.t2;
-drop database test_test;
-#
-use test;
-handler test.t1 close;
-drop table test.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).
 #
 --disable_warnings
 drop table if exists t1;
@@ -87,28 +34,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/handler.test b/mysql-test/t/handler.test
index 1f7f32c930a..53fe8c0a059 100644
--- a/mysql-test/t/handler.test
+++ b/mysql-test/t/handler.test
@@ -135,3 +135,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;
-- 
cgit v1.2.1


From 263b33e12dd1776dce3ea62b544bab52e2307d4d Mon Sep 17 00:00:00 2001
From: "dlenev@mysql.com" <>
Date: Mon, 27 Sep 2004 00:50:00 +0400
Subject: Fix for bug #4131 "TIMESTAMP columns missing minutes and seconds when
 using GROUP BY" Now we are setting Field_timestamp::field_length to 19 in
 open_table() if we are in new mode (and we are restoring it back when we are
 coming back to normal mode). This also should solve potential problems with
 some of LOAD DATA INFILE and SELECT * INTO in this mode.

---
 mysql-test/t/type_timestamp.test | 32 ++++++++++++++++++++++++++++++++
 1 file changed, 32 insertions(+)

(limited to 'mysql-test/t')

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;
-- 
cgit v1.2.1


From 4ec50dc4d7b216489eda1da5d0053dad30fd738f Mon Sep 17 00:00:00 2001
From: "ram@gw.mysql.r18.ru" <>
Date: Fri, 1 Oct 2004 16:23:54 +0500
Subject: delete.result, delete.test:   A fix (bug #5733: Table handler error
 with self-join multi-table DELETE). records.cc:   A fix (bug #5733: Table
 handler error with self-join multi-table DELETE).

---
 mysql-test/t/delete.test | 10 ++++++++++
 1 file changed, 10 insertions(+)

(limited to 'mysql-test/t')

diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test
index 904d959d148..07cb9155b3f 100644
--- a/mysql-test/t/delete.test
+++ b/mysql-test/t/delete.test
@@ -61,3 +61,13 @@ 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;
-- 
cgit v1.2.1


From 2973a047eab453bae86705c33e901a947a0e6db7 Mon Sep 17 00:00:00 2001
From: "antony@ltantony.rdg.cyberkinetica.homeunix.net" <>
Date: Sun, 3 Oct 2004 00:20:47 +0100
Subject: Bug#4118: multi-table UPDATE takes WRITE lock on read table   Ensures
 that WRITE lock is not obtained on all tables referenced.

---
 mysql-test/t/lock_multi.test   | 24 ++++++++++++++++++++++++
 mysql-test/t/multi_update.test |  2 --
 2 files changed, 24 insertions(+), 2 deletions(-)

(limited to 'mysql-test/t')

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
@@ -50,6 +50,30 @@ connection reader;
 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;
-- 
cgit v1.2.1