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

947

select * from t1;

948

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

949

6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0

950

333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0

951

select * from t2;

952

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

953

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

954

select * from t2;

955

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

956

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

957

drop table t1,t2;

896

958

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=innodb;

897

959

BEGIN;

898

960

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

931

993

7 4 Matt

932

994

COMMIT;

933

995

DROP TABLE t1;

996

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

997

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

998

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

999

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

1000

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

1001

select * from t1;

1002

n d

1003

1 10

1004

1 10

1005

select * from t2;

1006

n d

1007

1 30

1008

2 20

1009

drop table t1,t2;

934

1010

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

935

1011

insert into t1 values(20,null);

936

1012

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

948

1024

NULL this is null

949

1025

NULL this is null

950

1026

drop table t1;

951

create TEMPORARY table t1 (a varchar(10) not null) engine=myisam;

1027

create table t1 (a varchar(10) not null) engine=myisam;

952

1028

create table t2 (b varchar(10) not null unique) engine=innodb;

953

1029

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

954

1030

957

1033

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

958

1034

insert into t1 values (10, 20);

959

1035

insert into t2 values (10, 20);

1036

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

960

1037

drop table t1,t2;

1038

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

1039

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;

1040

insert into t1 set id=1;

1041

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

1042

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

1043

select * from t1;

1044

1045

select * from t2;

1046

id t1_id

1047

drop table t2,t1;

1048

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

1049

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

1050

INSERT INTO t1 VALUES(1);

1051

INSERT INTO t2 VALUES(1, 1);

1052

SELECT * from t1;

1053

1054

1055

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

1056

SELECT * from t1;

1057

1058

1059

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

1060

SELECT * from t1;

1061

1062

1063

DROP TABLE t1,t2;

961

1064

set autocommit=0;

962

1065

CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

963

1066

CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

1004

1107

102 2

1005

1108

103 3

1006

1109

drop table t1;

1110

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

1111

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

1112

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

1113

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

1114

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

1115

select * from t1;

1116

a b

1117

101 1

1118

102 2

1119

103 3

1120

104 4

1121

105 5

1122

106 6

1123

107 7

1124

108 8

1125

109 9

1126

110 10

1127

111 11

1128

112 12

1129

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

1130

select * from t1;

1131

a b

1132

102 2

1133

103 3

1134

104 4

1135

105 5

1136

106 6

1137

107 7

1138

108 8

1139

109 9

1140

110 10

1141

111 11

1142

112 12

1143

201 1

1144

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

1145

select * from t1;

1146

a b

1147

102 12

1148

103 3

1149

104 4

1150

105 5

1151

106 6

1152

107 7

1153

108 8

1154

109 9

1155

110 10

1156

111 11

1157

112 12

1158

201 1

1159

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;

1160

select * from t1;

1161

a b

1162

102 12

1163

103 5

1164

104 6

1165

105 7

1166

106 6

1167

107 7

1168

108 8

1169

109 9

1170

110 10

1171

111 11

1172

112 12

1173

201 1

1174

select * from t2;

1175

a b

1176

1 1

1177

2 2

1178

3 13

1179

4 14

1180

5 15

1181

6 6

1182

7 7

1183

8 8

1184

9 9

1185

drop table t1,t2;

1186

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

1187

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

1188

SET AUTOCOMMIT=0;

1189

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

1190

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

1191

ROLLBACK;

1192

Warnings:

1193

Warning 1196 Some non-transactional changed tables couldn't be rolled back

1194

SELECT * FROM t1;

1195

B_ID

1196

drop table t1,t2;

1197

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

1198

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

1199

select distinct parent,child from t1 order by parent;

1200

parent child

1201

0 4

1202

1 2

1203

1 3

1204

2 1

1205

drop table t1;

1007

1206

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

1008

1207

create table t2 (a int not null auto_increment primary key, b int);

1009

1208

insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);

1022

1221

623

1023

1222

explain select * from t1 where c between 1 and 2500;

1024

1223

id select_type table type possible_keys key key_len ref rows Extra

1025

1 SIMPLE t1 range c c 5 NULL # Using where

1224

1 SIMPLE t1 range c c 5 NULL # Using where; Using MRR

1026

1225

update t1 set c=a;

1027

1226

explain select * from t1 where c between 1 and 2500;

1028

1227

id select_type table type possible_keys key key_len ref rows Extra

1061

1260

111 100

1062

1261

111 100

1063

1262

drop table t1;

1263

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

1264

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

1265

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

1266

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

1267

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

1268

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

1269

update t1 set a=c, b=c;

1270

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;

1271

insert into t2 select * from t1;

1272

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

1273

drop table t1,t2;

1064

1274

SET AUTOCOMMIT=1;

1065

1275

create table t1 (a integer auto_increment primary key) engine=innodb;

1066

1276

insert into t1 (a) values (NULL),(NULL);

1074

1284

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

1075

1285

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

1076

1286

drop table t2,t1;

1287

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

1288

insert into `t1`values ( 1 ) ;

1289

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;

1290

insert into `t2`values ( 1 ) ;

1291

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

1292

insert into `t3`values ( 1 ) ;

1293

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

1294

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))

1295

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;

1296

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))

1297

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

1298

ERROR 42S22: Unknown column 't1.id' in 'where clause'

1299

drop table t3,t2,t1;

1077

1300

