~drizzle-trunk/drizzle/development

Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ('a' in (`test`.`t1`.`a`,`test`.`t1`.`b`,(`test`.`t1`.`c` collate utf8_bin)))

152

drop table t1;

153

create table t1 (a char(10) not null);

154

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

155

select a from t1 where a IN ('a','b','c') order by a;

156

157

158

159

160

drop table t1;

161

select '1.0' in (1,2);

162

'1.0' in (1,2)

163

164

select 1 in ('1.0',2);

165

1 in ('1.0',2)

166

167

select 1 in (1,'2.0');

168

1 in (1,'2.0')

169

170

select 1 in ('1.0',2.0);

171

1 in ('1.0',2.0)

172

173

select 1 in (1.0,'2.0');

174

1 in (1.0,'2.0')

175

176

select 1 in ('1.1',2);

177

1 in ('1.1',2)

178

179

select 1 in ('1.1',2.0);

180

1 in ('1.1',2.0)

181

182

create table t1 (a char(2));

183

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

184

select * from t1 where a in (NULL, 'aa');

185

186

187

drop table t1;

188

create table t1 (id int, key(id));

189

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

190

select count(*) from t1 where id not in (1);

191

count(*)

192

193

select count(*) from t1 where id not in (1,2);

194

count(*)

195

196

drop table t1;

197

DROP TABLE IF EXISTS t1;

198

CREATE TABLE t1 SELECT 1 IN (2, NULL);

199

SELECT should return NULL.

200

SELECT * FROM t1;

201

1 IN (2, NULL)

202

NULL

203

DROP TABLE t1;

204

End of 4.1 tests

205

CREATE TABLE t1 (a int PRIMARY KEY);

206

INSERT INTO t1 VALUES (44), (45), (46);

207

SELECT * FROM t1 WHERE a IN (45);

208

209

210

SELECT * FROM t1 WHERE a NOT IN (0, 45);

211

212

213

214

SELECT * FROM t1 WHERE a NOT IN (45);

215

216

217

218

DROP TABLE t1;

219

create table t1 (a int);

220

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

221

create temporary table t2 (a int, filler char(200), key(a)) engine=myisam;

222

insert into t2 select C.a*2, 'no' from t1 A, t1 B, t1 C where A.a < 10;

223

insert into t2 select C.a*2+1, 'yes' from t1 C;

224

explain

225

select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);

226

id select_type table type possible_keys key key_len ref rows Extra

227

1 SIMPLE t2 range a a 5 NULL 12 Using where

228

select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);

229

a filler

230

1 yes

231

3 yes

232

5 yes

233

7 yes

234

9 yes

235

11 yes

236

13 yes

237

15 yes

238

17 yes

239

19 yes

240

explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2);

241

id select_type table type possible_keys key key_len ref rows Extra

242

1 SIMPLE t2 range a a 5 NULL 912 Using where

243

explain select * from t2 force index(a) where a <> 2;

244

id select_type table type possible_keys key key_len ref rows Extra

245

1 SIMPLE t2 range a a 5 NULL 912 Using where

246

drop table t2;

247

create table t2 (a datetime, filler char(200), key(a));

248

insert into t2 select '2006-04-25 10:00:00' + interval C.a minute,

249

'no' from t1 A, t1 B, t1 C where C.a % 2 = 0;

250

insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute,

251

'yes' from t1 C;

252

explain

253

select * from t2 where a NOT IN (

254

'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',

255

'2006-04-25 10:06:00', '2006-04-25 10:08:00');

256

id select_type table type possible_keys key key_len ref rows Extra

257

1 SIMPLE t2 range a a 9 NULL 11 Using where

258

select * from t2 where a NOT IN (

259

'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',

260

'2006-04-25 10:06:00', '2006-04-25 10:08:00');

261

a filler

262

2006-04-25 10:01:00 yes

263

2006-04-25 10:03:00 yes

264

2006-04-25 10:05:00 yes

265

2006-04-25 10:07:00 yes

266

2006-04-25 10:09:00 yes

267

2006-04-25 10:11:00 yes

268

2006-04-25 10:13:00 yes

269

2006-04-25 10:15:00 yes

270

2006-04-25 10:17:00 yes

271

2006-04-25 10:19:00 yes

272

drop table t2;

273

create table t2 (a varchar(10), filler char(200), key(a));

274

insert into t2 select 'foo', 'no' from t1 A, t1 B where A.a < 10;

275

insert into t2 select 'barbar', 'no' from t1 A, t1 B where A.a < 10;

276

insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B where A.a < 10;

277

insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'),

278

('barbas','1'), ('bazbazbay', '1'),('zz','1');

279

explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');

280

id select_type table type possible_keys key key_len ref rows Extra

