~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

655

607

# A simple test with some isolation levels

656

608

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

657

609

# this works.

680

632

DROP TABLE t1;

681

633

682

634

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

695

635

# Testing of IFNULL

696

636

697

637

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

717

657

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

718

658

insert into t1 values (10, 20);

719

659

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;

721

660

drop table t1,t2;

722

661

723

662

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

747

663

# Test of range_optimizer

748

664

749

665

787

703

drop table t1;

788

704

789

705

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

830

706

# Test that MySQL priorities clustered indexes

831

707

832

708

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

872

748

drop table t1;

873

749

874

750

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

894

751

# test autoincrement with TRUNCATE

895

752

896

753

912

769

drop table t2,t1;

913

770

914

771

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

933

772

# test for recursion depth limit

934

773

935

774

create table t1(

1328

1167

DROP TABLE t1;

1329

1168

1330

1169

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

1342

1170

# Test that the slow TRUNCATE implementation resets autoincrement columns

1343

1171

# (bug #11946)

1344

1172

1745

1573

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

1746

1574

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

1747

1575

commit;

1748

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

1749

WHERE mm.id IS NULL;

1750

1576

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

1751

1577

where mm.id is null lock in share mode;

1752

1578

drop table t1,t2;

Older »