~drizzle-trunk/drizzle/development

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;

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

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

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

eval CREATE TABLE t1 (

id int NOT NULL auto_increment,

parent_id int DEFAULT '0' NOT NULL,

level int DEFAULT '0' NOT NULL,

id int(11) NOT NULL auto_increment,

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

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

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

121

122

123

eval CREATE TABLE t1 (

124

gesuchnr int DEFAULT '0' NOT NULL,

125

benutzer_id int DEFAULT '0' NOT NULL,

124

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

125

benutzer_id int(11) 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 DEFAULT '0' NOT NULL,

318

user_id int(10) 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 NOT NULL auto_increment,

383

id int(11) 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 DEFAULT '0' NOT NULL,

420

quota bigint,

419

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

420

quota bigint(20),

421

weight double,

422

access_date date,

423

access_time time,

424

approved datetime,

425

dummy_primary_key int NOT NULL auto_increment,

425

dummy_primary_key int(11) 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 NOT NULL auto_increment,

442

parent_id int DEFAULT '0' NOT NULL,

443

level int DEFAULT '0' NOT NULL,

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,

444

KEY (id),

445

KEY parent_id (parent_id),

446

KEY level (level)

534

535

536

eval CREATE TABLE t1 (

537

a int NOT NULL,

538

b int NOT NULL,

537

a int3 unsigned NOT NULL,

538

b int1 unsigned NOT NULL,

539

UNIQUE (a, b)

540

) ENGINE = $engine_type;

541

547

# Test INSERT DELAYED

548

549

550

eval CREATE TABLE t1 (a int NOT NULL) engine=$engine_type;

550

eval CREATE TABLE t1 (a int unsigned NOT NULL) engine=$engine_type;

551

# Can't test this in 3.23

552

# INSERT DELAYED INTO t1 VALUES (1);

553

INSERT INTO t1 VALUES (1);

625

) engine=$engine_type;

626

insert into t1 values (1,'one','one value',101),

627

(2,'two','two value',102),(3,'three','three value',103);

628

set insert_id=5;

628

629

replace into t1 (value,name,uid) values ('other value','two',102);

629

630

delete from t1 where uid=102;

631

set insert_id=5;

630

632

replace into t1 (value,name,uid) values ('other value','two',102);

633

set insert_id=6;

631

634

replace into t1 (value,name,uid) values ('other value','two',102);

632

635

select * from t1;

633

636

drop table t1;

720

723

721

724

722

725

eval CREATE TABLE t1 (

723

number bigint NOT NULL default '0',

726

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

724

727

cname char(15) NOT NULL default '',

725

carrier_id int NOT NULL default '0',

726

privacy int NOT NULL default '0',

728

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

729

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

727

730

last_mod_date timestamp NOT NULL,

728

last_mod_id int NOT NULL default '0',

731

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

729

732

last_app_date timestamp NOT NULL,

730

last_app_id int default '-1',

731

version int NOT NULL default '0',

732

assigned_scps int default '0',

733

status int default '0'

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'

734

737

) ENGINE=$engine_type;

735

738

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

736

739

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

739

742

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

740

743

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

741

744

eval CREATE TABLE t2 (

742

number bigint NOT NULL default '0',

745

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

743

746

cname char(15) NOT NULL default '',

744

carrier_id int NOT NULL default '0',

745

privacy int NOT NULL default '0',

747

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

748

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

746

749

last_mod_date timestamp NOT NULL,

747

last_mod_id int NOT NULL default '0',

750

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

748

751

last_app_date timestamp NOT NULL,

749

last_app_id int default '-1',

750

version int NOT NULL default '0',

751

assigned_scps int default '0',

752

status int default '0'

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'

753

756

) ENGINE=$engine_type;

754

757

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

755

758

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

769

772

# this works.

770

773

771

774

772

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;

775

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

773

776

774

777

BEGIN;

775

778

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

794

797

795

798

# Test of multi-table-update

796

799

797

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

798

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

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;

799

802

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

800

803

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

801

804

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

1044

1047

# Test of multi updated and foreign keys

1045

1048

1046

1049

1047

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

1050

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

1048

1051

insert into `t1`values ( 1 ) ;

1049

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;

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;

1050

1053

insert into `t2`values ( 1 ) ;

1051

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;

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;

1052

1055

insert into `t3`values ( 1 ) ;

1053

1056

--error 1451

1054

1057

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

1081

1084

# Test timestamps

1082

1085

1083

1086

1084

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

1085

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

1087

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

1088

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

1086

1089

(stamp))ENGINE=$engine_type;

