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 ++++++++--- 1 file changed, 8 insertions(+), 3 deletions(-) (limited to 'mysql-test/r') 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"; -- 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 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') 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, -- 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 +++++ 1 file changed, 5 insertions(+) (limited to 'mysql-test/r') 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 -- 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 +++++++ 1 file changed, 7 insertions(+) (limited to 'mysql-test/r') 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; -- 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 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test/r') 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; -- 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 ++++++++ 1 file changed, 8 insertions(+) (limited to 'mysql-test/r') 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; -- 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 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/r') 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; -- 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 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) (limited to 'mysql-test/r') 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); -- 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 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/r') 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; -- 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 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/r') 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 -- 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 +++++++++++++++++++++++++++++++++++++++++- 1 file changed, 42 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') 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; -- 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 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'mysql-test/r') 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; -- 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 +++++++++- 1 file changed, 9 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') 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; -- 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/r') 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/r') 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 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 116 insertions(+) (limited to 'mysql-test/r') 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; -- 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 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/r') 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 -- 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/r') 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 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'mysql-test/r') 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; -- 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 ++++++++++++++++++---- 6 files changed, 136 insertions(+), 43 deletions(-) (limited to 'mysql-test/r') 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; -- cgit v1.2.1 From 81a83ca8b667f16c7fd40e666ac26c251b2b87de Mon Sep 17 00:00:00 2001 From: unknown 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 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') 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, -- 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 ++++++++++++++++++++++++++-- 1 file changed, 26 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') 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; -- 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/r') 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 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) (limited to 'mysql-test/r') 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; -- 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 ------ 4 files changed, 96 insertions(+), 18 deletions(-) create mode 100644 mysql-test/r/ctype_ujis.result (limited to 'mysql-test/r') 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 -- 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 ++++++++-------- 4 files changed, 76 insertions(+), 12 deletions(-) (limited to 'mysql-test/r') 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; -- 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 ------ 1 file changed, 6 deletions(-) (limited to 'mysql-test/r') 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 -- 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 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/r') 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); -- 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 --- 1 file changed, 3 deletions(-) (limited to 'mysql-test/r') 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 -- 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 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/r') 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 -- 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 +++++++++++++++++++++++----------- 1 file changed, 23 insertions(+), 11 deletions(-) (limited to 'mysql-test/r') 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); -- 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/r') 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 +++++++++++ 1 file changed, 11 insertions(+) create mode 100644 mysql-test/r/loaddata.result (limited to 'mysql-test/r') 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; -- 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 ++++++++ 2 files changed, 11 insertions(+) (limited to 'mysql-test/r') 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; -- 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 ++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 58 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') 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; -- 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 ++++++++++ 1 file changed, 10 insertions(+) create mode 100644 mysql-test/r/ansi.result (limited to 'mysql-test/r') 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; -- 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 ++++++++++--------- 1 file changed, 10 insertions(+), 9 deletions(-) (limited to 'mysql-test/r') 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; -- 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 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'mysql-test/r') 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; -- 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 - 2 files changed, 9 insertions(+), 5 deletions(-) (limited to 'mysql-test/r') 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, -- 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 ------------ 1 file changed, 12 deletions(-) (limited to 'mysql-test/r') 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 -- 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 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/r') 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, -- 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 ++++++++++++++++ 3 files changed, 26 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/repair_part2.result (limited to 'mysql-test/r') 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; -- 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/r/type_decimal.result | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test/r') 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 -- 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 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') 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; -- cgit v1.2.1 From f72611b4fa7eb45259b26d75a733f7a29d20dc04 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 21 May 2003 21:39:58 +0300 Subject: After merge fixes Added initialization of all important global variables BUILD/SETUP.sh: build with readline client/mysqltest.c: Added variable SERVER_VERSION myisam/mi_key.c: Indentation change myisam/mi_open.c: After merge fix myisam/mi_range.c: After merge fix myisam/mi_rkey.c: After merge fix myisam/mi_search.c: After merge fix myisam/myisamdef.h: After merge fix mysql-test/include/not_embedded.inc: Fix test (because of wrong utf8 test) mysql-test/r/alter_table.result: Updated results after merge mysql-test/r/create.result: Updated results after merge mysql-test/r/ctype_recoding.result: Updated results after merge mysql-test/r/fulltext.result: Updated results after merge mysql-test/r/func_group.result: Updated results after merge mysql-test/r/group_by.result: Updated results after merge mysql-test/r/innodb.result: Updated results after merge mysql-test/r/join_outer.result: Updated results after merge mysql-test/r/null_key.result: Updated results after merge mysql-test/r/order_by.result: Updated results after merge mysql-test/r/query_cache.result: Updated results after merge mysql-test/r/repair.result: Updated results after merge mysql-test/r/rpl_flush_tables.result: Updated results after merge mysql-test/r/union.result: Updated results after merge mysql-test/r/update.result: Updated results after merge mysql-test/t/ansi.test: After merge fixes mysql-test/t/create.test: After merge fixes mysql-test/t/ctype_recoding.test: After merge fixes mysql-test/t/ctype_ujis.test: After merge fixes mysql-test/t/fulltext.test: After merge fixes mysql-test/t/innodb.test: After merge fixes mysql-test/t/join_outer.test: After merge fixes mysql-test/t/loaddata.test: After merge fixes mysql-test/t/order_by.test: After merge fixes mysql-test/t/rpl_flush_tables.test: After merge fixes mysql-test/t/status.test: After merge fixes mysql-test/t/subselect.test: After merge fixes sql/convert.cc: Code cleanup sql/field.cc: After merge fixes sql/filesort.cc: Remove compiler warning sql/item.cc: More efficient set_name() (no mallocs) sql/item_cmpfunc.cc: Code Code cleanup Item_bool_func2::fix_fields() added to get error handling right for cmp_charset sql/item_cmpfunc.h: New prototypes sql/item_func.cc: After merge fix sql/item_strfunc.cc: Faster check for BINARY sql/log_event.cc: Comment cleanup sql/mysql_priv.h: New prototypes and variables sql/mysqld.cc: Added initialization of all important global variables. Cleanup of variable declarations This is needed ot make the embedded version restartable sql/opt_sum.cc: After merge fix sql/set_var.cc: Code cleanup sql/sql_acl.cc: After merge fix Better error message sql/sql_db.cc: After merge fix sql/sql_derived.cc: After merge fix sql/sql_insert.cc: Indentation cleanups sql/sql_list.h: Added empty() to base_ilist sql/sql_parse.cc: After merge fix sql/sql_select.cc: After merge fix Fixed derived name handling in EXPLAIN sql/sql_show.cc: After merge fix sql/sql_string.cc: Made copy_and_convert global sql/sql_string.h: Made copy_and_convert global sql/sql_update.cc: After merge fix sql/sql_yacc.yy: After merge fix sql/thr_malloc.cc: Added sql_strmake_with_convert() sql/unireg.h: Added MAX_ALIAS_NAME strings/ctype-ujis.c: Fixed bug in converting to ujis --- mysql-test/r/alter_table.result | 4 +- mysql-test/r/create.result | 6 ++- mysql-test/r/ctype_recoding.result | 1 + mysql-test/r/fulltext.result | 1 - mysql-test/r/func_group.result | 94 ++++++++++++++++-------------------- mysql-test/r/group_by.result | 16 +++--- mysql-test/r/innodb.result | 12 +++-- mysql-test/r/join_outer.result | 1 - mysql-test/r/null_key.result | 2 +- mysql-test/r/order_by.result | 4 +- mysql-test/r/query_cache.result | 20 ++++---- mysql-test/r/repair.result | 2 +- mysql-test/r/rpl_flush_tables.result | 4 +- mysql-test/r/union.result | 37 +++++++------- mysql-test/r/update.result | 5 +- 15 files changed, 101 insertions(+), 108 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 1fe874a211c..ec2f7220a3d 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -158,8 +158,8 @@ alter table t2 rename t1, add c char(10) comment "no comment"; show columns from t1; Field Type Collation Null Key Default Extra i int(10) unsigned binary PRI NULL auto_increment -c char(10) latin1_swedish_ci YES NULL -drop table t1; +c char(10) latin1_swedish_ci 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); diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 520227c0fba..4e06c724142 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -183,18 +183,20 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0' -) TYPE=HEAP +) TYPE=HEAP CHARSET=latin1 drop table t1; SET SESSION table_type="gemini"; SELECT @@table_type; @@table_type GEMINI CREATE TABLE t1 (a int not null); +Warnings: +Warning 1259 Using storage engine MYISAM for table 't1' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 SET SESSION table_type=default; drop table t1; create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2)); diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result index fb813e394f4..815445150cd 100644 --- a/mysql-test/r/ctype_recoding.result +++ b/mysql-test/r/ctype_recoding.result @@ -41,3 +41,4 @@ Field Type Collation Null Key Default Extra поле char(32) character set koi8r koi8r_general_ci SET CHARACTER SET koi8r; DROP TABLE ÔÁÂÌÉÃÁ; +SET CHARACTER SET default; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index d87b00dd4d7..5b410396390 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -216,7 +216,6 @@ 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); diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index e9fb56a77d6..99d7bd2d087 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -42,22 +42,12 @@ insert into t1 values (null,null,''); select count(distinct a),count(distinct grp) from t1; count(distinct a) count(distinct grp) 6 3 -select sum(all a),count(all a),avg(all a),std(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; -sum(all a) count(all a) avg(all a) std(all a) bit_or(all a) bit_and(all a) min(all a) max(all a) min(all c) max(all c) -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 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 -select grp, sum(a)+count(a)+avg(a)+std(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp; select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; sum(all a) count(all a) avg(all a) std(all a) variance(all a) bit_or(all a) bit_and(all a) min(all a) max(all a) min(all c) max(all c) 21 6 3.5000 1.7078 2.9167 7 0 1 6 E select grp, sum(a),count(a),avg(a),std(a),variance(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) variance(a) bit_or(a) bit_and(a) min(a) max(a) min(c) max(c) -NULL 0 0 NULL NULL NULL 0 0 NULL NULL +NULL NULL 0 NULL NULL NULL 0 0 NULL NULL 1 1 1 1.0000 0.0000 0.0000 1 1 1 1 a a 2 5 2 2.5000 0.5000 0.2500 3 2 2 3 b c 3 15 3 5.0000 0.8165 0.6667 7 4 4 6 C E @@ -231,47 +221,6 @@ select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; max(t1.a2) max(t2.a1) zzz BBB 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; create table t1( a1 char(3) primary key, a2 smallint, @@ -613,3 +562,44 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 3 NULL 14 Using index 1 SIMPLE t2 index NULL k2 4 NULL 6 Using where; Using index 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 18446744073709551615 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 18446744073709551615 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 18446744073709551615 0 +2 0 NULL NULL NULL NULL NULL 18446744073709551615 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 18446744073709551615 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 18446744073709551615 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/r/group_by.result b/mysql-test/r/group_by.result index 5a5689f0185..2f9a55e0c42 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -54,9 +54,9 @@ userid MIN(t1.score+0.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 eq_ref PRIMARY PRIMARY 4 t1.userID 1 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 t1.userID 1 Using index drop table t1,t2; CREATE TABLE t1 ( PID int(10) unsigned NOT NULL auto_increment, @@ -253,10 +253,10 @@ key (score) 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort explain select userid,count(*) from t1 group by userid desc order by null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary select userid,count(*) from t1 group by userid desc; userid count(*) 3 5 @@ -269,13 +269,13 @@ select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*)); userid count(*) explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range spID spID 5 NULL 2 Using where; Using index +1 SIMPLE 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range spID spID 5 NULL 2 Using where; Using index +1 SIMPLE 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range spID spID 5 NULL 2 Using where; Using index +1 SIMPLE 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 diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 922f4816e90..98613dad628 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1209,7 +1209,8 @@ 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 +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back SELECT * FROM t1; B_ID drop table t1,t2; @@ -1247,12 +1248,12 @@ select count(*) from t1; count(*) 29267 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 +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE 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 +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE 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); @@ -1286,6 +1287,7 @@ a b 13 2 111 100 111 100 +drop table t1; CREATE TABLE t1 (col1 int(1))TYPE=InnoDB; CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP(+0),INDEX stamp_idx (stamp))TYPE=InnoDB; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 1412c5e7d10..123de82fe09 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -646,7 +646,6 @@ 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); diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 3248d359546..c55f29a4e78 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -84,7 +84,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 12 Using where +1 SIMPLE 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index a8a6dd6312d..0cfe1534252 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -549,10 +549,10 @@ KEY FieldKey (FieldKey), KEY LongField (FieldKey,LongVal), KEY StringField (FieldKey,StringVal(32)) ); -INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); +INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 36 const 2 Using where +1 SIMPLE t1 ref FieldKey,LongField,StringField StringField 36 const 3 Using where; Using filesort SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; FieldKey LongVal StringVal 1 0 2 diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 48a2a8a5a21..1d46d1dcf25 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -382,14 +382,14 @@ a set CHARACTER SET cp1251_koi8; select * from t1; a -? +á set CHARACTER SET DEFAULT; show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 2 +Qcache_queries_in_cache 1 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 4 +Qcache_hits 5 drop table t1; create database if not exists mysqltest; create table mysqltest.t1 (i int not null); @@ -415,7 +415,7 @@ Variable_name Value Qcache_queries_in_cache 2 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 6 +Qcache_hits 7 drop database mysqltest; drop table t1; create table t1 (i int not null); @@ -429,7 +429,7 @@ FOUND_ROWS() 4 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 6 +Qcache_hits 7 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 @@ -441,7 +441,7 @@ FOUND_ROWS() 1 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 6 +Qcache_hits 7 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 @@ -454,7 +454,7 @@ FOUND_ROWS() 4 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 7 +Qcache_hits 8 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 @@ -466,7 +466,7 @@ FOUND_ROWS() 1 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 8 +Qcache_hits 9 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 @@ -535,7 +535,7 @@ a 3 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 11 +Qcache_hits 12 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 @@ -552,7 +552,7 @@ a 3 show status like "Qcache_hits"; Variable_name Value -Qcache_hits 12 +Qcache_hits 13 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result index 6c2107b2cf3..eb46622dcc1 100644 --- a/mysql-test/r/repair.result +++ b/mysql-test/r/repair.result @@ -7,7 +7,7 @@ test.t1 repair status OK 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 +test.t1 repair error The storage enginge for the table doesn't support repair drop table t1; repair table t1 use_frm; Table Op Msg_type Msg_text diff --git a/mysql-test/r/rpl_flush_tables.result b/mysql-test/r/rpl_flush_tables.result index 7eb3b77758e..6353a6101d6 100644 --- a/mysql-test/r/rpl_flush_tables.result +++ b/mysql-test/r/rpl_flush_tables.result @@ -14,7 +14,7 @@ rename table t1 to t5, t2 to t1; flush no_write_to_binlog tables; show binlog events; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.000001 4 Start 1 4 Server ver: 4.1.1-alpha-debug-log, Binlog ver: 3 +master-bin.000001 4 Start 1 4 Server ver: SERVER_VERSION, Binlog ver: 3 master-bin.000001 79 Query 1 79 use `test`; create table t1 (a int) master-bin.000001 137 Query 1 137 use `test`; insert into t1 values (10) master-bin.000001 198 Query 1 198 use `test`; create table t2 (a int) @@ -27,7 +27,7 @@ a flush tables; show binlog events; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.000001 4 Start 1 4 Server ver: 4.1.1-alpha-debug-log, Binlog ver: 3 +master-bin.000001 4 Start 1 4 Server ver: SERVER_VERSION, Binlog ver: 3 master-bin.000001 79 Query 1 79 use `test`; create table t1 (a int) master-bin.000001 137 Query 1 137 use `test`; insert into t1 values (10) master-bin.000001 198 Query 1 198 use `test`; create table t2 (a int) diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index d506891b0fb..272ffdd330e 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -90,9 +90,6 @@ explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a l id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 2 UNION t2 ALL NULL NULL NULL NULL 4 Using filesort -select found_rows(); -found_rows() -0 explain select a,b from t1 union all select a,b from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 @@ -132,6 +129,23 @@ create table t3 select a,b from t1 union all select a,b from t2; insert into t3 select a,b from t1 union all select a,b from t2; replace into t3 select a,b as c from t1 union all select a,b from t2; drop table t1,t2,t3; +select * union select 1; +No tables used +select 1 as a,(select a union select a); +a (select a union select a) +1 1 +(select 1) union (select 2) order by 0; +Unknown column '0' in 'order clause' +SELECT @a:=1 UNION SELECT @a:=@a+1; +@a:=1 +1 +2 +(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a); +Unknown column 'a' in 'field list' +(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2); +1 3 +1 3 +2 1 CREATE TABLE t1 ( `pseudo` char(35) NOT NULL default '', `pseudo1` char(35) NOT NULL default '', @@ -255,11 +269,6 @@ uid rl g1 cid gg uid rl g1 cid gg 1 NULL V1 NULL 1 drop table t1,t2,t3,t4,t5,t6; -select * union select 1; -No tables used -select 1 as a,(select a union select a); -a (select a union select a) -1 1 CREATE TABLE t1 ( id int(3) unsigned default '0') TYPE=MyISAM; INSERT INTO t1 (id) VALUES("1"); CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) TYPE=MyISAM; @@ -286,10 +295,6 @@ id_master id text1 text2 1 3 NULL bar3 1 4 foo4 bar4 drop table if exists t1,t2; -(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2); -1 3 -1 3 -2 1 create table t1 (a int not null primary key auto_increment, b int, key(b)); create table t2 (a int not null primary key auto_increment, b int); insert into t1 (b) values (1),(2),(2),(3); @@ -323,11 +328,3 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 UNION t1 ref b b 5 const 1 Using where drop table t1,t2; -(select 1) union (select 2) order by 0; -Unknown column '0' in 'order clause' -SELECT @a:=1 UNION SELECT @a:=@a+1; -@a:=1 -1 -2 -(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a); -Unknown column 'a' in 'field list' diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index c308b8fcbcd..1d483da2c77 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -137,7 +137,10 @@ a b 13 2 111 100 111 100 -drop table t1; +create table t2 (a int not null, b int not null); +insert into t2 values (1,1),(1,2),(1,3); +update t1 set b=(select distinct 1 from (select * from t2) a); +drop table t1,t2; CREATE TABLE t1 ( `id_param` smallint(3) unsigned NOT NULL default '0', `nom_option` char(40) NOT NULL default '', -- cgit v1.2.1