~drizzle-trunk/drizzle/development : revision 642.1.40

66

drop database if exists mysqltest;

67

--enable_warnings

68

69

eval create table t1 (id int not null auto_increment, code tinyint not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

69

eval create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

70

71

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');

72

select id, code, name from t1 order by id;

85

#

86

87

eval CREATE TABLE t1 (

88

id int(11) NOT NULL auto_increment,

89

parent_id int(11) DEFAULT '0' NOT NULL,

90

level tinyint(4) DEFAULT '0' NOT NULL,

88

id int NOT NULL auto_increment,

89

parent_id int DEFAULT '0' NOT NULL,

90

level int DEFAULT '0' NOT NULL,

91

PRIMARY KEY (id),

92

KEY parent_id (parent_id),

93

KEY level (level)

121

#

122

123

eval CREATE TABLE t1 (

124

gesuchnr int(11) DEFAULT '0' NOT NULL,

125

benutzer_id int(11) DEFAULT '0' NOT NULL,

124

gesuchnr int DEFAULT '0' NOT NULL,

125

benutzer_id int DEFAULT '0' NOT NULL,

126

PRIMARY KEY (gesuchnr,benutzer_id)

127

) engine=$engine_type;

128

315

# Test when reading on part of unique key

316

#

317

eval CREATE TABLE t1 (

318

user_id int(10) DEFAULT '0' NOT NULL,

318

user_id int DEFAULT '0' NOT NULL,

319

name varchar(100),

320

phone varchar(100),

321

ref_email varchar(100) DEFAULT '' NOT NULL,

380

#

381

382

eval CREATE TABLE t1 (

383

id int(11) NOT NULL auto_increment,

383

id int NOT NULL auto_increment,

384

ggid varchar(32) binary DEFAULT '' NOT NULL,

385

email varchar(64) DEFAULT '' NOT NULL,

386

passwd varchar(32) binary DEFAULT '' NOT NULL,

416

user_name varchar(12),

417

password text,

418

subscribed char(1),

419

user_id int(11) DEFAULT '0' NOT NULL,

420

quota bigint(20),

419

user_id int DEFAULT '0' NOT NULL,

420

quota bigint,

421

weight double,

422

access_date date,

423

access_time time,

424

approved datetime,

425

dummy_primary_key int(11) NOT NULL auto_increment,

425

dummy_primary_key int NOT NULL auto_increment,

426

PRIMARY KEY (dummy_primary_key)

427

) ENGINE=$engine_type;

428

INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);

438

#

439

440

eval CREATE TABLE t1 (

441

id int(11) NOT NULL auto_increment,

442

parent_id int(11) DEFAULT '0' NOT NULL,

443

level tinyint(4) DEFAULT '0' NOT NULL,

441

id int NOT NULL auto_increment,

442

parent_id int DEFAULT '0' NOT NULL,

443

level int DEFAULT '0' NOT NULL,

444

KEY (id),

445

KEY parent_id (parent_id),

446

KEY level (level)

534

#

535

536

eval CREATE TABLE t1 (

537

a int3 NOT NULL,

538

b int1 NOT NULL,

537

a int NOT NULL,

538

b int NOT NULL,

539

UNIQUE (a, b)

540

) ENGINE = $engine_type;

541

723

#

724

725

eval CREATE TABLE t1 (

726

number bigint(20) NOT NULL default '0',

726

number bigint NOT NULL default '0',

727

cname char(15) NOT NULL default '',

728

carrier_id smallint(6) NOT NULL default '0',

729

privacy tinyint(4) NOT NULL default '0',

728

carrier_id int NOT NULL default '0',

729

privacy int NOT NULL default '0',

730

last_mod_date timestamp NOT NULL,

731

last_mod_id smallint(6) NOT NULL default '0',

731

last_mod_id int NOT NULL default '0',

732

last_app_date timestamp NOT NULL,

733

last_app_id smallint(6) default '-1',

734

version smallint(6) NOT NULL default '0',

735

assigned_scps int(11) default '0',

736

status tinyint(4) default '0'

733

last_app_id int default '-1',

734

version int NOT NULL default '0',

735

assigned_scps int default '0',

736

status int default '0'

737

) ENGINE=$engine_type;

738

INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);

739

INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);

742

INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);