1087

1090

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

1088

1091

# Note that timestamp 3 is wrong

1089

--error 1292

1090

1092

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

1091

1093

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

1092

1094

'20020204120000' GROUP BY col1;

1097

1099

1098

1100

1099

1101

eval CREATE TABLE t1 (

1100

`id` int NOT NULL auto_increment,

1101

`id_object` int default '0',

1102

`id_version` int NOT NULL default '1',

1102

`id` int(10) unsigned NOT NULL auto_increment,

1103

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

1104

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

1103

1105

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

1104

1106

`description` text,

1105

1107

PRIMARY KEY (`id`),

1110

1112

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);

1111

1113

1112

1114

eval CREATE TABLE t2 (

1113

`id` int NOT NULL auto_increment,

1114

`id_version` int NOT NULL default '1',

1115

`id` int(10) unsigned NOT NULL auto_increment,

1116

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

1115

1117

PRIMARY KEY (`id`),

1116

1118

KEY `id_version` (`id_version`)

1117

1119

) ENGINE=$engine_type;

1173

1175

# Test of automaticly created foreign keys

1174

1176

1175

1177

1176

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

1177

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

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;

1178

1180

show create table t1;

1179

1181

show create table t2;

1180

1182

create index id on t2 (id);

1187

1189

show create table t2;

1188

1190

drop table t2;

1189

1191

1190

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;

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;

1191

1193

show create table t2;

1192

1194

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

1193

1195

show create table t2;

1194

1196

drop table t2;

1195

1197

1196

1198

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

1197

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;

1198

show create table t2;

1199

drop table t2;

1200

1201

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;

1202

show create table t2;

1203

drop table t2;

1204

1205

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;

1206

show create table t2;

1207

drop table t2;

1208

1209

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;

1210

show create table t2;

1211

drop table t2;

1212

1213

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;

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;

1214

1216

show create table t2;

1215

1217

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

1216

1218

show create table t2;

1220

1222

1221

1223

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

1222

1224

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

1223

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

1225

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

1224

1226

--error 1005

1225

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;

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;

1226

1228

1227

1229

# bug#3749

1228

1230

1297

1299

# Bug #4082: integer truncation

1298

1300

1299

1301

1300

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

1302

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

1301

1303

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

1302

1304

select distinct concat(a, b) from t1;

1303

1305

drop table t1;

1322

1324

1323

1325

1324

1326

--disable_warnings

1325

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

1327

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

1326

1328

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

1327

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),

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),

1328

1330

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;

1329

1331

--enable_warnings

1330

1332

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

1377

1379

# range optimizer problem

1378

1380

1379

1381

1380

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

1381

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

1382

--error 1264

1382

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

1383

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

1384

select * from t1;

1385

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

1467

1468

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

1469

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

1470

# @TODO The below fails because it assumes latin1

1471

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

1472

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

1473

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

1474

#drop table t1;

1475

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

1476

#show create table t1;

1477

#drop table t1;

1478

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

1479

#show create table t1;

1480

#drop table t1;

1481

1482

#eval set storage_engine=$default;

1483

1470

--replace_result \\ / $MYSQL_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

1484

1482

# InnoDB specific varchar tests

1485

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

1486

#drop table t1;

1483

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

1484

drop table t1;

1487

1485

1488

1486

1489

1487

# BUG#11039 Wrong key length in min()

1499

1497

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

1500

1498

1501

1499

1502

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

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;

1503

1501

insert into t1 (b) values (1);

1504

1502

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

1505

1503

select * from t1;

1983

1981

DROP TABLE t2,t1;

1984

1982

}

1985

1983

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

1986

2075

if ($test_foreign_keys)

1987

2076

{

1988

2077

Older »