~drizzle-trunk/drizzle/development

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

649

select * from t1;

650

select * from t2;

651

select * from t2;

652

drop table t1,t2;

653

654

608

655

# A simple test with some isolation levels

609

656

# TODO: Make this into a test using replication to really test how

610

657

# this works.

633

680

DROP TABLE t1;

634

681

635

682

683

# Test of multi-table-update

684

685

create table t1 (n int, d int) engine=innodb;

686

create table t2 (n int, d int) engine=innodb;

687

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

688

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

689

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

690

select * from t1;

691

select * from t2;

692

drop table t1,t2;

693

694

636

695

# Testing of IFNULL

637

696

638

697

create table t1 (a int, b int) engine=innodb;

658

717

create table t2 (a int not null, b int, primary key (a)) engine = innodb;

659

718

insert into t1 values (10, 20);

660

719

insert into t2 values (10, 20);

720

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

661

721

drop table t1,t2;

662

722

663

723

724

# Test of multi-table-delete with foreign key constraints

725

726

727

CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

728

CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=INNODB;

729

insert into t1 set id=1;

730

insert into t2 set id=1, t1_id=1;

731

delete t1,t2 from t1,t2 where t1.id=t2.t1_id;

732

select * from t1;

733

select * from t2;

734

drop table t2,t1;

735

CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

736

CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;

737

INSERT INTO t1 VALUES(1);

738

INSERT INTO t2 VALUES(1, 1);

739

SELECT * from t1;

740

UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;

741

SELECT * from t1;

742

UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;

743

SELECT * from t1;

744

DROP TABLE t1,t2;

745

746

664

747

# Test of range_optimizer

665

748

666

749

704

787

drop table t1;

705

788

706

789

790

# Test multi update with different join methods

791

792

793

CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;

794

CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;

795

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

796

INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

797

798

# Full join, without key

799

update t1,t2 set t1.a=t1.a+100;

800

select * from t1;

801

802

# unique key

803

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

804

select * from t1;

805

806

# ref key

807

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

808

select * from t1;

809

810

# Range key (in t1)

811

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;

812

select * from t1;

813

select * from t2;

814

815

drop table t1,t2;

816

CREATE TEMPORARY TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

817

CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;

818

SET AUTOCOMMIT=0;

819

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

820

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

821

ROLLBACK;

822

SELECT * FROM t1;

823

drop table t1,t2;

824

create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;

825

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

826

select distinct parent,child from t1 order by parent;

827

drop table t1;

828

829

707

830

# Test that MySQL priorities clustered indexes

708

831

709

832

create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;

749

872

drop table t1;

750

873

751

874

875

# Test of multi-table-updates (bug #1980).

876

877

878

create table t1 ( c char(8) not null ) engine=innodb;

879

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

880

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

881

882

alter table t1 add b char(8) not null;

883

alter table t1 add a char(8) not null;

884

alter table t1 add primary key (a,b,c);

885

update t1 set a=c, b=c;

886

887

create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb;

888

insert into t2 select * from t1;

889

890

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

891

drop table t1,t2;

892

893

752

894

# test autoincrement with TRUNCATE

753

895

754

896

770

912

drop table t2,t1;

771

913

772

914

915

# Test of multi updated and foreign keys

916

917

918

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

919

insert into `t1`values ( 1 ) ;

920

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

921

insert into `t2`values ( 1 ) ;

922

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

923

insert into `t3`values ( 1 ) ;

924

--error 1451

925

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

926

--error 1451

927

update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

928

--error 1054

929

update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

930

drop table t3,t2,t1;

931

932

773

933

# test for recursion depth limit

774

934

775

935

create table t1(

830

990

ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);

831

991

drop table t1,t2;

832

992

833

create TEMPORARY table t1 (a int, b varchar(200), c text not null) engine=myisam;

834

create table t2 (a int, b varchar(200), c text not null) engine=innodb;

835

create table t3 (a int, b varchar(200), c text not null) engine=innodb;

993

create TEMPORARY table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;

994

create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;

995

create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;

836

996

insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");

837

997

insert t2 select * from t1;

838

998

insert t3 select * from t1;

839

--replace_column 1 # 6 # 7 # 8 # 9 # 10 #

999

checksum table t1, t2, t3, t4 quick;

1000

checksum table t1, t2, t3, t4;

1001

checksum table t1, t2, t3, t4 extended;

1002

#show table status;

840

1003

drop table t1,t2,t3;

841

1004

842

1005

868

1031

create index id2 on t2 (id);

869

1032

show create table t2;

870

1033

