~drizzle-trunk/drizzle/development

INSERT INTO t1 VALUES ('massecot',1101106491),('altec',1101106492),('stitch+',1101106304),('Seb Corgan',1101106305),('beerfilou',1101106263),('flaker',1101106529),('joce8',5),('M4vrick',1101106418),('gabay008',1101106525),('Vamp irX',1101106291),('ZoomZip',1101106546),('rip666',1101106502),('CBP ',1101106397),('guezpard',1101106496);

238

DELETE FROM t1 WHERE date<1101106546;

239

SELECT * FROM t1;

259

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

260

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

261

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

262

*+ *+ *+ *

263

*+ *+ *+ *

262

*+ *+*+ *

263

*+ *+*+ *

264

show create table t1;

265

Table Create Table

266

t1 CREATE TABLE "t1" (

267

"v" varchar(10),

268

"c" varchar(10),

269

"t" varchar(50)

266

t1 CREATE TABLE `t1` (

267

`v` varchar(10) DEFAULT NULL,

268

`c` char(10) DEFAULT NULL,

269

`t` varchar(50) DEFAULT NULL

270

) ENGINE=MEMORY DEFAULT CHARSET=latin1

271

create table t2 like t1;

272

show create table t2;

273

Table Create Table

274

t2 CREATE TABLE "t2" (

275

"v" varchar(10),

276

"c" varchar(10),

277

"t" varchar(50)

274

t2 CREATE TABLE `t2` (

275

`v` varchar(10) DEFAULT NULL,

276

`c` char(10) DEFAULT NULL,

277

`t` varchar(50) DEFAULT NULL

278

) ENGINE=MEMORY DEFAULT CHARSET=latin1

279

create table t3 select * from t1;

280

show create table t3;

281

Table Create Table

282

t3 CREATE TABLE "t3" (

283

"v" varchar(10),

284

"c" varchar(10),

285

"t" varchar(50)

282

t3 CREATE TABLE `t3` (

283

`v` varchar(10) DEFAULT NULL,

284

`c` char(10) DEFAULT NULL,

285

`t` varchar(50) DEFAULT NULL

286

) ENGINE=MEMORY DEFAULT CHARSET=latin1

287

alter table t1 modify c varchar(10);

288

show create table t1;

289

Table Create Table

290

t1 CREATE TABLE "t1" (

291

"v" varchar(10),

292

"c" varchar(10),

293

"t" varchar(50)

290

t1 CREATE TABLE `t1` (

291

`v` varchar(10) DEFAULT NULL,

292

`c` varchar(10) DEFAULT NULL,

293

`t` varchar(50) DEFAULT NULL

294

) ENGINE=MEMORY DEFAULT CHARSET=latin1

295

alter table t1 modify v char(10);

296

show create table t1;

297

Table Create Table

298

t1 CREATE TABLE "t1" (

299

"v" varchar(10),

300

"c" varchar(10),

301

"t" varchar(50)

298

t1 CREATE TABLE `t1` (

299

`v` char(10) DEFAULT NULL,

300

`c` varchar(10) DEFAULT NULL,

301

`t` varchar(50) DEFAULT NULL

302

) ENGINE=MEMORY DEFAULT CHARSET=latin1

303

alter table t1 modify t varchar(50);

303

alter table t1 modify t varchar(10);

304

Warnings:

305

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

304

306

show create table t1;

305

307

Table Create Table

306

t1 CREATE TABLE "t1" (

307

"v" varchar(10),

308

"c" varchar(10),

309

"t" varchar(50)

308

t1 CREATE TABLE `t1` (

309

`v` char(10) DEFAULT NULL,

310

`c` varchar(10) DEFAULT NULL,

311

`t` varchar(10) DEFAULT NULL

310

312

) ENGINE=MEMORY DEFAULT CHARSET=latin1

311

313

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

312

314

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

313

*+ *+ *+ *

314

*+ *+ *+ *

315

*+*+*+ *

316

*+*+*+ *

315

317

drop table t1,t2,t3;

316

318

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

317

319

show create table t1;

318

320

Table Create Table

319

t1 CREATE TABLE "t1" (

320

"v" varchar(10),

321

"c" varchar(10),

322

"t" varchar(50),

323

KEY "v" ("v"),

324

KEY "c" ("c"),

325

KEY "t" ("t"(10))

321

t1 CREATE TABLE `t1` (

322

`v` varchar(10) DEFAULT NULL,

323

`c` char(10) DEFAULT NULL,

324

`t` varchar(50) DEFAULT NULL,

325

KEY `v` (`v`),

326

KEY `c` (`c`),

327

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

326

328

) ENGINE=MEMORY DEFAULT CHARSET=latin1

327

329

select count(*) from t1;

328

330

count(*)

369

371

1 SIMPLE t1 ref v v 13 const 10 Using where

370

372

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

371

373

id select_type table type possible_keys key key_len ref rows Extra

372

1 SIMPLE t1 ref c c 13 const 10 Using where

374

1 SIMPLE t1 ref c c 11 const 10 Using where

373

375

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

374

376

id select_type table type possible_keys key key_len ref rows Extra

375

377

1 SIMPLE t1 ref t t 13 const 10 Using where

387

389

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

388

390

389

391

*a*a*a*

390

*a *a *a *

391

*a *a *a *

392

*a *a *a *

393

*a *a *a *

394

*a *a *a *

395

*a *a *a *

396

*a *a *a *

397

*a *a *a *

398

*a *a *a *

392

*a *a*a *

393

*a *a*a *

394

*a *a*a *

395

*a *a*a *

396

*a *a*a *

397

*a *a*a *

398

*a *a*a *

399

*a *a*a *

400

*a *a*a *

399

401

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

400

402

id select_type table type possible_keys key key_len ref rows Extra

401

403

1 SIMPLE t1 ref v v 13 const 10 Using where

486

488

select sql_big_result c,count(t) from t1 group by c limit 10;

487

489

c count(t)

488

490

a 1

489

a 10

490

b 10

491

c 10

492

d 10

493

e 10

494

f 10

495

g 10

496

h 10

497

i 10

491

a 10

492

b 10

493

c 10

494

d 10

495

e 10

496

f 10

497

g 10

498

h 10

499

i 10

498

500

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

499

501

t count(*)

500

502

a 1

535

537

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

536

538

insert into t1 values ('a');

537

539

insert into t1 values ('a ');

538

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

540

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

539

541

alter table t1 modify a varchar(10);

540

542

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

541

543

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

551

553

create table t1 (v varchar(10), c char(10), t varchar(50), key using btree (v), key using btree (c), key using btree (t(10)));

552

554

show create table t1;

553

555

Table Create Table

554

t1 CREATE TABLE "t1" (

555

"v" varchar(10),

556

"c" varchar(10),

557

"t" varchar(50),

558

KEY "v" ("v") USING BTREE,

559

KEY "c" ("c") USING BTREE,

560

KEY "t" ("t"(10)) USING BTREE

556

t1 CREATE TABLE `t1` (

557

`v` varchar(10) DEFAULT NULL,

558

`c` char(10) DEFAULT NULL,

559

`t` varchar(50) DEFAULT NULL,

560

KEY `v` (`v`) USING BTREE,

561

KEY `c` (`c`) USING BTREE,

562

KEY `t` (`t`(10)) USING BTREE

561

563

) ENGINE=MEMORY DEFAULT CHARSET=latin1

562

564

select count(*) from t1;

563

565

count(*)

592

594

1 SIMPLE t1 ref v v 13 const # Using where

593

595

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

594

596

id select_type table type possible_keys key key_len ref rows Extra

595

1 SIMPLE t1 ref c c 13 const # Using where

597

1 SIMPLE t1 ref c c 11 const # Using where

596

598

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

597

599

id select_type table type possible_keys key key_len ref rows Extra

598

600

1 SIMPLE t1 ref t t 13 const # Using where

610

612

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

611

613

612

614

*a*a*a*

613

*a *a *a *

614

*a *a *a *

615

*a *a *a *

616

*a *a *a *

617

*a *a *a *

618

*a *a *a *

619

*a *a *a *

620

*a *a *a *

621

*a *a *a *

615

*a *a*a *

616

*a *a*a *

617

*a *a*a *

618

*a *a*a *

619

*a *a*a *

620

*a *a*a *

621

*a *a*a *

622

*a *a*a *

623

*a *a*a *

622

624

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

623

625

id select_type table type possible_keys key key_len ref rows Extra

624

626

1 SIMPLE t1 ref v v 13 const # Using where

626

628

create table t1 (a char(10), unique using btree (a)) engine=heap;

627

629

insert into t1 values ('a');

628

630

insert into t1 values ('a ');

629

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

631

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

630

632

alter table t1 modify a varchar(10);

631

633

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

632

634

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

642

644

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

643

645

show create table t1;

644

646

Table Create Table

645

t1 CREATE TABLE "t1" (

646

"v" varchar(10),

647

"c" varchar(10),

648

"t" varchar(50),

649

KEY "v" ("v"(5)),

650

KEY "c" ("c"(5)),

651

KEY "t" ("t"(5))

647

t1 CREATE TABLE `t1` (

648

`v` varchar(10) DEFAULT NULL,

649

`c` char(10) DEFAULT NULL,

650

`t` varchar(50) DEFAULT NULL,

651

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

652

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

653

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

652

654

) ENGINE=MEMORY DEFAULT CHARSET=latin1

653

655

drop table t1;

654

656

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

655

657

show create table t1;

656

658

Table Create Table

657

t1 CREATE TABLE "t1" (

658

"v" varchar(65530),

659

KEY "v" ("v"(10))

659

t1 CREATE TABLE `t1` (

660

`v` varchar(65530) DEFAULT NULL,

661

KEY `v` (`v`(10))

660

662

) ENGINE=MEMORY DEFAULT CHARSET=latin1

661

663

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

662

664

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

663

665

length(v)

664

666

65530

665

667

drop table t1;

666

set storage_engine=InnoDB;

668

set storage_engine=MyISAM;

667

669

create table t1 (a bigint unsigned auto_increment primary key, b int,

668

670

key (b, a)) engine=heap;

669

671

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

714

716

create table t2 (c varchar(10)) engine=memory;

715

717

show table status like 't_';

716

718

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

717

t1 MEMORY 10 Fixed 0 12 0 # 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL

719

t1 MEMORY 10 Fixed 0 11 0 # 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL

718

720

t2 MEMORY 10 Fixed 0 12 0 # 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL

719

721

drop table t1, t2;

720

722

CREATE TABLE t1(a VARCHAR(1), b VARCHAR(2), c VARCHAR(256),

Older »