~drizzle-trunk/drizzle/development

SELECT * FROM t1 WHERE area='Vancouver' and transityes='y' and schoolsyes='y' and ( ((type='1 Bedroom' or type='Studio/Bach') and (price<=500)) or ((type='2 Bedroom') and (price<=550)) or ((type='Shared/Roomate') and (price<=300)) or ((type='Room and Board') and (price<=500)) ) and price <= 400;

drop table t1;

# No longer a problem with primary key

CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), PRIMARY KEY (program));

# This no longer give an error for wrong primary key

ALTER TABLE t1 modify program enum('signup','unique','sliding');

drop table t1;

# Test of compressed decimal index.

CREATE TABLE t1 (

name varchar(50) DEFAULT '' NOT NULL,

author varchar(50) DEFAULT '' NOT NULL,

category decimal(10,0) DEFAULT '0' NOT NULL,

email varchar(50),

password varchar(50),

proxy varchar(50),

bitmap varchar(20),

msg varchar(255),

urlscol varchar(127),

urlhttp varchar(127),

timeout decimal(10,0),

nbcnx decimal(10,0),

creation decimal(10,0),

livinguntil decimal(10,0),

lang decimal(10,0),

type decimal(10,0),

subcat decimal(10,0),

subtype decimal(10,0),

reg char(1),

scs varchar(255),

capacity decimal(10,0),

userISP varchar(50),

CCident varchar(50) DEFAULT '' NOT NULL,

PRIMARY KEY (name,author,category)

);

INSERT INTO t1 VALUES

100

('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai salut','scol://195.242.78.119:patauteur.patnom',NULL,NULL,NULL,950036174,-882087474,NULL,3,0,3,'1','Pub/patnom/futur_divers.scs',NULL,'pat','CC1');

101

INSERT INTO t1 VALUES

102

('LeNomDeMonSite','Marc',0,'m.barilley@cryo-networks.fr',NULL,NULL,NULL,NULL,'scol://195.242.78.119:Marc.LeNomDeMonSite',NULL,NULL,NULL,950560434,-881563214,NULL,3,0,3,'1','Pub/LeNomDeMonSite/domus_hibere.scs',NULL,'Marq','CC1');

103

select * from t1 where name='patnom' and author='patauteur' and category=0;

104

drop table t1;

105

106

107

# Problem with search on partial index

108

109

110

create table t1

111

(

112

name_id int not null auto_increment,

113

name blob,

114

INDEX name_idx (name(5)),

115

primary key (name_id)

116

);

117

118

INSERT t1 VALUES(NULL,'/');

119

INSERT t1 VALUES(NULL,'[T,U]_axpby');

120

SELECT * FROM t1 WHERE name='[T,U]_axpy';

121

SELECT * FROM t1 WHERE name='[T,U]_axpby';

122

create table t2

123

(

124

name_id int not null auto_increment,

125

name char(255),

126

INDEX name_idx (name(5)),

127

primary key (name_id)

128

);

129

INSERT t2 select * from t1;

130

SELECT * FROM t2 WHERE name='[T,U]_axpy';

131

SELECT * FROM t2 WHERE name='[T,U]_axpby';

132

# Test possible problems with warnings in CREATE ... SELECT

133

CREATE TABLE t3 SELECT * FROM t2 WHERE name='[T,U]_axpby';

134

SELECT * FROM t2 WHERE name='[T,U]_axpby';

135

136

drop table t1,t2,t3;

137

138

139

# Test bug with long primary key

140

141

142

create table t1

143

(

144

SEQNO numeric(12 ) not null,

145

MOTYPEID numeric(12 ) not null,

146

MOINSTANCEID numeric(12 ) not null,

147

ATTRID numeric(12 ) not null,

148

VALUE varchar(120) not null,

149

primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE )

150

);

151

INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');

152

INSERT INTO t1 VALUES (1, 1, 1, 1, 'b');

153

--error ER_DUP_ENTRY

154

INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');

155

drop table t1;

156

157

158

# Test with blob + int key

159

# (Failed for Greg Valure)

160

161

162

CREATE TEMPORARY TABLE t1 (

163

a tinytext NOT NULL,

164

b int NOT NULL default '0',

165

PRIMARY KEY (a(32),b)

166

) ENGINE=MyISAM;

167

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

168

