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

889

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;

890

959

BEGIN;

891

960

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

924

993

7 4 Matt

925

994

COMMIT;

926

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;

927

1010

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

928

1011

insert into t1 values(20,null);

929

1012

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

941

1024

NULL this is null

942

1025

NULL this is null

943

1026

drop table t1;

944

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

1027

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

945

1028

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

946

1029

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

947

1030

950

1033

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

951

1034

insert into t1 values (10, 20);

952

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;

953

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;

954

1064

set autocommit=0;

955

1065

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

956

1066

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

997

1107

102 2

998

1108

103 3

999

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;

1000

1206

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

1001

1207

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

1002

1208

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

1015

1221

623

1016

1222

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

1017

1223

id select_type table type possible_keys key key_len ref rows Extra

1018

1 SIMPLE t1 range c c 5 NULL # Using where

1224

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

1019

1225

update t1 set c=a;

1020

1226

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

1021

1227

id select_type table type possible_keys key key_len ref rows Extra

1054

1260

111 100

1055

1261

111 100

1056

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;

1057

1274

SET AUTOCOMMIT=1;

1058

1275

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

1059

1276

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

1067

1284

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

1068

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;

1069

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;

1070

1300

create table t1(

1071

1301

id int primary key,

1072

1302

pid int,

1121

1351

3524 Societe Test

1122

1352

3525 Fournisseur Test

1123

1353

drop table t1,t2;

1124

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

1125

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

1126

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;

1127

1357

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

1128

1358

insert t2 select * from t1;

1129

1359

insert t3 select * from t1;

1130

checksum table t1, t2, t3, t4;

1131

Table Checksum

1132

test.t1 2948697075

1133

test.t2 3505595080

1134

test.t3 3505595080

1135

test.t4 NULL

1136

Warnings:

1137

Error 1146 Table 'test.t4' doesn't exist

1138

checksum table t1, t2, t3, t4;

1139

Table Checksum

1140

test.t1 2948697075

1141

test.t2 3505595080

1142

test.t3 3505595080

1143

test.t4 NULL

1144

Warnings:

1145

Error 1146 Table 'test.t4' doesn't exist

1146

checksum table t1, t2, t3, t4;

1147

Table Checksum

1148

test.t1 2948697075

1149

test.t2 3505595080

1150

test.t3 3505595080

1360

checksum table t1, t2, t3, t4 quick;

1361

Table Checksum

1362

test.t1 2948697075

1363

test.t2 NULL

1364

test.t3 NULL

1365

test.t4 NULL

1366

Warnings:

1367

Error 1146 Table 'test.t4' doesn't exist

1368

checksum table t1, t2, t3, t4;

1369

Table Checksum

1370

test.t1 2948697075

1371

test.t2 2948697075

1372

test.t3 2948697075

1373

test.t4 NULL

1374

Warnings:

1375

Error 1146 Table 'test.t4' doesn't exist

1376

checksum table t1, t2, t3, t4 extended;

1377

Table Checksum

1378

test.t1 2948697075

1379

test.t2 2948697075

1380

test.t3 2948697075

1151

1381

test.t4 NULL

1152

1382

Warnings:

1153

1383

Error 1146 Table 'test.t4' doesn't exist

1428

1658

count(*)

1429

1659

1430

1660

drop table t1;

1431

show status like "Innodb_buffer_pool_pages_total";

1432

Variable_name Value

1433

show status like "Innodb_page_size";

1434

Variable_name Value

1435

show status like "Innodb_rows_deleted";

1436

Variable_name Value

1437

show status like "Innodb_rows_inserted";

1438

Variable_name Value

1439

show status like "Innodb_rows_updated";

1440

Variable_name Value

1441

show status like "Innodb_row_lock_waits";

1442

Variable_name Value

1443

show status like "Innodb_row_lock_current_waits";

1444

Variable_name Value

1445

show status like "Innodb_row_lock_time";

1446

Variable_name Value

1447

show status like "Innodb_row_lock_time_max";

1448

Variable_name Value

1449

show status like "Innodb_row_lock_time_avg";

1450

Variable_name Value

1451

1661

show variables like "innodb_sync_spin_loops";

1452

1662

Variable_name Value

1453

innodb_sync_spin_loops 30

1663

innodb_sync_spin_loops 20

1454

1664

set global innodb_sync_spin_loops=1000;

1455

1665

show variables like "innodb_sync_spin_loops";

1456

1666

Variable_name Value

1516

1726

drop table if exists t1,t2,t3;

1517

1727

--- Testing varchar ---

1518

1728

--- Testing varchar ---

1519

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

1729

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

1520

1730

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

1521

1731

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

1522

1732

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

1534

1744

`c` varchar(10) DEFAULT NULL,

1535

1745

`t` text

1536

1746

) ENGINE=InnoDB

1537