drop index id2 on t2;

871

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1034

--error 1025,1025

872

1035

drop index id on t2;

873

1036

show create table t2;

874

1037

drop table t2;

905

1068

# Test error handling

906

1069

907

1070

# Embedded server doesn't chdir to data directory

908

--replace_result $DRIZZLETEST_VARDIR . master-data/ ''

1071

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

909

1072

--error ER_WRONG_FK_DEF

910

1073

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

911

1074

922

1085

923

1086

# Bug #6126: Duplicate columns in keys gives misleading error message

924

1087

925

--error ER_DUP_FIELDNAME

1088

--error 1060

926

1089

create table t1 (c char(10), index (c,c)) engine=innodb;

927

--error ER_DUP_FIELDNAME

1090

--error 1060

928

1091

create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;

929

--error ER_DUP_FIELDNAME

1092

--error 1060

930

1093

create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;

931

--error ER_DUP_FIELDNAME

1094

--error 1060

932

1095

create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;

933

1096

create table t1 (c1 char(10), c2 char(10)) engine=innodb;

934

--error ER_DUP_FIELDNAME

1097

--error 1060

935

1098

alter table t1 add key (c1,c1);

936

--error ER_DUP_FIELDNAME

1099

--error 1060

937

1100

alter table t1 add key (c2,c1,c1);

938

--error ER_DUP_FIELDNAME

1101

--error 1060

939

1102

alter table t1 add key (c1,c2,c1);

940

--error ER_DUP_FIELDNAME

1103

--error 1060

941

1104

alter table t1 add key (c1,c1,c2);

942

1105

drop table t1;

943

1106

1027

1190

## Not deterministic.

1028

1191

# Test for testable InnoDB status variables. This test

1029

1192

# uses previous ones(pages_created, rows_deleted, ...).

1030

--replace_column 2 #

1031

show status like "Innodb_buffer_pool_pages_total";

1032

--replace_column 2 #

1033

show status like "Innodb_page_size";

1034

--replace_column 2 #

1035

show status like "Innodb_rows_deleted";

1036

--replace_column 2 #

1037

show status like "Innodb_rows_inserted";

1038

--replace_column 2 #

1039

show status like "Innodb_rows_updated";

1193

#show status like "Innodb_buffer_pool_pages_total";

1194

#show status like "Innodb_page_size";

1195

#show status like "Innodb_rows_deleted";

1196

#show status like "Innodb_rows_inserted";

1197

#show status like "Innodb_rows_updated";

1040

1198

1041

1199

## Test for row locks InnoDB status variables.

1042

--replace_column 2 #

1043

show status like "Innodb_row_lock_waits";

1044

--replace_column 2 #

1045

show status like "Innodb_row_lock_current_waits";

1046

--replace_column 2 #

1047

show status like "Innodb_row_lock_time";

1048

--replace_column 2 #

1049

show status like "Innodb_row_lock_time_max";

1050

--replace_column 2 #

1051

show status like "Innodb_row_lock_time_avg";

1200

#show status like "Innodb_row_lock_waits";

1201

#show status like "Innodb_row_lock_current_waits";

1202

#show status like "Innodb_row_lock_time";

1203

#show status like "Innodb_row_lock_time_max";

1204

#show status like "Innodb_row_lock_time_avg";

1052

1205

1053

1206

# Test for innodb_sync_spin_loops variable

1054

1207

show variables like "innodb_sync_spin_loops";

1099

1252

1100

1253

1101

1254

# Embedded server doesn't chdir to data directory

1102

--replace_result $DRIZZLETEST_VARDIR . master-data/ ''

1255

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1103

1256

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

1104

1257

drop table t1;

1105

1258

create table t1 (v varchar(16383));

1151

1304

drop table t1;

1152

1305

1153

1306

1307

# Test that update does not change internal auto-increment value

1308

1309

1310

create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;

1311

insert into t1 (val) values (1);

1312

update t1 set a=2 where a=1;

1313

# We should get the following error because InnoDB does not update the counter

1314

--error ER_DUP_ENTRY

1315

insert into t1 (val) values (1);

1316

select * from t1;

1317

drop table t1;

1318

1154

1319

# Bug #10465

1155

1320

1156

1321

1163

1328

DROP TABLE t1;

1164

1329

1165

1330

1331

# Bug #12340 multitable delete deletes only one record

1332

1333

create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;

1334

create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;

1335

insert into t2 values ('aa','cc');

1336

insert into t1 values ('aa','bb'),('aa','cc');

1337

delete t1 from t1,t2 where f1=f3 and f4='cc';