SELECT * FROM t1 WHERE a='a' AND b=2;

169

SELECT * FROM t1 WHERE a='a' AND b in (2);

170

SELECT * FROM t1 WHERE a='a' AND b in (1,2);

171

drop table t1;

172

173

174

# Test of create key order

175

176

177

create table t1 (a int not null unique, b int unique, c int, d int not null primary key, key(c), e int not null unique);

178

show keys from t1;

179

drop table t1;

180

181

182

# Problem with UNIQUE() with NULL parts and auto increment

183

184

185

CREATE TABLE t1 (c VARCHAR(10) NOT NULL,i INT PRIMARY KEY NOT NULL AUTO_INCREMENT, UNIQUE (c,i));

186

--error ER_BAD_NULL_ERROR

187

INSERT INTO t1 (c) VALUES (NULL),(NULL);

188

SELECT * FROM t1;

189

INSERT INTO t1 (c) VALUES ('a'),('a');

190

SELECT * FROM t1;

191

DROP TABLE IF EXISTS t1;

192

CREATE TABLE t1 (c CHAR(10) NULL, i INT PRIMARY KEY NOT NULL AUTO_INCREMENT, UNIQUE (c,i));

193

INSERT INTO t1 (c) VALUES (NULL),(NULL);

194

SELECT * FROM t1;

195

INSERT INTO t1 (c) VALUES ('a'),('a');

196

SELECT * FROM t1;

197

drop table t1;

198

199

200

# Test of key read with primary key (Bug #3497)

201

202

203

CREATE TEMPORARY TABLE t1 (id int auto_increment, name char(50), primary key (id)) engine=myisam;

204

insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g');

205

explain select 1 from t1 where id =2;

206

explain select 1 from t1 where id =2 or id=3;

207

explain select name from t1 where id =2;

208

ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id);

209

explain select 1 from t1 where id =2;

210

drop table t1;

211

212

213

# Test of problem with key read (Bug #3666)

214

215

216

CREATE TABLE t1 (numeropost int NOT NULL default '0', numreponse int NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse));

217

INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4');

218

SELECT numeropost FROM t1 WHERE numreponse='1';

219

EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1';

220

FLUSH TABLES;

221

SELECT numeropost FROM t1 WHERE numreponse='1';

222

drop table t1;

223

224

225

# UNIQUE prefix keys and multi-byte charsets

226

227

228

create temporary table t1 (c varchar(30), t text, unique (c(2)), unique (t(3))) engine=myisam;

229

show create table t1;

230

insert t1 values ('cccc', 'tttt'),

231

(0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1),

232

(0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1);

233

--error ER_DUP_ENTRY

234

insert t1 (c) values ('cc22');

235

--error ER_DUP_ENTRY

236

insert t1 (t) values ('ttt22');

237

--error ER_DUP_ENTRY

238

insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1);

239

--error ER_DUP_ENTRY

240

insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1);

241

select c from t1 where c='cccc';

242

select t from t1 where t='tttt';

243

select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1;

244

select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1;

245

drop table t1;

246

247

248

# BUG#6151 - myisam index corruption

249

250

DROP TABLE IF EXISTS t1;

251

CREATE TEMPORARY TABLE t1 (

252

c1 int,

253

c2 varbinary(240),

254

UNIQUE KEY (c1),

255

KEY (c2)

256

) ENGINE=MyISAM;

257

INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z');

258

INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z');

259

INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z');

260

select c1 from t1 where c2='\Z\Z\Z\Z';

261

DELETE FROM t1 WHERE (c1 = 1);

262

check table t1;

263

select c1 from t1 where c2='\Z\Z\Z\Z';

264

DELETE FROM t1 WHERE (c1 = 3);

265

check table t1;

266

select c1 from t1 where c2='\Z\Z\Z\Z';

267

268

269

# test delete of keys in a different order

270

271

truncate table t1;

272

insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc");

273

delete from t1 where c1=3;

274

delete from t1 where c1=1;

275

delete from t1 where c1=4;

276

check table t1;

277

278

drop table t1;

279

280

281

# Bug 6166: index prefix length of 0 not rejected

282

283

# this test should fail in 5.0

284

# to fix it, remove #ifdef in

285

# file sql_yacc.yy(key_part)

286

# create dedicated error code for this and

287