create table t2 like t1;

1747

create table t2 like t1;

1538

1748

show create table t2;

1539

1749

Table Create Table

1540

1750

t2 CREATE TABLE `t2` (

1542

1752

`c` varchar(10) DEFAULT NULL,

1543

1753

`t` text

1544

1754

) ENGINE=InnoDB

1545

create table t3 select * from t1;

1755

create table t3 select * from t1;

1546

1756

show create table t3;

1547

1757

Table Create Table

1548

1758

t3 CREATE TABLE `t3` (

1581

1791

*+ *+ *+ *

1582

1792

*+ *+ *+ *

1583

1793

drop table t1,t2,t3;

1584

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

1585

1795

show create table t1;

1586

1796

Table Create Table

1587

1797

t1 CREATE TABLE `t1` (

2012

2222

h 10

2013

2223

i 10

2014

2224

drop table t1;

2015

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

2225

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

2016

2226

insert into t1 values ('a ');

2017

2227

insert into t1 values ('a ');

2018

2228

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

2042

2252

concat(a,'.')

2043

2253

a .

2044

2254

drop table t1;

2045

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

2046

2256

show create table t1;

2047

2257

Table Create Table

2048

2258

t1 CREATE TABLE `t1` (

2054

2264

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

2055

2265

) ENGINE=InnoDB

2056

2266

drop table t1;

2057

create table t1 (v char(10));

2267

create table t1 (v char(10));

2058

2268

show create table t1;

2059

2269

Table Create Table

2060

2270

t1 CREATE TABLE `t1` (

2061

2271

`v` varchar(10) DEFAULT NULL

2062

2272

) ENGINE=InnoDB

2063

2273

drop table t1;

2064

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

2274

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

2275

Warnings:

2276

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

2065

2277

show create table t1;

2066

2278

Table Create Table

2067

2279

t1 CREATE TABLE `t1` (

2068

2280

`v` varchar(10) DEFAULT NULL,

2069

2281

`c` varchar(10) DEFAULT NULL

2070

) ENGINE=InnoDB

2282

) ENGINE=InnoDB ROW_FORMAT=FIXED

2071

2283

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

2072

2284

select concat('*',v,'*',c,'*') from t1;

2073

2285

concat('*',v,'*',c,'*')

2074

2286

*a*a*

2075

2287

*a *a *

2076

2288

drop table t1;

2077

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

2078

2290

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

2079

2291

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

2080

2292

id select_type table type possible_keys key key_len ref rows Extra

2161

2373

GRADE

2162

2374

151

2163

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;

2164

2384

CREATE TABLE t1 (

2165

2385

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

2166

2386

) ENGINE=InnoDB;

2378

2598

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

2379

2599

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

2380

2600

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

2381

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

2382

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;

2383

2603

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

2384

2604

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

2385

2605

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

2448

2668

4120

2449

2669

4120

2450

2670

drop table t2,t1;

2451

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

2452

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;

2453

2673

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

2454

2674

insert into t2 values(0x41);

2455

2675

select hex(s1) from t2;

2553

2773

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

2554

2774

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

2555

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;

2556

2778

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

2557

2779

where mm.id is null lock in share mode;

2558

2780

id f_id f

2711

2933

CREATE TABLE t1 ( a int ) ENGINE=innodb;

2712

2934

BEGIN;

2713

2935

INSERT INTO t1 VALUES (1);

2714

ALTER TABLE t1 ENGINE=innodb;

2936

OPTIMIZE TABLE t1;

2937

Table Op Msg_type Msg_text

2938

test.t1 optimize status OK

2715

2939

DROP TABLE t1;

2716

2940

CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;

2717

2941

CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,

2736

2960

INSERT INTO t2 VALUES (1);

2737

2961

ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;

2738

2962

ALTER TABLE t2 MODIFY a INT NOT NULL;

2739

ERROR HY000: Error on rename of '#sql-temporary' to 'test.t2' (errno: 150)

2963

ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150)

2740

2964

DELETE FROM t1;

2741

2965

DROP TABLE t2,t1;

2742

2966

CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)

2761

2985

t1 CREATE TABLE `t1` (

2762

2986

`id` int NOT NULL AUTO_INCREMENT,

2763

2987

PRIMARY KEY (`id`)

2764

) ENGINE=InnoDB

2988

) ENGINE=InnoDB AUTO_INCREMENT=349

2765

2989

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

2766

2990

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

2767

2991

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

2771

2995

`id` int NOT NULL AUTO_INCREMENT,

2772

2996

PRIMARY KEY (`id`),

2773

2997

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

2774

) ENGINE=InnoDB

2998

) ENGINE=InnoDB AUTO_INCREMENT=349

2775

2999

DROP TABLE t1,t2;

2776

3000

DROP TABLE IF EXISTS t1;

2777

3001

Warnings:

Older »