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

186

DELETE FROM t1 WHERE date<1101106546;

187

SELECT * FROM t1;

188

DROP TABLE t1;

189

190

191

# Bug #6878: a problem with small length records

192

193

194

create temporary table t1(a char(2)) engine=memory;

195

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

196

delete from t1 where a is null;

197

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

198

select * from t1;

199

drop table t1;

200

201

202

# Test varchar

203

# We can't use varchar.inc becasue MEMORY doesn't support blob's

204

205

206

let $default=`select @@storage_engine`;

207

set storage_engine=MEMORY;

208

209

210

# Simple basic test that endspace is saved

211

212

213

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

214

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

215

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

216

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

217

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

218

219

# Check how columns are copied

220

show create table t1;

221

create temporary table t2 like t1;

222

show create table t2;

223

create temporary table t3 select * from t1;

224

show create table t3;

225

alter table t1 modify c varchar(10);

226

show create table t1;

227

alter table t1 modify v char(10);

228

show create table t1;

229

alter table t1 modify t varchar(50);

230

show create table t1;

231

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

232

drop table t1,t2,t3;

233

234

235

# Testing of keys

236

237

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

238

show create table t1;

239

disable_query_log;

240

begin;

241

let $1=10;

242

while ($1)

243

{

244

let $2=27;

245

eval set @space=repeat(' ',10-$1);

246

while ($2)

247

{

248

eval set @char=char(ascii('a')+$2-1);

249

insert into t1 values(concat(@char,@space),concat(@char,@space),concat(@char,@space));

250

dec $2;

251

}

252

dec $1;

253

}

254

commit;

255

enable_query_log;

256

select count(*) from t1;

257

insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));

258

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

259

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

260

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

261

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

262

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

263

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

264

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

265

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

266

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

267

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

268

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

269

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

270

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

271

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

272

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

273

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

274

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

275

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

276

277

--error ER_DUP_ENTRY

278

alter table t1 add unique(v);

279

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

280

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

281

282

# GROUP BY

283

284

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

285

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

286

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

287

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

288

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

289

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

290

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

291

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

292

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

293

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

294

drop table t1;

295

296

297

# Test unique keys

298

299

300

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

301

insert into t1 values ('a');

302

--error ER_DUP_ENTRY

303

insert into t1 values ('a ');

304

305

alter table t1 modify a varchar(10);

306

--error ER_DUP_ENTRY

307

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

308

--error ER_DUP_ENTRY

309

insert into t1 values ('a ');

310

--error ER_DUP_ENTRY

311

insert into t1 values ('a ');

312

--error ER_DUP_ENTRY

313

insert into t1 values ('a ');

314

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

315

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

316

drop table t1;

317

318

319

# Testing of btree keys

320

321

322

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

323

show create table t1;

324

disable_query_log;

325

let $1=10;

326

begin;

327

while ($1)

328

{

329

let $2=27;

330

eval set @space=repeat(' ',10-$1);

331

while ($2)

332

{

333

eval set @char=char(ascii('a')+$2-1);

334

insert into t1 values(concat(@char,@space),concat(@char,@space),concat(@char,@space));

335

dec $2;

336

}

337

dec $1;

338

}

339

commit;

340

enable_query_log;

341

select count(*) from t1;

342

insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));

343

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

344

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

345

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

346

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

347

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

348

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

349

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

350

--replace_column 9 #

351

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

352

--replace_column 9 #

353

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

354

--replace_column 9 #

355

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

356

--replace_column 9 #

357

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

358

--replace_column 9 #

359

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

360

--replace_column 9 #

361

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

362

--replace_column 9 #

363

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

364

365

--error ER_DUP_ENTRY

366

alter table t1 add unique(v);

367

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

368

# Number of rows is not constant for b-trees keys

369

--replace_column 9 #

370

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

371

372

drop table t1;

373

374

375

# Test unique btree keys

376

377

378

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

379

insert into t1 values ('a');

380

--error ER_DUP_ENTRY

381

insert into t1 values ('a ');

382

383

alter table t1 modify a varchar(10);

384

--error ER_DUP_ENTRY

385

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

386

--error ER_DUP_ENTRY

387

insert into t1 values ('a ');

388

--error ER_DUP_ENTRY

389

insert into t1 values ('a ');

390

--error ER_DUP_ENTRY

391

insert into t1 values ('a ');

392

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

393

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

394

drop table t1;

395

396

397

# test show create table

398

399

400

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

401

show create table t1;

402

drop table t1;

403

404

create temporary table t1 (v varchar(16383), key(v(10)));

405

show create table t1;

406

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

407

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

408

drop table t1;

409

410

411

# Reset varchar test

412

413

eval set storage_engine=$default;

414

415

416

# Bug #8489: Strange auto_increment behaviour

417

418

419

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

420

key (b, a)) engine=MEMORY;

421

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

422

select * from t1;

423

drop table t1;

424

425

create temporary table t1 (a int not null, b int not null auto_increment,

426

primary key(a, b), key(b)) engine=MEMORY;

427

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

428

select * from t1;

429

drop table t1;

430

431

--error 1075

432

create temporary table t1 (a int not null, b int not null auto_increment,

433

primary key(a, b)) engine=MEMORY;

434

435

436

# Bug #10566: Verify that we can create temporary a prefixed key with length > 255

437

438

create temporary table t1 (c char(255), primary key(c(90)));

439

insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");

440

--error ER_DUP_ENTRY

441

insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");

442

drop table t1;

443

444

445

# Bug 12796: Record doesn't show when selecting through index

446

447

CREATE TEMPORARY TABLE t1 (a int, key(a)) engine=MEMORY;

448

insert into t1 values (0);

449

delete from t1;

450

select * from t1;

451

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

452

select * from t1 where a = 0;

453

drop table t1;

454

455

# End of 4.1 tests

456

457

458

# Bug #3094: Row format of memory tables should always be reported as Fixed

459

460

create temporary table t1 (c char(10)) engine=memory;

461

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

462

--replace_column 1 # 6 # 7 # 8 # 9 # 10 #

463

show table status like 't_';

464

drop table t1, t2;

465

466

467

# BUG#18233 - Memory tables INDEX USING HASH (a,b) returns 1 row on

468

# SELECT WHERE a= AND b=

469

470

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

471

KEY(a), KEY(b), KEY(c)) ENGINE=MEMORY;

472

INSERT INTO t1 VALUES('a','aa',REPEAT('a', 256)),('a','aa',REPEAT('a',256));

473

SELECT COUNT(*) FROM t1 WHERE a='a';

474

SELECT COUNT(*) FROM t1 WHERE b='aa';

475

SELECT COUNT(*) FROM t1 WHERE c=REPEAT('a',256);

476

DROP TABLE t1;

477

478

# End of 5.0 tests

479

480

481

# BUG#26080 - Memory Storage engine not working properly

482

483

CREATE TEMPORARY TABLE t1(c1 VARCHAR(100), c2 INT) ENGINE=MEMORY;

484

INSERT INTO t1 VALUES('', 0);

485

ALTER TABLE t1 MODIFY c1 VARCHAR(101);

486

SELECT c2 FROM t1;

487

DROP TABLE t1;

Older »