~drizzle-trunk/drizzle/development

create TEMPORARY 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=MyISAM;

create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');

select id, code, name from t1 order by id;

id code name

8 1 Sinisa

12 1 Ralph

drop table t1;

create TEMPORARY table t1 (

id int NOT NULL auto_increment,

parent_id int DEFAULT '0' NOT NULL,

level int DEFAULT '0' NOT NULL,

CREATE TABLE t1 (

id int(11) NOT NULL auto_increment,

parent_id int(11) DEFAULT '0' NOT NULL,

level tinyint(4) DEFAULT '0' NOT NULL,

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

142

1015 102 2

143

explain select level from t1 where level=1;

144

id select_type table type possible_keys key key_len ref rows Extra

145

1 SIMPLE t1 ref level level 4 const # Using index

145

1 SIMPLE t1 ref level level 1 const # Using index

146

explain select level,id from t1 where level=1;

147

id select_type table type possible_keys key key_len ref rows Extra

148

1 SIMPLE t1 ref level level 4 const #

148

1 SIMPLE t1 ref level level 1 const #

149

explain select level,id,parent_id from t1 where level=1;

150

id select_type table type possible_keys key key_len ref rows Extra

151

1 SIMPLE t1 ref level level 4 const #

151

1 SIMPLE t1 ref level level 1 const #

152

select level,id from t1 where level=1;

153

level id

154

1 1003

165

1 1006 101

166

1 1007 101

167

1 1005 101

168

alter table t1 engine=MyISAM;

168

optimize table t1;

169

Table Op Msg_type Msg_text

170

test.t1 optimize status OK

171

show keys from t1;

172

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

173

t1 0 PRIMARY 1 id A # NULL NULL BTREE

174

t1 1 parent_id 1 parent_id A # NULL NULL BTREE

175

t1 1 level 1 level A # NULL NULL BTREE

169

176

drop table t1;

170

create TEMPORARY table t1 (

171

gesuchnr int DEFAULT '0' NOT NULL,

172

benutzer_id int DEFAULT '0' NOT NULL,

177

CREATE TABLE t1 (

178

gesuchnr int(11) DEFAULT '0' NOT NULL,

179

benutzer_id int(11) DEFAULT '0' NOT NULL,

173

180

PRIMARY KEY (gesuchnr,benutzer_id)

174

181

) engine=MyISAM;

175

182

replace into t1 (gesuchnr,benutzer_id) values (2,1);

180

187

1 1

181

188

2 1

182

189

drop table t1;

183

create TEMPORARY table t1 (a int) engine=MyISAM;

190

create table t1 (a int) engine=MyISAM;

184

191

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

185

alter table t1 ENGINE=MyISAM;

192

optimize table t1;

193

Table Op Msg_type Msg_text

194

test.t1 optimize status OK

186

195

delete from t1 where a = 1;

187

196

select * from t1;

188

197

191

200

Table Op Msg_type Msg_text

192

201

test.t1 check status OK

193

202

drop table t1;

194

create TEMPORARY table t1 (a int,b varchar(20)) engine=MyISAM;

203

create table t1 (a int,b varchar(20)) engine=MyISAM;

195

204

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

196

205

delete from t1 where a = 1;

197

206

select * from t1;

201

210

insert into t1 values (3,""), (4,"testing");

202

211

analyze table t1;

203

212

Table Op Msg_type Msg_text

204

test.t1 analyze note The storage engine for the table doesn't support analyze

213

test.t1 analyze status OK

214

show keys from t1;

215

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

216

t1 1 skr 1 a A # NULL NULL YES BTREE

205

217

drop table t1;

206

create TEMPORARY table t1 (a int,b varchar(20),key(a)) engine=MyISAM;

218

create table t1 (a int,b varchar(20),key(a)) engine=MyISAM;

207

219

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

208

220

select * from t1 where a = 1;

209

221

a b

210

222

211

223

drop table t1;

212

create TEMPORARY table t1 (

213

user_id int DEFAULT '0' NOT NULL,

224

CREATE TABLE t1 (

225

user_id int(10) DEFAULT '0' NOT NULL,

214

226

name varchar(100),

215

227

phone varchar(100),

216

228

ref_email varchar(100) DEFAULT '' NOT NULL,

242

254

user_id name phone ref_email detail

243

255

10291 sanjeev 29153373 sansh777@hotmail.com xxx

244

256

drop table t1;

245

create TEMPORARY table t1 (a int not null, b int not null,c int not null,

257

CREATE TABLE t1 (a int not null, b int not null,c int not null,

246

258

key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM;

259

show index from t1;

260

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

261

t1 0 PRIMARY 1 a A # NULL NULL BTREE

262

t1 0 PRIMARY 2 b A # NULL NULL BTREE

263

t1 0 c 1 c A # NULL NULL BTREE

264

t1 0 b 1 b A # NULL NULL BTREE

265

t1 1 a 1 a A # NULL NULL BTREE

266

t1 1 a_2 1 a A # NULL NULL BTREE

247

267

drop table t1;

248

create TEMPORARY table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY;

268

create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY;

249

269

alter table t1 engine=MyISAM;

250

270

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

251

271

select * from t1;

263

283

2 3

264

284

3 4

265

285

4 7

266

alter table t1 add co3 int DEFAULT 42 not null;

286

alter table t1 add co3 int not null;

267

287

select * from t1;

268

288

col1 col2 co3

269

1 1 42

270

5 2 42

271

2 3 42

272

3 4 42

273

4 7 42

289

1 1 0

290

5 2 0

291

2 3 0

292

3 4 0

293

4 7 0

274

294

update t1 set col2='9' where col1='2';

275

295

select * from t1;

276

296

col1 col2 co3

277

1 1 42

278

5 2 42

279

2 9 42

280

3 4 42

281

4 7 42

297

1 1 0

298

5 2 0

299

2 9 0

300

3 4 0

301

4 7 0

282

302

drop table t1;

283

create TEMPORARY table t1 (a int not null , b int, primary key (a)) engine = MyISAM;

284

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

303

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

304

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

285

305

insert into t1 VALUES (1,3) , (2,3), (3,3);

286

306

select * from t1;

287

307

a b

309

329

2 3

310

330

3 3

311

331

drop table t1,t2;

312

create TEMPORARY table t1 (

313

id int NOT NULL auto_increment,

314

ggid varchar(32) DEFAULT '' NOT NULL,

332

CREATE TABLE t1 (

333

id int(11) NOT NULL auto_increment,

334

ggid varchar(32) binary DEFAULT '' NOT NULL,

315

335

email varchar(64) DEFAULT '' NOT NULL,

316

passwd varchar(32) DEFAULT '' NOT NULL,

336

passwd varchar(32) binary DEFAULT '' NOT NULL,

317

337

PRIMARY KEY (id),

318

338

UNIQUE ggid (ggid)

319

339

) ENGINE=MyISAM;

346

366

select * from t1 where id=999;

347

367

id ggid email passwd

348

368

drop table t1;

349

create TEMPORARY table t1 (

369

CREATE TABLE t1 (

350

370

user_name varchar(12),

351

371

password text,

352

372

subscribed char(1),

353

user_id int DEFAULT '0' NOT NULL,

354

quota bigint,

373

user_id int(11) DEFAULT '0' NOT NULL,

374

quota bigint(20),

355

375

weight double,

356

376

access_date date,

377

access_time time,

357

378

approved datetime,

358

dummy_primary_key int NOT NULL auto_increment,

379

dummy_primary_key int(11) NOT NULL auto_increment,

359

380

PRIMARY KEY (dummy_primary_key)

360

381

) ENGINE=MyISAM;

361

INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','2000-09-07 23:06:59',1);

362

INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','2000-09-07 23:06:59',2);

363

INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','2000-09-07 23:06:59',3);

364

INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','2000-09-07 23:06:59',4);

365

INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','2000-09-07 23:06:59',5);

366

select user_name, password , subscribed, user_id, quota, weight, access_date, approved, dummy_primary_key from t1 order by user_name;

367

user_name password subscribed user_id quota weight access_date approved dummy_primary_key

368

user_0 somepassword N 0 0 0 2000-09-07 2000-09-07 23:06:59 1

369

user_1 somepassword Y 1 1 1 2000-09-07 2000-09-07 23:06:59 2

370

user_2 somepassword N 2 2 1.4142135623731 2000-09-07 2000-09-07 23:06:59 3

371

user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 2000-09-07 23:06:59 4

372

user_4 somepassword N 4 4 2 2000-09-07 2000-09-07 23:06:59 5

382

INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);

383

INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);

384

INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);

385

INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);

386

INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);

387

select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;

388

user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key

389

user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1

390

user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2

391

user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3

392

user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4

393

user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5

373

394

drop table t1;

374

create TEMPORARY table t1 (

375

id int NOT NULL auto_increment,

376

parent_id int DEFAULT '0' NOT NULL,

377

level int DEFAULT '0' NOT NULL,

395

CREATE TABLE t1 (

396

id int(11) NOT NULL auto_increment,

397

parent_id int(11) DEFAULT '0' NOT NULL,

398

level tinyint(4) DEFAULT '0' NOT NULL,

378

399

KEY (id),

379

400

KEY parent_id (parent_id),

380

401

KEY level (level)

480

501

1016 102 2

481

502

explain select level from t1 where level=1;

482

503

id select_type table type possible_keys key key_len ref rows Extra

483

1 SIMPLE t1 ref level level 4 const # Using index

504

1 SIMPLE t1 ref level level 1 const # Using index

484

505

select level,id from t1 where level=1;

485

506

level id

486

507

1 1004

542

563

1020 103 2

543

564

1180 105 2

544

565

drop table t1;

545

create TEMPORARY table t1 (

566

CREATE TABLE t1 (

546

567

sca_code char(6) NOT NULL,

547

568

cat_code char(6) NOT NULL,

548

569

sca_desc varchar(50),

584

605

update t1 set sca_pic="test" where sca_pic is null;

585

606

delete from t1 where sca_code='pd';

586

607

drop table t1;

587

TEMPORARY

588

create TEMPORARY table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM;

608

set @a:=now();

609

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

610

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

611

select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;

612

613

614

615

616

select a from t1 natural join t1 as t2 where b >= @a order by a;

617

618

619

620

621

update t1 set a=5 where a=1;

622

select a from t1;

623

624

625

626

627

drop table t1;

628

create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM;

589

629

insert into t1 values("hello",1),("world",2);

590

630

select * from t1 order by b desc;

591

631

a b

592

632

world 2

593

633

hello 1

594

alter table t1 ENGINE=MyISAM;

634

optimize table t1;

635

Table Op Msg_type Msg_text

636

test.t1 optimize status OK

637

show keys from t1;

638

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

639

t1 0 PRIMARY 1 a A # NULL NULL BTREE

595

640

drop table t1;

596

create TEMPORARY table t1 (i int, j int ) ENGINE=MyISAM;

641

create table t1 (i int, j int ) ENGINE=MyISAM;

597

642

insert into t1 values (1,2);

598

643

select * from t1 where i=1 and j=2;

599

644

i j

603

648

i j

604

649

1 2

605

650

drop table t1;

606

create TEMPORARY table t1 (

607

a int NOT NULL,

608

b int NOT NULL,

651

CREATE TABLE t1 (

652

a int3 unsigned NOT NULL,

653

b int1 unsigned NOT NULL,

609

654

UNIQUE (a, b)

610

655

) ENGINE = MyISAM;

611

656

INSERT INTO t1 VALUES (1, 1);

613

658

MIN(B) MAX(b)

614

659

1 1

615

660

drop table t1;

616

create TEMPORARY table t1 (a int NOT NULL) engine=MyISAM;

661

CREATE TABLE t1 (a int unsigned NOT NULL) engine=MyISAM;

617

662

INSERT INTO t1 VALUES (1);

618

663

SELECT * FROM t1;

619

664

620

665

621

666

DROP TABLE t1;

622

create TEMPORARY table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = MyISAM;

667

create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = MyISAM;

623

668

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

624

669

explain select * from t1 where a > 0 and a < 50;

625

670

id select_type table type possible_keys key key_len ref rows Extra

626

671

1 SIMPLE t1 system PRIMARY NULL NULL NULL #

627

672

drop table t1;

628

create TEMPORARY table t1 (a char(20), unique (a(5))) engine=MyISAM;

673

create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=MyISAM;

674

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

675

LOCK TABLES t1 WRITE;

676

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

677

ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'

678

select id from t1;

679

680

681

682

683

684

select id from t1;

685

686

687

688

689

690

UNLOCK TABLES;

691

DROP TABLE t1;

692

create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=MyISAM;

693

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

694

LOCK TABLES t1 WRITE;

695

begin;

696

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

697

ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'

698

select id from t1;

699

700

701

702

703

704

insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');

705

commit;

706

select id,id3 from t1;

707

id id3

708

0 0

709

2 2

710

1 1

711

99 2

712

100 2

713

UNLOCK TABLES;

714

DROP TABLE t1;

715

create table t1 (a char(20), unique (a(5))) engine=MyISAM;

629

716

drop table t1;

630

create TEMPORARY table t1 (a char(20), index (a(5))) engine=MyISAM;

717

create table t1 (a char(20), index (a(5))) engine=MyISAM;

631

718

show create table t1;

632

719

Table Create Table

633

t1 CREATE TEMPORARY TABLE `t1` (

634

`a` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,

720

t1 CREATE TABLE `t1` (

721

`a` char(20) DEFAULT NULL,

635

722

KEY `a` (`a`(5))

636

) ENGINE=MyISAM COLLATE = utf8_general_ci

723

) ENGINE=MyISAM DEFAULT CHARSET=latin1

637

724

drop table t1;

638

725

create temporary table t1 (a int not null auto_increment, primary key(a)) engine=MyISAM;

639

726

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

651

738

2 NULL

652

739

4 NULL

653

740

drop table t1;

654

create TEMPORARY table t1

741

create table t1

655

742

(

656

743

id int auto_increment primary key,

657

744

name varchar(32) not null,

661

748

) engine=MyISAM;

662

749

insert into t1 values (1,'one','one value',101),

663

750

(2,'two','two value',102),(3,'three','three value',103);

751

set insert_id=5;

664

752

replace into t1 (value,name,uid) values ('other value','two',102);

665

753

delete from t1 where uid=102;

754

set insert_id=5;

666

755

replace into t1 (value,name,uid) values ('other value','two',102);

756

set insert_id=6;

667

757

replace into t1 (value,name,uid) values ('other value','two',102);

668

758

select * from t1;

669

759

id name value uid

670

760

1 one one value 101

761

3 three three value 103

671

762

6 two other value 102

672

3 three three value 103

673

763

drop table t1;

674

764

create database mysqltest;

675

create TEMPORARY table mysqltest.t1 (a int not null) engine= MyISAM;

765

create table mysqltest.t1 (a int not null) engine= MyISAM;

676

766

insert into mysqltest.t1 values(1);

677

create TEMPORARY table mysqltest.t2 (a int not null) engine= MEMORY;

767

create table mysqltest.t2 (a int not null) engine= MEMORY;

678

768

insert into mysqltest.t2 values(1);

679

create TEMPORARY table mysqltest.t3 (a int not null) engine= MEMORY;

769

create table mysqltest.t3 (a int not null) engine= MEMORY;

680

770

insert into mysqltest.t3 values(1);

681

771

commit;

682

772

drop database mysqltest;

683

773

show tables from mysqltest;

684

ERROR 42000: Unknown schema 'mysqltest'

774

ERROR 42000: Unknown database 'mysqltest'

685

775

set autocommit=0;

686

create TEMPORARY table t1 (a int not null) engine= MyISAM;

776

create table t1 (a int not null) engine= MyISAM;

687

777

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

688

778

truncate table t1;

689

779

commit;

698

788

commit;

699

789

drop table t1;

700

790

set autocommit=1;

701

create TEMPORARY table t1 (a int not null) engine= MyISAM;

791

create table t1 (a int not null) engine= MyISAM;

702

792

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

703

793

truncate table t1;

704

794

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

712

802

select * from t1;

713

803

714

804

drop table t1;

715

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

805

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

716

806

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

717

807

explain select * from t1 order by a;

718

808

id select_type table type possible_keys key key_len ref rows Extra

739

829

id select_type table type possible_keys key key_len ref rows Extra

740

830

1 SIMPLE t1 ALL NULL NULL NULL NULL #

741

831

drop table t1;

742

create TEMPORARY table t1 (t int not null default 1, key (t)) engine=MyISAM;

832

create table t1 (t int not null default 1, key (t)) engine=MyISAM;

833

desc t1;

834

Field Type Null Key Default Extra

835

t int(11) NO MUL 1

743

836

drop table t1;

744

create TEMPORARY 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=MyISAM;

837

CREATE TABLE t1 (

838

number bigint(20) NOT NULL default '0',

839

cname char(15) NOT NULL default '',

840

carrier_id smallint(6) NOT NULL default '0',

841

privacy tinyint(4) NOT NULL default '0',

842

last_mod_date timestamp NOT NULL,

843

last_mod_id smallint(6) NOT NULL default '0',

844

last_app_date timestamp NOT NULL,

845

last_app_id smallint(6) default '-1',

846

version smallint(6) NOT NULL default '0',

847

assigned_scps int(11) default '0',

848

status tinyint(4) default '0'

849

) ENGINE=MyISAM;

850

INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);

851

INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);

852

INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);

853

INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);

854

INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);

855

INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);

856

CREATE TABLE t2 (

857

number bigint(20) NOT NULL default '0',

858

cname char(15) NOT NULL default '',

859

carrier_id smallint(6) NOT NULL default '0',

860

privacy tinyint(4) NOT NULL default '0',

861

last_mod_date timestamp NOT NULL,

862

last_mod_id smallint(6) NOT NULL default '0',

863

last_app_date timestamp NOT NULL,

864

last_app_id smallint(6) default '-1',

865

version smallint(6) NOT NULL default '0',

866

assigned_scps int(11) default '0',

867

status tinyint(4) default '0'

868

) ENGINE=MyISAM;

869

INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);

870

INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);

871

INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);