1338

select * from t1;

1339

drop table t1,t2;

1340

1341

1166

1342

# Test that the slow TRUNCATE implementation resets autoincrement columns

1167

1343

# (bug #11946)

1168

1344

1196

1372

id INT PRIMARY KEY

1197

1373

) ENGINE=InnoDB;

1198

1374

1199

--error ER_CANT_CREATE_TABLE, ER_CANT_CREATE_TABLE

1375

--error 1005,1005

1200

1376

CREATE TEMPORARY TABLE t2

1201

1377

(

1202

1378

id INT NOT NULL PRIMARY KEY,

1250

1426

drop table t1, t2, t3, t4;

1251

1427

1252

1428

# these should be refused

1253

--error ER_TOO_LONG_KEY

1429

--error 1071

1254

1430

create table t1 (col1 varchar(768) primary key)

1255

1431

engine = innodb;

1256

--error ER_TOO_LONG_KEY

1257

create table t2 (col1 varbinary(1024) primary key)

1432

--error 1071

1433

create table t2 (col1 varbinary(768) primary key)

1258

1434

engine = innodb;

1259

--error ER_TOO_LONG_KEY

1435

--error 1071

1260

1436

create table t3 (col1 text, primary key(col1(768)))

1261

1437

engine = innodb;

1262

--error ER_TOO_LONG_KEY

1263

create table t4 (col1 blob, primary key(col1(1024)))

1438

--error 1071

1439

create table t4 (col1 blob, primary key(col1(768)))

1264

1440

engine = innodb;

1265

1441

1266

1442

1278

1454

CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)

1279

1455

) ENGINE=InnoDB;

1280

1456

1281

--error ER_NO_REFERENCED_ROW_2

1457

--error 1452

1282

1458

INSERT INTO t2 VALUES(2);

1283

1459

1284

1460

INSERT INTO t1 VALUES(1);

1285

1461

INSERT INTO t2 VALUES(1);

1286

1462

1287

--error ER_ROW_IS_REFERENCED_2

1463

--error 1451

1288

1464

DELETE FROM t1 WHERE id = 1;

1289

1465

1290

--error ER_ROW_IS_REFERENCED

1466

--error 1217

1291

1467

DROP TABLE t1;

1292

1468

1293

1469

SET FOREIGN_KEY_CHECKS=0;

1294

1470

DROP TABLE t1;

1295

1471

SET FOREIGN_KEY_CHECKS=1;

1296

1472

1297

--error ER_NO_REFERENCED_ROW_2

1473

--error 1452

1298

1474

INSERT INTO t2 VALUES(3);

1299

1475

1300

1476

DROP TABLE t2;

1477

1478

# Test that checksum table uses a consistent read Bug #12669

1479

1480

connect (a,localhost,root,,);

1481

connect (b,localhost,root,,);

1482

connection a;

1483

create table t1(a int not null) engine=innodb;

1484

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

1485

set autocommit=0;

1486

checksum table t1;

1487

connection b;

1488

insert into t1 values(3);

1489

connection a;

1490

1491

# Here checksum should not see insert

1492

1493

checksum table t1;

1494

connection a;

1495

commit;

1496

checksum table t1;

1497

commit;

1498

drop table t1;

1499

1500

# autocommit = 1

1501

1502

connection a;

1503

create table t1(a int not null) engine=innodb;

1504

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

1505

set autocommit=1;

1506

checksum table t1;

1507

connection b;

1508

set autocommit=1;

1509

insert into t1 values(3);

1510

connection a;

1511

1512

# Here checksum sees insert

1513

1514

checksum table t1;

1515

drop table t1;

1516

1517

connection default;

1518

disconnect a;

1519

disconnect b;

1301

1520

1302

1521

# tests for bugs #9802 and #13778

1303

1522

1306

1525

set foreign_key_checks=0;

1307

1526

create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;

1308

1527

# Embedded server doesn't chdir to data directory

1309

--replace_result $DRIZZLETEST_VARDIR . master-data/ ''

1528

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1310

1529

-- error 1005

1311

1530

create table t1(a char(10) primary key, b varchar(20)) engine = innodb;

1312

1531

set foreign_key_checks=1;

1364

1583

b varchar(255),

1365

1584

c varchar(255),

1366

1585

d varchar(255),

1367

key (a(200),b(200),c(200),d(200))) engine=innodb;

1586

key (a,b,c,d)) engine=innodb;

1368

1587

drop table t1;

1369

1588

--error ER_TOO_LONG_KEY

1370

1589