281

1 SIMPLE t2 range a a 43 NULL 5 Using where

282

drop table t2;

283

create table t2 (a decimal(10,5), filler char(200), key(a));

284

insert into t2 select 345.67890, 'no' from t1 A, t1 B where A.a < 10;

285

insert into t2 select 43245.34, 'no' from t1 A, t1 B where A.a < 10;

286

insert into t2 select 64224.56344, 'no' from t1 A, t1 B where A.a < 10;

287

insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'),

288

(55555,'1'), (77777, '1');

289

explain

290

select * from t2 where a not in (345.67890, 43245.34, 64224.56344);

291

id select_type table type possible_keys key key_len ref rows Extra

292

1 SIMPLE t2 range a a 7 NULL 4 Using where

293

select * from t2 where a not in (345.67890, 43245.34, 64224.56344);

294

a filler

295

0.00000 1

296

22334.12300 1

297

33333.00000 1

298

55555.00000 1

299

77777.00000 1

300

drop table t2;

301

create table t2 (a int, key(a), b int);

302

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

303

set @cnt= 1;

304

set @str="update t2 set b=1 where a not in (";

305

select count(*) from (

306

select @str:=concat(@str, @cnt:=@cnt+1, ",")

307

from t1 A, t1 B, t1 C, t1 D where A.a < 10) Z;

308

count(*)

309

10000

310

set @str:=concat(@str, "10000)");

311

select substr(@str, 1, 50);

312

substr(@str, 1, 50)

313

update t2 set b=1 where a not in (2,3,4,5,6,7,8,9,

314

set @str=NULL;

315

drop table t2;

316

drop table t1;

317

create table t1 (

318

some_id int,

319

key (some_id)

320

);

321

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

322

select some_id from t1 where some_id not in(2,-1);

323

some_id

324

325

select some_id from t1 where some_id not in(-4,-1,-4);

326

some_id

327

328

329

select some_id from t1 where some_id not in(-4,-1,3423534,2342342);

330

some_id

331

332

333

select some_id from t1 where some_id not in('-1', '0');

334

some_id

335

336

337

drop table t1;

338

CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));

339

INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);

340

CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));

341

INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);

342

CREATE TABLE t3 (a int PRIMARY KEY);

343

INSERT INTO t3 VALUES (1),(2),(3),(4);

344

CREATE TABLE t4 (a int PRIMARY KEY,b int);

345

INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),

346

(1003,1003),(1004,1004);

347

EXPLAIN SELECT STRAIGHT_JOIN * FROM t3

348

JOIN t1 ON t3.a=t1.a

349

JOIN t2 ON t3.a=t2.a

350

JOIN t4 WHERE t4.a IN (t1.b, t2.b);

351

id select_type table type possible_keys key key_len ref rows Extra

352

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1

353

1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer

354

1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index

355

1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)

356

SELECT STRAIGHT_JOIN * FROM t3

357

JOIN t1 ON t3.a=t1.a

358

JOIN t2 ON t3.a=t2.a

359

JOIN t4 WHERE t4.a IN (t1.b, t2.b);

360

a a b a b a b

361

3 3 1 3 2 1 1

362

3 3 1 3 2 2 2

363

4 4 1 4 2 1 1

364

4 4 1 4 2 2 2

365

EXPLAIN SELECT STRAIGHT_JOIN

366

(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))

367

FROM t3, t1, t2

368

WHERE t3.a=t1.a AND t3.a=t2.a;

369

id select_type table type possible_keys key key_len ref rows Extra

370

1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1

371

1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer

372

1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index

373

2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index

374

SELECT STRAIGHT_JOIN

375

(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))

376

FROM t3, t1, t2

377

WHERE t3.a=t1.a AND t3.a=t2.a;

378

(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))

379

380

381

DROP TABLE t1,t2,t3,t4;

382

CREATE TABLE t1(a BIGINT);

383

INSERT INTO t1 VALUES (0x0FFFFFFFFFFFFFFF);

384

SELECT * FROM t1 WHERE a=-1 OR a=-2 ;

385

386

SELECT * FROM t1 WHERE a IN (-1, -2);

387

388

CREATE TABLE t2 (a BIGINT UNSIGNED);

389

insert into t2 values(13491727406643098568),

390

(0x0fffffefffffffff),

391

(0x0ffffffeffffffff),

392

(0x0fffffffefffffff),

393

(0x0ffffffffeffffff),

394

(0x0fffffffffefffff),

395

(0x0ffffffffffeffff),

396

(0x0fffffffffffefff),

397

(0x0ffffffffffffeff),

398

(0x0fffffffffffffef),

399

(0x0ffffffffffffffe),

400