872

INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);

873

select * from t1;

874

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

875

4077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1

876

9197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0

877

650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1

878

302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0

879

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

880

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

881

select * from t2;

882

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

883

4077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1

884

9197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0

885

650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1

886

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

887

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

888

select * from t1;

889

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

890

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

891

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

892

select * from t2;

893

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

894

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

895

select * from t2;

896

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

897

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

898

drop table t1,t2;

899

create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;

745

900

BEGIN;

746

901

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

747

902

SELECT @@tx_isolation,@@global.tx_isolation;

779

934

7 4 Matt

780

935

COMMIT;

781

936

DROP TABLE t1;

782

create TEMPORARY table t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM;

937

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

938

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

939

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

940

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

941

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

942

select * from t1;

943

n d

944

1 10

945

1 10

946

select * from t2;

947

n d

948

1 30

949

2 20

950

drop table t1,t2;

951

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

952

insert into t1 values(20,null);

953

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

954

t2.b=t3.a;

955

b ifnull(t2.b,"this is null")

956

NULL this is null

957

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

958

t2.b=t3.a order by 1;

959

b ifnull(t2.b,"this is null")

960

NULL this is null

961

insert into t1 values(10,null);

962

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

963

t2.b=t3.a order by 1;

964

b ifnull(t2.b,"this is null")