create table t1 (a varchar(255),

1378

1597

# test the padding of BINARY types and collations (Bug #14189)

1379

1598

1380

1599

create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;

1381

create table t3 (s1 varchar(2) ,primary key (s1)) engine=innodb;

1382

create table t4 (s1 char(2) ,primary key (s1)) engine=innodb;

1600

create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;

1601

create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;

1383

1602

1384

1603

insert into t1 values (0x41),(0x4120),(0x4100);

1385

1604

-- error ER_DUP_ENTRY

1423

1642

1424

1643

drop table t2,t1;

1425

1644

1426

create table t1 (a int primary key,s1 varchar(2) not null unique) engine=innodb;

1427

create table t2 (s1 char(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;

1645

create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;

1646

create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;

1428

1647

1429

1648

insert into t1 values(1,0x4100),(2,0x41);

1430

1649

insert into t2 values(0x41);

1526

1745

INSERT INTO t2 VALUES (1, 0, '');

1527

1746

INSERT INTO t2 VALUES (8, 1, '');

1528

1747

commit;

1748

DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)

1749

WHERE mm.id IS NULL;

1529

1750

select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)

1530

1751

where mm.id is null lock in share mode;

1531

1752

drop table t1,t2;

1579

1800

1580

1801

# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update

1581

1802

1582

--error ER_LOCK_WAIT_TIMEOUT

1803

--error 1205

1583

1804

select * from t1 where a = 2 and b = 2 for update;

1584

1805

1585

1806

# X-lock to record (1,1),(3,1),(5,1) should not be released in a update

1586

1807

1587

--error ER_LOCK_WAIT_TIMEOUT

1808

--error 1205

1588

1809

connection a;

1589

1810

commit;

1590

1811

connection b;

1714

1935

create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;

1715

1936

1716

1937

connection b;

1717

--error ER_LOCK_WAIT_TIMEOUT

1938

--error 1205

1718

1939

reap;

1719

1940

1720

1941

connection c;

1721

--error ER_LOCK_WAIT_TIMEOUT

1942

--error 1205

1722

1943

reap;

1723

1944

1724

1945

connection d;

1725

--error ER_LOCK_WAIT_TIMEOUT

1946

--error 1205

1726

1947

reap;

1727

1948

1728

1949

connection e;

1729

--error ER_LOCK_WAIT_TIMEOUT

1950

--error 1205

1730

1951

reap;

1731

1952

1732

1953

connection f;

1733

--error ER_LOCK_WAIT_TIMEOUT

1954

--error 1205

1734

1955

reap;

1735

1956

1736

1957

connection g;

1737

--error ER_LOCK_WAIT_TIMEOUT

1958

--error 1205

1738

1959

reap;

1739

1960

1740

1961

connection h;

1741

--error ER_LOCK_WAIT_TIMEOUT

1962

--error 1205

1742

1963

reap;

1743

1964

1744

1965

connection i;

1745

--error ER_LOCK_WAIT_TIMEOUT

1966

--error 1205

1746

1967

reap;

1747

1968

1748

1969

connection j;

1749

--error ER_LOCK_WAIT_TIMEOUT

1970

--error 1205

1750

1971

reap;

1751

1972

1752

1973

connection a;

1766

1987

drop table t1, t2, t3, t5, t6, t8, t9;

1767

1988

1768

1989

# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"

1769

--error ER_CANT_CREATE_TABLE,1166

1990

--error 1005

1770

1991

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

1771

1992

1772

1993

1810

2031

CREATE TABLE t1 ( a int ) ENGINE=innodb;

1811

2032

BEGIN;

1812

2033

INSERT INTO t1 VALUES (1);

1813

ALTER TABLE t1 ENGINE=innodb;

2034

OPTIMIZE TABLE t1;

1814

2035

DROP TABLE t1;

1815

2036

1816

2037

1842

2063

# mysqltest first does replace_regex, then replace_result

1843

2064

--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/

1844

2065

# Embedded server doesn't chdir to data directory

1845

--replace_result $DRIZZLETEST_VARDIR . master-data/ ''

1846

--error ER_ERROR_ON_RENAME

2066

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

2067

--error 1025

1847

2068

ALTER TABLE t2 MODIFY a INT NOT NULL;

1848

2069

DELETE FROM t1;

1849

2070

DROP TABLE t2,t1;

1899

2120

SELECT * FROM t1;

1900

2121

DROP TABLE t1;

1901

2122

1902

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

1903

1904

1905

2123

#######################################################################

1906

2124

# #

1907

2125

# Please, DO NOT TOUCH this file as well as the innodb.result file. #

Older »