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

736

select * from t1;

737

select * from t2;

738

select * from t2;

739

drop table t1,t2;

740

741

694

742

# A simple test with some isolation levels

695

743

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

696

744

# this works.

719

767

DROP TABLE t1;

720

768

721

769

770

# Test of multi-table-update

771

772

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

773

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

774

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

775

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

776

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

777

select * from t1;

778

select * from t2;

779

drop table t1,t2;

780

781

722

782

# Testing of IFNULL

723

783

724

784

if(!$using_temp)

734

794

t2.b=t3.a order by 1;

735

795

drop table t1;

736

796

}

797

798

# Test of read_through not existing const_table

799

800

801

eval create $temp table t1 (a varchar(10) not null) engine = $other_engine_type;

802

eval create $temp table t2 (b varchar(10) not null unique) engine=$engine_type;

803

select t1.a from t1,t2 where t1.a=t2.b;

804

drop table t1,t2;

805

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

806

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

807

insert into t1 values (10, 20);

808

insert into t2 values (10, 20);

809

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

810

drop table t1,t2;

737

811

738

812

if ($test_foreign_keys)

739

813

{

808

882

drop table t1;

809

883

810

884

885

# Test multi update with different join methods

886

887

888

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

889

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

890

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

891

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

892

893

# Full join, without key

894

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

895

select * from t1;

896

897

# unique key

898

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

899

select * from t1;

900

901

# ref key

902

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

903

select * from t1;

904

905

# Range key (in t1)

906

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;

907

select * from t1;

908

select * from t2;

909

910

drop table t1,t2;

911

eval create $temp table t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=$other_non_trans_engine_type;

912

eval create $temp table t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=$engine_type;

913

SET AUTOCOMMIT=0;

914

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

915

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

916

ROLLBACK;

917

SELECT * FROM t1;

918

drop table t1,t2;

919

eval create $temp table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = $engine_type;

920

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

921

select distinct parent,child from t1 order by parent;

922

drop table t1;

923

924

811

925

# Test that MySQL priorities clustered indexes

812

926

813

927

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

861

975

drop table t1;

862

976

863

977

978

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

979

980

981

eval create $temp table t1 ( c char(8) not null ) engine=$engine_type;

982

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

983

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

984

985

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

986

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

987

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

988

update t1 set a=c, b=c;

989

990

eval create $temp table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=$engine_type;

991

insert into t2 select * from t1;

992

993

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

994

drop table t1,t2;

995

996

864

997

# test autoincrement with TRUNCATE

865

998

866

999

1398

1531

SELECT GRADE FROM t1 WHERE GRADE= 151;

1399

1532

DROP TABLE t1;

1400

1533

1534

1535

# Bug #12340 multitable delete deletes only one record

1536

1537

eval create $temp table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=$engine_type;

1538

eval create $temp table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=$engine_type;

1539

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

1540

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

1541

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

1542

select * from t1;

1543

drop table t1,t2;

1544

1401

1545

if ($test_foreign_keys)

1402

1546

{

1403

1547

1899

2043

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

1900

2044

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

1901

2045

commit;

2046

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

2047

WHERE mm.id IS NULL;

1902

2048

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

1903

2049

where mm.id is null lock in share mode;

1904

2050

drop table t1,t2;

Older »