# and change my_printf_error() to my_error

288

289

--error ER_KEY_PART_0

290

create table t1 (c char(10), index (c(0)));

291

292

293

# Bug #6126: Duplicate columns in keys should fail

294

# Bug #6252: (dup)

295

296

--error ER_DUP_FIELDNAME

297

create table t1 (c char(10), index (c,c));

298

--error ER_DUP_FIELDNAME

299

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

300

--error ER_DUP_FIELDNAME

301

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

302

--error ER_DUP_FIELDNAME

303

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

304

create table t1 (c1 char(10), c2 char(10));

305

--error ER_DUP_FIELDNAME

306

alter table t1 add key (c1,c1);

307

--error ER_DUP_FIELDNAME

308

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

309

--error ER_DUP_FIELDNAME

310

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

311

--error ER_DUP_FIELDNAME

312

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

313

drop table t1;

314

315

316

# Bug#11228: DESC shows arbitrary column as "PRI"

317

318

create table t1 (

319

i1 INT NOT NULL,

320

i2 INT NOT NULL,

321

UNIQUE i1idx (i1),

322

UNIQUE i2idx (i2));

323

desc t1;

324

show create table t1;

325

drop table t1;

326

327

328

# Bug#12565 - ERROR 1034 when running simple UPDATE or DELETE

329

# on large MyISAM table

330

331

create temporary table t1 (

332

c1 int,

333

c2 varchar(20) not null,

334

primary key (c1),

335

key (c2(10))

336

) engine=myisam;

337

insert into t1 values (1,'');

338

insert into t1 values (2,' \t\tTest String');

339

insert into t1 values (3,' \n\tTest String');

340

update t1 set c2 = 'New Test String' where c1 = 1;

341

select * from t1;

342

drop table t1;

343

344

345

# If we use a partial field for a key that is actually the length of the

346

# field, and we extend the field, we end up with a key that includes the

347

# whole new length of the field.

348

349

create table t1 (a varchar(10), b varchar(10), key(a(10),b(10)));

350

show create table t1;

351

alter table t1 modify b varchar(20);

352

show create table t1;

353

alter table t1 modify a varchar(20);

354

show create table t1;

355

drop table t1;

356

357

358

# Bug #11227: Incorrectly reporting 'MUL' vs. 'UNI' on varchar

359

360

create table t1 (a int not null primary key, b varchar(20) not null unique);

361

desc t1;

362

drop table t1;

363

create table t1 (a int not null primary key, b int not null unique);

364

desc t1;

365

drop table t1;

366

create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10)));

367

desc t1;

368

drop table t1;

369

create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not null, unique(b(10),c(10)));

370

desc t1;

371

drop table t1;

372

373

# End of 4.1 tests

374

375

376

# WL#1563 - Modify MySQL to support on-line CREATE/DROP INDEX

377

# To test if this really works, you need to run with --debug

378

# and check the trace file.

379

380

# Create a table with named and unnamed indexes.

381

create table t1 (

382

c1 int,

383

c2 char(12),

384

c3 varchar(123),

385

c4 timestamp,

386

index (c1),

387

index i1 (c1),

388

index i2 (c2),

389

index i3 (c3),

390

unique i4 (c4),

391

index i5 (c1, c2, c3, c4),

392

primary key (c2, c3),

393

index (c2, c4));

394

show create table t1;

395

# Some simple tests.

396

alter table t1 drop index c1;

397

alter table t1 add index (c1);

398

# This creates index 'c1_2'.

399

alter table t1 add index (c1);

400

alter table t1 drop index i3;

401

alter table t1 add index i3 (c3);

402

# Two indexes at the same time.

403

alter table t1 drop index i2, drop index i4;

404

alter table t1 add index i2 (c2), add index i4 (c4);

405

# Three indexes, one of them reversely.

406

alter table t1 drop index i2, drop index i4, add index i6 (c2, c4);

407

alter table t1 add index i2 (c2), add index i4 (c4), drop index i6;

408

# include an unique index.

409

alter table t1 drop index i2, drop index i4, add unique i4 (c4);

410

alter table t1 add index i2 (c2), drop index i4, add index i4 (c4);

411

# Modify an index by changing its definition.

412

alter table t1 drop index c2, add index (c2(4),c3(7));

413

# Change nothing. The new key definition is the same as the old one.

