~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

742

694

# A simple test with some isolation levels

743

695

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

744

696

# this works.

767

719

DROP TABLE t1;

768

720

769

721

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

782

722

# Testing of IFNULL

783

723

784

724

if(!$using_temp)

794

734

t2.b=t3.a order by 1;

795

735

drop table t1;

796

736

}

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;

811

737

812

738

if ($test_foreign_keys)

813

739

{

882

808

drop table t1;

883

809

884

810

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

925

811

# Test that MySQL priorities clustered indexes

926

812

927

813

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

975

861

drop table t1;

976

862

977

863

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

997

864

# test autoincrement with TRUNCATE

998

865

999

866

1531

1398

SELECT GRADE FROM t1 WHERE GRADE= 151;

1532

1399

DROP TABLE t1;

1533

1400

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

1545

1401

if ($test_foreign_keys)

1546

1402

{

1547

1403

2043

1899

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

2044

1900

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

2045

1901

commit;

2046

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

2047

WHERE mm.id IS NULL;

2048

1902

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

2049

1903

where mm.id is null lock in share mode;

2050

1904

drop table t1,t2;

Older »