diff options
author | unknown <monty@hundin.mysql.fi> | 2002-01-31 04:36:58 +0200 |
---|---|---|
committer | unknown <monty@hundin.mysql.fi> | 2002-01-31 04:36:58 +0200 |
commit | 83c83a0223664b2937e9522afb47e7c78e6b425f (patch) | |
tree | f5fa2405fe86664ded2345a4330fe4b8a31e8666 /mysql-test | |
parent | 85278245af45fc301dc7940bec9a3434003285a6 (diff) | |
download | mariadb-git-83c83a0223664b2937e9522afb47e7c78e6b425f.tar.gz |
Fixes for new getopt
Bug fix when using --no-deaults
Sets ref_length to right value (faster rnd_pos() handling in InnoDB).
Fixed problem with multi-table-delete and InnoDB
Fixed problem with truncate table, which required a COMMIT before.
Fixed multi-table-delete bug with InnoDB.
Remove not used index from EXPLAIN
Docs/manual.texi:
ChangeLog
include/my_getopt.h:
Removed compiler warnings
myisam/myisamchk.c:
Fixes for new getopt
mysql-test/r/explain.result:
Updated test results
mysql-test/r/innodb.result:
Updated test results
mysql-test/r/join.result:
Updated test results
mysql-test/t/innodb.test:
Updated test results
mysys/default.c:
Bug fix when using --no-defaults
mysys/my_getopt.c:
Fixes for new getopt
sql/ha_innodb.cc:
Sets ref_length to right value (faster rnd_pos() handling).
Fixed problem with multi-table-delete.
Fixed problem with truncate table, which required a COMMIT before.
sql/opt_sum.cc:
Remove not used index from EXPLAIN
sql/sql_cache.cc:
Fixed core dump bug when not using query cache
sql/sql_select.cc:
Remove not used index from EXPLAIN
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/r/explain.result | 2 | ||||
-rw-r--r-- | mysql-test/r/innodb.result | 88 | ||||
-rw-r--r-- | mysql-test/r/join.result | 72 | ||||
-rw-r--r-- | mysql-test/t/innodb.test | 15 |
4 files changed, 169 insertions, 8 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 681baea5e07..5b4da25d535 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -16,7 +16,7 @@ table type possible_keys key key_len ref rows Extra t1 const str str 11 const 1 explain select * from t1 ignore key (str) where str="foo"; table type possible_keys key key_len ref rows Extra -t1 ALL str NULL NULL NULL 4 where used +t1 ALL NULL NULL NULL NULL 4 where used explain select * from t1 use key (str,str) where str="foo"; table type possible_keys key key_len ref rows Extra t1 const str str 11 const 1 diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 1a48b6d6294..73f3dcf4899 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -168,9 +168,9 @@ Table Op Msg_type Msg_text test.t1 optimize error The handler for the table doesn't support check/repair 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 PRIMARY 1 id A 87 NULL NULL BTREE -t1 1 parent_id 1 parent_id A 21 NULL NULL BTREE -t1 1 level 1 level A 4 NULL NULL BTREE +t1 0 PRIMARY 1 id A NULL NULL NULL BTREE +t1 1 parent_id 1 parent_id A NULL NULL NULL BTREE +t1 1 level 1 level A NULL NULL NULL BTREE drop table t1; CREATE TABLE t1 ( gesuchnr int(11) DEFAULT '0' NOT NULL, @@ -211,7 +211,7 @@ Table Op Msg_type Msg_text test.t1 analyze error The handler for the table doesn't support check/repair 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 1 NULL NULL YES BTREE +t1 1 skr 1 a A NULL NULL NULL YES BTREE drop table t1; create table t1 (a int,b varchar(20),key(a)) type=innodb; insert into t1 values (1,""), (2,"testing"); @@ -727,7 +727,7 @@ Table Op Msg_type Msg_text test.t1 optimize error The handler for the table doesn't support check/repair 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 PRIMARY 1 a A 1 NULL NULL BTREE +t1 0 PRIMARY 1 a A NULL NULL NULL BTREE drop table t1; create table t1 (i int, j int ) TYPE=innodb; insert into t1 values (1,2); @@ -859,6 +859,7 @@ commit; drop database mysqltest; show tables from mysqltest; Got one of the listed errors +set autocommit=0; create table t1 (a int not null) type= innodb; insert into t1 values(1),(2); truncate table t1; @@ -873,6 +874,21 @@ select * from t1; a commit; drop table t1; +set autocommit=1; +create table t1 (a int not null) type= innodb; +insert into t1 values(1),(2); +truncate table t1; +insert into t1 values(1),(2); +select * from t1; +a +1 +2 +truncate table t1; +insert into t1 values(1),(2); +delete from t1; +select * from t1; +a +drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=innodb; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); explain select * from t1 order by a; @@ -911,3 +927,65 @@ Unknown table 't1' in HANDLER handler t1 close; Unknown table 't1' in HANDLER drop table t1; +CREATE TABLE t1 ( +number bigint(20) NOT NULL default '0', +cname char(15) NOT NULL default '', +carrier_id smallint(6) NOT NULL default '0', +privacy tinyint(4) NOT NULL default '0', +last_mod_date timestamp(14) NOT NULL, +last_mod_id smallint(6) NOT NULL default '0', +last_app_date timestamp(14) NOT NULL, +last_app_id smallint(6) default '-1', +version smallint(6) NOT NULL default '0', +assigned_scps int(11) default '0', +status tinyint(4) default '0' +) TYPE=InnoDB; +INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1); +INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0); +INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1); +INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0); +INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0); +INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0); +CREATE TABLE t2 ( +number bigint(20) NOT NULL default '0', +cname char(15) NOT NULL default '', +carrier_id smallint(6) NOT NULL default '0', +privacy tinyint(4) NOT NULL default '0', +last_mod_date timestamp(14) NOT NULL, +last_mod_id smallint(6) NOT NULL default '0', +last_app_date timestamp(14) NOT NULL, +last_app_id smallint(6) default '-1', +version smallint(6) NOT NULL default '0', +assigned_scps int(11) default '0', +status tinyint(4) default '0' +) TYPE=InnoDB; +INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1); +INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0); +INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1); +INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0); +select * from t1; +number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status +4077711111 SeanWheeler 90 2 20020111112846 500 00000000000000 -1 2 3 1 +9197722223 berry 90 3 20020111112809 500 20020102114532 501 4 10 0 +650 San Francisco 0 0 20011227111336 342 00000000000000 -1 1 24 1 +302467 Sue's Subshop 90 3 20020109113241 500 20020102115111 501 7 24 0 +6014911113 SudzCarwash 520 1 20020102115234 500 20020102115259 501 33 32768 0 +333 tubs 99 2 20020109113440 501 20020109113440 500 3 10 0 +select * from t2; +number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status +4077711111 SeanWheeler 0 2 20020111112853 500 00000000000000 -1 2 3 1 +9197722223 berry 90 3 20020111112818 500 20020102114532 501 4 10 0 +650 San Francisco 90 0 20020109113158 342 00000000000000 -1 1 24 1 +333 tubs 99 2 20020109113453 501 20020109113453 500 3 10 0 +delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null); +select * from t1; +number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status +6014911113 SudzCarwash 520 1 20020102115234 500 20020102115259 501 33 32768 0 +333 tubs 99 2 20020109113440 501 20020109113440 500 3 10 0 +select * from t2; +number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status +333 tubs 99 2 20020109113453 501 20020109113453 500 3 10 0 +select * from t2; +number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status +333 tubs 99 2 20020109113453 501 20020109113453 500 3 10 0 +drop table t1,t2; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 011465d7553..5069fbaf527 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2; +drop table if exists t1,t2,t3; create table t1 (id int primary key); create table t2 (id int); insert into t1 values (75); @@ -116,3 +116,73 @@ SELECT * from t1 WHERE t1.d IS NULL; d 0000-00-00 DROP TABLE t1,t2; +CREATE TABLE t1 ( +Document_ID varchar(50) NOT NULL default '', +Contractor_ID varchar(6) NOT NULL default '', +Language_ID char(3) NOT NULL default '', +Expiration_Date datetime default NULL, +Publishing_Date datetime default NULL, +Title text, +Column_ID varchar(50) NOT NULL default '', +PRIMARY KEY (Language_ID,Document_ID,Contractor_ID) +); +INSERT INTO t1 VALUES ('xep80','1','ger','2001-12-31 20:00:00','2001-11-12 10:58:00','Kartenbestellung - jetzt auch online','anle'),('','999998','',NULL,NULL,NULL,''); +CREATE TABLE t2 ( +Contractor_ID char(6) NOT NULL default '', +Language_ID char(3) NOT NULL default '', +Document_ID char(50) NOT NULL default '', +CanRead char(1) default NULL, +Customer_ID int(11) NOT NULL default '0', +PRIMARY KEY (Contractor_ID,Language_ID,Document_ID,Customer_ID) +); +INSERT INTO t2 VALUES ('5','ger','xep80','1',999999),('1','ger','xep80','1',999999); +CREATE TABLE t3 ( +Language_ID char(3) NOT NULL default '', +Column_ID char(50) NOT NULL default '', +Contractor_ID char(6) NOT NULL default '', +CanRead char(1) default NULL, +Active char(1) default NULL, +PRIMARY KEY (Language_ID,Column_ID,Contractor_ID) +); +INSERT INTO t3 VALUES ('ger','home','1','1','1'),('ger','Test','1','0','0'),('ger','derclu','1','0','0'),('ger','clubne','1','0','0'),('ger','philos','1','0','0'),('ger','clubko','1','0','0'),('ger','clubim','1','1','1'),('ger','progra','1','0','0'),('ger','progvo','1','0','0'),('ger','progsp','1','0','0'),('ger','progau','1','0','0'),('ger','progku','1','0','0'),('ger','progss','1','0','0'),('ger','nachl','1','0','0'),('ger','mitgli','1','0','0'),('ger','mitsu','1','0','0'),('ger','mitbus','1','0','0'),('ger','ergmar','1','1','1'),('ger','home','4','1','1'),('ger','derclu','4','1','1'),('ger','clubne','4','0','0'),('ger','philos','4','1','1'),('ger','clubko','4','1','1'),('ger','clubim','4','1','1'),('ger','progra','4','1','1'),('ger','progvo','4','1','1'),('ger','progsp','4','1','1'),('ger','progau','4','0','0'),('ger','progku','4','1','1'),('ger','progss','4','1','1'),('ger','nachl','4','1','1'),('ger','mitgli','4','0','0'),('ger','mitsu','4','0','0'),('ger','mitbus','4','0','0'),('ger','ergmar','4','1','1'),('ger','progra2','1','0','0'),('ger','archiv','4','1','1'),('ger','anmeld','4','1','1'),('ger','thema','4','1','1'),('ger','edito','4','1','1'),('ger','madis','4','1','1'),('ger','enma','4','1','1'),('ger','madis','1','1','1'),('ger','enma','1','1','1'),('ger','vorsch','4','0','0'),('ger','veranst','4','0','0'),('ger','anle','4','1','1'),('ger','redak','4','1','1'),('ger','nele','4','1','1'),('ger','aukt','4','1','1'),('ger','callcenter','4','1','1'),('ger','anle','1','0','0'); +delete from t1 where Contractor_ID='999998'; +insert into t1 (Contractor_ID) Values ('999998'); +SELECT DISTINCT COUNT(t1.Title) FROM t1, +t2, t3 WHERE +t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND +t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= +Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND +t1.Document_ID = t2.Document_ID AND +t1.Language_ID = t2.Language_ID AND +t1.Contractor_ID = t2.Contractor_ID AND ( +t2.Customer_ID = '4' OR +t2.Customer_ID = '999999' OR +t2.Customer_ID = '1' )AND t2.CanRead += '1' AND t1.Column_ID=t3.Column_ID AND +t1.Language_ID=t3.Language_ID AND ( +t3.Contractor_ID = '4' OR +t3.Contractor_ID = '999999' OR +t3.Contractor_ID = '1') AND +t3.CanRead='1' AND t3.Active='1'; +COUNT(t1.Title) +1 +SELECT DISTINCT COUNT(t1.Title) FROM t1, +t2, t3 WHERE +t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND +t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= +Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND +t1.Document_ID = t2.Document_ID AND +t1.Language_ID = t2.Language_ID AND +t1.Contractor_ID = t2.Contractor_ID AND ( +t2.Customer_ID = '4' OR +t2.Customer_ID = '999999' OR +t2.Customer_ID = '1' )AND t2.CanRead += '1' AND t1.Column_ID=t3.Column_ID AND +t1.Language_ID=t3.Language_ID AND ( +t3.Contractor_ID = '4' OR +t3.Contractor_ID = '999999' OR +t3.Contractor_ID = '1') AND +t3.CanRead='1' AND t3.Active='1'; +COUNT(t1.Title) +1 +drop table t1,t2,t3; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index a26049fcd83..1b7ee99e15a 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -532,9 +532,10 @@ drop database mysqltest; show tables from mysqltest; # -# Test truncate table +# Test truncate table with and without auto_commit # +set autocommit=0; create table t1 (a int not null) type= innodb; insert into t1 values(1),(2); --error 1192 @@ -547,6 +548,18 @@ delete from t1; select * from t1; commit; drop table t1; +set autocommit=1; + +create table t1 (a int not null) type= innodb; +insert into t1 values(1),(2); +truncate table t1; +insert into t1 values(1),(2); +select * from t1; +truncate table t1; +insert into t1 values(1),(2); +delete from t1; +select * from t1; +drop table t1; # # Test of how ORDER BY works when doing it on the whole table |