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

price area type transityes shopsyes schoolsyes petsyes

drop table t1;

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

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

drop table t1;

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

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

INSERT INTO t1 VALUES

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

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

name author category email password proxy bitmap msg urlscol urlhttp timeout nbcnx creation livinguntil lang type subcat subtype reg scs capacity userISP CCident

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

drop table t1;

create table t1

(

name_id int not null auto_increment,

name blob,

INDEX name_idx (name(5)),

primary key (name_id)

);

INSERT t1 VALUES(NULL,'/');

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

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

name_id name

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

name_id name

2 [T,U]_axpby

create table t2

(

name_id int not null auto_increment,

name char(255),

INDEX name_idx (name(5)),

100

primary key (name_id)

101

);

102

INSERT t2 select * from t1;

103

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

104

name_id name

105

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

106

name_id name

107

2 [T,U]_axpby

108

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

109

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

110

name_id name

111

2 [T,U]_axpby

112

drop table t1,t2,t3;

113

create table t1

114

(

115

SEQNO numeric(12 ) not null,

116

MOTYPEID numeric(12 ) not null,

117

MOINSTANCEID numeric(12 ) not null,

118

ATTRID numeric(12 ) not null,

119

VALUE varchar(120) not null,

120

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

121

);

122

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

123

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

124

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

125

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

126

drop table t1;

127

CREATE TEMPORARY TABLE t1 (

128

a tinytext NOT NULL,

129

b int NOT NULL default '0',

130

PRIMARY KEY (a(32),b)

131

) ENGINE=MyISAM;

132

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

133

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

134

a b

135

a 2

136

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

137

a b

138

a 2

139

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

140

a b

141

a 1

142

a 2

143

drop table t1;

144

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

145

show keys from t1;

146

Table Unique Key_name Seq_in_index Column_name

147

t1 YES PRIMARY 1 d

148

t1 YES a 1 a

149

t1 YES e 1 e

150

t1 YES b 1 b

151

t1 NO c 1 c

152

drop table t1;

153

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

154

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

155

ERROR 23000: Column 'c' cannot be null

156

SELECT * FROM t1;

157

c i

158

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

159

SELECT * FROM t1;

160

c i

161

a 1

162

a 2

163

DROP TABLE IF EXISTS t1;

164

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

165

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

166

SELECT * FROM t1;

167

c i

168

NULL 1

169

NULL 2

170

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

171

SELECT * FROM t1;

172

c i

173

NULL 1

174

NULL 2

175

a 3

176

a 4

177

drop table t1;

178

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

179

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

180

explain select 1 from t1 where id =2;

181

id select_type table type possible_keys key key_len ref rows Extra

182

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index

183

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

184

id select_type table type possible_keys key key_len ref rows Extra

185

1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index

186

explain select name from t1 where id =2;

187

id select_type table type possible_keys key key_len ref rows Extra

188

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

189

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

190

explain select 1 from t1 where id =2;

191

id select_type table type possible_keys key key_len ref rows Extra

192

1 SIMPLE t1 ref id id 4 const 1 Using index

193

drop table t1;

194

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

195

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

196

SELECT numeropost FROM t1 WHERE numreponse='1';

197

numeropost

198

199

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

200

id select_type table type possible_keys key key_len ref rows Extra

201

1 SIMPLE t1 const numreponse numreponse 4 const 1

202

FLUSH TABLES;

203

SELECT numeropost FROM t1 WHERE numreponse='1';

204

numeropost

205

206

drop table t1;

207

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

208

show create table t1;

209

Table Create Table

210

t1 CREATE TEMPORARY TABLE `t1` (

211

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

212

`t` TEXT COLLATE utf8_general_ci,

213

UNIQUE KEY `c` (`c`(2)),

214

UNIQUE KEY `t` (`t`(3))

215

) ENGINE=MyISAM COLLATE = utf8_general_ci

216

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

217

(0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1),

218

(0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1);

219

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

220

ERROR 23000: Duplicate entry 'cc' for key 'c'

221

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

222

ERROR 23000: Duplicate entry 'ttt' for key 't'

223

insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1);

224

ERROR 23000: Duplicate entry 'б!' for key 'c'

225

insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1);

226

ERROR 23000: Duplicate entry 'бб!' for key 't'

227

select c from t1 where c='cccc';

228

229

cccc

230

select t from t1 where t='tttt';

231

232

tttt

233

select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1;

234

235

б!"#ббббб

236

select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1;

237

238

бб!"#бббб

239

drop table t1;

240

DROP TABLE IF EXISTS t1;

241

Warnings:

242

Note 1051 Unknown table 't1'

243

CREATE TEMPORARY TABLE t1 (

244

c1 int,

245

c2 varbinary(240),

246

UNIQUE KEY (c1),

247

KEY (c2)

248

) ENGINE=MyISAM;

249

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

250

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

251

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

252

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

253

254

255

256

DELETE FROM t1 WHERE (c1 = 1);

257

check table t1;

258

Table Op Msg_type Msg_text

259

test.t1 check status OK

260

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

261

262

263