414

alter table t1 drop index c2, add index (c2(4),c3(7));

415

# Test primary key handling.

416

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

417

alter table t1 drop primary key;

418

# Drop is checked first. Primary key must exist.

419

--error ER_CANT_DROP_FIELD_OR_KEY

420

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

421

show create table t1;

422

# Insert non-unique values.

423

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

424

insert into t1 values(1, 'b', 'b', NULL);

425

# Drop some indexes for new adds.

426

alter table t1 drop index i3, drop index i2, drop index i1;

427

# Add indexes, one is unique on non-unique values.

428

--error ER_DUP_ENTRY

429

alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);

430

drop table t1;

431

432

433

434

# Bug #20604: Test for disabled keys with aggregate functions and FORCE INDEX.

435

436

437

CREATE TEMPORARY TABLE t1( a int, KEY(a) ) ENGINE=MyISAM;

438

INSERT INTO t1 VALUES( 1 );

439

ALTER TABLE t1 DISABLE KEYS;

440

EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);

441

442

drop table t1;

443

444

445

# Bug #24778: Innodb: No result when using ORDER BY

446

447

CREATE TABLE t1 (

448

a INTEGER auto_increment PRIMARY KEY,

449

b INTEGER NOT NULL,

450

c INTEGER NOT NULL,

451

d CHAR(64)

452

);

453

454

CREATE TABLE t2 (

455

a INTEGER auto_increment PRIMARY KEY,

456

b INTEGER NOT NULL,

457

c int NOT NULL,

458

d DATETIME NOT NULL,

459

e int NOT NULL,

460

f INTEGER NOT NULL,

461

g INTEGER NOT NULL,

462

h int NOT NULL,

463

i INTEGER NOT NULL,

464

j INTEGER NOT NULL,

465

UNIQUE INDEX (b),

466

INDEX (b, d, e, f, g, h, i, j, c),

467

INDEX (c)

468

);

469

470

INSERT INTO t2 VALUES

471

(NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),

472

(NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),

473

(NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),

474

(NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),

475

(NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),

476

(NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),

477

(NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),

478

(NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);

479

480

INSERT INTO t1 (b, c, d) VALUES

481

(3388000, -553000, NULL),

482

(3388000, -553000, NULL);

483

484

# psergey/sergefp: This crashes for a mysterious reason with MRR + Semijoin

485

# opts. TODO: fix it.

486

#SELECT *

487

#FROM t2 c JOIN t1 pa ON c.b = pa.a

488

#WHERE c.c = 1

489

#ORDER BY c.b, c.d

490

491

492

DROP TABLE t1, t2;

493

494

495

# Bug #31137: Assertion failed: primary_key_no == -1 || primary_key_no == 0

496

497

create table t1(a int not null, key aa(a),

498

b char(10) not null, unique key bb(b(1)),

499

c char(4) not null, unique key cc(c));

500

desc t1;

501

show create table t1;

502

drop table t1;

503

create table t1(a int not null, key aa(a),

504

b char(10) not null, unique key bb(b(1)),

505

c char(4) not null);

506

desc t1;

507

alter table t1 add unique key cc(c);

508

desc t1;

509

show create table t1;

510

drop table t1;

511

512

--echo End of 5.0 tests

513

514

515

# Bug #31148: bool close_thread_table(THD*, TABLE**): Assertion

516

# `table->key_read == 0' failed.

517

518

519

--disable_warnings

520

DROP TABLE IF EXISTS t1;

521

--enable_warnings

522

523

CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT);

524

525

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

526

527

SELECT 1 AS c1

528

FROM t1

529

ORDER BY (

530

SELECT 1 AS c2

531

FROM t1

532

GROUP BY GREATEST(LAST_INSERT_ID(), t1.a) ASC

533

LIMIT 1);

534

535

DROP TABLE t1;

536

537

538

539

# Bug #31974: Wrong EXPLAIN output

540

541

542

CREATE TABLE t1 (a INT, b INT, INDEX (a,b));

543

INSERT INTO t1 (a, b)

544

VALUES

545

(1,1), (1,2), (1,3), (1,4), (1,5),

546

(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);

547

EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE

548

(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;

549

SELECT 1 as RES FROM t1 AS t1_outer WHERE

550

(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;

551

552

DROP TABLE t1;

Older »