summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
authorunknown <monty@hundin.mysql.fi>2002-01-31 04:36:58 +0200
committerunknown <monty@hundin.mysql.fi>2002-01-31 04:36:58 +0200
commit83c83a0223664b2937e9522afb47e7c78e6b425f (patch)
treef5fa2405fe86664ded2345a4330fe4b8a31e8666 /mysql-test
parent85278245af45fc301dc7940bec9a3434003285a6 (diff)
downloadmariadb-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.result2
-rw-r--r--mysql-test/r/innodb.result88
-rw-r--r--mysql-test/r/join.result72
-rw-r--r--mysql-test/t/innodb.test15
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