DELETE FROM t1 WHERE (c1 = 3);

264

check table t1;

265

Table Op Msg_type Msg_text

266

test.t1 check status OK

267

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

268

269

truncate table t1;

270

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

271

delete from t1 where c1=3;

272

delete from t1 where c1=1;

273

delete from t1 where c1=4;

274

check table t1;

275

Table Op Msg_type Msg_text

276

test.t1 check status OK

277

drop table t1;

278

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

279

ERROR HY000: Key part 'c' length cannot be 0

280

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

281

ERROR 42S21: Duplicate column name 'c'

282

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

283

ERROR 42S21: Duplicate column name 'c1'

284

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

285

ERROR 42S21: Duplicate column name 'c1'

286

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

287

ERROR 42S21: Duplicate column name 'c1'

288

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

289

alter table t1 add key (c1,c1);

290

ERROR 42S21: Duplicate column name 'c1'

291

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

292

ERROR 42S21: Duplicate column name 'c1'

293

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

294

ERROR 42S21: Duplicate column name 'c1'

295

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

296

ERROR 42S21: Duplicate column name 'c1'

297

drop table t1;

298

create table t1 (

299

i1 INT NOT NULL,

300

i2 INT NOT NULL,

301

UNIQUE i1idx (i1),

302

UNIQUE i2idx (i2));

303

desc t1;

304

Field Type Null Default Default_is_NULL On_Update

305

i1 INTEGER NO NO

306

i2 INTEGER NO NO

307

show create table t1;

308

Table Create Table

309

t1 CREATE TABLE `t1` (

310

`i1` INT NOT NULL,

311

`i2` INT NOT NULL,

312

UNIQUE KEY `i1idx` (`i1`),

313

UNIQUE KEY `i2idx` (`i2`)

314

) ENGINE=PBXT COLLATE = utf8_general_ci

315

drop table t1;

316

create temporary table t1 (

317

c1 int,

318

c2 varchar(20) not null,

319

primary key (c1),

320

key (c2(10))

321

) engine=myisam;

322

insert into t1 values (1,'');

323

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

324

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

325

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

326

select * from t1;

327

c1 c2

328

1 New Test String

329

2 Test String

330

331

Test String

332

drop table t1;

333

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

334

show create table t1;

335

Table Create Table

336

t1 CREATE TABLE `t1` (

337

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

338

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

339

KEY `a` (`a`,`b`)

340

) ENGINE=PBXT COLLATE = utf8_general_ci

341

alter table t1 modify b varchar(20);

342

show create table t1;

343

Table Create Table

344

t1 CREATE TABLE `t1` (

345

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

346

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

347

KEY `a` (`a`,`b`)

348

) ENGINE=PBXT COLLATE = utf8_general_ci

349

alter table t1 modify a varchar(20);

350

show create table t1;

351

Table Create Table

352

t1 CREATE TABLE `t1` (

353

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

354

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

355

KEY `a` (`a`,`b`)

356

) ENGINE=PBXT COLLATE = utf8_general_ci

357

drop table t1;

358

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

359

desc t1;

360

Field Type Null Default Default_is_NULL On_Update

361

a INTEGER NO NO

362

b VARCHAR NO NO

363

drop table t1;

364

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

365

desc t1;

366

Field Type Null Default Default_is_NULL On_Update

367

a INTEGER NO NO

368

b INTEGER NO NO

369

drop table t1;

370

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

371

desc t1;

372

Field Type Null Default Default_is_NULL On_Update

373

a INTEGER NO NO

374

b VARCHAR NO NO

375

drop table t1;

376

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

377

desc t1;

378

Field Type Null Default Default_is_NULL On_Update

379

a INTEGER NO NO

380

b VARCHAR NO NO

381

c VARCHAR NO NO

382

drop table t1;

383

create table t1 (

384

c1 int,

385

c2 char(12),

386

c3 varchar(123),

387

c4 timestamp,

388

index (c1),

389

index i1 (c1),

390

index i2 (c2),

391

index i3 (c3),

392

unique i4 (c4),

393

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

394

primary key (c2, c3),

395

index (c2, c4));

396

show create table t1;

397

Table Create Table

398

t1 CREATE TABLE `t1` (

399

`c1` INT DEFAULT NULL,

400

`c2` VARCHAR(12) COLLATE utf8_general_ci NOT NULL,

401

`c3` VARCHAR(123) COLLATE utf8_general_ci NOT NULL,

402

`c4` TIMESTAMP NULL DEFAULT NULL,

403

PRIMARY KEY (`c2`,`c3`),

404

UNIQUE KEY `i4` (`c4`),

405

KEY `c1` (`c1`),

406

KEY `i1` (`c1`),

407

KEY `i2` (`c2`),

408

KEY `i3` (`c3`),

409

KEY `i5` (`c1`,`c2`,`c3`,`c4`),

410

KEY `c2` (`c2`,`c4`)

411

) ENGINE=PBXT COLLATE = utf8_general_ci

412

alter table t1 drop index c1;

413

alter table t1 add index (c1);

414

alter table t1 add index (c1);

415

alter table t1 drop index i3;