965

NULL this is null

966

NULL this is null

967

drop table t1;

968

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

969

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

970

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

971

972

drop table t1,t2;

973

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

974

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

975

insert into t1 values (10, 20);

976

insert into t2 values (10, 20);

977

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

978

drop table t1,t2;

979

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

783

980

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

784

981

UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;

785

982

SELECT * from t1;

794

991

8 8

795

992

9 9

796

993

drop table t1;

797

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

798

create TEMPORARY table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY;

994

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

995

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

996

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

997

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

998

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

999

select * from t1;

1000

a b

1001

101 1

1002

102 2

1003

103 3

1004

104 4

1005

105 5

1006

106 6

1007

107 7

1008

108 8

1009

109 9

1010

110 10

1011

111 11

1012

112 12

1013

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

1014

select * from t1;

1015

a b

1016

201 1

1017

102 2

1018

103 3

1019

104 4

1020

105 5

1021

106 6

1022

107 7

1023

108 8

1024

109 9

1025

110 10

1026

111 11

1027

112 12

1028

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

1029

select * from t1;

1030

a b

1031

201 1

1032

102 12

1033

103 3

1034

104 4

1035

105 5

1036

106 6

1037

107 7

1038

108 8

1039

109 9

1040

110 10

1041

111 11

1042