743

INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);

744

eval CREATE TABLE t2 (

745

number bigint(20) NOT NULL default '0',

745

number bigint NOT NULL default '0',

746

cname char(15) NOT NULL default '',

747

carrier_id smallint(6) NOT NULL default '0',

748

privacy tinyint(4) NOT NULL default '0',

747

carrier_id int NOT NULL default '0',

748

privacy int NOT NULL default '0',

749

last_mod_date timestamp NOT NULL,

750

last_mod_id smallint(6) NOT NULL default '0',

750

last_mod_id int NOT NULL default '0',

751

last_app_date timestamp NOT NULL,

752

last_app_id smallint(6) default '-1',

753

version smallint(6) NOT NULL default '0',

754

assigned_scps int(11) default '0',

755

status tinyint(4) default '0'

752

last_app_id int default '-1',

753

version int NOT NULL default '0',

754

assigned_scps int default '0',

755

status int default '0'

756

) ENGINE=$engine_type;

757

INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);

758

INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);

772

# this works.

773

#

774

775

eval create table t1 (id int not null auto_increment, code tinyint not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

775

eval create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

776

777

BEGIN;

778

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

797

#

798

# Test of multi-table-update

799

#

800

eval create table t1 (n int(10), d int(10)) engine=$engine_type;

801

eval create table t2 (n int(10), d int(10)) engine=$engine_type;

800

eval create table t1 (n int, d int) engine=$engine_type;

801

eval create table t2 (n int, d int) engine=$engine_type;

802

insert into t1 values(1,1),(1,2);

803

insert into t2 values(1,10),(2,20);

804

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

1047

# Test of multi updated and foreign keys

1048

#

1049

1050

eval create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = $engine_type;

1050

eval create table `t1` (`id` int not null ,primary key ( `id` )) engine = $engine_type;

1051

insert into `t1`values ( 1 ) ;

1052

eval create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = $engine_type;

1052

eval create table `t2` (`id` int not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = $engine_type;

1053

insert into `t2`values ( 1 ) ;

1054

eval create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = $engine_type;

1054

eval create table `t3` (`id` int not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = $engine_type;

1055

insert into `t3`values ( 1 ) ;

1056

--error 1451

1057

delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

1084

# Test timestamps

1085

#

1086

1087

eval CREATE TABLE t1 (col1 int(1))ENGINE=$engine_type;

1088

eval CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx

1087

eval CREATE TABLE t1 (col1 int)ENGINE=$engine_type;

1088

eval CREATE TABLE t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx

1089

(stamp))ENGINE=$engine_type;

1090

insert into t1 values (1),(2),(3);

1091

# Note that timestamp 3 is wrong

1092

--error 1292

1092

1093

insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);

1093

1094

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <

1094

1095

'20020204120000' GROUP BY col1;

1099

1100

#

1100

1101

1102

eval CREATE TABLE t1 (

1102

`id` int(10) NOT NULL auto_increment,

1103

`id_object` int(10) default '0',

1104

`id_version` int(10) NOT NULL default '1',

1103

`id` int NOT NULL auto_increment,

1104

`id_object` int default '0',

1105

`id_version` int NOT NULL default '1',

1105

1106

`label` varchar(100) NOT NULL default '',

1106

1107

`description` text,

1107

1108

PRIMARY KEY (`id`),

1112

1113

INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);

1113

1114

1115

eval CREATE TABLE t2 (

1115

`id` int(10) NOT NULL auto_increment,

1116

`id_version` int(10) NOT NULL default '1',

1116

`id` int NOT NULL auto_increment,

1117

`id_version` int NOT NULL default '1',

1117

1118

PRIMARY KEY (`id`),

1118

1119

KEY `id_version` (`id_version`)

1119

1120

) ENGINE=$engine_type;

1175

1176

# Test of automaticly created foreign keys

1176

1177

#

1177

1178

eval create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=$engine_type;

1179

eval create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = $engine_type;

1179

eval create table t1 (id int not null, id2 int not null, unique (id,id2)) engine=$engine_type;

1180

eval create table t2 (id int not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = $engine_type;

1180

1181

show create table t1;

1181

1182

show create table t2;

1182

1183

create index id on t2 (id);

1189

1190

show create table t2;

1190

1191

drop table t2;

1191

1192

eval create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = $engine_type;

1193

eval create table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = $engine_type;

1193

1194

show create table t2;

1194

1195

create unique index id on t2 (id,id2);

1195

1196

show create table t2;

1196

1197

drop table t2;

1197

1198

1199

# Check foreign key columns created in different order than key columns

1199

eval create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1200

show create table t2;

1201

drop table t2;

1202

1203

eval create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = $engine_type;

1204

show create table t2;

1205

drop table t2;

1206

1207

eval create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1208

show create table t2;

1209

drop table t2;

1210

1211

eval create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = $engine_type;

1212

show create table t2;

1213

drop table t2;

1214

1215

eval create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= $engine_type;

1200

eval create table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1201

show create table t2;

1202

drop table t2;

1203

1204

eval create table t2 (id int not null, id2 int not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = $engine_type;

1205

show create table t2;

1206

drop table t2;

1207

1208

eval create table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1209

show create table t2;

1210

drop table t2;

1211

1212

eval create table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = $engine_type;

1213

show create table t2;

1214

drop table t2;

1215

1216

eval create table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= $engine_type;

1216

1217

show create table t2;

1217

1218

alter table t2 add index id_test (id), add index id_test2 (id,id2);

1218

1219

show create table t2;

1224

1225

# regular server reports relative path with .frm (argh!)

1225

1226

--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t2.frm t2

1226

1227

--error 1005

1227

eval create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = $engine_type;

1228

eval create table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = $engine_type;

1228

1229

1230

# bug#3749

1230

1231

1299

1300

# Bug #4082: integer truncation

1300

1301

#

1301

1302

eval create table t1(a int(1) , b int(1)) engine=$engine_type;

1303

eval create table t1(a int, b int) engine=$engine_type;

1303

1304

insert into t1 values ('1111', '3333');

1304

1305

select distinct concat(a, b) from t1;

1305

1306

drop table t1;

1324

1325

#

1325

1326

1327

--disable_warnings

1327

eval CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1328

eval CREATE TABLE t1 (a_id int NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1328

1329

INSERT INTO t1 VALUES (1),(2),(3);

1329

eval CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1330

eval CREATE TABLE t2 (b_id int NOT NULL default '0',b_a int NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1330

1331

CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1331

1332

--enable_warnings

1332

1333

INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);

1380

1381

#

1381

1382

1383

eval create table t1 (x bigint not null primary key) engine=$engine_type;

1384

# The below is out of range for a BIGINT (signed)

1385

--error 1264

1383

1386

insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);

1384

1387

select * from t1;

1385

1388

select count(*) from t1 where x>0;

1467

1470

1468

1471

# Clean up filename -- embedded server reports whole path without .frm,

1469

1472

# regular server reports relative path with .frm (argh!)

1470

--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t1.frm t1

1471

create table t1 (v varchar(65530), key(v));

1472

drop table t1;

1473

create table t1 (v varchar(65536));

1474

show create table t1;

1475

drop table t1;

1476

create table t1 (v varchar(65530) character set utf8);

1477

show create table t1;

1478

drop table t1;

1479

1480

eval set storage_engine=$default;

1481

1473

# @TODO The below fails because it assumes latin1

1474

# as the charset. Possibly re-enable a similar test

1475

#--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t1.frm t1

1476

#create table t1 (v varchar(65530), key(v));

1477

#drop table t1;

1478

#create table t1 (v varchar(65536));

1479

#show create table t1;

1480

#drop table t1;

1481

#create table t1 (v varchar(65530) character set utf8);

1482

#show create table t1;

1483

#drop table t1;

1484

#

1485

#eval set storage_engine=$default;

1486

#

1482

1487

# InnoDB specific varchar tests

1483

eval create table t1 (v varchar(16384)) engine=$engine_type;

1484

drop table t1;

1488

#eval create table t1 (v varchar(16384)) engine=$engine_type;

1489

#drop table t1;

1485

1490

1486

1491

#

1487

1492

# BUG#11039 Wrong key length in min()

1497

1502

# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error

1498

1503

#

1499

1504

1500

eval CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=$engine_type;

1505

eval CREATE TABLE t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=$engine_type;

1501

1506

insert into t1 (b) values (1);

1502

1507

replace into t1 (b) values (2), (1), (3);

1503

1508

select * from t1;

1981

1986

DROP TABLE t2,t1;

1982

1987

}

1983

1988

1984

#

1985

# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing

1986

#

1987

1988

connect (a,localhost,root,,);

1989

connect (b,localhost,root,,);

1990

connection a;

1991

eval create table t1(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

1992

insert into t1(a) values (1),(2),(3);

1993

commit;

1994

connection b;

1995

set autocommit = 0;

1996

update t1 set b = 5 where a = 2;

1997

connection a;

1998

delimiter |;

1999

create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |

2000

delimiter ;|

2001

set autocommit = 0;

2002

connection a;

2003

insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),

2004

(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),

2005

(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),

2006

(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),

2007

(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);

2008

connection b;

2009

commit;

2010

connection a;

2011

commit;

2012

drop trigger t1t;

2013

drop table t1;

2014

disconnect a;

2015

disconnect b;

2016

#

2017

# Another trigger test

2018

#

2019

connect (a,localhost,root,,);

2020

connect (b,localhost,root,,);

2021

connection a;

2022

eval create table t1(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2023

eval create table t2(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2024

eval create table t3(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2025

eval create table t4(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2026

eval create table t5(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2027

insert into t1(a) values (1),(2),(3);

2028

insert into t2(a) values (1),(2),(3);

2029

insert into t3(a) values (1),(2),(3);

2030

insert into t4(a) values (1),(2),(3);

2031

insert into t3(a) values (5),(7),(8);

2032

insert into t4(a) values (5),(7),(8);

2033

insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

2034

2035

delimiter |;

2036

create trigger t1t before insert on t1 for each row begin

2037

INSERT INTO t2 SET a = NEW.a;

2038

end |

2039

2040

create trigger t2t before insert on t2 for each row begin

2041

DELETE FROM t3 WHERE a = NEW.a;

2042

end |

2043

2044

create trigger t3t before delete on t3 for each row begin

2045

UPDATE t4 SET b = b + 1 WHERE a = OLD.a;

2046

end |

2047

2048

create trigger t4t before update on t4 for each row begin

2049

UPDATE t5 SET b = b + 1 where a = NEW.a;

2050

end |

2051

delimiter ;|

2052

commit;

2053

set autocommit = 0;

2054

update t1 set b = b + 5 where a = 1;

2055

update t2 set b = b + 5 where a = 1;

2056

update t3 set b = b + 5 where a = 1;

2057

update t4 set b = b + 5 where a = 1;

2058

insert into t5(a) values(20);

2059

connection b;

2060

set autocommit = 0;

2061

insert into t1(a) values(7);

2062

insert into t2(a) values(8);

2063

delete from t2 where a = 3;

2064

update t4 set b = b + 1 where a = 3;

2065

commit;

2066

drop trigger t1t;

2067

drop trigger t2t;

2068

drop trigger t3t;

2069

drop trigger t4t;

2070

drop table t1, t2, t3, t4, t5;

2071

connection default;

2072

disconnect a;

2073

disconnect b;

2074

2075

1989

if ($test_foreign_keys)

2076

1990

{

2077

1991

#