create table t1(

1078

1301

id int primary key,

1079

1302

pid int,

1128

1351

3524 Societe Test

1129

1352

3525 Fournisseur Test

1130

1353

drop table t1,t2;

1131

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

1132

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

1133

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

1354

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

1355

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

1356

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

1134

1357

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

1135

1358

insert t2 select * from t1;

1136

1359

insert t3 select * from t1;

1137

1360

checksum table t1, t2, t3, t4 quick;

1138

1361

Table Checksum

1139

test.t1 NULL

1362

test.t1 2948697075

1140

1363

test.t2 NULL

1141

1364

test.t3 NULL

1142

1365

test.t4 NULL

1437

1660

drop table t1;

1438

1661

show variables like "innodb_sync_spin_loops";

1439

1662

Variable_name Value

1440

innodb_sync_spin_loops 30

1663

innodb_sync_spin_loops 20

1441

1664

set global innodb_sync_spin_loops=1000;

1442

1665

show variables like "innodb_sync_spin_loops";

1443

1666

Variable_name Value

1503

1726

drop table if exists t1,t2,t3;

1504

1727

--- Testing varchar ---

1505

1728

--- Testing varchar ---

1506

create table t1 (v varchar(10), c char(10), t text);

1729

create table t1 (v varchar(10), c char(10), t text);

1507

1730

insert into t1 values('+ ', '+ ', '+ ');

1508

1731

set @a=repeat(' ',20);

1509

1732

insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));

1521

1744

`c` varchar(10) DEFAULT NULL,

1522

1745

`t` text

1523

1746

) ENGINE=InnoDB

1524

create table t2 like t1;

1747

create table t2 like t1;

1525

1748

show create table t2;

1526

1749

Table Create Table

1527

1750

t2 CREATE TABLE `t2` (

1529

1752

`c` varchar(10) DEFAULT NULL,

1530

1753

`t` text

1531

1754

) ENGINE=InnoDB

1532

create table t3 select * from t1;

1755

create table t3 select * from t1;

1533

1756

show create table t3;

1534

1757

Table Create Table

1535

1758

t3 CREATE TABLE `t3` (

1568

1791

*+ *+ *+ *

1569

1792

*+ *+ *+ *

1570

1793

drop table t1,t2,t3;

1571

create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));

1794

create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));

1572

1795

show create table t1;

1573

1796

Table Create Table

1574

1797

t1 CREATE TABLE `t1` (

1999

2222

h 10

2000

2223

i 10

2001

2224

drop table t1;

2002

create table t1 (a char(10), unique (a));

2225

create table t1 (a char(10), unique (a));

2003

2226

insert into t1 values ('a ');

2004

2227

insert into t1 values ('a ');

2005

2228

ERROR 23000: Duplicate entry 'a ' for key 'a'

2029

2252

concat(a,'.')

2030

2253

a .

2031

2254

drop table t1;

2032

create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));

2255

create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));

2033

2256

show create table t1;

2034

2257

Table Create Table

2035

2258

t1 CREATE TABLE `t1` (

2041

2264

KEY `t` (`t`(5))

2042

2265

) ENGINE=InnoDB

2043

2266

drop table t1;

2044

create table t1 (v char(10));

2267

create table t1 (v char(10));

2045

2268

show create table t1;

2046

2269

Table Create Table

2047

2270

t1 CREATE TABLE `t1` (

2048

2271

`v` varchar(10) DEFAULT NULL

2049

2272

) ENGINE=InnoDB

2050

2273

drop table t1;

2051

create table t1 (v varchar(10), c char(10)) row_format=fixed;

2274

create table t1 (v varchar(10), c char(10)) row_format=fixed;

2052

2275

Warnings:

2053

2276

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

2054

2277

show create table t1;

2063

2286

*a*a*

2064

2287

*a *a *

2065

2288

drop table t1;

2066

create table t1(a int, b varchar(12), key ba(b, a));

2289

create table t1(a int, b varchar(12), key ba(b, a));

2067

2290

insert into t1 values (1, 'A'), (20, NULL);

2068

2291

explain select * from t1 where a=20 and b is null;

2069

2292

id select_type table type possible_keys key key_len ref rows Extra

2150

2373

GRADE

2151

2374

151

2152

2375

DROP TABLE t1;

2376

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

2377

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

2378

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

2379

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

2380

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

2381

select * from t1;

2382

f1 f2

2383

drop table t1,t2;

2153

2384

CREATE TABLE t1 (

2154

2385

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

2155

2386

) ENGINE=InnoDB;

2367

2598

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

2368

2599

ERROR 42000: Specified key was too long; max key length is 3500 bytes

2369

2600

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

2370

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

2371

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

2601

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

2602

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

2372

2603

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

2373

2604

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

2374

2605

ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'

2437

2668

4120

2438

2669

4120

2439

2670

drop table t2,t1;

2440

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

2441

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

2671

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

2672

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

2442

2673

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

2443

2674

insert into t2 values(0x41);

2444

2675

select hex(s1) from t2;

2542

2773

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

2543

2774

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

2544

2775

commit;

2776

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

2777

WHERE mm.id IS NULL;

2545

2778

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

2546

2779

where mm.id is null lock in share mode;

2547

2780

id f_id f

2752

2985

t1 CREATE TABLE `t1` (

2753

2986

`id` int NOT NULL AUTO_INCREMENT,

2754

2987

PRIMARY KEY (`id`)

2755

) ENGINE=InnoDB

2988

) ENGINE=InnoDB AUTO_INCREMENT=349

2756

2989

CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;

2757

2990

INSERT INTO t2 VALUES(42),(347),(348);

2758

2991

ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);

2762

2995

`id` int NOT NULL AUTO_INCREMENT,

2763

2996

PRIMARY KEY (`id`),

2764

2997

CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)

2765

) ENGINE=InnoDB

2998

) ENGINE=InnoDB AUTO_INCREMENT=349

2766

2999

DROP TABLE t1,t2;

2767

3000

DROP TABLE IF EXISTS t1;

2768

3001

Warnings:

Older »