112 12

1043

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;

1044

select * from t1;

1045

a b

1046

201 1

1047

102 12

1048

103 5

1049

104 6

1050

105 7

1051

106 6

1052

107 7

1053

108 8

1054

109 9

1055

110 10

1056

111 11

1057

112 12

1058

select * from t2;

1059

a b

1060

1 1

1061

2 2

1062

3 13

1063

4 14

1064

5 15

1065

6 6

1066

7 7

1067

8 8

1068

9 9

1069

drop table t1,t2;

1070

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

1071

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

1072

SET AUTOCOMMIT=0;

1073

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

1074

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

1075

ROLLBACK;

1076

Warnings:

1077

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

1078

SELECT * FROM t1;

1079

B_ID

1080

1081

drop table t1,t2;

1082

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

1083

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

1084

select distinct parent,child from t1 order by parent;

1085

parent child

1086

0 4

1087

1 2

1088

1 3

1089

2 1

1090

drop table t1;

1091

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

1092

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

799

1093

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

800

1094

insert into t2 (a) select b from t1;

801

1095

insert into t1 (b) select b from t2;

820

1114

29267

821

1115

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

822

1116

id select_type table type possible_keys key key_len ref rows Extra

823

1 SIMPLE t1 range c c 5 NULL # Using where

1117

1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR

824

1118

update t1 set c=a;

825

1119

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

826

1120

id select_type table type possible_keys key key_len ref rows Extra

827

1 SIMPLE t1 range c c 5 NULL # Using where

1121

1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR

828

1122

drop table t1,t2;

829

create TEMPORARY table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM;

1123

create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM;

830

1124

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

831

1125

update t1 set fk=69 where fk is null order by id limit 1;

832

1126

SELECT * from t1;

837

1131

4 NULL

838

1132

5 NULL

839

1133

drop table t1;

840

create TEMPORARY table t1 (a int not null, b int not null, key (a)) engine=MyISAM;

1134

create table t1 (a int not null, b int not null, key (a)) engine=MyISAM;

841

1135

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

842

1136

SET @tmp=0;

843

1137

update t1 set b=(@tmp:=@tmp+1) order by a;

859

1153

111 100

860

1154

111 100

861

1155

drop table t1;

1156

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

1157

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

1158

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

1159

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

1160

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

1161

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

1162

update t1 set a=c, b=c;

1163

create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=MyISAM;

1164

insert into t2 select * from t1;

1165

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

1166

drop table t1,t2;

862

1167

SET AUTOCOMMIT=1;

863

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

1168

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

864

1169

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

865

1170

truncate table t1;

866

1171

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

869

1174

870

1175

871

1176

drop table t1;

872

create TEMPORARY table t1 (col1 int)ENGINE=MyISAM;

873

create TEMPORARY table t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx

1177

CREATE TABLE t1 (col1 int(1))ENGINE=MyISAM;

1178

CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx

874

1179

(stamp))ENGINE=MyISAM;

875

1180

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

876

1181

insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);

877

ERROR HY000: Received an invalid value '20020204310000' for a UNIX timestamp.

1182

Warnings:

1183

Warning 1265 Data truncated for column 'stamp' at row 3

878

1184

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <

879

1185

'20020204120000' GROUP BY col1;

880

1186

col1

881

1187

882

1188

883

1189

884

drop table t1,t2;

885

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

886

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

887

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

888

create TEMPORARY table t4 (a int, b varchar(200), c varchar(200) not null) engine=MEMORY;

889

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

890

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

1190

1191

drop table t1,t2;

1192

CREATE TABLE t1 (

1193

`id` int(10) unsigned NOT NULL auto_increment,

1194

`id_object` int(10) unsigned default '0',

1195

`id_version` int(10) unsigned NOT NULL default '1',

1196

`label` varchar(100) NOT NULL default '',

1197

`description` text,

1198

PRIMARY KEY (`id`),

1199

KEY `id_object` (`id_object`),

1200

KEY `id_version` (`id_version`)

1201

) ENGINE=MyISAM;

1202

INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);

1203

CREATE TABLE t2 (

1204

`id` int(10) unsigned NOT NULL auto_increment,

1205

`id_version` int(10) unsigned NOT NULL default '1',

1206

PRIMARY KEY (`id`),

1207

KEY `id_version` (`id_version`)

1208

) ENGINE=MyISAM;

1209

INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");

1210

SELECT t2.id, t1.`label` FROM t2 INNER JOIN

1211

(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl

1212

ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);

1213

id label

1214

3382 Test

1215

102 Le Pekin (Test)

1216

1794 Test de resto

1217

1822 Test 3

1218

3524 Societe Test

1219

3525 Fournisseur Test

1220

drop table t1,t2;

1221

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

1222

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

1223

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

1224

create table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=MEMORY;

1225

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

1226

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

891

1227

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

892

1228

insert t2 select * from t1;

893

1229

insert t3 select * from t1;

894

1230

insert t4 select * from t1;

895

1231

insert t5 select * from t1;

896

1232

insert t6 select * from t1;

1233

checksum table t1, t2, t3, t4, t5, t6, t7 quick;

1234

Table Checksum

1235

test.t1 2948697075

1236

test.t2 NULL

1237

test.t3 NULL

1238

test.t4 NULL

1239

test.t5 2948697075

1240

test.t6 NULL

1241

test.t7 NULL

1242

Warnings:

1243

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

1244

checksum table t1, t2, t3, t4, t5, t6, t7;

1245

Table Checksum

1246

test.t1 2948697075

1247

test.t2 2948697075

1248

test.t3 2948697075

1249

test.t4 2948697075

1250

test.t5 2948697075

1251

test.t6 2948697075

1252

test.t7 NULL

1253

Warnings:

1254

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

1255

checksum table t1, t2, t3, t4, t5, t6, t7 extended;

1256

Table Checksum

1257

test.t1 2948697075

1258

test.t2 2948697075

1259

test.t3 2948697075

1260

test.t4 2948697075

1261

test.t5 2948697075

1262

test.t6 2948697075

1263

test.t7 NULL

1264

Warnings:

1265

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

897

1266

drop table t1,t2,t3, t4, t5, t6;

898

create TEMPORARY table t1 (a int) engine=MyISAM;

1267

create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=MyISAM;

1268

insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');

1269

select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;

1270

trim(name2)

1271

fff

1272

sss

1273

ttt

1274

first

1275

second

1276

third

1277

1278

1279

1280

drop table t1;

1281

create table t1 (a int) engine=MyISAM;

899

1282

create table t2 like t1;

900

ERROR HY000: Can't create table 'test.t2' (errno: 138)

901

create table t2 like t1 engine=innodb;

902

1283

show create table t2;

903

1284

Table Create Table

904

1285

t2 CREATE TABLE `t2` (

905

`a` INT DEFAULT NULL

906

) ENGINE=InnoDB COLLATE = utf8_general_ci

1286

