From 5b2a810e5d857856971dcd0e9f88ec1a397486d2 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 27 Feb 2003 22:26:09 +0200 Subject: fixed SQL_SELECT option with UNIONs mysql-test/r/query_cache.result: test of SQL_CACHE with UNIONs mysql-test/t/query_cache.test: test of SQL_CACHE with UNIONs --- mysql-test/r/query_cache.result | 11 ++++++++--- mysql-test/t/query_cache.test | 5 +++-- 2 files changed, 11 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 41fccc2743c..74dee666e05 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -237,13 +237,18 @@ a show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 -select sql_cache * from t1; +select sql_cache * from t1 union select * from t1; a 1 2 3 set query_cache_type=2; -select sql_cache * from t1; +select sql_cache * from t1 union select * from t1; +a +1 +2 +3 +select * from t1 union select sql_cache * from t1; a 1 2 @@ -253,7 +258,7 @@ Variable_name Value Qcache_hits 4 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 1 +Qcache_queries_in_cache 2 set query_cache_type=on; reset query cache; show status like "Qcache_queries_in_cache"; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index dd092866e44..83c11978436 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -144,9 +144,10 @@ create table t1 (a int not null); insert into t1 values (1),(2),(3); select * from t1; show status like "Qcache_queries_in_cache"; -select sql_cache * from t1; +select sql_cache * from t1 union select * from t1; set query_cache_type=2; -select sql_cache * from t1; +select sql_cache * from t1 union select * from t1; +select * from t1 union select sql_cache * from t1; show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; set query_cache_type=on; -- cgit v1.2.1 From 2d87079675b28f2b29a30b82c9243169987b226e Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 20 Mar 2003 19:04:30 +0200 Subject: A fix for a bug when INSERT is attempted into a table with a BLOB and many NULL columns. It could be ported back to 3.23 too .... --- mysql-test/r/myisam.result | 4 ++-- mysql-test/t/myisam.test | 4 ++-- 2 files changed, 4 insertions(+), 4 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 3a3558eedcb..9a653aff99e 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -246,7 +246,7 @@ int, i967 int, i968 int, i969 int, i970 int, i971 int, i972 int, i973 int, i974 int, i975 int, i976 int, i977 int, i978 int, i979 int, i980 int, i981 int, i982 int, i983 int, i984 int, i985 int, i986 int, i987 int, i988 int, i989 int, i990 int, i991 int, i992 int, i993 int, i994 int, i995 int, i996 int, i997 int, i998 -int, i999 int, i1000 int) row_format=dynamic; +int, i999 int, i1000 int, b blob) row_format=dynamic; insert into t1 values (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, @@ -285,7 +285,7 @@ insert into t1 values (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1); +1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, "Sergei"); drop table if exists t1; CREATE TABLE `t1` ( `post_id` mediumint(8) unsigned NOT NULL auto_increment, diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index c96a21e73dd..2ae3c27f699 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -252,7 +252,7 @@ int, i967 int, i968 int, i969 int, i970 int, i971 int, i972 int, i973 int, i974 int, i975 int, i976 int, i977 int, i978 int, i979 int, i980 int, i981 int, i982 int, i983 int, i984 int, i985 int, i986 int, i987 int, i988 int, i989 int, i990 int, i991 int, i992 int, i993 int, i994 int, i995 int, i996 int, i997 int, i998 -int, i999 int, i1000 int) row_format=dynamic; +int, i999 int, i1000 int, b blob) row_format=dynamic; insert into t1 values (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, @@ -291,7 +291,7 @@ insert into t1 values (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1); +1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, "Sergei"); drop table if exists t1; # -- cgit v1.2.1 From fc7ab8d1b176740ca167f17fec9dd12998723d36 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 20 Mar 2003 19:13:45 +0200 Subject: A test case for bug in record #166 : select @@not_a_variable closes the connection --- mysql-test/r/variables.result | 5 +++++ mysql-test/t/variables.test | 7 +++++++ 2 files changed, 12 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index ed477a8519b..1cc48d2aeac 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -335,3 +335,8 @@ Table Op Msg_type Msg_text test.t1 check status OK test.t2 check status OK drop table t1,t2; +select @@xxxxxxxxxx; +Unknown system variable 'xxxxxxxxxx' +select 1; +1 +1 diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 5863cb97d57..639a28edd38 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -241,3 +241,10 @@ select * from t2 where a=3; check table t1,t2; drop table t1,t2; +# +# error conditions +# + +--error 1193 +select @@xxxxxxxxxx; +select 1; -- cgit v1.2.1 From d99370dd1b2d5e85c36ed45a9f7758c3d2ebb35c Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 24 Mar 2003 22:52:46 +0200 Subject: A fix for bug #176 code cleanup mysql-test/r/having.result: A test for the bug fix for bug #176 mysql-test/t/having.test: A test for the bug fix for bug #176 sql/item.cc: A for the bug #176 sql/item_func.cc: Code cleanup --- mysql-test/r/having.result | 7 +++++++ mysql-test/t/having.test | 4 ++++ 2 files changed, 11 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index a33ce457176..f113eb6ed49 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -63,3 +63,10 @@ Fld1 max(Fld2) 1 20 3 50 drop table t1; +create table t1 (id int not null, qty int not null); +insert into t1 values (1,2),(1,3),(2,4),(2,5); +select id, sum(qty) as sqty from t1 group by id having sqty>2; +id sqty +1 5 +2 9 +drop table t1; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 8dd7606d82b..fd972fea1ad 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -60,3 +60,7 @@ select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null; select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null; select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null; drop table t1; +create table t1 (id int not null, qty int not null); +insert into t1 values (1,2),(1,3),(2,4),(2,5); +select id, sum(qty) as sqty from t1 group by id having sqty>2; +drop table t1; -- cgit v1.2.1 From a424d01173d0b6d446f752a220c827d28358831a Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 31 Mar 2003 09:49:09 +0300 Subject: fixed bug 209 (SQL_SELECT_LIMIT and query cache incompatibility) mysql-test/r/query_cache.result: test of SET OPTION SQL_SELECT_LIMIT mysql-test/t/query_cache.test: test of SET OPTION SQL_SELECT_LIMIT sql/sql_cache.cc: layout fixed SQL_SELECT_LIMIT stored in query cache now sql/sql_parse.cc: room for SQL_SELECT_LAYOUT storing added --- mysql-test/r/query_cache.result | 18 ++++++++++++++++++ mysql-test/t/query_cache.test | 17 ++++++++++++++--- 2 files changed, 32 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 936bbf30006..a55e05578e0 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -581,3 +581,21 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1; +create table t1 (a int); +insert into t1 values (1),(2); +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +select * from t1; +a +1 +2 +SET OPTION SQL_SELECT_LIMIT=1; +select * from t1; +a +1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +SET OPTION SQL_SELECT_LIMIT=DEFAULT; +drop table t1; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 23950ce9caa..33f226f9253 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -401,7 +401,6 @@ select * from t1 where id=2; # # Load data invalidation test # - create table t1 (word char(20) not null); select * from t1; show status like "Qcache_queries_in_cache"; @@ -412,7 +411,6 @@ drop table t1; # # INTO OUTFILE/DUMPFILE test # - drop table if exists t1; create table t1 (a int); insert into t1 values (1),(2),(3); @@ -420,4 +418,17 @@ show status like "Qcache_queries_in_cache"; select * from t1 into outfile "query_caceh.out.file"; select * from t1 limit 1 into dumpfile "query_cache.dump.file"; show status like "Qcache_queries_in_cache"; -drop table t1; \ No newline at end of file +drop table t1; + +# +# test of SQL_SELECT_LIMIT +# +create table t1 (a int); +insert into t1 values (1),(2); +show status like "Qcache_queries_in_cache"; +select * from t1; +SET OPTION SQL_SELECT_LIMIT=1; +select * from t1; +show status like "Qcache_queries_in_cache"; +SET OPTION SQL_SELECT_LIMIT=DEFAULT; +drop table t1; -- cgit v1.2.1 From bd4d87297d03706b49eab04d030b2e63a4bc9997 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Apr 2003 17:05:34 +0300 Subject: A fix for a bug in multi-table updates which was always caused whenever a temporary MyISAM table had to be created from HEAP table. --- mysql-test/r/multi_update.result | 8 ++++++++ mysql-test/t/multi_update-master.opt | 1 + mysql-test/t/multi_update.test | 8 ++++++++ 3 files changed, 17 insertions(+) create mode 100644 mysql-test/t/multi_update-master.opt (limited to 'mysql-test') diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 0f3644f15bc..75c22caaa72 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -310,3 +310,11 @@ a b 8 3 9 3 drop table t1,t2; +CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) TYPE=MyISAM; +INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL); +create table t1 (A varchar(1)); +insert into t1 values ("A") ,("B"),("C"),("D"); +create table t2(Z varchar(15)); +insert into t2(Z) select concat(a.a,b.a,c.a,d.a) from t1 as a, t1 as b, t1 as c, t1 as d; +update t2,t3 set Z =param_scenario_costs; +drop table t1,t2,t3; diff --git a/mysql-test/t/multi_update-master.opt b/mysql-test/t/multi_update-master.opt new file mode 100644 index 00000000000..9f1a29461ff --- /dev/null +++ b/mysql-test/t/multi_update-master.opt @@ -0,0 +1 @@ +--set-variable=tmp_table_size=1024 diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index d5543f69bd5..04f3508e078 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -252,3 +252,11 @@ select * from t1; select * from t2; drop table t1,t2; +CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) TYPE=MyISAM; +INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL); +create table t1 (A varchar(1)); +insert into t1 values ("A") ,("B"),("C"),("D"); +create table t2(Z varchar(15)); +insert into t2(Z) select concat(a.a,b.a,c.a,d.a) from t1 as a, t1 as b, t1 as c, t1 as d; +update t2,t3 set Z =param_scenario_costs; +drop table t1,t2,t3; -- cgit v1.2.1 From 89bb3165501b09b2922d361905a4efb7b490d8b0 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Apr 2003 20:24:15 +0300 Subject: One test case, one bug fix and one new feature mysql-test/r/innodb.result: A test case for non-functional rollback after inserting a row into MyISAM table with binary log enabled. mysql-test/t/innodb.test: A test case for non-functional rollback after inserting a row into MyISAM table with binary log enabled. sql/sql_show.cc: Displaying disabled keys in SHOW KEYS sql/sql_table.cc: Fix for a serious bug with ALTER TABLE ENABLE / DISABLE KEYS --- mysql-test/r/innodb.result | 9 +++++++++ mysql-test/t/innodb.test | 8 ++++++++ 2 files changed, 17 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 48db5412da7..b12466983f8 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1201,3 +1201,12 @@ a b 8 5 9 5 drop table t1,t2; +CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) TYPE=MyISAM; +CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) TYPE=InnoDB; +SET AUTOCOMMIT=0; +INSERT INTO t1 ( B_ID ) VALUES ( 1 ); +INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); +ROLLBACK; +SELECT * FROM t1; +B_ID +drop table t1,t2; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 2f7285e7898..9d614e8d163 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -797,3 +797,11 @@ select * from t1; select * from t2; drop table t1,t2; +CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) TYPE=MyISAM; +CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) TYPE=InnoDB; +SET AUTOCOMMIT=0; +INSERT INTO t1 ( B_ID ) VALUES ( 1 ); +INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); +ROLLBACK; +SELECT * FROM t1; +drop table t1,t2; -- cgit v1.2.1 From 00d9b06a7a6a659762e5fd4a6b8e32bbf3b639d0 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Apr 2003 21:17:53 +0300 Subject: test case for displaying and correct functioning of ALTER TABLE ENABLE / DISABLE KEYS --- mysql-test/r/alter_table.result | 38 ++++++++++++++++++++++++++++++++++++++ mysql-test/t/alter_table.test | 2 ++ 2 files changed, 40 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 096ef2fd79e..670ed172c76 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -92,6 +92,25 @@ key (n2, n3, n4, n1), key (n3, n4, n1, n2), key (n4, n1, n2, n3) ); alter table t1 disable keys; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 n1 1 n1 A 0 NULL NULL BTREE +t1 1 n1_2 1 n1 A NULL NULL NULL BTREE disabled +t1 1 n1_2 2 n2 A NULL NULL NULL YES BTREE disabled +t1 1 n1_2 3 n3 A NULL NULL NULL YES BTREE disabled +t1 1 n1_2 4 n4 A NULL NULL NULL YES BTREE disabled +t1 1 n2 1 n2 A NULL NULL NULL YES BTREE disabled +t1 1 n2 2 n3 A NULL NULL NULL YES BTREE disabled +t1 1 n2 3 n4 A NULL NULL NULL YES BTREE disabled +t1 1 n2 4 n1 A NULL NULL NULL BTREE disabled +t1 1 n3 1 n3 A NULL NULL NULL YES BTREE disabled +t1 1 n3 2 n4 A NULL NULL NULL YES BTREE disabled +t1 1 n3 3 n1 A NULL NULL NULL BTREE disabled +t1 1 n3 4 n2 A NULL NULL NULL YES BTREE disabled +t1 1 n4 1 n4 A NULL NULL NULL YES BTREE disabled +t1 1 n4 2 n1 A NULL NULL NULL BTREE disabled +t1 1 n4 3 n2 A NULL NULL NULL YES BTREE disabled +t1 1 n4 4 n3 A NULL NULL NULL YES BTREE disabled insert into t1 values(10,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(9,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(8,RAND()*1000,RAND()*1000,RAND()); @@ -103,6 +122,25 @@ insert into t1 values(3,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(2,RAND()*1000,RAND()*1000,RAND()); insert into t1 values(1,RAND()*1000,RAND()*1000,RAND()); alter table t1 enable keys; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 n1 1 n1 A 10 NULL NULL BTREE +t1 1 n1_2 1 n1 A 10 NULL NULL BTREE +t1 1 n1_2 2 n2 A 10 NULL NULL YES BTREE +t1 1 n1_2 3 n3 A 10 NULL NULL YES BTREE +t1 1 n1_2 4 n4 A 10 NULL NULL YES BTREE +t1 1 n2 1 n2 A 10 NULL NULL YES BTREE +t1 1 n2 2 n3 A 10 NULL NULL YES BTREE +t1 1 n2 3 n4 A 10 NULL NULL YES BTREE +t1 1 n2 4 n1 A 10 NULL NULL BTREE +t1 1 n3 1 n3 A 10 NULL NULL YES BTREE +t1 1 n3 2 n4 A 10 NULL NULL YES BTREE +t1 1 n3 3 n1 A 10 NULL NULL BTREE +t1 1 n3 4 n2 A 10 NULL NULL YES BTREE +t1 1 n4 1 n4 A 10 NULL NULL YES BTREE +t1 1 n4 2 n1 A 10 NULL NULL BTREE +t1 1 n4 3 n2 A 10 NULL NULL YES BTREE +t1 1 n4 4 n3 A 10 NULL NULL YES BTREE drop table t1; create table t1 (i int unsigned not null auto_increment primary key); insert into t1 values (null),(null),(null),(null); diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 1c3987e2a31..d32c554aec8 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -86,6 +86,7 @@ create table t1 (n1 int not null, n2 int, n3 int, n4 float, key (n3, n4, n1, n2), key (n4, n1, n2, n3) ); alter table t1 disable keys; +show keys from t1; #let $1=10000; let $1=10; while ($1) @@ -94,6 +95,7 @@ while ($1) dec $1; } alter table t1 enable keys; +show keys from t1; drop table t1; # -- cgit v1.2.1 From da68e3d69ff23aa89322bc4460a214aa57798c5b Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 7 Apr 2003 21:36:45 +0300 Subject: Changed innobase_flush_log_at_trx_commit to be 1 as default Fixed problem with not freed thr_alarm() on slave connect mysql-test/t/rpl_alter.test: Changed name of created test database sql/ha_innobase.cc: Changed innobase_flush_log_at_trx_commit to be 1 as default sql/mini_client.cc: Fixed problem with not freed thr_alarm() sql/mysqld.cc: Allocate some extra thr_alarm slots just to be safe sql/slave.cc: Simple code cleanup --- mysql-test/t/rpl_alter.test | 22 +++++++++++----------- 1 file changed, 11 insertions(+), 11 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/t/rpl_alter.test b/mysql-test/t/rpl_alter.test index a65605f703e..61ac55843cf 100644 --- a/mysql-test/t/rpl_alter.test +++ b/mysql-test/t/rpl_alter.test @@ -1,21 +1,21 @@ source include/master-slave.inc; connection master; use test; -drop database if exists d1; -create database d1; -create table d1.t1 ( n int); -alter table d1.t1 add m int; -insert into d1.t1 values (1,2); -create table d1.t2 (n int); -insert into d1.t2 values (45); -rename table d1.t2 to d1.t3, d1.t1 to d1.t2; +drop database if exists test_$1; +create database test_$1; +create table test_$1.t1 ( n int); +alter table test_$1.t1 add m int; +insert into test_$1.t1 values (1,2); +create table test_$1.t2 (n int); +insert into test_$1.t2 values (45); +rename table test_$1.t2 to test_$1.t3, test_$1.t1 to test_$1.t2; save_master_pos; connection slave; sync_with_master; -select * from d1.t2; -select * from d1.t3; +select * from test_$1.t2; +select * from test_$1.t3; connection master; -drop database d1; +drop database test_$1; save_master_pos; connection slave; sync_with_master; -- cgit v1.2.1 From 3e335144be1af3f7c70a0c385b4e88da2c1e5e94 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 11 Apr 2003 20:09:24 +0300 Subject: Two bug fixes mysql-test/r/innodb.result: Fix for a bug that caused queries with ORDER BY field_in_select_list to be returned entirely unsorted. mysql-test/t/innodb.test: Fix for a bug that caused queries with ORDER BY field_in_select_list to be returned entirely unsorted. sql/sql_select.cc: Fix for a bug that caused queries with ORDER BY field_in_select_list to be returned entirely unsorted. sql/sql_update.cc: Fix for a glitch that caused too many tables to be locked. --- mysql-test/r/innodb.result | 10 ++++++++++ mysql-test/t/innodb.test | 5 +++++ 2 files changed, 15 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index b12466983f8..96bc66222d3 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1207,6 +1207,16 @@ SET AUTOCOMMIT=0; INSERT INTO t1 ( B_ID ) VALUES ( 1 ); INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); ROLLBACK; +Warning: Some non-transactional changed tables couldn't be rolled back SELECT * FROM t1; B_ID drop table t1,t2; +create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) type = innodb; +insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); +select distinct parent,child from t1 order by parent; +parent child +0 4 +1 2 +1 3 +2 1 +drop table t1; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 9d614e8d163..b8929d5c4de 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -802,6 +802,11 @@ CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) TYPE=InnoDB; SET AUTOCOMMIT=0; INSERT INTO t1 ( B_ID ) VALUES ( 1 ); INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); +-- error 1196 ROLLBACK; SELECT * FROM t1; drop table t1,t2; +create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) type = innodb; +insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); +select distinct parent,child from t1 order by parent; +drop table t1; -- cgit v1.2.1 From 24aba046bf287388622e2fdc34bb66a6cca40876 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 14 Apr 2003 21:46:24 +0300 Subject: Fix for a NAN problem in FORMAT(...) string function .. --- mysql-test/r/func_misc.result | 3 +++ mysql-test/t/func_misc.test | 1 + 2 files changed, 4 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 4eed80c4cc9..67f43ca5ba4 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -10,3 +10,6 @@ inet_aton("255.255.255.255.255") inet_aton("255.255.1.255") inet_aton("0.1.255") select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); inet_ntoa(1099511627775) inet_ntoa(4294902271) inet_ntoa(511) NULL 255.255.1.255 0.0.1.255 +select format("nan",2); +format("nan",2) +nan diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index d48b17e87af..d9ae288cc59 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -7,3 +7,4 @@ select format(1.5555,0),format(123.5555,1),format(1234.5555,2),format(12345.5555 select inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")); select inet_aton("255.255.255.255.255"),inet_aton("255.255.1.255"),inet_aton("0.1.255"); select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); +select format("nan",2); -- cgit v1.2.1 From 3ac05ea4812531bd213de6b35508d7da0a679378 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 15 Apr 2003 22:04:16 +0300 Subject: Fixed bug in SUM() with NULL:s Added logging of warnings for failed connections with ssl. Fixed bug in symbolic link handling on Windows include/my_global.h: Added replication flags from 4.1 mysql-test/r/func_group.result: Added test for SUM(NULL) mysql-test/t/func_group.test: Added test for SUM(NULL) sql/item_sum.cc: Fixed bug in SUM() with NULL:s sql/item_sum.h: Fixed bug in SUM() with NULL:s sql/mysqld.cc: Added option --symbolic-links Fixed bug in symbolic link handling on Windows sql/net_serv.cc: Code cleanup sql/sql_acl.cc: Added logging of warnings for failed connections with ssl. sql/sql_show.cc: Changed to use HAVE_REPLICATION instead of EMBEDDED_LIBRARY --- mysql-test/r/func_group.result | 43 +++++++++++++++++++++++++++++++++++++++++- mysql-test/t/func_group.test | 21 +++++++++++++++++++++ 2 files changed, 63 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index b129beaac81..80ccb6c7bb8 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -47,7 +47,7 @@ sum(all a) count(all a) avg(all a) std(all a) bit_or(all a) bit_and(all a) min(a 21 6 3.5000 1.7078 7 0 1 6 E select grp, sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; grp sum(a) count(a) avg(a) std(a) bit_or(a) bit_and(a) min(a) max(a) min(c) max(c) -NULL 0 0 NULL NULL 0 0 NULL NULL +NULL NULL 0 NULL NULL 0 0 NULL NULL 1 1 1 1.0000 0.0000 1 1 1 1 a a 2 5 2 2.5000 0.5000 3 2 2 3 b c 3 15 3 5.0000 0.8165 7 4 4 6 C E @@ -204,3 +204,44 @@ select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1= max(t2.a1) NULL drop table t1,t2; +CREATE TABLE t1 (a int, b int); +select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; +count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +0 NULL NULL NULL NULL NULL -1 0 +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +insert into t1 values (1,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL -1 0 +insert into t1 values (1,null); +insert into t1 values (2,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 0 0 +2 0 NULL NULL NULL NULL NULL 0 0 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL -1 0 +2 0 NULL NULL NULL NULL NULL -1 0 +insert into t1 values (2,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 0 0 +2 1 1 1.0000 0.0000 1 1 0 1 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL -1 0 +2 1 1 1.0000 0.0000 1 1 1 1 +insert into t1 values (3,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 0 0 +2 1 1 1.0000 0.0000 1 1 0 1 +3 1 1 1.0000 0.0000 1 1 1 1 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL -1 0 +2 1 1 1.0000 0.0000 1 1 1 1 +3 1 1 1.0000 0.0000 1 1 1 1 +drop table t1; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 1915c2172ad..40d829d3e70 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -126,3 +126,24 @@ select max(t1.a2) from t1 left outer join t2 on t1.a1=10 where t1.a1=20; select max(t1.a2) from t1 left outer join t2 on t1.a1=10 where t1.a1=10; select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA'; drop table t1,t2; + +# +# Test of group function and NULL values +# + +CREATE TABLE t1 (a int, b int); +select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (1,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (1,null); +insert into t1 values (2,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (2,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (3,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +drop table t1; -- cgit v1.2.1 From a3274c2222bc09a055cdcac8d5606d11da37ed8e Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 16 Apr 2003 13:39:39 +0300 Subject: fix for a bug with two natural joins. --- mysql-test/r/join_outer.result | 12 ++++++++++++ mysql-test/t/join_outer.test | 9 +++++++++ 2 files changed, 21 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 6e69616b7c6..e68f3e6b006 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -652,3 +652,15 @@ fooID barID fooID 20 2 NULL 30 3 30 drop table t1,t2; +drop table if exists t3; +create table t1 (i int); +create table t2 (i int); +create table t3 (i int); +insert into t1 values(1),(2); +insert into t2 values(2),(3); +insert into t3 values(2),(4); +select * from t1 natural left join t2 natural left join t3; +i i i +1 NULL NULL +2 2 2 +drop table t1,t2,t3; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 71c3643b2f4..80fa6501758 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -416,3 +416,12 @@ explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30; drop table t1,t2; +drop table if exists t3; +create table t1 (i int); +create table t2 (i int); +create table t3 (i int); +insert into t1 values(1),(2); +insert into t2 values(2),(3); +insert into t3 values(2),(4); +select * from t1 natural left join t2 natural left join t3; +drop table t1,t2,t3; -- cgit v1.2.1 From dad32c931086985a007932bdabf48af91478b7cb Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 16 Apr 2003 21:10:45 +0300 Subject: Fix for a bug with InnoDB SQL handler, when previous query failed and an attempt is made to read previous / next value. --- mysql-test/r/innodb_handler.result | 10 +++++++++- mysql-test/t/innodb_handler.test | 8 +++++++- 2 files changed, 16 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_handler.result b/mysql-test/r/innodb_handler.result index 321aedabdc7..949756a6546 100644 --- a/mysql-test/r/innodb_handler.result +++ b/mysql-test/r/innodb_handler.result @@ -139,4 +139,12 @@ a b alter table t1 type=innodb; handler t2 read first; Unknown table 't2' in HANDLER -drop table if exists t1; +drop table t1; +CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)) TYPE=InnoDB; +INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); +HANDLER t1 OPEN; +HANDLER t1 READ `primary` = (1, 1000); +no1 no2 +HANDLER t1 READ `primary` PREV; +no1 no2 +DROP TABLE t1; diff --git a/mysql-test/t/innodb_handler.test b/mysql-test/t/innodb_handler.test index c6448eba3ef..6777e4f49bd 100644 --- a/mysql-test/t/innodb_handler.test +++ b/mysql-test/t/innodb_handler.test @@ -72,5 +72,11 @@ alter table t1 type=innodb; --error 1109 handler t2 read first; -drop table if exists t1; +drop table t1; +CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)) TYPE=InnoDB; +INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); +HANDLER t1 OPEN; +HANDLER t1 READ `primary` = (1, 1000); +HANDLER t1 READ `primary` PREV; +DROP TABLE t1; -- cgit v1.2.1 From 1b23ce2d358c1db696744a9ca0a6a611e20e5242 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 19 Apr 2003 23:18:35 +0300 Subject: Three bug fixes and one fix being undone. mysql-test/r/innodb_handler.result: change of test case after Heikki's fix sql/sql_db.cc: Adding suport for backquotes in change_db() sql/sql_update.cc: Undoing a fix strings/ctype-tis620.c: Fixing a crashing bug --- mysql-test/r/innodb_handler.result | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_handler.result b/mysql-test/r/innodb_handler.result index 949756a6546..8aa5309308f 100644 --- a/mysql-test/r/innodb_handler.result +++ b/mysql-test/r/innodb_handler.result @@ -147,4 +147,5 @@ HANDLER t1 READ `primary` = (1, 1000); no1 no2 HANDLER t1 READ `primary` PREV; no1 no2 +1 275 DROP TABLE t1; -- cgit v1.2.1 From ae89db4904de34cdfb9cdb3ecdc3217f6a32834f Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 21 Apr 2003 22:54:02 +0300 Subject: Fixing few small problems in SET PASSWORD: * comparison with correct cached variable * preventing check_change_password to be called twice, once from set_var::check and the other time from change_password itself mysql-test/r/select_safe.result: Fixing result for fixed error message --- mysql-test/r/select_safe.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index e225a0e3995..8f2b621ac8a 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -30,7 +30,7 @@ You are using safe update mode and you tried to update a table without a WHERE t delete from t1 where a+0=1; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; -The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok +The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET SQL_BIG_SELECTS=1 if the SELECT is ok update t1 set b="a" limit 1; update t1 set b="a" where b="b" limit 2; delete from t1 where b="test" limit 1; -- cgit v1.2.1 From b534154b12c6b592594588efb3dacc5dbf2e33e7 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 23 Apr 2003 00:53:07 +0400 Subject: do not mark the table as analyzed unless all the key parts are REALLY analyzed. --- mysql-test/r/alter_table.result | 116 ++++++++++++++++++++++++++++++++++++++++ mysql-test/t/alter_table.test | 16 ++++++ 2 files changed, 132 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 096ef2fd79e..a296e0a87ac 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -122,3 +122,119 @@ Field Type Null Key Default Extra i int(10) unsigned PRI NULL auto_increment c char(10) YES NULL drop table t1; +create table t1 (a int, b int); +insert into t1 values(1,100), (2,100), (3, 100); +insert into t1 values(1,99), (2,99), (3, 99); +insert into t1 values(1,98), (2,98), (3, 98); +insert into t1 values(1,97), (2,97), (3, 97); +insert into t1 values(1,96), (2,96), (3, 96); +insert into t1 values(1,95), (2,95), (3, 95); +insert into t1 values(1,94), (2,94), (3, 94); +insert into t1 values(1,93), (2,93), (3, 93); +insert into t1 values(1,92), (2,92), (3, 92); +insert into t1 values(1,91), (2,91), (3, 91); +insert into t1 values(1,90), (2,90), (3, 90); +insert into t1 values(1,89), (2,89), (3, 89); +insert into t1 values(1,88), (2,88), (3, 88); +insert into t1 values(1,87), (2,87), (3, 87); +insert into t1 values(1,86), (2,86), (3, 86); +insert into t1 values(1,85), (2,85), (3, 85); +insert into t1 values(1,84), (2,84), (3, 84); +insert into t1 values(1,83), (2,83), (3, 83); +insert into t1 values(1,82), (2,82), (3, 82); +insert into t1 values(1,81), (2,81), (3, 81); +insert into t1 values(1,80), (2,80), (3, 80); +insert into t1 values(1,79), (2,79), (3, 79); +insert into t1 values(1,78), (2,78), (3, 78); +insert into t1 values(1,77), (2,77), (3, 77); +insert into t1 values(1,76), (2,76), (3, 76); +insert into t1 values(1,75), (2,75), (3, 75); +insert into t1 values(1,74), (2,74), (3, 74); +insert into t1 values(1,73), (2,73), (3, 73); +insert into t1 values(1,72), (2,72), (3, 72); +insert into t1 values(1,71), (2,71), (3, 71); +insert into t1 values(1,70), (2,70), (3, 70); +insert into t1 values(1,69), (2,69), (3, 69); +insert into t1 values(1,68), (2,68), (3, 68); +insert into t1 values(1,67), (2,67), (3, 67); +insert into t1 values(1,66), (2,66), (3, 66); +insert into t1 values(1,65), (2,65), (3, 65); +insert into t1 values(1,64), (2,64), (3, 64); +insert into t1 values(1,63), (2,63), (3, 63); +insert into t1 values(1,62), (2,62), (3, 62); +insert into t1 values(1,61), (2,61), (3, 61); +insert into t1 values(1,60), (2,60), (3, 60); +insert into t1 values(1,59), (2,59), (3, 59); +insert into t1 values(1,58), (2,58), (3, 58); +insert into t1 values(1,57), (2,57), (3, 57); +insert into t1 values(1,56), (2,56), (3, 56); +insert into t1 values(1,55), (2,55), (3, 55); +insert into t1 values(1,54), (2,54), (3, 54); +insert into t1 values(1,53), (2,53), (3, 53); +insert into t1 values(1,52), (2,52), (3, 52); +insert into t1 values(1,51), (2,51), (3, 51); +insert into t1 values(1,50), (2,50), (3, 50); +insert into t1 values(1,49), (2,49), (3, 49); +insert into t1 values(1,48), (2,48), (3, 48); +insert into t1 values(1,47), (2,47), (3, 47); +insert into t1 values(1,46), (2,46), (3, 46); +insert into t1 values(1,45), (2,45), (3, 45); +insert into t1 values(1,44), (2,44), (3, 44); +insert into t1 values(1,43), (2,43), (3, 43); +insert into t1 values(1,42), (2,42), (3, 42); +insert into t1 values(1,41), (2,41), (3, 41); +insert into t1 values(1,40), (2,40), (3, 40); +insert into t1 values(1,39), (2,39), (3, 39); +insert into t1 values(1,38), (2,38), (3, 38); +insert into t1 values(1,37), (2,37), (3, 37); +insert into t1 values(1,36), (2,36), (3, 36); +insert into t1 values(1,35), (2,35), (3, 35); +insert into t1 values(1,34), (2,34), (3, 34); +insert into t1 values(1,33), (2,33), (3, 33); +insert into t1 values(1,32), (2,32), (3, 32); +insert into t1 values(1,31), (2,31), (3, 31); +insert into t1 values(1,30), (2,30), (3, 30); +insert into t1 values(1,29), (2,29), (3, 29); +insert into t1 values(1,28), (2,28), (3, 28); +insert into t1 values(1,27), (2,27), (3, 27); +insert into t1 values(1,26), (2,26), (3, 26); +insert into t1 values(1,25), (2,25), (3, 25); +insert into t1 values(1,24), (2,24), (3, 24); +insert into t1 values(1,23), (2,23), (3, 23); +insert into t1 values(1,22), (2,22), (3, 22); +insert into t1 values(1,21), (2,21), (3, 21); +insert into t1 values(1,20), (2,20), (3, 20); +insert into t1 values(1,19), (2,19), (3, 19); +insert into t1 values(1,18), (2,18), (3, 18); +insert into t1 values(1,17), (2,17), (3, 17); +insert into t1 values(1,16), (2,16), (3, 16); +insert into t1 values(1,15), (2,15), (3, 15); +insert into t1 values(1,14), (2,14), (3, 14); +insert into t1 values(1,13), (2,13), (3, 13); +insert into t1 values(1,12), (2,12), (3, 12); +insert into t1 values(1,11), (2,11), (3, 11); +insert into t1 values(1,10), (2,10), (3, 10); +insert into t1 values(1,9), (2,9), (3, 9); +insert into t1 values(1,8), (2,8), (3, 8); +insert into t1 values(1,7), (2,7), (3, 7); +insert into t1 values(1,6), (2,6), (3, 6); +insert into t1 values(1,5), (2,5), (3, 5); +insert into t1 values(1,4), (2,4), (3, 4); +insert into t1 values(1,3), (2,3), (3, 3); +insert into t1 values(1,2), (2,2), (3, 2); +insert into t1 values(1,1), (2,1), (3, 1); +alter table t1 add unique (a,b), add key (b); +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 0 a 2 b A NULL NULL NULL YES BTREE +t1 1 b 1 b A 100 NULL NULL YES BTREE +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 0 a 2 b A NULL NULL NULL YES BTREE +t1 1 b 1 b A 100 NULL NULL YES BTREE +drop table t1; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 1c3987e2a31..301a50bf07d 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -115,3 +115,19 @@ alter table t1 rename t2; alter table t2 rename t1, add c char(10) comment "no comment"; show columns from t1; drop table t1; + +# implicit analyze + +create table t1 (a int, b int); +let $1=100; +while ($1) +{ + eval insert into t1 values(1,$1), (2,$1), (3, $1); + dec $1; +} +alter table t1 add unique (a,b), add key (b); +show keys from t1; +analyze table t1; +show keys from t1; +drop table t1; + -- cgit v1.2.1 From 963d57a39446490ce2fb91c773ac2466c3723aa1 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 23 Apr 2003 00:13:37 +0300 Subject: Fixed problem with timestamps in binary log on 64 bit machines Backported fix from 4.1 for bug 212: SELECT query containing a NATURAL JOIN and parentheses in the WHERE clause mysql-test/r/join.result: New test results mysql-test/t/join.test: Test for bug 212 sql/log_event.cc: Removed wrong cast sql/log_event.h: Fixed problem with timestamps in binary log on 64 bit machines sql/sql_list.h: Fix for bug 212 (back ported from 4.1) --- mysql-test/r/join.result | 3 +++ mysql-test/t/join.test | 14 ++++++++++++++ 2 files changed, 17 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 0c575d7505e..713d7736585 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -41,3 +41,6 @@ rate_code base_rate cust 20 rate_code base_rate cust 20 +ID Value1 ID Value2 +ID Value1 ID Value2 +ID Value1 ID Value2 diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 6a022e690f2..5fe1834873f 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -243,3 +243,17 @@ 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; + +# +# Problem with internal list handling when reducing WHERE +# + +CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255)); +CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255)); +INSERT INTO t1 VALUES (1, 'A'); +INSERT INTO t2 VALUES (1, 'B'); + +SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B'); +SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B'; +SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1; +drop table t1,t2; -- cgit v1.2.1 From 29b593987d81b987a0feb1750afec23bd8cac9c7 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 23 Apr 2003 15:37:11 +0400 Subject: correct test result --- mysql-test/r/alter_table.result | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index a296e0a87ac..d24f8087bc1 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -231,10 +231,10 @@ t1 0 a 2 b A NULL NULL NULL YES BTREE t1 1 b 1 b A 100 NULL NULL YES BTREE analyze table t1; Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date +test.t1 analyze status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 0 a 2 b A NULL NULL NULL YES BTREE +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; -- cgit v1.2.1 From b21a0be804fbff458ffbfff4e01f28f8730d3820 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 23 Apr 2003 15:44:39 +0400 Subject: bug 283: FULLTEXT index on a TEXT filed converted to a CHAR field doesn't work anymore --- mysql-test/r/fulltext.result | 11 +++++++++++ mysql-test/t/fulltext.test | 13 +++++++++++++ 2 files changed, 24 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index edf109fcc93..eaaaf9c8880 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -215,3 +215,14 @@ test.t1 repair status OK select * from t1 where match (a) against ('aaaa'); a drop table t1; +drop table if exists t1; +create table t1 ( ref_mag text not null, fulltext (ref_mag)); +insert into t1 values ('test'); +select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); +ref_mag +test +alter table t1 change ref_mag ref_mag char (255) not null; +select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); +ref_mag +test +drop table t1; diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 5a64f2614aa..128af680854 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -173,3 +173,16 @@ repair table t1; select * from t1 where match (a) against ('aaaa'); drop table t1; +# +# bug 283 by jocelyn fournier +# FULLTEXT index on a TEXT filed converted to a CHAR field doesn't work anymore +# + +drop table if exists t1; +create table t1 ( ref_mag text not null, fulltext (ref_mag)); +insert into t1 values ('test'); +select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); +alter table t1 change ref_mag ref_mag char (255) not null; +select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); +drop table t1; + -- cgit v1.2.1 From 7032486889f42b3f18b4a0a5be6cb97b74790ea8 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 23 Apr 2003 21:52:16 +0300 Subject: Fixes for valgrind Added optimzation for clustered index Fixed bug in UPDATE ... ORDER BY Fixed handling of UPDATE ... LIMIT BitKeeper/deleted/.del-.cvsignore~7e29af89a3559f4c: Delete: Images/.cvsignore BitKeeper/deleted/.del-README~d5a4e7ca3a2e87a9: Delete: repl-tests/README BitKeeper/deleted/.del-run-all-tests~4deb6479a13e4568: Delete: repl-tests/run-all-tests BitKeeper/deleted/.del-run.test~3dc5b9bd1e9feea5: Delete: repl-tests/test-repl-alter/run.test BitKeeper/deleted/.del-run.test~4020771cff278f14: Delete: repl-tests/test-bad-query/run.test BitKeeper/deleted/.del-run.test~452f2b66537404a8: Delete: repl-tests/test-dump/run.test BitKeeper/deleted/.del-run.test~b1f0c1f96554df8: Delete: repl-tests/test-auto-inc/run.test BitKeeper/deleted/.del-table-dump-check.master~e13afeb8c79264b5: Delete: repl-tests/test-dump/table-dump-check.master BitKeeper/deleted/.del-table-dump-select.master~744acb955e33f3db: Delete: repl-tests/test-dump/table-dump-select.master BitKeeper/deleted/.del-x.master~29a93ed7956c8693: Delete: repl-tests/test-auto-inc/x.master BitKeeper/deleted/.del-x.master~3b248cbac9abda2b: Delete: repl-tests/test-bad-query/x.master BitKeeper/deleted/.del-foo-dump-master.master~b49ae6bec1e918ee: Delete: repl-tests/test-repl/foo-dump-master.master BitKeeper/deleted/.del-foo-dump-slave.master~f16ed20457d59be9: Delete: repl-tests/test-repl/foo-dump-slave.master BitKeeper/deleted/.del-repl-timestamp.master.reject~3492d2b74b413771: Delete: repl-tests/test-repl-ts/repl-timestamp.master.reject BitKeeper/deleted/.del-repl-timestamp.master~4b7782da5cc13161: Delete: repl-tests/test-repl-ts/repl-timestamp.master BitKeeper/deleted/.del-run.test~a1e32ea1e4253af4: Delete: repl-tests/test-repl/run.test BitKeeper/deleted/.del-run.test~ce5e626c91b760ec: Delete: repl-tests/test-repl-ts/run.test BitKeeper/deleted/.del-sum-wlen-master.master~1a5ea625c79e978: Delete: repl-tests/test-repl/sum-wlen-master.master BitKeeper/deleted/.del-sum-wlen-slave.master~f016d98833433084: Delete: repl-tests/test-repl/sum-wlen-slave.master BitKeeper/deleted/.del-test.master~5829e7b3770179db: Delete: repl-tests/test-repl-alter/test.master BitKeeper/deleted/.del-master-slave.inc~6775f6ae10137c39: Delete: repl-tests/include/master-slave.inc include/my_global.h: Fix for purify/valgrind myisam/mi_info.c: Updated comment mysql-test/r/group_by.result: New test results mysql-test/r/innodb.result: New test results mysql-test/r/join_outer.result: New test results mysql-test/r/multi_update.result: New test results mysql-test/r/null_key.result: New test results mysql-test/r/update.result: New test results mysql-test/t/group_by.test: Added extra explain to 'suspicious' test. mysql-test/t/innodb.test: Added test for UPDATE ... ORDER BY mysql-test/t/join_outer.test: Changed test to be repeatable mysql-test/t/multi_update.test: Slight change of test to catch more bugs mysql-test/t/update.test: Better test for UPDATE ... ORDER BY sql/field.cc: Simple optimization sql/ha_heap.h: Added optimzation for clustered index sql/ha_innodb.cc: Added optimzation for clustered index sql/ha_innodb.h: Added optimzation for clustered index sql/handler.h: Added optimzation for clustered index sql/item_sum.cc: Removed some usage of current_thd sql/mysqld.cc: Fix bug when compiling for purify/valgrind sql/opt_range.cc: Added optimzation for clustered index sql/records.cc: Fixed comment sql/sql_list.h: Fixed comment sql/sql_select.cc: Removed some usage of current_thd sql/sql_select.h: Removed some usage of current_thd sql/sql_union.cc: Removed some usage of current_thd sql/sql_update.cc: Fixed bug in UPDATE ... ORDER BY Fixed handling of UPDATE ... LIMIT support-files/my-huge.cnf.sh: Added default size for query cache support-files/my-large.cnf.sh: Added default size for query cache --- mysql-test/r/group_by.result | 42 +++++++++++------------- mysql-test/r/innodb.result | 71 ++++++++++++++++++++++++++++++++++++++++ mysql-test/r/join_outer.result | 4 +-- mysql-test/r/multi_update.result | 20 +++++------ mysql-test/r/null_key.result | 2 +- mysql-test/r/update.result | 40 ++++++++++++++++++---- mysql-test/t/group_by.test | 16 ++------- mysql-test/t/innodb.test | 53 ++++++++++++++++++++++++++++++ mysql-test/t/join_outer.test | 2 +- mysql-test/t/multi_update.test | 2 +- mysql-test/t/update.test | 14 +++++--- 11 files changed, 203 insertions(+), 63 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index f73c4d7f8af..43f1bf8f6d4 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -49,6 +49,10 @@ SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1 userid MIN(t1.score+0.0) 1 1.0 2 2.0 +EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary +t2 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index drop table test.t1,test.t2; CREATE TABLE t1 ( PID int(10) unsigned NOT NULL auto_increment, @@ -242,34 +246,32 @@ score smallint(5) unsigned, key (spid), key (score) ); -INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3); +INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3); explain select userid,count(*) from t1 group by userid desc; table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort explain select userid,count(*) from t1 group by userid desc order by null; table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 6 Using temporary +t1 ALL NULL NULL NULL NULL 8 Using temporary select userid,count(*) from t1 group by userid desc; userid count(*) -3 3 +3 5 2 1 1 2 select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3); userid count(*) -3 3 1 2 select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*)); userid count(*) -3 3 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; table type possible_keys key key_len ref rows Extra -t1 range spID spID 5 NULL 2 Using where; Using index +t1 range spID spID 5 NULL 3 Using where; Using index explain select spid,count(*) from t1 where spid between 1 and 2 group by spid; table type possible_keys key key_len ref rows Extra -t1 range spID spID 5 NULL 2 Using where; Using index +t1 range spID spID 5 NULL 3 Using where; Using index explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null; table type possible_keys key key_len ref rows Extra -t1 range spID spID 5 NULL 2 Using where; Using index +t1 range spID spID 5 NULL 3 Using where; Using index select spid,count(*) from t1 where spid between 1 and 2 group by spid; spid count(*) 1 1 @@ -280,12 +282,14 @@ spid count(*) 1 1 explain select sql_big_result spid,sum(userid) from t1 group by spid desc; table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 6 Using filesort +t1 ALL NULL NULL NULL NULL 8 Using filesort explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null; table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 6 +t1 ALL NULL NULL NULL NULL 8 select sql_big_result spid,sum(userid) from t1 group by spid desc; spid sum(userid) +7 3 +6 3 5 3 4 3 3 3 @@ -293,13 +297,13 @@ spid sum(userid) 1 1 explain select sql_big_result score,count(*) from t1 group by score desc; table type possible_keys key key_len ref rows Extra -t1 index NULL score 3 NULL 6 Using index +t1 index NULL score 3 NULL 8 Using index explain select sql_big_result score,count(*) from t1 group by score desc order by null; table type possible_keys key key_len ref rows Extra -t1 index NULL score 3 NULL 6 Using index +t1 index NULL score 3 NULL 8 Using index select sql_big_result score,count(*) from t1 group by score desc; score count(*) -3 3 +3 5 2 1 1 2 drop table t1; @@ -535,15 +539,7 @@ t1 ALL NULL NULL NULL NULL 6 Using temporary t2 ALL a NULL NULL NULL 4 Using where drop table t1,t2; create table t1 (a int, b int); -insert into t1 values (1, 4); -insert into t1 values (10, 40); -insert into t1 values (1, 4); -insert into t1 values (10, 43); -insert into t1 values (1, 4); -insert into t1 values (10, 41); -insert into t1 values (1, 4); -insert into t1 values (10, 43); -insert into t1 values (1, 4); +insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a; a MAX(b) INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) 1 4 2 diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 48db5412da7..293519604dd 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1201,3 +1201,74 @@ a b 8 5 9 5 drop table t1,t2; +create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) type=innodb; +create table t2 (a int not null auto_increment primary key, b int); +insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); +insert into t2 (a) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +insert into t2 (a) select b from t1; +insert into t1 (a) select b from t2; +select count(*) from t1; +count(*) +29267 +explain select a from t1 where a between 1 and 10000; +table type possible_keys key key_len ref rows Extra +t1 range PRIMARY PRIMARY 4 NULL 14790 Using where; Using index +explain select * from t1 where a between 1 and 10000; +table type possible_keys key key_len ref rows Extra +t1 range PRIMARY PRIMARY 4 NULL 14790 Using where +explain select * from t1 where c between 1 and 10000; +table type possible_keys key key_len ref rows Extra +t1 range c c 5 NULL 1 Using where +update t1 set c=a; +explain select * from t1 where c between 1 and 10000; +table type possible_keys key key_len ref rows Extra +t1 ALL c NULL NULL NULL 29537 Using where +drop table t1,t2; +create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb; +insert into t1 (id) values (null),(null),(null),(null),(null); +update t1 set fk=69 where fk is null order by id limit 1; +SELECT * from t1; +id fk +2 NULL +3 NULL +4 NULL +5 NULL +1 69 +drop table t1; +create table t1 (a int not null, b int not null, key (a)); +insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3); +SET @tmp=0; +update t1 set b=(@tmp:=@tmp+1) order by a; +update t1 set b=99 where a=1 order by b asc limit 1; +update t1 set b=100 where a=1 order by b desc limit 2; +update t1 set a=a+10+b where a=1 order by b; +select * from t1 order by a,b; +a b +2 4 +2 5 +2 6 +3 7 +3 8 +3 9 +3 10 +3 11 +3 12 +13 2 +111 100 +111 100 +drop table t1; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 6e69616b7c6..98f8882aa10 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -373,10 +373,10 @@ Thimble Smith Happy 3 3 3 drop table t1,t2; create table t1 (id int not null, str char(10), index(str)); insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar"); -select * from t1 where str is not null; +select * from t1 where str is not null order by id; id str -4 bar 3 foo +4 bar select * from t1 where str is null; id str 1 NULL diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 0f3644f15bc..daac5438eaa 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -286,7 +286,7 @@ a b 107 7 108 8 109 9 -update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5; +update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1.a-100; select * from t1; a b 201 1 @@ -300,13 +300,13 @@ a b 109 9 select * from t2; a b -1 3 -2 3 -3 3 -4 3 -5 3 -6 3 -7 3 -8 3 -9 3 +1 1 +2 2 +3 13 +4 14 +5 15 +6 6 +7 7 +8 8 +9 9 drop table t1,t2; diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 236def64b5e..009a3e06eb2 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -84,7 +84,7 @@ table type possible_keys key key_len ref rows Extra t1 range a,b a 5 NULL 5 Using where explain select * from t1 where (a is null or a = 7) and b=7 and c=0; table type possible_keys key key_len ref rows Extra -t1 range a,b a 5 NULL 4 Using where +t1 ALL a,b NULL NULL NULL 12 Using where explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; table type possible_keys key key_len ref rows Extra t1 ref a,b a 5 const 3 Using where diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index db209c5855c..86647e845a9 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -102,13 +102,39 @@ select status from t1; status 1 drop table t1; -create table t1 (a int not null, b int not null); -insert into t1 values (1,1),(1,2),(1,3); -update t1 set b=4 where a=1 order by b asc limit 1; -update t1 set b=4 where a=1 order by b desc limit 1; -select * from t1; +create table t1 (a int not null, b int not null, key (a)); +insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3); +SET @tmp=0; +update t1 set b=(@tmp:=@tmp+1) order by a; +update t1 set b=99 where a=1 order by b asc limit 1; +select * from t1 order by a,b; a b -1 4 1 2 -1 4 +1 3 +1 99 +2 4 +2 5 +2 6 +3 7 +3 8 +3 9 +3 10 +3 11 +3 12 +update t1 set b=100 where a=1 order by b desc limit 2; +update t1 set a=a+10+b where a=1 order by b; +select * from t1 order by a,b; +a b +2 4 +2 5 +2 6 +3 7 +3 8 +3 9 +3 10 +3 11 +3 12 +13 2 +111 100 +111 100 drop table t1; diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 9e5ee4b8b2a..8c111430eea 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -40,6 +40,7 @@ SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid; SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid; SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL; +EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL; drop table test.t1,test.t2; # @@ -236,7 +237,7 @@ CREATE TABLE t1 ( key (score) ); -INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3); +INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3); explain select userid,count(*) from t1 group by userid desc; explain select userid,count(*) from t1 group by userid desc order by null; select userid,count(*) from t1 group by userid desc; @@ -255,8 +256,6 @@ explain select sql_big_result score,count(*) from t1 group by score desc order b 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); @@ -265,7 +264,6 @@ select a,min(b) c,count(distinct rand()) from t1 group by a having c Date: Thu, 24 Apr 2003 02:23:25 +0300 Subject: Changed test slightly to make it repeatable (There was a problem on some AMD system when the floating point precision wasn't accurate enough) mysql-test/r/group_by.result: new test result mysql-test/t/group_by.test: Changed test slightly to make it repeatable --- mysql-test/r/group_by.result | 6 ++++-- mysql-test/t/group_by.test | 2 ++ 2 files changed, 6 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 43f1bf8f6d4..94e1ce59585 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -27,6 +27,8 @@ PRIMARY KEY (userID) INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1'); INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1'); INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1'); +INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1'); +INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1'); SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid; userid MIN(t1.score) 1 1 @@ -47,12 +49,12 @@ userid MIN(t1.score+0.0) 2 2.0 SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL; userid MIN(t1.score+0.0) -1 1.0 2 2.0 +1 1.0 EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary -t2 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index +t2 eq_ref PRIMARY PRIMARY 4 t1.userID 1 Using index drop table test.t1,test.t2; CREATE TABLE t1 ( PID int(10) unsigned NOT NULL auto_increment, diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 8c111430eea..67f4eb580a6 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -34,6 +34,8 @@ CREATE TABLE t2 ( INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1'); INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1'); INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1'); +INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1'); +INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1'); SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid; SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL; -- cgit v1.2.1 From cdf3e090e5d1cd9a0aaf17dba52acf39abdfe1a1 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 24 Apr 2003 09:18:53 +0300 Subject: Fixed bug in SET SQL_BIG_SELECTS mysql-test/r/select_safe.result: Updated results mysql-test/t/select_safe.test: Added test for SQL_BIG_SELECTS sql/set_var.cc: Fixed bug in SQL_BIG_SELECTS sql/share/czech/errmsg.txt: Updated error messages sql/share/danish/errmsg.txt: Updated error messages sql/share/dutch/errmsg.txt: Updated error messages sql/share/english/errmsg.txt: Updated error messages sql/share/estonian/errmsg.txt: Updated error messages sql/share/french/errmsg.txt: Updated error messages sql/share/german/errmsg.txt: Updated error messages sql/share/greek/errmsg.txt: Updated error messages sql/share/hungarian/errmsg.txt: Updated error messages sql/share/italian/errmsg.txt: Updated error messages sql/share/japanese/errmsg.txt: Updated error messages sql/share/korean/errmsg.txt: Updated error messages sql/share/norwegian-ny/errmsg.txt: Updated error messages sql/share/norwegian/errmsg.txt: Updated error messages sql/share/portuguese/errmsg.txt: Updated error messages sql/share/romanian/errmsg.txt: Updated error messages sql/share/russian/errmsg.txt: Updated error messages sql/share/slovak/errmsg.txt: Updated error messages sql/share/spanish/errmsg.txt: Updated error messages sql/share/swedish/errmsg.txt: Updated error messages sql/share/ukrainian/errmsg.txt: Updated error messages --- mysql-test/r/select_safe.result | 28 ++++++++++++++++++++++++++-- mysql-test/t/select_safe.test | 36 +++++++++++++++++++++++++++++------- 2 files changed, 55 insertions(+), 9 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index e225a0e3995..3303f19d9c7 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -1,6 +1,6 @@ drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9; -create table t1 (a int primary key, b char(20)); +create table t1 (a int auto_increment primary key, b char(20)); insert into t1 values(1,"test"); SELECT SQL_BUFFER_RESULT * from t1; a b @@ -30,10 +30,34 @@ You are using safe update mode and you tried to update a table without a WHERE t delete from t1 where a+0=1; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; -The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok +The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok update t1 set b="a" limit 1; update t1 set b="a" where b="b" limit 2; delete from t1 where b="test" limit 1; delete from t1 where a+0=1 limit 2; +SET MAX_JOIN_SIZE=2; +SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS; +@@max_join_size @@sql_big_selects +2 0 +insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); +SELECT * from t1; +The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok +SET SQL_BIG_SELECTS=1; +SELECT * from t1; +a b +3 a +2 test2 +4 a +5 a +SET MAX_JOIN_SIZE=2; +SELECT * from t1; +The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok +SET MAX_JOIN_SIZE=DEFAULT; +SELECT * from t1; +a b +3 a +2 test2 +4 a +5 a drop table t1; SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test index e76b7558997..a085cfee29d 100644 --- a/mysql-test/t/select_safe.test +++ b/mysql-test/t/select_safe.test @@ -4,7 +4,7 @@ drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9; -create table t1 (a int primary key, b char(20)); +create table t1 (a int auto_increment primary key, b char(20)); insert into t1 values(1,"test"); SELECT SQL_BUFFER_RESULT * from t1; update t1 set b="a" where a=1; @@ -15,18 +15,40 @@ update t1 set b="a" where a=1; select 1 from t1,t1 as t2,t1 as t3,t1 as t4; # The following should give errors: -!$1175 update t1 set b="a"; -!$1175 update t1 set b="a" where b="test"; -!$1175 delete from t1; -!$1175 delete from t1 where b="test"; -!$1175 delete from t1 where a+0=1; -!$1104 select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; +--error 1175 +update t1 set b="a"; +--error 1175 +update t1 set b="a" where b="test"; +--error 1175 +delete from t1; +--error 1175 +delete from t1 where b="test"; +--error 1175 +delete from t1 where a+0=1; +--error 1104 +select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; # The following should be ok: update t1 set b="a" limit 1; update t1 set b="a" where b="b" limit 2; delete from t1 where b="test" limit 1; delete from t1 where a+0=1 limit 2; + +# Test SQL_BIG_SELECTS + +SET MAX_JOIN_SIZE=2; +SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS; +insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); +--error 1104 +SELECT * from t1; +SET SQL_BIG_SELECTS=1; +SELECT * from t1; +SET MAX_JOIN_SIZE=2; +--error 1104 +SELECT * from t1; +SET MAX_JOIN_SIZE=DEFAULT; +SELECT * from t1; + drop table t1; SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; -- cgit v1.2.1 From a8d6374fef97a4fb8617880386e706b4856e807d Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 25 Apr 2003 14:42:01 +0200 Subject: - Updated innodb test results on Heikki's request (InnoDB now supports ANALYZE TABLE) BitKeeper/etc/ignore: Added support-files/MacOSX/ReadMe.txt to the ignore list --- mysql-test/r/innodb.result | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 293519604dd..5049dec9ef1 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -208,7 +208,7 @@ create index skr on t1 (a); insert into t1 values (3,""), (4,"testing"); analyze table t1; Table Op Msg_type Msg_text -test.t1 analyze error The handler for the table doesn't support analyze +test.t1 analyze status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 skr 1 a A 3 NULL NULL YES BTREE @@ -1227,10 +1227,10 @@ count(*) 29267 explain select a from t1 where a between 1 and 10000; table type possible_keys key key_len ref rows Extra -t1 range PRIMARY PRIMARY 4 NULL 14790 Using where; Using index +t1 range PRIMARY PRIMARY 4 NULL 14745 Using where; Using index explain select * from t1 where a between 1 and 10000; table type possible_keys key key_len ref rows Extra -t1 range PRIMARY PRIMARY 4 NULL 14790 Using where +t1 range PRIMARY PRIMARY 4 NULL 14745 Using where explain select * from t1 where c between 1 and 10000; table type possible_keys key key_len ref rows Extra t1 range c c 5 NULL 1 Using where -- cgit v1.2.1 From 046c66819fafd0f83dc16fda56c5b249c8b195c2 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 26 Apr 2003 14:54:53 +0300 Subject: filesort.cc, order_by.result: Fixed bug 263 order_by.test: Fixed bug 263 mysql-test/t/order_by.test: Fixed bug 263 mysql-test/r/order_by.result: Fixed bug 263 sql/filesort.cc: Fixed bug 263 --- mysql-test/r/order_by.result | 25 +++++++++++++++++++++++++ mysql-test/t/order_by.test | 14 ++++++++++++++ 2 files changed, 39 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 79c6c9fa6fb..ff2dd93311c 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -517,3 +517,28 @@ SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,des titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug drop table t1,t2; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1, 2); +INSERT INTO t1 VALUES (3, 4); +INSERT INTO t1 VALUES (5, NULL); +SELECT * FROM t1 ORDER BY b; +a b +5 NULL +1 2 +3 4 +SELECT * FROM t1 ORDER BY b DESC; +a b +3 4 +1 2 +5 NULL +SELECT * FROM t1 ORDER BY (a + b); +a b +5 NULL +1 2 +3 4 +SELECT * FROM t1 ORDER BY (a + b) DESC; +a b +3 4 +1 2 +5 NULL +DROP TABLE t1; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 06a5d0ceb20..1fb83509ebb 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -331,3 +331,17 @@ INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug'); SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; drop table t1,t2; + +# +# Test order by with NULL values +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1, 2); +INSERT INTO t1 VALUES (3, 4); +INSERT INTO t1 VALUES (5, NULL); +SELECT * FROM t1 ORDER BY b; +SELECT * FROM t1 ORDER BY b DESC; +SELECT * FROM t1 ORDER BY (a + b); +SELECT * FROM t1 ORDER BY (a + b) DESC; +DROP TABLE t1; + -- cgit v1.2.1 From 62af18cb8729894625d005e260838f68b7c57175 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 26 Apr 2003 20:43:28 +0300 Subject: Fix for -fbranch-probabilites (bug 268) Fix for LEFT/RIGHT/MID with multi-byte-character sets (bug 314) Fix for new bison 1.875 max_insert_delayed_threads and delayed_insert_timeout now works as documented (bug 211) Don't show port in SHOW PROCESSLIST for system threads Fix problem with ORDER BY being discarded for some DISTINCT queries (bug 275) Fixed bug with NATURAL LEFT JOIN, NATURAL RIGHT JOIN and RIGHT JOIN when using many joined tables (Bug 212) acinclude.m4: Fix for -fbranch-probabilites configure.in: Fix for -fbranch-probabilites myisam/mi_open.c: Give error instead of writing to stderr (Which user can't see) mysql-test/r/join.result: Update results after change to NATURAL/LEFT/RIGHT JOIN mysql-test/r/join_outer.result: Update results after change to NATURAL/LEFT/RIGHT JOIN mysql-test/r/select.result: Update results after change to NATURAL/LEFT/RIGHT JOIN mysql-test/t/join.test: Update results after change to NATURAL/LEFT/RIGHT JOIN sql/item_strfunc.cc: Fix for LEFT/RIGHT/MID with multi-byte-character sets sql/mysqld.cc: Fix for new bison 1.875 (Wrong error text message when one got syntax error) sql/sql_class.cc: Don't show port in SHOW PROCESSLIST for system threads sql/sql_insert.cc: max_insert_delayed_threads now works as documented. delayed_insert_timeout now works on Linux sql/sql_parse.cc: Added comment sql/sql_select.cc: Fix problem with ORDER BY being discarded and results returned un-sorted sql/sql_yacc.yy: Fixed bug with NATURAL LEFT JOIN, NATURAL RIGHT JOIN and RIGHT JOIN when using many joined tables. --- mysql-test/r/ctype_ujis.result | 8 ++++ mysql-test/r/join.result | 87 ++++++++++++++++++++++++++++++++++++++ mysql-test/r/join_outer.result | 7 +-- mysql-test/r/select.result | 12 ------ mysql-test/t/ctype_ujis-master.opt | 1 + mysql-test/t/ctype_ujis.test | 13 ++++++ mysql-test/t/join.test | 32 ++++++++++++++ 7 files changed, 142 insertions(+), 18 deletions(-) create mode 100644 mysql-test/r/ctype_ujis.result create mode 100644 mysql-test/t/ctype_ujis-master.opt create mode 100644 mysql-test/t/ctype_ujis.test (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result new file mode 100644 index 00000000000..223a18f19e9 --- /dev/null +++ b/mysql-test/r/ctype_ujis.result @@ -0,0 +1,8 @@ +drop table if exists t1; +create table t1 (c text); +insert into t1 values (0xa4a2),(0xa4a3); +select hex(left(c,1)) from t1 group by c; +hex(left(c,1)) +A4A2 +A4A3 +drop table t1; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 9f6a8762325..3211dcf76a9 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -274,3 +274,90 @@ SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (site rate_code base_rate cust 20 drop table t1,t2; +create table t1 (i int); +create table t2 (i int); +create table t3 (i int); +insert into t1 values(1),(2); +insert into t2 values(2),(3); +insert into t3 values (2),(4); +select * from t1 natural left join t2; +i i +1 NULL +2 2 +select * from t1 left join t2 on (t1.i=t2.i); +i i +1 NULL +2 2 +select * from t1 natural left join t2 natural left join t3; +i i i +1 NULL NULL +2 2 2 +select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i); +i i i +1 NULL NULL +2 2 2 +select * from t3 natural right join t2; +i i +2 2 +NULL 3 +select * from t3 right join t2 on (t3.i=t2.i); +i i +2 2 +NULL 3 +select * from t3 natural right join t2 natural right join t1; +i i i +NULL NULL 1 +2 2 2 +select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i); +i i i +NULL NULL 1 +2 2 2 +select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i; +i i i +1 2 2 +1 3 NULL +2 2 2 +2 3 NULL +select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i; +i i i +1 2 2 +1 3 NULL +2 2 2 +2 3 NULL +select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i; +i i i +1 2 2 +1 3 NULL +2 2 2 +2 3 NULL +select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i; +i i i +1 2 2 +1 3 NULL +2 2 2 +2 3 NULL +select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i; +i i i +1 NULL 4 +1 2 2 +2 NULL 4 +2 2 2 +select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i; +i i i +1 NULL 4 +1 2 2 +2 NULL 4 +2 2 2 +select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i; +i i i +1 NULL 4 +1 2 2 +2 NULL 4 +2 2 2 +select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i; +i i i +1 NULL 4 +1 2 2 +2 NULL 4 +2 2 2 +drop table t1,t2,t3; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 98f8882aa10..e64cc07e344 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -351,12 +351,7 @@ select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) name name id id Antonio Paz El Gato 1 1 Antonio Paz Perrito 2 1 -NULL Happy 3 1 -NULL El Gato 1 2 -NULL Perrito 2 2 -NULL Happy 3 2 -NULL El Gato 1 3 -NULL Perrito 2 3 +NULL NULL NULL 2 Thimble Smith Happy 3 3 select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner; name name id owner id diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 1ad3043b4b2..a4c2533ec1a 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3402,13 +3402,7 @@ a a a select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a)); a a a 1 1 1 -2 1 NULL -3 1 NULL -1 2 NULL 2 2 2 -3 2 NULL -1 3 NULL -2 3 NULL 3 3 3 select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; a a a @@ -3464,13 +3458,7 @@ a a a select * from t1 natural join (t1 as t2 left join t1 as t3 using (a)); a a a 1 1 1 -2 1 NULL -3 1 NULL -1 2 NULL 2 2 2 -3 2 NULL -1 3 NULL -2 3 NULL 3 3 3 select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1; a a a diff --git a/mysql-test/t/ctype_ujis-master.opt b/mysql-test/t/ctype_ujis-master.opt new file mode 100644 index 00000000000..1f4183d5027 --- /dev/null +++ b/mysql-test/t/ctype_ujis-master.opt @@ -0,0 +1 @@ +--default-character-set=ujis diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test new file mode 100644 index 00000000000..cd1dc965000 --- /dev/null +++ b/mysql-test/t/ctype_ujis.test @@ -0,0 +1,13 @@ +# +# Tests with the ujis character set +# +drop table if exists t1; + +# +# Test problem with LEFT() +# + +create table t1 (c text); +insert into t1 values (0xa4a2),(0xa4a3); +select hex(left(c,1)) from t1 group by c; +drop table t1; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 63ec90f854c..7a9868e60ef 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -271,3 +271,35 @@ 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; + +# +# Test combination of join methods +# + +create table t1 (i int); +create table t2 (i int); +create table t3 (i int); +insert into t1 values(1),(2); +insert into t2 values(2),(3); +insert into t3 values (2),(4); + +select * from t1 natural left join t2; +select * from t1 left join t2 on (t1.i=t2.i); +select * from t1 natural left join t2 natural left join t3; +select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i); + +select * from t3 natural right join t2; +select * from t3 right join t2 on (t3.i=t2.i); +select * from t3 natural right join t2 natural right join t1; +select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i); + +select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i; +select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i; +select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i; +select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i; + +select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i; +select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i; +select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i; +select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i; +drop table t1,t2,t3; -- cgit v1.2.1 From f22be777341f53b4deb58851828c0733ab5380bf Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 27 Apr 2003 22:12:08 +0300 Subject: Fixed problem when comparing a key for a multi-byte-character set. (bug 152) Use 0x.... as strings if 'new' mode. (bug 152) Don't report -max on windows when InnoDB is enabled. (bug 332) Reset current_linfo; This could cause a hang when doing PURGE LOGS. Fix for row numbers in EXPLAIN (bug 322) Fix that USE_FRM works for all table types (bug 97) VC++Files/libmysql/libmysql.dsp: Added new source files myisam/mi_key.c: Fixed problem when comparing a key for a multi-byte-character set. myisam/mi_range.c: Fixed problem when comparing a key for a multi-byte-character set. myisam/mi_rkey.c: Fixed problem when comparing a key for a multi-byte-character set. myisam/mi_search.c: Fixed problem when comparing a key for a multi-byte-character set. myisam/mi_test2.c: Fixed printf statements myisam/myisamdef.h: Fixed problem when comparing a key for a multi-byte-character set. myisam/sort.c: Fixed printf statements mysql-test/r/ctype_latin1_de.result: New test results mysql-test/r/join.result: New test results mysql-test/r/repair.result: New test results mysql-test/r/rpl_alter.result: New test results mysql-test/t/ctype_latin1_de-master.opt: --new is needed to get 0x... strings to work properly mysql-test/t/ctype_latin1_de.test: New test for latin1_de mysql-test/t/repair.test: Test of USE_FRM and HEAP tables sql/field.cc: Fixed problem when comparing a key for a multi-byte-character set. sql/item.cc: Use 0x.... as strings if 'new' mode sql/item.h: Use 0x.... as strings if 'new' mode sql/mysqld.cc: Don't report -max on windows when InnoDB is enabled. sql/sql_analyse.cc: Removed unused variable sql/sql_insert.cc: Removed debug message sql/sql_repl.cc: Reset current_linfo; This could cause a hang when doing PURGE LOGS. sql/sql_select.cc: Fix for row numbers in EXPLAIN sql/sql_table.cc: Fix that USE_FRM works for all table types (without strange errors) sql/sql_yacc.yy: Removed compiler warnings. --- mysql-test/r/ctype_latin1_de.result | 52 +++++++++++++++++++++++++++++++++ mysql-test/r/join.result | 8 +++++ mysql-test/r/repair.result | 6 +++- mysql-test/r/rpl_alter.result | 22 +++++++------- mysql-test/t/ctype_latin1_de-master.opt | 3 +- mysql-test/t/ctype_latin1_de.test | 23 +++++++++++++++ mysql-test/t/repair.test | 4 ++- 7 files changed, 104 insertions(+), 14 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index b79bc67138c..630fef9b679 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -212,3 +212,55 @@ select * from t1 where match a against ("te*" in boolean mode)+0; a test drop table t1; +create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word)); +insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae'); +update t1 set word2=word; +select word, word=0xdf as t from t1 having t > 0; +word t +ß 1 +select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0; +word t +ss 1 +ß 1 +select * from t1 where word=0xDF; +word word2 +ß ß +select * from t1 where word=CAST(0xDF as CHAR); +word word2 +ss ss +ß ß +select * from t1 where word2=0xDF; +word word2 +ß ß +select * from t1 where word2=CAST(0xDF as CHAR); +word word2 +ss ss +ß ß +select * from t1 where word='ae'; +word word2 +ä ä +ae ae +select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR); +word word2 +ä ä +ae ae +select * from t1 where word between 0xDF and 0xDF; +word word2 +ß ß +select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR); +word word2 +ss ss +ß ß +select * from t1 where word like 'ae'; +word word2 +ae ae +select * from t1 where word like 'AE'; +word word2 +ae ae +select * from t1 where word like 0xDF; +word word2 +ß ß +select * from t1 where word like CAST(0xDF as CHAR); +word word2 +ß ß +drop table t1; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 1b5766e3ab4..e063b5c3e02 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -273,8 +273,16 @@ cust 20 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'; rate_code base_rate cust 20 +drop table t1,t2; +CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255)); +CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255)); +INSERT INTO t1 VALUES (1, 'A'); +INSERT INTO t2 VALUES (1, 'B'); +SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B'); ID Value1 ID Value2 +SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B'; ID Value1 ID Value2 +SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1; ID Value1 ID Value2 drop table t1,t2; create table t1 (i int); diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result index 8b50f9a92e8..adc09ded0e2 100644 --- a/mysql-test/r/repair.result +++ b/mysql-test/r/repair.result @@ -4,4 +4,8 @@ repair table t1 use_frm; Table Op Msg_type Msg_text test.t1 repair warning Number of rows changed from 0 to 1 test.t1 repair status OK -drop table if exists t1; +alter table t1 TYPE=HEAP; +repair table t1 use_frm; +Table Op Msg_type Msg_text +test.t1 repair error The handler for the table doesn't support repair +drop table t1; diff --git a/mysql-test/r/rpl_alter.result b/mysql-test/r/rpl_alter.result index 1dc73c6524a..729c7df6808 100644 --- a/mysql-test/r/rpl_alter.result +++ b/mysql-test/r/rpl_alter.result @@ -4,18 +4,18 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -drop database if exists d1; -create database d1; -create table d1.t1 ( n int); -alter table d1.t1 add m int; -insert into d1.t1 values (1,2); -create table d1.t2 (n int); -insert into d1.t2 values (45); -rename table d1.t2 to d1.t3, d1.t1 to d1.t2; -select * from d1.t2; +drop database if exists test_$1; +create database test_$1; +create table test_$1.t1 ( n int); +alter table test_$1.t1 add m int; +insert into test_$1.t1 values (1,2); +create table test_$1.t2 (n int); +insert into test_$1.t2 values (45); +rename table test_$1.t2 to test_$1.t3, test_$1.t1 to test_$1.t2; +select * from test_$1.t2; n m 1 2 -select * from d1.t3; +select * from test_$1.t3; n 45 -drop database d1; +drop database test_$1; diff --git a/mysql-test/t/ctype_latin1_de-master.opt b/mysql-test/t/ctype_latin1_de-master.opt index 98accd58c46..895a62364d6 100644 --- a/mysql-test/t/ctype_latin1_de-master.opt +++ b/mysql-test/t/ctype_latin1_de-master.opt @@ -1 +1,2 @@ ---default-character-set=latin1_de +--default-character-set=latin1_de --new + diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index 4b96f5f5867..6353650f420 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -45,3 +45,26 @@ select * from t1 where a like "test%"; select * from t1 where a like "te_t"; select * from t1 where match a against ("te*" in boolean mode)+0; drop table t1; + +# +# Test bug report #152 (problem with index on latin1_de) +# + +create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word)); +insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae'); +update t1 set word2=word; +select word, word=0xdf as t from t1 having t > 0; +select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0; +select * from t1 where word=0xDF; +select * from t1 where word=CAST(0xDF as CHAR); +select * from t1 where word2=0xDF; +select * from t1 where word2=CAST(0xDF as CHAR); +select * from t1 where word='ae'; +select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR); +select * from t1 where word between 0xDF and 0xDF; +select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR); +select * from t1 where word like 'ae'; +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; diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test index 6d79014b23d..b901fb3467f 100644 --- a/mysql-test/t/repair.test +++ b/mysql-test/t/repair.test @@ -5,4 +5,6 @@ drop table if exists t1; create table t1 SELECT 1,"table 1"; repair table t1 use_frm; -drop table if exists t1; +alter table t1 TYPE=HEAP; +repair table t1 use_frm; +drop table t1; -- cgit v1.2.1 From 671e77a1f7bfbb51fdd2d9af361e226e89506048 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 28 Apr 2003 16:05:27 +0200 Subject: - Removed two EXPLAIN SELECT statements from the InnoDB test suite (the results are nondeterministic and provide different output on 32bit and 64bit architectures) mysql-test/r/innodb.result: - removed two EXPLAIN SELECT results because the output is nondeterministic (the result was different on 64bit platforms) mysql-test/t/innodb.test: - removed two EXPLAIN SELECT tests because the results are nondeterministic (the result was different on 64bit platforms) --- mysql-test/r/innodb.result | 6 ------ mysql-test/t/innodb.test | 2 -- 2 files changed, 8 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 5049dec9ef1..3f28f00f322 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1225,12 +1225,6 @@ insert into t1 (a) select b from t2; select count(*) from t1; count(*) 29267 -explain select a from t1 where a between 1 and 10000; -table type possible_keys key key_len ref rows Extra -t1 range PRIMARY PRIMARY 4 NULL 14745 Using where; Using index -explain select * from t1 where a between 1 and 10000; -table type possible_keys key key_len ref rows Extra -t1 range PRIMARY PRIMARY 4 NULL 14745 Using where explain select * from t1 where c between 1 and 10000; table type possible_keys key key_len ref rows Extra t1 range c c 5 NULL 1 Using where diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 88edaac190a..ab3157a7f86 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -823,8 +823,6 @@ insert into t1 (a) select b from t2; insert into t2 (a) select b from t1; insert into t1 (a) select b from t2; select count(*) from t1; -explain select a from t1 where a between 1 and 10000; -explain select * from t1 where a between 1 and 10000; explain select * from t1 where c between 1 and 10000; update t1 set c=a; explain select * from t1 where c between 1 and 10000; -- cgit v1.2.1 From 38b70f0608fbd8f5c3f25fba16c2887611311803 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 29 Apr 2003 14:13:22 +0300 Subject: code cleanup mysql-test/r/innodb.result: fixing result mysql-test/t/innodb.test: fixing test sql/sql_base.cc: committing a fix in order to pull new stuff --- mysql-test/r/innodb.result | 1 + mysql-test/t/innodb.test | 2 +- 2 files changed, 2 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index aefcd135dd8..39214348244 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1219,6 +1219,7 @@ parent child 1 2 1 3 2 1 +drop table t1; create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) type=innodb; create table t2 (a int not null auto_increment primary key, b int); insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 393112beb27..4fb9351020e 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -809,7 +809,7 @@ drop table t1,t2; create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) type = innodb; insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); select distinct parent,child from t1 order by parent; -drop table t1,t2; +drop table t1; # # Test that MySQL priorities clustered indexes -- cgit v1.2.1 From edde138b30dc970931e16eca78255f18e7101c10 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 30 Apr 2003 17:39:23 +0300 Subject: removing architecture dependent test --- mysql-test/r/func_misc.result | 3 --- mysql-test/t/func_misc.test | 1 - 2 files changed, 4 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 67f43ca5ba4..4eed80c4cc9 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -10,6 +10,3 @@ inet_aton("255.255.255.255.255") inet_aton("255.255.1.255") inet_aton("0.1.255") select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); inet_ntoa(1099511627775) inet_ntoa(4294902271) inet_ntoa(511) NULL 255.255.1.255 0.0.1.255 -select format("nan",2); -format("nan",2) -nan diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index d9ae288cc59..d48b17e87af 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -7,4 +7,3 @@ select format(1.5555,0),format(123.5555,1),format(1234.5555,2),format(12345.5555 select inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")); select inet_aton("255.255.255.255.255"),inet_aton("255.255.1.255"),inet_aton("0.1.255"); select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); -select format("nan",2); -- cgit v1.2.1 From e2a839ff470f99f629dba2ddd31c0f2dce314941 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 3 May 2003 15:21:39 +0200 Subject: test case added --- mysql-test/r/repair.result | 3 +++ mysql-test/t/repair.test | 5 +++++ 2 files changed, 8 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result index adc09ded0e2..ad869787b8a 100644 --- a/mysql-test/r/repair.result +++ b/mysql-test/r/repair.result @@ -9,3 +9,6 @@ repair table t1 use_frm; Table Op Msg_type Msg_text test.t1 repair error The handler for the table doesn't support repair drop table t1; +repair table t1 use_frm; +Table Op Msg_type Msg_text +t1 repair error table is read-only or does not exists diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test index b901fb3467f..e7da79d8c9e 100644 --- a/mysql-test/t/repair.test +++ b/mysql-test/t/repair.test @@ -8,3 +8,8 @@ repair table t1 use_frm; alter table t1 TYPE=HEAP; repair table t1 use_frm; drop table t1; + +# non-existent table +repair table t1 use_frm; + + -- cgit v1.2.1 From 34c3c0279441554e8d42ff0ec18a2890c1a6e148 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 3 May 2003 16:21:43 +0300 Subject: Fix problem where key_read was not cleaned up properly, which caused assert in innodb test. mysql-test/r/innodb.result: Changed tests to make them repeatable. mysql-test/t/innodb.test: Changed tests to make them repeatable. sql/opt_sum.cc: Safety fix sql/sql_base.cc: Safety assert --- mysql-test/r/innodb.result | 34 +++++++++++++++++++++++----------- mysql-test/t/innodb.test | 4 ++-- 2 files changed, 25 insertions(+), 13 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 3f28f00f322..f954928befc 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1139,7 +1139,7 @@ a b drop table t1; CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) type=innodb; CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) type=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12); INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); update t1,t2 set t1.a=t1.a+100; select * from t1; @@ -1153,6 +1153,9 @@ a b 107 7 108 8 109 9 +110 10 +111 11 +112 12 update t1,t2 set t1.a=t1.a+100 where t1.a=101; select * from t1; a b @@ -1165,6 +1168,9 @@ a b 107 7 108 8 109 9 +110 10 +111 11 +112 12 update t1,t2 set t1.b=t1.b+10 where t1.b=2; select * from t1; a b @@ -1176,8 +1182,11 @@ a b 107 7 108 8 109 9 +110 10 +111 11 102 12 -update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5; +112 12 +update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100; select * from t1; a b 201 1 @@ -1188,18 +1197,21 @@ a b 107 7 108 8 109 9 +110 10 +111 11 102 12 +112 12 select * from t2; a b -1 5 -2 5 -3 5 -4 5 -5 5 -6 5 -7 5 -8 5 -9 5 +1 1 +2 2 +6 6 +7 7 +8 8 +9 9 +3 13 +4 14 +5 15 drop table t1,t2; create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) type=innodb; create table t2 (a int not null auto_increment primary key, b int); diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index ab3157a7f86..8aa26b567a6 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -776,7 +776,7 @@ drop table t1; CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) type=innodb; CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) type=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12); INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); # Full join, without key @@ -792,7 +792,7 @@ update t1,t2 set t1.b=t1.b+10 where t1.b=2; select * from t1; # Range key (in t1) -update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5; +update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100; select * from t1; select * from t2; drop table t1,t2; -- cgit v1.2.1 From 155b8e001307bced530900fe92644c1daa929fb1 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 3 May 2003 18:08:11 +0200 Subject: "delete from table where const" bug fixed --- mysql-test/t/delete.test | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 13fa617b3cf..904d959d148 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -53,5 +53,11 @@ 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; +select count(*) from t1; +delete from t1 where 1 > 2; +select count(*) from t1; +delete from t1 where 3 > 2; +select count(*) from t1; + drop table t1; -- cgit v1.2.1 From 6b1d89b1c9cae92d31c73ba3818fb3ec9b2de13d Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 4 May 2003 11:00:45 +0200 Subject: result updated --- mysql-test/r/delete.result | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 582ab894233..abc8245e69f 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -38,4 +38,15 @@ NULL d 7 delete from t1 where misc > 5 and bool is null; select * from t1 where misc > 5 and bool is null; bool not_null misc +select count(*) from t1; +count(*) +2 +delete from t1 where 1 > 2; +select count(*) from t1; +count(*) +2 +delete from t1 where 3 > 2; +select count(*) from t1; +count(*) +0 drop table t1; -- cgit v1.2.1 From afe9738efb8d9704b66f6b7cea57ccb011d79a1a Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 5 May 2003 14:52:39 +0300 Subject: Fixed that reading a DATE string of 000000 is interpreted as 0000-00-00 instead of 2000-00-00 sql/time.cc: Removed not used code Fixed that reading a DATE string of 000000 is interpreted as 0000-00-00 instead of 2000-00-00 --- mysql-test/r/loaddata.result | 11 +++++++++++ mysql-test/std_data/loaddata1.dat | 3 +++ mysql-test/t/loaddata.test | 11 +++++++++++ 3 files changed, 25 insertions(+) create mode 100644 mysql-test/r/loaddata.result create mode 100644 mysql-test/std_data/loaddata1.dat create mode 100644 mysql-test/t/loaddata.test (limited to 'mysql-test') diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result new file mode 100644 index 00000000000..d121a4e6c40 --- /dev/null +++ b/mysql-test/r/loaddata.result @@ -0,0 +1,11 @@ +drop table if exists t1; +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; +a b c d +0000-00-00 NULL 0000-00-00 0000-00-00 +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 +drop table t1; diff --git a/mysql-test/std_data/loaddata1.dat b/mysql-test/std_data/loaddata1.dat new file mode 100644 index 00000000000..c9e8549b211 --- /dev/null +++ b/mysql-test/std_data/loaddata1.dat @@ -0,0 +1,3 @@ +,\N,NULL,, +00,0,000000,, +2003-03-03, 20030303,030303,\N diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test new file mode 100644 index 00000000000..ceb5c47af11 --- /dev/null +++ b/mysql-test/t/loaddata.test @@ -0,0 +1,11 @@ +# +# Some simple test of load data +# + +drop table if exists t1; + +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; +drop table t1; -- cgit v1.2.1 From 10c790eff016ff0fc779baeb7ebf94940d3544e7 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 10:54:07 +0300 Subject: Safety fix to enable RAID in max binaries Better fix for format('nan') Fix for HAVING COUNT(DISTINCT...) myisam/mi_check.c: Better error message myisam/mi_dynrec.c: Simple code cleanup myisam/myisamchk.c: Better error messages mysql-test/r/func_misc.result: Added back test for format('nan') mysql-test/r/having.result: New test mysql-test/t/func_misc.test: Added back test for format('nan') mysql-test/t/having.test: Added test for count(distinct) in having mysys/raid.cc: Safety fix to enable RAID in max binaries scripts/mysql_install_db.sh: Create data directories even if --in-rpm is used (for MaxOSX) sql/item_strfunc.cc: Better fix for format('nan') sql/mysqld.cc: Give stacktrace on assert() sql/sql_yacc.yy: Fix for HAVING COUNT(DISTINCT...) tests/big_record.pl: Extend test to abuse packed MyISAM tables tests/table_types.pl: Fixed wrong merge --- mysql-test/r/func_misc.result | 3 +++ mysql-test/r/having.result | 8 ++++++++ mysql-test/t/func_misc.test | 5 +++++ mysql-test/t/having.test | 2 ++ 4 files changed, 18 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 4eed80c4cc9..8d05adcc1ba 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -10,3 +10,6 @@ inet_aton("255.255.255.255.255") inet_aton("255.255.1.255") inet_aton("0.1.255") select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); inet_ntoa(1099511627775) inet_ntoa(4294902271) inet_ntoa(511) NULL 255.255.1.255 0.0.1.255 +select length(format('nan', 2)) > 0; +length(format('nan', 2)) > 0 +1 diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index f113eb6ed49..d643070f7f9 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -69,4 +69,12 @@ select id, sum(qty) as sqty from t1 group by id having sqty>2; id sqty 1 5 2 9 +select sum(qty) as sqty from t1 group by id having count(id) > 0; +sqty +5 +9 +select sum(qty) as sqty from t1 group by id having count(distinct id) > 0; +sqty +5 +9 drop table t1; diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index d48b17e87af..be64c170fa1 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -7,3 +7,8 @@ select format(1.5555,0),format(123.5555,1),format(1234.5555,2),format(12345.5555 select inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")); select inet_aton("255.255.255.255.255"),inet_aton("255.255.1.255"),inet_aton("0.1.255"); select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); + +# +# Test for core dump with nan +# +select length(format('nan', 2)) > 0; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index fd972fea1ad..cb6fa85ffde 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -63,4 +63,6 @@ drop table t1; create table t1 (id int not null, qty int not null); insert into t1 values (1,2),(1,3),(2,4),(2,5); select id, sum(qty) as sqty from t1 group by id having sqty>2; +select sum(qty) as sqty from t1 group by id having count(id) > 0; +select sum(qty) as sqty from t1 group by id having count(distinct id) > 0; drop table t1; -- cgit v1.2.1 From 179b1b68422ca100e9ffc6ef6f47cb1d287b1a17 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 11:39:07 +0300 Subject: Removed timing of tests from mysql-test-run. Needed by QNX, and there was no real use for timing. --- mysql-test/mysql-test-run.sh | 28 +++++----------------------- 1 file changed, 5 insertions(+), 23 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 65c960d13fa..40726b776e8 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -110,7 +110,6 @@ GREP=grep if test $? != 0; then exit 1; fi PRINTF=printf RM=rm -TIME=`which time` if test $? != 0; then exit 1; fi TR=tr XARGS=`which xargs` @@ -409,7 +408,7 @@ fi if test ${COLUMNS:-0} -lt 80 ; then COLUMNS=80 ; fi E=`$EXPR $COLUMNS - 8` -DASH72=`$ECHO '------------------------------------------------------------------------'|$CUT -c 1-$E` +DASH72=`$ECHO '------------------------------------------'|$CUT -c 1-$E` # on source dist, we pick up freshly build executables # on binary, use what is installed @@ -588,9 +587,8 @@ skip_test() { USERT=" ...." SYST=" ...." REALT=" ...." - timestr="$USERT $SYST $REALT" pname=`$ECHO "$1 "|$CUT -c 1-24` - RES="$pname $timestr" + RES="$pname" skip_inc $ECHO "$RES$RES_SPACE [ skipped ]" } @@ -1217,31 +1215,15 @@ run_testcase () $RM -f r/$tname.*reject mysql_test_args="-R r/$tname.result $EXTRA_MYSQL_TEST_OPT" if [ -z "$DO_CLIENT_GDB" ] ; then - mytime=`$TIME -p $MYSQL_TEST $mysql_test_args < $tf 2> $TIMEFILE` + `$MYSQL_TEST $mysql_test_args < $tf 2> $TIMEFILE`; else do_gdb_test "$mysql_test_args" "$tf" fi res=$? - if [ $res = 0 ]; then - mytime=`$CAT $TIMEFILE | $TAIL -3 | $TR '\n' ':'` - - USERT=`$ECHO $mytime | $CUT -d : -f 2 | $CUT -d ' ' -f 2` - USERT=`prefix_to_8 $USERT` - SYST=`$ECHO $mytime | $CUT -d : -f 3 | $CUT -d ' ' -f 2` - SYST=`prefix_to_8 $SYST` - REALT=`$ECHO $mytime | $CUT -d : -f 1 | $CUT -d ' ' -f 2` - REALT=`prefix_to_8 $REALT` - else - USERT=" ...." - SYST=" ...." - REALT=" ...." - fi - - timestr="$USERT $SYST $REALT" pname=`$ECHO "$tname "|$CUT -c 1-24` - RES="$pname $timestr" + RES="$pname" if [ x$many_slaves = x1 ] ; then stop_slave 1 @@ -1373,7 +1355,7 @@ then fi $ECHO -$ECHO " TEST USER SYSTEM ELAPSED RESULT" +$ECHO " TEST RESULT" $ECHO $DASH72 if [ -z "$1" ] ; -- cgit v1.2.1 From dc1e55f8194db83e7a40dea5bf49e9fef970826c Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 18:58:26 +0300 Subject: Fix for UNION and LEFT JOIN (Bug #386) Fixed wrong logging of Access denied error (Bug #398) include/my_global.h: Fix for QNX mysql-test/r/union.result: new test case mysql-test/t/union.test: Test of bug in union and left join mysys/my_seek.c: Safety fix to find out when pos gets a wrong value sql/field.h: Fix for UNION and LEFT JOIN sql/mysql_priv.h: Fix for UNION and LEFT JOIN sql/sql_base.cc: Fix for UNION and LEFT JOIN sql/sql_insert.cc: Fix for UNION and LEFT JOIN sql/sql_parse.cc: Fixed wrong logging of Access denied error sql/sql_union.cc: Fix for UNION and LEFT JOIN sql/sql_update.cc: Fix for UNION and LEFT JOIN --- mysql-test/r/union.result | 59 ++++++++++++++++++++++++++++++++++++++++++++- mysql-test/t/union.test | 61 ++++++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 118 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 2af9d5a3584..970e83c6752 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5,t6; CREATE TABLE t1 (a int not null, b char (10) not null); insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); @@ -202,3 +202,60 @@ a 11 set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; +CREATE TABLE t1 ( +cid smallint(5) unsigned NOT NULL default '0', +cv varchar(250) NOT NULL default '', +PRIMARY KEY (cid), +UNIQUE KEY cv (cv) +) ; +INSERT INTO t1 VALUES (8,'dummy'); +CREATE TABLE t2 ( +cid bigint(20) unsigned NOT NULL auto_increment, +cap varchar(255) NOT NULL default '', +PRIMARY KEY (cid), +KEY cap (cap) +) ; +CREATE TABLE t3 ( +gid bigint(20) unsigned NOT NULL auto_increment, +gn varchar(255) NOT NULL default '', +must tinyint(4) default NULL, +PRIMARY KEY (gid), +KEY gn (gn) +) ; +INSERT INTO t3 VALUES (1,'V1',NULL); +CREATE TABLE t4 ( +uid bigint(20) unsigned NOT NULL default '0', +gid bigint(20) unsigned default NULL, +rid bigint(20) unsigned default NULL, +cid bigint(20) unsigned default NULL, +UNIQUE KEY m (uid,gid,rid,cid), +KEY uid (uid), +KEY rid (rid), +KEY cid (cid), +KEY container (gid,rid,cid) +) ; +INSERT INTO t4 VALUES (1,1,NULL,NULL); +CREATE TABLE t5 ( +rid bigint(20) unsigned NOT NULL auto_increment, +rl varchar(255) NOT NULL default '', +PRIMARY KEY (rid), +KEY rl (rl) +) ; +CREATE TABLE t6 ( +uid bigint(20) unsigned NOT NULL auto_increment, +un varchar(250) NOT NULL default '', +uc smallint(5) unsigned NOT NULL default '0', +PRIMARY KEY (uid), +UNIQUE KEY nc (un,uc), +KEY un (un) +) ; +INSERT INTO t6 VALUES (1,'test',8); +SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"; +uid rl g1 cid gg +1 NULL V1 NULL 1 +SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"; +uid rl g1 cid gg +(SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"); +uid rl g1 cid gg +1 NULL V1 NULL 1 +drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 6e6b43ebe3b..7262b8f78d7 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -2,7 +2,7 @@ # Test of unions # -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5,t6; CREATE TABLE t1 (a int not null, b char (10) not null); insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); @@ -98,3 +98,62 @@ set SQL_SELECT_LIMIT=2; (select * from t1 limit 1) union (select * from t2 limit 3); set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; + +# +# Test error with left join +# + +CREATE TABLE t1 ( + cid smallint(5) unsigned NOT NULL default '0', + cv varchar(250) NOT NULL default '', + PRIMARY KEY (cid), + UNIQUE KEY cv (cv) +) ; +INSERT INTO t1 VALUES (8,'dummy'); +CREATE TABLE t2 ( + cid bigint(20) unsigned NOT NULL auto_increment, + cap varchar(255) NOT NULL default '', + PRIMARY KEY (cid), + KEY cap (cap) +) ; +CREATE TABLE t3 ( + gid bigint(20) unsigned NOT NULL auto_increment, + gn varchar(255) NOT NULL default '', + must tinyint(4) default NULL, + PRIMARY KEY (gid), + KEY gn (gn) +) ; +INSERT INTO t3 VALUES (1,'V1',NULL); +CREATE TABLE t4 ( + uid bigint(20) unsigned NOT NULL default '0', + gid bigint(20) unsigned default NULL, + rid bigint(20) unsigned default NULL, + cid bigint(20) unsigned default NULL, + UNIQUE KEY m (uid,gid,rid,cid), + KEY uid (uid), + KEY rid (rid), + KEY cid (cid), + KEY container (gid,rid,cid) +) ; +INSERT INTO t4 VALUES (1,1,NULL,NULL); +CREATE TABLE t5 ( + rid bigint(20) unsigned NOT NULL auto_increment, + rl varchar(255) NOT NULL default '', + PRIMARY KEY (rid), + KEY rl (rl) +) ; +CREATE TABLE t6 ( + uid bigint(20) unsigned NOT NULL auto_increment, + un varchar(250) NOT NULL default '', + uc smallint(5) unsigned NOT NULL default '0', + PRIMARY KEY (uid), + UNIQUE KEY nc (un,uc), + KEY un (un) +) ; +INSERT INTO t6 VALUES (1,'test',8); + +SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"; +SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"; +(SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"); + +drop table t1,t2,t3,t4,t5,t6; -- cgit v1.2.1 From d05cd28c10bdc94783074ebaba40a3ed1fabe6a6 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 19:26:07 +0300 Subject: Fixed problem with ansi mode and GROUP BY with constants. (Bug #387) sql/sql_select.cc: Fixed problem with ansi mode and GROUP BY with constants --- mysql-test/r/ansi.result | 10 ++++++++++ mysql-test/t/ansi-master.opt | 1 + mysql-test/t/ansi.test | 17 +++++++++++++++++ 3 files changed, 28 insertions(+) create mode 100644 mysql-test/r/ansi.result create mode 100644 mysql-test/t/ansi-master.opt create mode 100644 mysql-test/t/ansi.test (limited to 'mysql-test') diff --git a/mysql-test/r/ansi.result b/mysql-test/r/ansi.result new file mode 100644 index 00000000000..f9f96310b73 --- /dev/null +++ b/mysql-test/r/ansi.result @@ -0,0 +1,10 @@ +drop table if exists t1; +SELECT 'A' || 'B'; +'A' || 'B' +AB +CREATE TABLE t1 (id INT, id2 int); +SELECT id,NULL,1,1.1,'a' FROM t1 GROUP BY id; +id NULL 1 1.1 a +SELECT id FROM t1 GROUP BY id2; +'t1.id' isn't in GROUP BY +drop table t1; diff --git a/mysql-test/t/ansi-master.opt b/mysql-test/t/ansi-master.opt new file mode 100644 index 00000000000..6bf7a4f30e2 --- /dev/null +++ b/mysql-test/t/ansi-master.opt @@ -0,0 +1 @@ +--ansi diff --git a/mysql-test/t/ansi.test b/mysql-test/t/ansi.test new file mode 100644 index 00000000000..e1ac8ffd4f9 --- /dev/null +++ b/mysql-test/t/ansi.test @@ -0,0 +1,17 @@ +# +# Test of ansi mode +# + +drop table if exists t1; + +# Test some functions that works different in ansi mode + +SELECT 'A' || 'B'; + +# Test GROUP BY behaviour + +CREATE TABLE t1 (id INT, id2 int); +SELECT id,NULL,1,1.1,'a' FROM t1 GROUP BY id; +--error 1055 +SELECT id FROM t1 GROUP BY id2; +drop table t1; -- cgit v1.2.1 From 496357a18069730b97b303d40d91a9ac3ec3783f Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 19:34:51 +0300 Subject: - Fixed a bug in myisam_max_[extra]_sort_file_size, bug ID 339 and 342 - Fix for QNX: UNIX sockets available since 6.2.1 include/my_global.h: Fix for QNX: UNIX sockets available since 6.2.1 mysql-test/r/variables.result: Fixed a bug in myisam_max_[extra]_sort_file_size, bug ID 339 mysql-test/t/variables.test: Fixed a bug in myisam_max_[extra]_sort_file_size, bug ID 339 sql/mysqld.cc: Fixed a bug in myisam_max_[extra]_sort_file_size, bug ID 339 sql/set_var.cc: Fixed a bug in myisam_max_[extra]_sort_file_size, bug ID 339 sql/set_var.h: Fixed a bug in myisam_max_[extra]_sort_file_size, bug ID 339 --- mysql-test/r/variables.result | 19 ++++++++++--------- mysql-test/t/variables.test | 13 ++++++++----- 2 files changed, 18 insertions(+), 14 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 1cc48d2aeac..8a6791004ee 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -108,17 +108,14 @@ show global variables like 'table_type'; Variable_name Value table_type INNODB set GLOBAL query_cache_size=100000; -set myisam_max_sort_file_size=10000, GLOBAL myisam_max_sort_file_size=20000; -show variables like 'myisam_max_sort_file_size'; -Variable_name Value -myisam_max_sort_file_size 10000 +set GLOBAL myisam_max_sort_file_size=2000000; show global variables like 'myisam_max_sort_file_size'; Variable_name Value -myisam_max_sort_file_size 20000 -set myisam_max_sort_file_size=default; +myisam_max_sort_file_size 1048576 +set GLOBAL myisam_max_sort_file_size=default; show variables like 'myisam_max_sort_file_size'; Variable_name Value -myisam_max_sort_file_size 20000 +myisam_max_sort_file_size 2147483647 set global net_retry_count=10, session net_retry_count=10; set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; @@ -211,6 +208,10 @@ set @@global.sql_auto_is_null=1; Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL select @@global.sql_auto_is_null; Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL +set myisam_max_sort_file_size=100; +Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL +set myisam_max_extra_sort_file_size=100; +Variable 'myisam_max_extra_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL set autocommit=1; set big_tables=1; select @@autocommit, @@big_tables; @@ -264,11 +265,11 @@ select @@max_user_connections; @@max_user_connections 100 set global max_write_lock_count=100; -set myisam_max_extra_sort_file_size=100; +set global myisam_max_extra_sort_file_size=100; select @@myisam_max_extra_sort_file_size; @@myisam_max_extra_sort_file_size 100 -set myisam_max_sort_file_size=100; +set global myisam_max_sort_file_size=100; set myisam_sort_buffer_size=100; set net_buffer_length=100; set net_read_timeout=100; diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 639a28edd38..c730f317fcb 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -69,10 +69,9 @@ show local variables like 'table_type'; show global variables like 'table_type'; set GLOBAL query_cache_size=100000; -set myisam_max_sort_file_size=10000, GLOBAL myisam_max_sort_file_size=20000; -show variables like 'myisam_max_sort_file_size'; +set GLOBAL myisam_max_sort_file_size=2000000; show global variables like 'myisam_max_sort_file_size'; -set myisam_max_sort_file_size=default; +set GLOBAL myisam_max_sort_file_size=default; show variables like 'myisam_max_sort_file_size'; set global net_retry_count=10, session net_retry_count=10; @@ -137,6 +136,10 @@ set @@concurrent_insert=1; set @@global.sql_auto_is_null=1; --error 1228 select @@global.sql_auto_is_null; +--error 1229 +set myisam_max_sort_file_size=100; +--error 1229 +set myisam_max_extra_sort_file_size=100; # Test setting all variables @@ -181,9 +184,9 @@ set max_tmp_tables=100; set global max_user_connections=100; select @@max_user_connections; set global max_write_lock_count=100; -set myisam_max_extra_sort_file_size=100; +set global myisam_max_extra_sort_file_size=100; select @@myisam_max_extra_sort_file_size; -set myisam_max_sort_file_size=100; +set global myisam_max_sort_file_size=100; set myisam_sort_buffer_size=100; set net_buffer_length=100; set net_read_timeout=100; -- cgit v1.2.1 From d4ae4f0ec0e43f395722dbd1153938c66658b037 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 20:07:43 +0300 Subject: Fixed 'Unknown error' when using 'UPDATE ... LIMIT'. Bug 373 mysql-test/r/update.result: new test mysql-test/t/update.test: Test of UPDATE ... LIMIT sql/sql_update.cc: Fixed 'Unknown error' when using 'UPDATE ... LIMIT' --- mysql-test/r/update.result | 12 ++++++++++++ mysql-test/t/update.test | 17 +++++++++++++++++ 2 files changed, 29 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 86647e845a9..5357e8367e3 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -138,3 +138,15 @@ a b 111 100 111 100 drop table t1; +CREATE TABLE t1 ( +`id_param` smallint(3) unsigned NOT NULL default '0', +`nom_option` char(40) NOT NULL default '', +`valid` tinyint(1) NOT NULL default '0', +KEY `id_param` (`id_param`,`nom_option`) +) TYPE=MyISAM; +INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1); +UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1; +select * from t1; +id_param nom_option valid +185 test 1 +drop table t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 359ae815197..6ac8543ec93 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -91,3 +91,20 @@ update t1 set b=100 where a=1 order by b desc limit 2; update t1 set a=a+10+b where a=1 order by b; select * from t1 order by a,b; drop table t1; + +# +# Test with limit (Bug #393) +# + +CREATE TABLE t1 ( + `id_param` smallint(3) unsigned NOT NULL default '0', + `nom_option` char(40) NOT NULL default '', + `valid` tinyint(1) NOT NULL default '0', + KEY `id_param` (`id_param`,`nom_option`) + ) TYPE=MyISAM; + +INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1); + +UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1; +select * from t1; +drop table t1; -- cgit v1.2.1 From 563c32ccd1f5a212dfb270097c1dbd1cdc1be907 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 19:16:30 +0200 Subject: Fix of bug 390: primary key now implies (silently) NOT NULL for key fields. --- mysql-test/r/create.result | 13 +++++++++---- mysql-test/r/key.result | 1 - mysql-test/t/create.test | 12 ++++++++++-- mysql-test/t/key.test | 4 ++-- 4 files changed, 21 insertions(+), 9 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index a2ab0e97905..2e2aa41c671 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -25,13 +25,9 @@ create table t1 (a int not null auto_increment,primary key (a)) type=heap; The used table type doesn't support AUTO_INCREMENT columns create table t1 (a int not null,b text) type=heap; The used table type doesn't support BLOB/TEXT columns -create table t1 (a int ,primary key(a)) type=heap; -All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead drop table if exists t1; create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) type=heap; The used table type doesn't support AUTO_INCREMENT columns -create table t1 (ordid int(8), primary key (ordid)); -All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead create table not_existing_database.test (a int); Got one of the listed errors create table `a/a` (a int); @@ -171,3 +167,12 @@ t1 CREATE TABLE `t1` ( ) TYPE=MyISAM SET SESSION table_type=default; drop table t1; +create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2)); +insert into t1 values ("a", 1), ("b", 2); +insert into t1 values ("c", NULL); +Column 'k2' cannot be null +insert into t1 values (NULL, 3); +Column 'k1' cannot be null +insert into t1 values (NULL, NULL); +Column 'k1' cannot be null +drop table t1; diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 1cd9c9dfe79..31d35a681aa 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -42,7 +42,6 @@ price area type transityes shopsyes schoolsyes petsyes drop table t1; CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program)); ALTER TABLE t1 modify program enum('signup','unique','sliding'); -All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead drop table t1; CREATE TABLE t1 ( name varchar(50) DEFAULT '' NOT NULL, diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 68d68929f07..86c3f6be0f5 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -24,11 +24,9 @@ drop table if exists t1,t2; !$1167 create table t1 (b char(0) not null, index(b)); !$1164 create table t1 (a int not null auto_increment,primary key (a)) type=heap; !$1163 create table t1 (a int not null,b text) type=heap; -!$1171 create table t1 (a int ,primary key(a)) type=heap; drop table if exists t1; !$1164 create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) type=heap; -!$1171 create table t1 (ordid int(8), primary key (ordid)); -- error 1044,1 create table not_existing_database.test (a int); @@ -119,3 +117,13 @@ show create table t1; SET SESSION table_type=default; drop table t1; + +# +# ISO requires that primary keys are implicitly NOT NULL +# +create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2)); +insert into t1 values ("a", 1), ("b", 2); +!$1048 insert into t1 values ("c", NULL); +!$1048 insert into t1 values (NULL, 3); +!$1048 insert into t1 values (NULL, NULL); +drop table t1; diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index 1996c793880..4c9b6479ac2 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -54,12 +54,12 @@ INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); drop table t1; # -# problem med primary key +# No longer a problem with primary key # CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program)); # The following should give an error for wrong primary key -!$1171 ALTER TABLE t1 modify program enum('signup','unique','sliding'); +ALTER TABLE t1 modify program enum('signup','unique','sliding'); drop table t1; # -- cgit v1.2.1 From a147307f426ef6c93b94e4b62fe14bf004585fa9 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 19:19:57 +0200 Subject: Forgot to correct comment in key.test (after fixing bug 390). mysql-test/t/key.test: Forgot to correct comment (after fixing bug 390). --- mysql-test/t/key.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index 4c9b6479ac2..cb80d88aee8 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -58,7 +58,7 @@ drop table t1; # CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program)); -# The following should give an error for wrong primary key +# This no longer give an error for wrong primary key ALTER TABLE t1 modify program enum('signup','unique','sliding'); drop table t1; -- cgit v1.2.1 From 7a96b137ef140d2e6d372927c33ca0aecb058ffa Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 19:30:07 +0200 Subject: - disabled all "EXPLAIN SELECT" tests from the InnoDB test because of nodeterministic results on some platforms --- mysql-test/r/innodb.result | 12 ------------ mysql-test/t/innodb.test | 8 ++++---- 2 files changed, 4 insertions(+), 16 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index b3c7c137a6f..e2dea324ff2 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -138,15 +138,6 @@ id parent_id level 1008 102 2 1010 102 2 1015 102 2 -explain select level from t1 where level=1; -table type possible_keys key key_len ref rows Extra -t1 ref level level 1 const 12 Using where; Using index -explain select level,id from t1 where level=1; -table type possible_keys key key_len ref rows Extra -t1 ref level level 1 const 12 Using where; Using index -explain select level,id,parent_id from t1 where level=1; -table type possible_keys key key_len ref rows Extra -t1 ref level level 1 const 12 Using where select level,id from t1 where level=1; level id 1 1002 @@ -595,9 +586,6 @@ id parent_id level 1009 102 2 1025 102 2 1016 102 2 -explain select level from t1 where level=1; -table type possible_keys key key_len ref rows Extra -t1 ref level level 1 const 6 Using where; Using index select level,id from t1 where level=1; level id 1 1004 diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index ae1f7dae922..dc3c76f1a91 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -40,9 +40,9 @@ update ignore t1 set id=id+1; # This will change all rows select * from t1; update ignore t1 set id=1023 where id=1010; select * from t1 where parent_id=102; -explain select level from t1 where level=1; -explain select level,id from t1 where level=1; -explain select level,id,parent_id from t1 where level=1; +# explain select level from t1 where level=1; +# explain select level,id from t1 where level=1; +# explain select level,id,parent_id from t1 where level=1; select level,id from t1 where level=1; select level,id,parent_id from t1 where level=1; optimize table t1; @@ -339,7 +339,7 @@ update ignore t1 set id=id+1; # This will change all rows select * from t1; update ignore t1 set id=1023 where id=1010; select * from t1 where parent_id=102; -explain select level from t1 where level=1; +# explain select level from t1 where level=1; select level,id from t1 where level=1; select level,id,parent_id from t1 where level=1; select level,id from t1 where level=1 order by id; -- cgit v1.2.1 From 32450d159b685660add0d2f68bc9d3a24b6bfb52 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 22:28:34 +0300 Subject: Fixed a bug in DROP DATABASE when database had a RAID type table. Bug ID 381. --- mysql-test/r/raid.result | 3 +++ mysql-test/t/raid.test | 3 +++ 2 files changed, 6 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/raid.result b/mysql-test/r/raid.result index 41af50851e9..fd47a9451f6 100644 --- a/mysql-test/r/raid.result +++ b/mysql-test/r/raid.result @@ -1,3 +1,6 @@ +create database test_raid; +create table test_raid.r1 (i int) raid_type=1; +drop database test_raid; DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 ( id int unsigned not null auto_increment primary key, diff --git a/mysql-test/t/raid.test b/mysql-test/t/raid.test index 8b608c1069f..4dbaf84a836 100644 --- a/mysql-test/t/raid.test +++ b/mysql-test/t/raid.test @@ -5,6 +5,9 @@ show variables like "have_raid"; # Test of raided tables # +create database test_raid; +create table test_raid.r1 (i int) raid_type=1; +drop database test_raid; DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 ( id int unsigned not null auto_increment primary key, -- cgit v1.2.1 From 13a23a879f3f2e9b3d09578e08802e6fe19f88f4 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 14 May 2003 01:27:26 +0300 Subject: Fix for MacOSX and symlinks Fix for USE_FRM and crashed index file configure.in: Fix for MacOSX and symlinks myisam/mi_open.c: Give better error message in case of of crashed index file mysql-test/r/repair.result: new test case mysql-test/r/update.result: new test case mysql-test/t/repair.test: Added test with crashed MyISAM index header mysql-test/t/update.test: Added test case from bugs system sql/handler.cc: Indentation changes sql/sql_table.cc: Fix for USE_FRM and crashed index file --- mysql-test/r/repair.result | 3 ++- mysql-test/r/repair_part2.result | 8 ++++++++ mysql-test/r/update.result | 16 ++++++++++++++++ mysql-test/t/repair.test | 4 ++++ mysql-test/t/repair_part2-master.sh | 1 + mysql-test/t/repair_part2.test | 7 +++++++ mysql-test/t/update.test | 15 +++++++++++++++ 7 files changed, 53 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/repair_part2.result create mode 100644 mysql-test/t/repair_part2-master.sh create mode 100644 mysql-test/t/repair_part2.test (limited to 'mysql-test') diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result index ad869787b8a..6c2107b2cf3 100644 --- a/mysql-test/r/repair.result +++ b/mysql-test/r/repair.result @@ -11,4 +11,5 @@ test.t1 repair error The handler for the table doesn't support repair drop table t1; repair table t1 use_frm; Table Op Msg_type Msg_text -t1 repair error table is read-only or does not exists +test.t1 repair error Table 'test.t1' doesn't exist +create table t1 type=myisam SELECT 1,"table 1"; diff --git a/mysql-test/r/repair_part2.result b/mysql-test/r/repair_part2.result new file mode 100644 index 00000000000..77aa98c3da9 --- /dev/null +++ b/mysql-test/r/repair_part2.result @@ -0,0 +1,8 @@ +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair error Can't open file: 't1.MYI'. (errno: 130) +repair table t1 use_frm; +Table Op Msg_type Msg_text +test.t1 repair warning Number of rows changed from 0 to 1 +test.t1 repair status OK +drop table t1; diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 5357e8367e3..11aff8fe50a 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -150,3 +150,19 @@ select * from t1; id_param nom_option valid 185 test 1 drop table t1; +create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid)); +insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6), +('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2), +('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4), +('2','2','0',1,7); +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; +F1 F2 F3 cnt groupid +0 0 0 1 6 +0 1 2 1 5 +0 2 0 1 3 +1 0 1 1 2 +1 2 1 1 1 +2 0 1 2 4 +2 2 0 1 7 +drop table t1; diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test index e7da79d8c9e..159fc090653 100644 --- a/mysql-test/t/repair.test +++ b/mysql-test/t/repair.test @@ -12,4 +12,8 @@ drop table t1; # non-existent table repair table t1 use_frm; +# +# Create test table for repair2 +# The following must be last in this file +create table t1 type=myisam SELECT 1,"table 1"; diff --git a/mysql-test/t/repair_part2-master.sh b/mysql-test/t/repair_part2-master.sh new file mode 100644 index 00000000000..964bde06c18 --- /dev/null +++ b/mysql-test/t/repair_part2-master.sh @@ -0,0 +1 @@ +echo "1" > $MYSQL_TEST_DIR/var/master-data/test/t1.MYI diff --git a/mysql-test/t/repair_part2.test b/mysql-test/t/repair_part2.test new file mode 100644 index 00000000000..8c27e382dff --- /dev/null +++ b/mysql-test/t/repair_part2.test @@ -0,0 +1,7 @@ +# +# This test starts with a crashed t1.MYI file left over from repair.test +# + +repair table t1; +repair table t1 use_frm; +drop table t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 6ac8543ec93..a455b308158 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -108,3 +108,18 @@ INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1); UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1; select * from t1; drop table t1; + +# +# Multi table update test from bugs +# + +create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid)); + +insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6), +('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2), +('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4), +('2','2','0',1,7); + +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; -- cgit v1.2.1 From ac316cfdb33bd4974a90c240c7a14ecb6a29da34 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 14 May 2003 22:12:55 +0300 Subject: Fixed bug in CREATE ... DECIMAL(-1,1). Bug #432 mysql-test/mysql-test-run.sh: Ensure that tests are sorted mysql-test/r/type_decimal.result: Test for bug with decimal() mysql-test/t/type_decimal.test: Test for bug with decimal() sql/sql_parse.cc: Fixed bug in CREATE ... DECIMAL(-1,1) --- mysql-test/mysql-test-run.sh | 3 ++- mysql-test/r/type_decimal.result | 6 ++++++ mysql-test/t/type_decimal.test | 10 ++++++++++ 3 files changed, 18 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 40726b776e8..7ce6ffe14af 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -114,6 +114,7 @@ if test $? != 0; then exit 1; fi TR=tr XARGS=`which xargs` if test $? != 0; then exit 1; fi +SORT=sort # Are we using a source or a binary distribution? @@ -1363,7 +1364,7 @@ then if [ x$RECORD = x1 ]; then $ECHO "Will not run in record mode without a specific test case." else - for tf in $TESTDIR/*.$TESTSUFFIX + for tf in `ls -1 $TESTDIR/*.$TESTSUFFIX | $SORT` do run_testcase $tf done diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index 4c326957c03..b74765696a2 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -357,3 +357,9 @@ select * from t1; a 99999999999 drop table t1; +CREATE TABLE t1 (a_dec DECIMAL(-1,0)); +Too big column length for column 'a_dec' (max = 255). Use BLOB instead +CREATE TABLE t1 (a_dec DECIMAL(-2,1)); +Too big column length for column 'a_dec' (max = 255). Use BLOB instead +CREATE TABLE t1 (a_dec DECIMAL(-1,1)); +Too big column length for column 'a_dec' (max = 255). Use BLOB instead diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 7d5d719592c..7aedc051905 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -230,3 +230,13 @@ insert into t1 values("1e4294967297"); select * from t1; drop table t1; +# +# Test of wrong decimal type +# + +--error 1074 +CREATE TABLE t1 (a_dec DECIMAL(-1,0)); +--error 1074 +CREATE TABLE t1 (a_dec DECIMAL(-2,1)); +--error 1074 +CREATE TABLE t1 (a_dec DECIMAL(-1,1)); -- cgit v1.2.1 From 43d371409589e835779a6236050ce92bb7344dae Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 14 May 2003 23:06:06 +0300 Subject: Portability fix for 64 bit machines --- mysql-test/r/variables.result | 2 +- mysql-test/t/variables.test | 1 + 2 files changed, 2 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 8a6791004ee..7c054f55acd 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -115,7 +115,7 @@ myisam_max_sort_file_size 1048576 set GLOBAL myisam_max_sort_file_size=default; show variables like 'myisam_max_sort_file_size'; Variable_name Value -myisam_max_sort_file_size 2147483647 +myisam_max_sort_file_size FILE_SIZE set global net_retry_count=10, session net_retry_count=10; set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index c730f317fcb..fa39906fd6a 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -72,6 +72,7 @@ set GLOBAL query_cache_size=100000; set GLOBAL myisam_max_sort_file_size=2000000; show global variables like 'myisam_max_sort_file_size'; set GLOBAL myisam_max_sort_file_size=default; +--replace_result 2147483647 FILE_SIZE 9223372036854775807 FILE_SIZE show variables like 'myisam_max_sort_file_size'; set global net_retry_count=10, session net_retry_count=10; -- cgit v1.2.1