(0x0fffffffffffffff),

401

(0x2000000000000000),

402

(0x2000000000000001),

403

(0x2000000000000002),

404

(0x2000000000000300),

405

(0x2000000000000400),

406

(0x2000000000000401),

407

(0x2000000000004001),

408

(0x2000000000040001),

409

(0x2000000000400001),

410

(0x2000000004000001),

411

(0x2000000040000001),

412

(0x2000000400000001),

413

(0x2000004000000001),

414

(0x2000040000000001);

415

SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);

416

HEX(a)

417

SELECT HEX(a) FROM t2 WHERE a IN

418

(0xBB3C3E98175D33C8,

419

0x2fffffffffffffff,

420

0x2000000000000000,

421

0x2000000000000400,

422

0x2000000000000401,

423

42);

424

HEX(a)

425

2000000000000000

426

2000000000000001

427

2000000000000002

428

2000000000000300

429

2000000000000400

430

2000000000000401

431

SELECT HEX(a) FROM t2 WHERE a IN

432

(0x7fffffffffffffff,

433

0x2000000000000001);

434

HEX(a)

435

2000000000000001

436

SELECT HEX(a) FROM t2 WHERE a IN

437

(0x2ffffffffffffffe,

438

0x2fffffffffffffff);

439

HEX(a)

440

SELECT HEX(a) FROM t2 WHERE a IN

441

(0x2ffffffffffffffe,

442

0x2fffffffffffffff,

443

'abc');

444

HEX(a)

445

CREATE TABLE t3 (a BIGINT);

446

INSERT INTO t3 VALUES (9223372036854775551);

447

SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);

448

HEX(a)

449

CREATE TABLE t4 (a DATE);

450

INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');

451

SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');

452

453

1972-02-06

454

Warnings:

455

Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1

456

DROP TABLE t1,t2,t3,t4;

457

CREATE TABLE t1 (id int not null);

458

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

459

SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );

460

ERROR 22012: Division by 0

461

DROP TABLE t1;

462

End of 5.0 tests

463

create TEMPORARY table t1(f1 char(1)) ENGINE=MYISAM;

464

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

465

select f1 from t1 where f1 in ('a',1);

466

467

468

469

select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;

470

f1 case f1 when 'a' then '+' when 1 then '-' end

471

a +

472

b NULL

473

1 -

474

create index t1f1_idx on t1(f1);

475

select f1 from t1 where f1 in ('a',1);

476

477

478

479

explain select f1 from t1 where f1 in ('a',1);

480

id select_type table type possible_keys key key_len ref rows Extra

481

1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index

482

select f1 from t1 where f1 in ('a','b');

483

484

485

486

explain select f1 from t1 where f1 in ('a','b');

487

id select_type table type possible_keys key key_len ref rows Extra

488

1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index

489

select f1 from t1 where f1 in (2,1);

490

491

492

explain select f1 from t1 where f1 in (2,1);

493

id select_type table type possible_keys key key_len ref rows Extra

494

1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index

495

create TEMPORARY table t2(f2 int, index t2f2(f2)) ENGINE=MYISAM;

496

insert into t2 values(0),(1),(2);

497

select f2 from t2 where f2 in ('a',2);

498

499

500

501

Warnings:

502

Warning 1292 Truncated incorrect DOUBLE value: 'a'

503

Warning 1292 Truncated incorrect DOUBLE value: 'a'

504

Warning 1292 Truncated incorrect DOUBLE value: 'a'

505

explain select f2 from t2 where f2 in ('a',2);

506

id select_type table type possible_keys key key_len ref rows Extra

507

1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index

508

select f2 from t2 where f2 in ('a','b');

509

510

511

Warnings:

512

Warning 1292 Truncated incorrect DOUBLE value: 'a'

513

Warning 1292 Truncated incorrect DOUBLE value: 'b'

514

explain select f2 from t2 where f2 in ('a','b');

515

id select_type table type possible_keys key key_len ref rows Extra

516

1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index

517

Warnings:

518

Warning 1292 Truncated incorrect DOUBLE value: 'a'

519

Warning 1292 Truncated incorrect DOUBLE value: 'b'

520

select f2 from t2 where f2 in (1,'b');

521

522

523

524

Warnings:

525

Warning 1292 Truncated incorrect DOUBLE value: 'b'

526

Warning 1292 Truncated incorrect DOUBLE value: 'b'

527

explain select f2 from t2 where f2 in (1,'b');

528

id select_type table type possible_keys key key_len ref rows Extra

529

1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index

530

drop table t1, t2;

531

create table t1 (a datetime, key(a));

532

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

533

select a from t1 where a not in (a,a,a) group by a;

534

535

drop table t1;

536

End of 5.1 tests

Older »