`a` int(11) DEFAULT NULL

1287

) ENGINE=MyISAM DEFAULT CHARSET=latin1

907

1288

drop table t1,t2;

908

1289

flush status;

909

1290

show status like "binlog_cache_use";

910

1291

Variable_name Value

1292

Binlog_cache_use 0

911

1293

show status like "binlog_cache_disk_use";

912

1294

Variable_name Value

913

create TEMPORARY table t1 (a int) engine=MyISAM;

1295

Binlog_cache_disk_use 0

1296

create table t1 (a int) engine=MyISAM;

914

1297

show status like "binlog_cache_use";

915

1298

Variable_name Value

1299

Binlog_cache_use 0

916

1300

show status like "binlog_cache_disk_use";

917

1301

Variable_name Value

1302

Binlog_cache_disk_use 0

918

1303

begin;

919

1304

delete from t1;

920

1305

commit;

921

1306

show status like "binlog_cache_use";

922

1307

Variable_name Value

1308

Binlog_cache_use 0

923

1309

show status like "binlog_cache_disk_use";

924

1310

Variable_name Value

1311

Binlog_cache_disk_use 0

925

1312

drop table t1;

926

create TEMPORARY table t1 (c char(10), index (c,c)) engine=MyISAM;

1313

create table t1 (c char(10), index (c,c)) engine=MyISAM;

927

1314

ERROR 42S21: Duplicate column name 'c'

928

create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM;

929

ERROR 42S21: Duplicate column name 'c1'

930

create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM;

931

ERROR 42S21: Duplicate column name 'c1'

932

create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM;

933

ERROR 42S21: Duplicate column name 'c1'

934

create TEMPORARY table t1 (c1 char(10), c2 char(10)) engine=MyISAM;

1315

create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM;

1316

ERROR 42S21: Duplicate column name 'c1'

1317

create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM;

1318

ERROR 42S21: Duplicate column name 'c1'

1319

create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM;

1320

ERROR 42S21: Duplicate column name 'c1'

1321

create table t1 (c1 char(10), c2 char(10)) engine=MyISAM;

935

1322

alter table t1 add key (c1,c1);

936

1323

ERROR 42S21: Duplicate column name 'c1'

937

1324

alter table t1 add key (c2,c1,c1);

941

1328

alter table t1 add key (c1,c1,c2);

942

1329

ERROR 42S21: Duplicate column name 'c1'

943

1330

drop table t1;

944

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

1331

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

945

1332

insert into t1 values ('1111', '3333');

946

1333

select distinct concat(a, b) from t1;

947

1334

concat(a, b)

955

1342

956

1343

957

1344

drop table t1;

958

create TEMPORARY table t1 (a int) engine=MyISAM;

1345

create table t1 (a int) engine=MyISAM;

959

1346

insert into t1 values (4711);

960

1347

truncate t1;

961

1348

insert into t1 values (42);

963

1350

964

1351

965

1352

drop table t1;

966

create TEMPORARY table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=MyISAM;

1353

create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=MyISAM;

967

1354

insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);

968

1355

select * from t1 order by a,b,c,d;

969

1356

a b c d e

974

1361

id select_type table type possible_keys key key_len ref rows Extra

975

1362

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort

976

1363

drop table t1;

977

create TEMPORARY table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

1364

create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

978

1365

insert into t1 values ('8', '6'), ('4', '7');

979

1366

select min(a) from t1;

980

1367

min(a)

983

1370

min(b)

984

1371

985

1372

drop table t1;

986

create TEMPORARY table t1 (x bigint not null primary key) engine=MyISAM;

1373

create table t1 (x bigint unsigned not null primary key) engine=MyISAM;

987

1374

insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);

988

ERROR 22003: Out of range value for column 'x' at row 1

989

1375

select * from t1;

990

1376

1377

18446744073709551600

1378

18446744073709551601

991

1379

select count(*) from t1 where x>0;

992

1380

count(*)

993

1381

994

1382

select count(*) from t1 where x=0;

995

1383

count(*)

996

1384

1005

1393

1006

1394

explain select count(*) from t1 where x > -16;

1007

1395

id select_type table type possible_keys key key_len ref rows Extra

1008

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables

1396

1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index

1009

1397

select count(*) from t1 where x > -16;

1010

1398

count(*)

1011

1399

1012

1400

select * from t1 where x > -16;

1013

1401

1402

18446744073709551600

1403

18446744073709551601

1014

1404

select count(*) from t1 where x = 18446744073709551601;

1015

1405

count(*)

1016

1406

1017

1407

drop table t1;

1018

1408

set storage_engine=MyISAM;

1019

1409

drop table if exists t1,t2,t3;

1020

1410

--- Testing varchar ---

1021

1411

--- Testing varchar ---

1022

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

1412

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

1023

1413

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

1024

1414

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

1025

1415

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

1026

ERROR 22001: Data too long for column 'v' at row 1

1027

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

1028

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

1029

ERROR 22001: Data too long for column 'v' at row 1

1030

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

1031

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

1416

Warnings:

1417

Note 1265 Data truncated for column 'v' at row 1

1418

Note 1265 Data truncated for column 'c' at row 1

1032

1419

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

1033

1420

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

1034

*+ *+ *+ *

1035

*+ *+ *+ *

1421

*+ *+*+ *

1422

*+ *+*+ *

1036

1423

show create table t1;

1037

1424

Table Create Table

1038

t1 CREATE TEMPORARY TABLE `t1` (

1039

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1040

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1041

`t` TEXT COLLATE utf8_general_ci

1042

) ENGINE=MyISAM COLLATE = utf8_general_ci

1043

create TEMPORARY table t2 like t1;

1425