416

alter table t1 add index i3 (c3);

417

alter table t1 drop index i2, drop index i4;

418

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

419

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

420

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

421

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

422

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

423

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

424

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

425

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

426

alter table t1 drop primary key;

427

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

428

ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists

429

show create table t1;

430

Table Create Table

431

t1 CREATE TABLE `t1` (

432

`c1` INT NOT NULL,

433

`c2` VARCHAR(12) COLLATE utf8_general_ci NOT NULL,

434

`c3` VARCHAR(123) COLLATE utf8_general_ci NOT NULL,

435

`c4` TIMESTAMP NULL DEFAULT NULL,

436

KEY `i1` (`c1`),

437

KEY `i5` (`c1`,`c2`,`c3`,`c4`),

438

KEY `c1` (`c1`),

439

KEY `c1_2` (`c1`),

440

KEY `i3` (`c3`),

441

KEY `i2` (`c2`),

442

KEY `i4` (`c4`),

443

KEY `c2` (`c2`(4),`c3`(7))

444

) ENGINE=PBXT COLLATE = utf8_general_ci

445

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

446

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

447

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

448

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

449

ERROR 23000: Duplicate entry '1' for key 'i1'

450

drop table t1;

451

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

452

INSERT INTO t1 VALUES( 1 );

453

ALTER TABLE t1 DISABLE KEYS;

454

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

455

id select_type table type possible_keys key key_len ref rows Extra

456

1 SIMPLE t1 system NULL NULL NULL NULL 1

457

drop table t1;

458

CREATE TABLE t1 (

459

a INTEGER auto_increment PRIMARY KEY,

460

b INTEGER NOT NULL,

461

c INTEGER NOT NULL,

462

d CHAR(64)

463

);

464

CREATE TABLE t2 (

465

a INTEGER auto_increment PRIMARY KEY,

466

b INTEGER NOT NULL,

467

c int NOT NULL,

468

d DATETIME NOT NULL,

469

e int NOT NULL,

470

f INTEGER NOT NULL,

471

g INTEGER NOT NULL,

472

h int NOT NULL,

473

i INTEGER NOT NULL,

474

j INTEGER NOT NULL,

475

UNIQUE INDEX (b),

476

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

477

INDEX (c)

478

);

479

INSERT INTO t2 VALUES

480

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

481

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

482

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

483

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

484

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

485

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

486

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

487

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

488

INSERT INTO t1 (b, c, d) VALUES

489

(3388000, -553000, NULL),

490

(3388000, -553000, NULL);

491

DROP TABLE t1, t2;

492

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

493

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

494

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

495

desc t1;

496

Field Type Null Default Default_is_NULL On_Update

497

a INTEGER NO NO

498

b VARCHAR NO NO

499

c VARCHAR NO NO

500

show create table t1;

501

Table Create Table

502

t1 CREATE TABLE `t1` (

503

`a` INT NOT NULL,

504

`b` VARCHAR(10) COLLATE utf8_general_ci NOT NULL,

505

`c` VARCHAR(4) COLLATE utf8_general_ci NOT NULL,

506

UNIQUE KEY `cc` (`c`),

507

UNIQUE KEY `bb` (`b`(1)),

508

KEY `aa` (`a`)

509

) ENGINE=PBXT COLLATE = utf8_general_ci

510

drop table t1;

511

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

512

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

513

c char(4) not null);

514

desc t1;

515

Field Type Null Default Default_is_NULL On_Update

516

a INTEGER NO NO

517

b VARCHAR NO NO

518

c VARCHAR NO NO

519

alter table t1 add unique key cc(c);

520

desc t1;

521

Field Type Null Default Default_is_NULL On_Update

522

a INTEGER NO NO

523

b VARCHAR NO NO

524

c VARCHAR NO NO

525

show create table t1;

526

Table Create Table

527

t1 CREATE TABLE `t1` (

528

`a` INT NOT NULL,

529

`b` VARCHAR(10) COLLATE utf8_general_ci NOT NULL,

530

`c` VARCHAR(4) COLLATE utf8_general_ci NOT NULL,

531

UNIQUE KEY `cc` (`c`),

532

UNIQUE KEY `bb` (`b`(1)),

533

KEY `aa` (`a`)

534

) ENGINE=PBXT COLLATE = utf8_general_ci

535

drop table t1;

536

End of 5.0 tests

537

DROP TABLE IF EXISTS t1;

538

CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT);

539

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

540

SELECT 1 AS c1

541

FROM t1

542

ORDER BY (

543

SELECT 1 AS c2

544

FROM t1

545

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

546

LIMIT 1);

547

548

549

550

551

DROP TABLE t1;

552

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

553

INSERT INTO t1 (a, b)

554

VALUES

555

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

556

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

557

EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE

558

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

559

id select_type table type possible_keys key key_len ref rows Extra

560

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

561

2 SUBQUERY t1 ALL NULL NULL NULL NULL 15 Using temporary; Using filesort

562

SELECT 1 as RES FROM t1 AS t1_outer WHERE

563

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

564

RES

565

DROP TABLE t1;

Older »