t1 CREATE TABLE `t1` (

1426

`v` varchar(10) DEFAULT NULL,

1427

`c` char(10) DEFAULT NULL,

1428

`t` text

1429

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1430

create table t2 like t1;

1044

1431

show create table t2;

1045

1432

Table Create Table

1046

t2 CREATE TEMPORARY TABLE `t2` (

1047

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1048

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1049

`t` TEXT COLLATE utf8_general_ci

1050

) ENGINE=MyISAM COLLATE = utf8_general_ci

1051

create TEMPORARY table t3 select * from t1;

1433

t2 CREATE TABLE `t2` (

1434

`v` varchar(10) DEFAULT NULL,

1435

`c` char(10) DEFAULT NULL,

1436

`t` text

1437

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1438

create table t3 select * from t1;

1052

1439

show create table t3;

1053

1440

Table Create Table

1054

t3 CREATE TEMPORARY TABLE `t3` (

1055

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1056

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1057

`t` TEXT COLLATE utf8_general_ci

1058

) ENGINE=MyISAM COLLATE = utf8_general_ci

1441

t3 CREATE TABLE `t3` (

1442

`v` varchar(10) DEFAULT NULL,

1443

`c` char(10) DEFAULT NULL,

1444

`t` text

1445

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1059

1446

alter table t1 modify c varchar(10);

1060

1447

show create table t1;

1061

1448

Table Create Table

1062

t1 CREATE TEMPORARY TABLE `t1` (

1063

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1064

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1065

`t` TEXT COLLATE utf8_general_ci

1066

) ENGINE=MyISAM COLLATE = utf8_general_ci

1449

t1 CREATE TABLE `t1` (

1450

`v` varchar(10) DEFAULT NULL,

1451

`c` varchar(10) DEFAULT NULL,

1452

`t` text

1453

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1067

1454

alter table t1 modify v char(10);

1068

1455

show create table t1;

1069

1456

Table Create Table

1070

t1 CREATE TEMPORARY TABLE `t1` (

1071

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1072

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1073

`t` TEXT COLLATE utf8_general_ci

1074

) ENGINE=MyISAM COLLATE = utf8_general_ci

1457

t1 CREATE TABLE `t1` (

1458

`v` char(10) DEFAULT NULL,

1459

`c` varchar(10) DEFAULT NULL,

1460

`t` text

1461

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1075

1462

alter table t1 modify t varchar(10);

1463

Warnings:

1464

Note 1265 Data truncated for column 't' at row 2

1076

1465

show create table t1;

1077

1466

Table Create Table

1078

t1 CREATE TEMPORARY TABLE `t1` (

1079

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1080

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1081

`t` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

1082

) ENGINE=MyISAM COLLATE = utf8_general_ci

1467

t1 CREATE TABLE `t1` (

1468

`v` char(10) DEFAULT NULL,

1469

`c` varchar(10) DEFAULT NULL,

1470

`t` varchar(10) DEFAULT NULL

1471

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1083

1472

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

1084

1473

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

1085

*+ *+ *+ *

1086

*+ *+ *+ *

1474

*+*+*+ *

1475

*+*+*+ *

1087

1476

drop table t1,t2,t3;

1088

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

1477

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

1089

1478

show create table t1;

1090

1479

Table Create Table

1091

t1 CREATE TEMPORARY TABLE `t1` (

1092

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1093

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1094

`t` TEXT COLLATE utf8_general_ci,

1480

t1 CREATE TABLE `t1` (

1481

`v` varchar(10) DEFAULT NULL,

1482

`c` char(10) DEFAULT NULL,

1483

`t` text,

1095

1484

KEY `v` (`v`),

1096

1485

KEY `c` (`c`),

1097

1486

KEY `t` (`t`(10))

1098

) ENGINE=MyISAM COLLATE = utf8_general_ci

1487

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1099

1488

select count(*) from t1;

1100

1489

count(*)

1101

1490

270

1138

1527

1139

1528

explain select count(*) from t1 where v='a ';

1140

1529

id select_type table type possible_keys key key_len ref rows Extra

1141

1 SIMPLE t1 ref v v 43 const # Using where; Using index

1530

1 SIMPLE t1 ref v v 13 const # Using where; Using index

1142

1531

explain select count(*) from t1 where c='a ';

1143

1532

id select_type table type possible_keys key key_len ref rows Extra

1144

1 SIMPLE t1 ref c c 43 const # Using where; Using index

1533

1 SIMPLE t1 ref c c 11 const # Using where; Using index

1145

1534

explain select count(*) from t1 where t='a ';

1146

1535

id select_type table type possible_keys key key_len ref rows Extra

1147

1 SIMPLE t1 ref t t 43 const # Using where

1536

1 SIMPLE t1 ref t t 13 const # Using where

1148

1537

explain select count(*) from t1 where v like 'a%';

1149

1538

id select_type table type possible_keys key key_len ref rows Extra

1150

1 SIMPLE t1 range v v 43 NULL # Using where; Using index

1539

1 SIMPLE t1 range v v 13 NULL # Using where; Using index

1151

1540

explain select count(*) from t1 where v between 'a' and 'a ';

1152

1541

id select_type table type possible_keys key key_len ref rows Extra

1153

1 SIMPLE t1 ref v v 43 const # Using where; Using index

1542

1 SIMPLE t1 ref v v 13 const # Using where; Using index

1154

1543

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1155

1544

id select_type table type possible_keys key key_len ref rows Extra

1156

1 SIMPLE t1 ref v v 43 const # Using where; Using index

1545

1 SIMPLE t1 ref v v 13 const # Using where; Using index

1157

1546

alter table t1 add unique(v);

1158

1547

ERROR 23000: Duplicate entry '{ ' for key 'v_2'

1159

1548

alter table t1 add key(v);

1160

1549

select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';

1161

1550

1162

1551

*a*a*a*

1163

*a *a *a *

1164

*a *a *a *

1165

*a *a *a *

1166

*a *a *a *

1167

*a *a *a *

1168

*a *a *a *

1169

*a *a *a *

1170

*a *a *a *

1171

*a *a *a *

1552

*a *a*a *

1553

*a *a*a *

1554

*a *a*a *

1555

*a *a*a *

1556

*a *a*a *

1557

*a *a*a *

1558

*a *a*a *

1559

*a *a*a *

1560

*a *a*a *

1172

1561

explain select * from t1 where v='a';

1173

1562

id select_type table type possible_keys key key_len ref rows Extra

1174

1 SIMPLE t1 ref v,v_2 # 43 const # Using where

1563

1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition

1175

1564

select v,count(*) from t1 group by v limit 10;

1176

1565

v count(*)

1177

1566

a 1

1307

1696

alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);

1308

1697

show create table t1;

1309

1698

Table Create Table

1310

t1 CREATE TEMPORARY TABLE `t1` (

1311

`v` VARCHAR(300) COLLATE utf8_general_ci DEFAULT NULL,

1312

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1313

`t` TEXT COLLATE utf8_general_ci,

1699

t1 CREATE TABLE `t1` (

1700

`v` varchar(300) DEFAULT NULL,

1701

`c` char(10) DEFAULT NULL,

1702

`t` text,

1314

1703

KEY `c` (`c`),

1315

1704

KEY `t` (`t`(10)),

1316

1705

KEY `v` (`v`)

1317

) ENGINE=MyISAM COLLATE = utf8_general_ci

1706

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1318

1707

select count(*) from t1 where v='a';

1319

1708

count(*)

1320

1709

1335

1724

1336

1725

explain select count(*) from t1 where v='a ';

1337

1726

id select_type table type possible_keys key key_len ref rows Extra

1338

1 SIMPLE t1 ref v v 1203 const # Using where; Using index

1727

1 SIMPLE t1 ref v v 303 const # Using where; Using index

1339

1728

explain select count(*) from t1 where v like 'a%';

1340

1729

id select_type table type possible_keys key key_len ref rows Extra

1341

1 SIMPLE t1 range v v 1203 NULL # Using where; Using index

1730

1 SIMPLE t1 range v v 303 NULL # Using where; Using index

1342

1731

explain select count(*) from t1 where v between 'a' and 'a ';

1343

1732

id select_type table type possible_keys key key_len ref rows Extra

1344

1 SIMPLE t1 ref v v 1203 const # Using where; Using index

1733

1 SIMPLE t1 ref v v 303 const # Using where; Using index

1345

1734

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1346

1735

id select_type table type possible_keys key key_len ref rows Extra

1347

1 SIMPLE t1 ref v v 1203 const # Using where; Using index

1736

1 SIMPLE t1 ref v v 303 const # Using where; Using index

1348

1737

explain select * from t1 where v='a';

1349

1738

id select_type table type possible_keys key key_len ref rows Extra

1350

1 SIMPLE t1 ref v v 1203 const # Using where

1739

1 SIMPLE t1 ref v v 303 const # Using index condition

1351

1740

select v,count(*) from t1 group by v limit 10;

1352

1741

v count(*)

1353

1742

a 1

1387

1776

alter table t1 drop key v, add key v (v(30));

1388

1777

show create table t1;

1389

1778

Table Create Table

1390

t1 CREATE TEMPORARY TABLE `t1` (

1391

`v` VARCHAR(300) COLLATE utf8_general_ci DEFAULT NULL,

1392

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1393

`t` TEXT COLLATE utf8_general_ci,

1779

t1 CREATE TABLE `t1` (

1780

`v` varchar(300) DEFAULT NULL,

1781

`c` char(10) DEFAULT NULL,

1782

`t` text,

1394

1783

KEY `c` (`c`),

1395

1784

KEY `t` (`t`(10)),

1396

1785

KEY `v` (`v`(30))

1397

) ENGINE=MyISAM COLLATE = utf8_general_ci

1786

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1398

1787

select count(*) from t1 where v='a';

1399

1788

count(*)

1400

1789

1415

1804

1416

1805

explain select count(*) from t1 where v='a ';

1417

1806

id select_type table type possible_keys key key_len ref rows Extra

1418

1 SIMPLE t1 ref v v 123 const # Using where

1807

1 SIMPLE t1 ref v v 33 const # Using where

1419

1808

explain select count(*) from t1 where v like 'a%';

1420

1809

id select_type table type possible_keys key key_len ref rows Extra

1421

1 SIMPLE t1 range v v 123 NULL # Using where

1810

1 SIMPLE t1 range v v 33 NULL # Using where

1422

1811

explain select count(*) from t1 where v between 'a' and 'a ';

1423

1812

id select_type table type possible_keys key key_len ref rows Extra

1424

1 SIMPLE t1 ref v v 123 const # Using where

1813

1 SIMPLE t1 ref v v 33 const # Using where

1425

1814

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1426

1815

id select_type table type possible_keys key key_len ref rows Extra

1427

1 SIMPLE t1 ref v v 123 const # Using where

1816

1 SIMPLE t1 ref v v 33 const # Using where

1428

1817

explain select * from t1 where v='a';

1429

1818

id select_type table type possible_keys key key_len ref rows Extra

1430

1 SIMPLE t1 ref v v 123 const # Using where

1819

1 SIMPLE t1 ref v v 33 const # Using where

1431

1820

select v,count(*) from t1 group by v limit 10;

1432

1821

v count(*)

1433

1822

a 1

1465

1854

h 10

1466

1855

i 10

1467

1856

alter table t1 modify v varchar(600), drop key v, add key v (v);

1468

Warnings:

1469

Warning 1071 Specified key was too long; max key length is 1332 bytes

1470

1857

show create table t1;

1471

1858

Table Create Table

1472

t1 CREATE TEMPORARY TABLE `t1` (

1473

`v` VARCHAR(600) COLLATE utf8_general_ci DEFAULT NULL,

1474

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1475

`t` TEXT COLLATE utf8_general_ci,

1859

t1 CREATE TABLE `t1` (

1860

`v` varchar(600) DEFAULT NULL,

1861

`c` char(10) DEFAULT NULL,

1862

`t` text,

1476

1863

KEY `c` (`c`),

1477

1864

KEY `t` (`t`(10)),

1478

KEY `v` (`v`(333))

1479

) ENGINE=MyISAM COLLATE = utf8_general_ci

1865

KEY `v` (`v`)

1866

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1480

1867

select v,count(*) from t1 group by v limit 10;

1481

1868

v count(*)

1482

1869

a 1

1514

1901

h 10

1515

1902

i 10

1516

1903

drop table t1;

1517

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

1904

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

1518

1905

insert into t1 values ('a ');

1519

1906

insert into t1 values ('a ');

1520

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

1907

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

1521

1908

alter table t1 modify a varchar(10);

1522

1909

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

1523

1910

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

1524

1911

insert into t1 values ('a ');

1525

1912

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

1526

1913

insert into t1 values ('a ');

1527

ERROR 22001: Data too long for column 'a' at row 1

1914

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

1528

1915

insert into t1 values ('a ');

1529

1916

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

1530

1917

update t1 set a='a ' where a like 'a%';

1544

1931

concat(a,'.')

1545

1932

a .

1546

1933

drop table t1;

1547

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

1934

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

1548

1935

show create table t1;

1549

1936

Table Create Table

1550

t1 CREATE TEMPORARY TABLE `t1` (

1551

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1552

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1553

`t` TEXT COLLATE utf8_general_ci,

1937

t1 CREATE TABLE `t1` (

1938

`v` varchar(10) DEFAULT NULL,

1939

`c` char(10) DEFAULT NULL,

1940

`t` text,

1554

1941

KEY `v` (`v`(5)),

1555

1942

KEY `c` (`c`(5)),

1556

1943

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

1557

) ENGINE=MyISAM COLLATE = utf8_general_ci

1558

drop table t1;

1559

create TEMPORARY table t1 (v char(10));

1560

show create table t1;

1561

Table Create Table

1562

t1 CREATE TEMPORARY TABLE `t1` (

1563

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

1564

) ENGINE=MyISAM COLLATE = utf8_general_ci

1565

drop table t1;

1566

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

1567

show create table t1;

1568

Table Create Table

1569

t1 CREATE TEMPORARY TABLE `t1` (

1570

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1571

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

1572

) ENGINE=MyISAM COLLATE = utf8_general_ci

1944

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1945

drop table t1;

1946

create table t1 (v char(10) character set utf8);

1947

show create table t1;

1948

Table Create Table

1949

t1 CREATE TABLE `t1` (

1950

`v` char(10) CHARACTER SET utf8 DEFAULT NULL

1951

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1952

drop table t1;

1953

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

1954

show create table t1;

1955

Table Create Table

1956

t1 CREATE TABLE `t1` (

1957

`v` varchar(10) DEFAULT NULL,

1958

`c` char(10) DEFAULT NULL

1959

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

1573

1960

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

1574

1961

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

1575

1962

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

1576

1963

*a*a*

1577

*a *a *

1578

drop table t1;

1579

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

1964

*a *a*

1965

drop table t1;

1966

create table t1 (v varchar(65530), key(v(10)));

1967

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

1968

select length(v) from t1 where v=repeat('a',65530);

1969

length(v)

1970

65530

1971

drop table t1;

1972

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

1580

1973

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

1581

1974

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

1582

1975

id select_type table type possible_keys key key_len ref rows Extra

1583

1 SIMPLE t1 ref ba ba 56 const,const 1 Using where; Using index

1976

1 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index

1584

1977

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

1585

1978

a b

1586

1979

20 NULL

1587

1980

drop table t1;

1588

create TEMPORARY table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

1981

create table t1 (v varchar(65530), key(v));

1982

Warnings:

1983

Warning 1071 Specified key was too long; max key length is 1332 bytes

1984

drop table t1;

1985

create table t1 (v varchar(65536));

1986

Warnings:

1987

Note 1246 Converting column 'v' from VARCHAR to TEXT

1988

show create table t1;

1989

Table Create Table

1990

t1 CREATE TABLE `t1` (

1991

`v` mediumtext

1992

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1993

drop table t1;

1994

create table t1 (v varchar(65530) character set utf8);

1995

Warnings:

1996

Note 1246 Converting column 'v' from VARCHAR to TEXT

1997

show create table t1;

1998

Table Create Table

1999

t1 CREATE TABLE `t1` (

2000

`v` mediumtext CHARACTER SET utf8

2001

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2002

drop table t1;

2003

set storage_engine=MEMORY;

2004

create table t1 (v varchar(16384)) engine=MyISAM;

2005

drop table t1;

2006

create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

1589

2007

insert into t1 values ('8', '6'), ('4', '7');

1590

2008

select min(a) from t1;

1591

2009

min(a)

1594

2012

min(b)

1595

2013

1596

2014

drop table t1;

1597

create TEMPORARY table t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM;

2015

CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM;

1598

2016

insert into t1 (b) values (1);

1599

2017

replace into t1 (b) values (2), (1), (3);

1600

2018

select * from t1;

1613

2031

2 2

1614

2032

4 3

1615

2033

drop table t1;

1616

create TEMPORARY table t1 (rowid int not null auto_increment, val int not null,primary

2034

create table t1 (rowid int not null auto_increment, val int not null,primary

1617

2035

key (rowid), unique(val)) engine=MyISAM;

1618

2036

replace into t1 (val) values ('1'),('2');

1619

2037

replace into t1 (val) values ('1'),('2');

1624

2042

3 1

1625

2043

4 2

1626

2044

drop table t1;

1627

create TEMPORARY table t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM;

2045

CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM;

1628

2046

INSERT INTO t1 (GRADE) VALUES (151),(252),(343);

1629

2047

SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;

1630

2048

GRADE

1633

2051

GRADE

1634

2052

151

1635

2053

DROP TABLE t1;

1636

create TEMPORARY table t1(a date) engine=MyISAM;

1637

create TEMPORARY table t2(a date, key(a)) engine=MyISAM;

2054

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

2055

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

2056

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

2057

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

2058

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

2059

select * from t1;

2060

f1 f2

2061

drop table t1,t2;

2062

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

2063

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

2064

commit;

2065

set autocommit = 0;

2066

update t1 set b = 5 where a = 2;

2067

create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |

2068

set autocommit = 0;

2069

insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),

2070

(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),

2071

(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),

2072

(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),

2073

(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);

2074

commit;

2075

commit;

2076

drop trigger t1t;

2077

drop table t1;

2078

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

2079

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

2080

create table t3(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM;

2081

create table t4(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM;

2082

create table t5(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM;

2083

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

2084

insert into t2(a) values (1),(2),(3);

2085

insert into t3(a) values (1),(2),(3);

2086

insert into t4(a) values (1),(2),(3);

2087

insert into t3(a) values (5),(7),(8);

2088

insert into t4(a) values (5),(7),(8);

2089

insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

2090

create trigger t1t before insert on t1 for each row begin

2091

INSERT INTO t2 SET a = NEW.a;

2092

end |

2093

create trigger t2t before insert on t2 for each row begin

2094

DELETE FROM t3 WHERE a = NEW.a;

2095

end |

2096

create trigger t3t before delete on t3 for each row begin

2097

UPDATE t4 SET b = b + 1 WHERE a = OLD.a;

2098

end |

2099

create trigger t4t before update on t4 for each row begin

2100

UPDATE t5 SET b = b + 1 where a = NEW.a;

2101

end |

2102

commit;

2103

set autocommit = 0;

2104

update t1 set b = b + 5 where a = 1;

2105

update t2 set b = b + 5 where a = 1;

2106

update t3 set b = b + 5 where a = 1;

2107

update t4 set b = b + 5 where a = 1;

2108

insert into t5(a) values(20);

2109

set autocommit = 0;

2110

insert into t1(a) values(7);

2111

insert into t2(a) values(8);

2112

delete from t2 where a = 3;

2113

update t4 set b = b + 1 where a = 3;

2114

commit;

2115

drop trigger t1t;

2116

drop trigger t2t;

2117

drop trigger t3t;

2118

drop trigger t4t;

2119

drop table t1, t2, t3, t4, t5;

2120

create table t1(a date) engine=MyISAM;

2121

create table t2(a date, key(a)) engine=MyISAM;

1638

2122

insert into t1 values('2005-10-01');

1639

2123

insert into t2 values('2005-10-01');

1640

2124

select * from t1, t2

1642

2126

a a

1643

2127

2005-10-01 2005-10-01

1644

2128

drop table t1, t2;

1645

create TEMPORARY table t1 (id int not null, f_id int not null, f int not null,

2129

create table t1 (id int not null, f_id int not null, f int not null,

1646

2130

primary key(f_id, id)) engine=MyISAM;

1647

create TEMPORARY table t2 (id int not null,s_id int not null,s varchar(200),

2131

create table t2 (id int not null,s_id int not null,s varchar(200),

1648

2132

primary key(id)) engine=MyISAM;

1649

2133

INSERT INTO t1 VALUES (8, 1, 3);

1650

2134

INSERT INTO t1 VALUES (1, 2, 1);

1651

2135

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

1652

2136

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

1653

2137

commit;

2138

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

2139

WHERE mm.id IS NULL;

1654

2140

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

1655

2141

where mm.id is null lock in share mode;

1656

2142

id f_id f

1657

2143

drop table t1,t2;

1658

create TEMPORARY table t1 ( a int ) ENGINE=MyISAM;

2144

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

2145

insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);

2146

commit;

2147

set autocommit = 0;

2148

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2149

update t1 set b = 5 where b = 1;

2150

set autocommit = 0;

2151

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2152

select * from t1 where a = 7 and b = 3 for update;

2153

a b

2154

7 3

2155

commit;

2156

commit;

2157

drop table t1;

2158

CREATE TABLE t1 ( a int ) ENGINE=MyISAM;

1659

2159

BEGIN;

1660

2160

INSERT INTO t1 VALUES (1);

1661

ALTER TABLE t1 ENGINE=MyISAM;

2161

OPTIMIZE TABLE t1;

2162

Table Op Msg_type Msg_text

2163

test.t1 optimize status OK

1662

2164

DROP TABLE t1;

Older »