~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

CREATE TABLE t3 (pk1 int unsigned NOT NULL PRIMARY KEY, attr1 int unsigned NOT NULL, attr2 bigint unsigned, attr3 tinyint unsigned, attr4 VARCHAR(10) ) ENGINE=ndbcluster;

insert into t3 values (0,0,0,0,"a"),(1,1,9223372036854775803,1,"b"),(2,2,9223372036854775804,2,"c"),(3,3,9223372036854775805,3,"d"),(4,4,9223372036854775806,4,"e"),(5,5,9223372036854775807,5,"f");

CREATE TABLE t4 (pk1 int unsigned NOT NULL PRIMARY KEY, attr1 int unsigned NOT NULL, attr2 bigint unsigned, attr3 tinyint unsigned, attr4 VARCHAR(10) , KEY (attr1)) ENGINE=ndbcluster;

insert into t4 values (0,0,0,0,"a"),(1,1,9223372036854775803,1,"b"),(2,2,9223372036854775804,2,"c"),(3,3,9223372036854775805,3,"d"),(4,4,9223372036854775806,4,"e"),(5,5,9223372036854775807,5,"f");

set @old_ecpd = @@session.engine_condition_pushdown;

set engine_condition_pushdown = off;

select auto from t1 where

string = "aaaa" and

vstring = "aaaa" and

bin = 0xAAAA and

vbin = 0xAAAA and

tiny = -1 and

short = -1 and

medium = -1 and

long_int = -1 and

longlong = -1 and

real_float > 1.0 and real_float < 2.0 and

real_double > 1.0 and real_double < 2.0 and

real_decimal > 1.0 and real_decimal < 2.0 and

utiny = 1 and

ushort = 1 and

umedium = 1 and

ulong = 1 and

ulonglong = 1 and

bits = b'001' and

options = 'one' and

flags = 'one' and

date_field = '1901-01-01' and

year_field = '1901' and

time_field = '01:01:01' and

date_time = '1901-01-01 01:01:01'

order by auto;

auto

select auto from t1 where

string != "aaaa" and

vstring != "aaaa" and

bin != 0xAAAA and

vbin != 0xAAAA and

tiny != -1 and

short != -1 and

medium != -1 and

long_int != -1 and

longlong != -1 and

(real_float < 1.0 or real_float > 2.0) and

(real_double < 1.0 or real_double > 2.0) and

(real_decimal < 1.0 or real_decimal > 2.0) and

utiny != 1 and

ushort != 1 and

umedium != 1 and

100

ulong != 1 and

101

ulonglong != 1 and

102

bits != b'001' and

103

options != 'one' and

104

flags != 'one' and

105

date_field != '1901-01-01' and

106

year_field != '1901' and

107

time_field != '01:01:01' and

108

date_time != '1901-01-01 01:01:01'

109

order by auto;

110

auto

111

112

113

114

select auto from t1 where

115

string > "aaaa" and

116

vstring > "aaaa" and

117

bin > 0xAAAA and

118

vbin > 0xAAAA and

119

tiny < -1 and

120

short < -1 and

121

medium < -1 and

122

long_int < -1 and

123

longlong < -1 and

124

real_float > 1.1 and

125

real_double > 1.1 and

126

real_decimal > 1.1 and

127

utiny > 1 and

128

ushort > 1 and

129

umedium > 1 and

130

ulong > 1 and

131

ulonglong > 1 and

132

bits > b'001' and

133

(options = 'two' or options = 'three' or options = 'four') and

134

(flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

135

date_field > '1901-01-01' and

136

year_field > '1901' and

137

time_field > '01:01:01' and

138

date_time > '1901-01-01 01:01:01'

139

order by auto;

140

auto

141

142

143

144

select auto from t1 where

145

string >= "aaaa" and

146

vstring >= "aaaa" and

147

bin >= 0xAAAA and

148

vbin >= 0xAAAA and

149

tiny <= -1 and

150

short <= -1 and

151

medium <= -1 and

152

long_int <= -1 and

153

longlong <= -1 and

154

real_float >= 1.0 and

155

real_double >= 1.0 and

156

real_decimal >= 1.0 and

157

utiny >= 1 and

158

ushort >= 1 and

159

umedium >= 1 and

160

ulong >= 1 and

161

ulonglong >= 1 and

162

bits >= b'001' and

163

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

164

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

165

date_field >= '1901-01-01' and

166

year_field >= '1901' and

167

time_field >= '01:01:01' and

168

date_time >= '1901-01-01 01:01:01'

169

order by auto;

170

auto

171

172

173

174

175

select auto from t1 where

176

string < "dddd" and

177

vstring < "dddd" and

178

bin < 0xDDDD and

179

vbin < 0xDDDD and

180

tiny > -4 and

181

short > -4 and

182

medium > -4 and

183

long_int > -4 and

184

longlong > -4 and

185

real_float < 4.4 and

186

real_double < 4.4 and

187

real_decimal < 4.4 and

188

utiny < 4 and

189

ushort < 4 and

190

umedium < 4 and

191

ulong < 4 and

192

ulonglong < 4 and

193

bits < b'100' and

194

(options = 'one' or options = 'two' or options = 'three') and

195

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and

196

date_field < '1904-01-01' and

197

year_field < '1904' and

198

time_field < '04:04:04' and

199

date_time < '1904-04-04 04:04:04'

200

order by auto;

201

auto

202

203

204

205

select auto from t1 where

206

string <= "dddd" and

207

vstring <= "dddd" and

208

bin <= 0xDDDD and

209

vbin <= 0xDDDD and

210

tiny >= -4 and

211

short >= -4 and

212

medium >= -4 and

213

long_int >= -4 and

214

longlong >= -4 and

215

real_float <= 4.5 and

216

real_double <= 4.5 and

217

real_decimal <= 4.5 and

218

utiny <= 4 and

219

ushort <= 4 and

220

umedium <= 4 and

221

ulong <= 4 and

222

ulonglong <= 4 and

223

bits <= b'100' and

224

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

225

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

226

date_field <= '1904-04-04' and

227

year_field <= '1904' and

228

time_field <= '04:04:04' and

229

date_time <= '1904-04-04 04:04:04'

230

order by auto;

231

auto

232

233

234

235

236

select auto from t1 where

237

string like "b%" and

238

vstring like "b%" and

239

bin like concat(0xBB, '%') and

240

vbin like concat(0xBB, '%')

241

order by auto;

242

auto

243

244

select auto from t1 where

245

string not like "b%" and

246

vstring not like "b%" and

247

bin not like concat(0xBB, '%') and

248

vbin not like concat(0xBB, '%')

249

order by auto;

250

auto

251

252

253

254

select auto from t1 where

255

(string between "aaaa" and "cccc") and

256

(vstring between "aaaa" and "cccc") and

257

(bin between 0xAAAA and 0xCCCC) and

258

(vbin between 0xAAAA and 0xCCCC) and

259

(tiny between -3 and -1) and

260

(short between -3 and -1) and

261

(medium between -3 and -1) and

262

(long_int between -3 and -1) and

263

(longlong between -3 and -1) and

264

(utiny between 1 and 3) and

265

(ushort between 1 and 3) and

266

(umedium between 1 and 3) and

267

(ulong between 1 and 3) and

268

(ulonglong between 1 and 3) and

269

(bits between b'001' and b'011') and

270

(options between 'one' and 'three') and

271

(flags between 'one' and 'one,two,three') and

272

(date_field between '1901-01-01' and '1903-03-03') and

273

(year_field between '1901' and '1903') and

274

(time_field between '01:01:01' and '03:03:03') and

275

(date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')

276

order by auto;

277

auto

278

279

280

select auto from t1 where

281

("aaaa" between string and string) and

282

("aaaa" between vstring and vstring) and

283

(0xAAAA between bin and bin) and

284

(0xAAAA between vbin and vbin) and

285

(-1 between tiny and tiny) and

286

(-1 between short and short) and

287

(-1 between medium and medium) and

288

(-1 between long_int and long_int) and

289

(-1 between longlong and longlong) and

290

(1 between utiny and utiny) and

291

(1 between ushort and ushort) and

292

(1 between umedium and umedium) and

293

(1 between ulong and ulong) and

294

(1 between ulonglong and ulonglong) and

295

(b'001' between bits and bits) and

296

('one' between options and options) and

297

('one' between flags and flags) and

298

('1901-01-01' between date_field and date_field) and

299

('1901' between year_field and year_field) and

300

('01:01:01' between time_field and time_field) and

301

('1901-01-01 01:01:01' between date_time and date_time)

302

order by auto;

303

auto

304

305

select auto from t1 where

306

(string not between "aaaa" and "cccc") and

307

(vstring not between "aaaa" and "cccc") and

308

(bin not between 0xAAAA and 0xCCCC) and

309

(vbin not between 0xAAAA and 0xCCCC) and

310

(tiny not between -3 and -1) and

311

(short not between -3 and -1) and

312

(medium not between -3 and -1) and

313

(long_int not between -3 and -1) and

314

(longlong not between -3 and -1) and

315

(utiny not between 1 and 3) and

316

(ushort not between 1 and 3) and

317

(umedium not between 1 and 3) and

318

(ulong not between 1 and 3) and

319

(ulonglong not between 1 and 3) and

320

(bits not between b'001' and b'011') and

321

(options not between 'one' and 'three') and

322

(flags not between 'one' and 'one,two,three') and

323

(date_field not between '1901-01-01' and '1903-03-03') and

324

(year_field not between '1901' and '1903') and

325

(time_field not between '01:01:01' and '03:03:03') and

326

(date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')

327

order by auto;

328

auto

329

330

select auto from t1 where

331

("aaaa" not between string and string) and

332

("aaaa" not between vstring and vstring) and

333

(0xAAAA not between bin and bin) and

334

(0xAAAA not between vbin and vbin) and

335

(-1 not between tiny and tiny) and

336

(-1 not between short and short) and

337

(-1 not between medium and medium) and

338

(-1 not between long_int and long_int) and

339

(-1 not between longlong and longlong) and

340

(1 not between utiny and utiny) and

341

(1 not between ushort and ushort) and

342

(1 not between umedium and umedium) and

343

(1 not between ulong and ulong) and

344

(1 not between ulonglong and ulonglong) and

345

(b'001' not between bits and bits) and

346

('one' not between options and options) and

347

('one' not between flags and flags) and

348

('1901-01-01' not between date_field and date_field) and

349

('1901' not between year_field and year_field) and

350

('01:01:01' not between time_field and time_field) and

351

('1901-01-01 01:01:01' not between date_time and date_time)

352

order by auto;

353

auto

354

355

356

357

select auto from t1 where

358

string in("aaaa","cccc") and

359

vstring in("aaaa","cccc") and

360

bin in(0xAAAA,0xCCCC) and

361

vbin in(0xAAAA,0xCCCC) and

362

tiny in(-1,-3) and

363

short in(-1,-3) and

364

medium in(-1,-3) and

365

long_int in(-1,-3) and

366

longlong in(-1,-3) and

367

utiny in(1,3) and

368

ushort in(1,3) and

369

umedium in(1,3) and

370

ulong in(1,3) and

371

ulonglong in(1,3) and

372

bits in(b'001',b'011') and

373

options in('one','three') and

374

flags in('one','one,two,three') and

375

date_field in('1901-01-01','1903-03-03') and

376

year_field in('1901','1903') and

377

time_field in('01:01:01','03:03:03') and

378

date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03')

379

order by auto;

380

auto

381

382

383

select auto from t1 where

384

"aaaa" in(string) and

385

"aaaa" in(vstring) and

386

0xAAAA in(bin) and

387

0xAAAA in(vbin) and

388

(-1 in(tiny)) and

389

(-1 in(short)) and

390

(-1 in(medium)) and

391

(-1 in(long_int)) and

392

(-1 in(longlong)) and

393

1 in(utiny) and

394

1 in(ushort) and

395

1 in(umedium) and

396

1 in(ulong) and

397

1 in(ulonglong) and

398

b'001' in(bits) and

399

'one' in(options) and

400

'one' in(flags) and

401

'1901-01-01' in(date_field) and

402

'1901' in(year_field) and

403

'01:01:01' in(time_field) and

404

'1901-01-01 01:01:01' in(date_time)

405

order by auto;

406

auto

407

408

select auto from t1 where

409

string not in("aaaa","cccc") and

410

vstring not in("aaaa","cccc") and

411

bin not in(0xAAAA,0xCCCC) and

412

vbin not in(0xAAAA,0xCCCC) and

413

tiny not in(-1,-3) and

414

short not in(-1,-3) and

415

medium not in(-1,-3) and

416

long_int not in(-1,-3) and

417

longlong not in(-1,-3) and

418

utiny not in(1,3) and

419

ushort not in(1,3) and

420

umedium not in(1,3) and

421

ulong not in(1,3) and

422

ulonglong not in(1,3) and

423

bits not in(b'001',b'011') and

424

options not in('one','three') and

425

flags not in('one','one,two,three') and

426

date_field not in('1901-01-01','1903-03-03') and

427

year_field not in('1901','1903') and

428

time_field not in('01:01:01','03:03:03') and

429

date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03')

430

order by auto;

431

auto

432

433

434

select auto from t1 where

435

"aaaa" not in(string) and

436

"aaaa" not in(vstring) and

437

0xAAAA not in(bin) and

438

0xAAAA not in(vbin) and

439

(-1 not in(tiny)) and

440

(-1 not in(short)) and

441

(-1 not in(medium)) and

442

(-1 not in(long_int)) and

443

(-1 not in(longlong)) and

444

1 not in(utiny) and

445

1 not in(ushort) and

446

1 not in(umedium) and

447

1 not in(ulong) and

448

1 not in(ulonglong) and

449

b'001' not in(bits) and

450

'one' not in(options) and

451

'one' not in(flags) and

452

'1901-01-01' not in(date_field) and

453

'1901' not in(year_field) and

454

'01:01:01' not in(time_field) and

455

'1901-01-01 01:01:01' not in(date_time)

456

order by auto;

457

auto

458

459

460

461

select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;

462

pk1 attr1 attr2 attr3

463

2 2 NULL NULL

464

3 3 3 d

465

select * from t2 where attr3 is not null and attr1 > 2 order by pk1;

466

pk1 attr1 attr2 attr3

467

3 3 3 d

468

4 4 4 e

469

5 5 5 f

470

select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;

471

pk1 attr1 attr2 attr3 attr4

472

2 2 9223372036854775804 2 c

473

4 4 9223372036854775806 4 e

474

5 5 9223372036854775807 5 f

475

select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;

476

pk1 attr1 attr2 attr3 pk1 attr1 attr2 attr3 attr4

477

0 0 0 a 0 0 0 0 a

478

select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;

479

pk1 attr1 attr2 attr3 attr4

480

2 2 9223372036854775804 2 c

481

4 4 9223372036854775806 4 e

482

select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;

483

pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4

484

2 2 9223372036854775804 2 c 2 2 9223372036854775804 2 c

485

3 3 9223372036854775805 3 d 3 3 9223372036854775805 3 d

486

4 4 9223372036854775806 4 e 4 4 9223372036854775806 4 e

487

set engine_condition_pushdown = on;

488

explain

489

select auto from t1 where

490

string = "aaaa" and

491

vstring = "aaaa" and

492

bin = 0xAAAA and

493

vbin = 0xAAAA and

494

tiny = -1 and

495

short = -1 and

496

medium = -1 and

497

long_int = -1 and

498

longlong = -1 and

499

real_float > 1.0 and real_float < 2.0 and

500

real_double > 1.0 and real_double < 2.0 and

501

real_decimal > 1.0 and real_decimal < 2.0 and

502

utiny = 1 and

503

ushort = 1 and

504

umedium = 1 and

505

ulong = 1 and

506

ulonglong = 1 and

507

/* bits = b'001' and */

508

options = 'one' and

509

flags = 'one' and

510

date_field = '1901-01-01' and

511

year_field = '1901' and

512

time_field = '01:01:01' and

513

date_time = '1901-01-01 01:01:01'

514

order by auto;

515

id select_type table type possible_keys key key_len ref rows Extra

516

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

517

select auto from t1 where

518

string = "aaaa" and

519

vstring = "aaaa" and

520

bin = 0xAAAA and

521

vbin = 0xAAAA and

522

tiny = -1 and

523

short = -1 and

524

medium = -1 and

525

long_int = -1 and

526

longlong = -1 and

527

real_float > 1.0 and real_float < 2.0 and

528

real_double > 1.0 and real_double < 2.0 and

529

real_decimal > 1.0 and real_decimal < 2.0 and

530

utiny = 1 and

531

ushort = 1 and

532

umedium = 1 and

533

ulong = 1 and

534

ulonglong = 1 and

535

/* bits = b'001' and */

536

options = 'one' and

537

flags = 'one' and

538

date_field = '1901-01-01' and

539

year_field = '1901' and

540

time_field = '01:01:01' and

541

date_time = '1901-01-01 01:01:01'

542

order by auto;

543

auto

544

545

explain

546

select auto from t1 where

547

string != "aaaa" and

548

vstring != "aaaa" and

549

bin != 0xAAAA and

550

vbin != 0xAAAA and

551

tiny != -1 and

552

short != -1 and

553

medium != -1 and

554

long_int != -1 and

555

longlong != -1 and

556

(real_float < 1.0 or real_float > 2.0) and

557

(real_double < 1.0 or real_double > 2.0) and

558

(real_decimal < 1.0 or real_decimal > 2.0) and

559

utiny != 1 and

560

ushort != 1 and

561

umedium != 1 and

562

ulong != 1 and

563

ulonglong != 1 and

564

/* bits != b'001' and */

565

options != 'one' and

566

flags != 'one' and

567

date_field != '1901-01-01' and

568

year_field != '1901' and

569

time_field != '01:01:01' and

570

date_time != '1901-01-01 01:01:01'

571

order by auto;

572

id select_type table type possible_keys key key_len ref rows Extra

573

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

574

select auto from t1 where

575

string != "aaaa" and

576

vstring != "aaaa" and

577

bin != 0xAAAA and

578

vbin != 0xAAAA and

579

tiny != -1 and

580

short != -1 and

581

medium != -1 and

582

long_int != -1 and

583

longlong != -1 and

584

(real_float < 1.0 or real_float > 2.0) and

585

(real_double < 1.0 or real_double > 2.0) and

586

(real_decimal < 1.0 or real_decimal > 2.0) and

587

utiny != 1 and

588

ushort != 1 and

589

umedium != 1 and

590

ulong != 1 and

591

ulonglong != 1 and

592

/* bits != b'001' and */

593

options != 'one' and

594

flags != 'one' and

595

date_field != '1901-01-01' and

596

year_field != '1901' and

597

time_field != '01:01:01' and

598

date_time != '1901-01-01 01:01:01'

599

order by auto;

600

auto

601

602

603

604

explain

605

select auto from t1 where

606

string > "aaaa" and

607

vstring > "aaaa" and

608

bin > 0xAAAA and

609

vbin > 0xAAAA and

610

tiny < -1 and

611

short < -1 and

612

medium < -1 and

613

long_int < -1 and

614

longlong < -1 and

615

real_float > 1.1 and

616

real_double > 1.1 and

617

real_decimal > 1.1 and

618

utiny > 1 and

619

ushort > 1 and

620

umedium > 1 and

621

ulong > 1 and

622

ulonglong > 1 and

623

/* bits > b'001' and */

624

(options = 'two' or options = 'three' or options = 'four') and

625

(flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

626

date_field > '1901-01-01' and

627

year_field > '1901' and

628

time_field > '01:01:01' and

629

date_time > '1901-01-01 01:01:01'

630

order by auto;

631

id select_type table type possible_keys key key_len ref rows Extra

632

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

633

select auto from t1 where

634

string > "aaaa" and

635

vstring > "aaaa" and

636

bin > 0xAAAA and

637

vbin > 0xAAAA and

638

tiny < -1 and

639

short < -1 and

640

medium < -1 and

641

long_int < -1 and

642

longlong < -1 and

643

real_float > 1.1 and

644

real_double > 1.1 and

645

real_decimal > 1.1 and

646

utiny > 1 and

647

ushort > 1 and

648

umedium > 1 and

649

ulong > 1 and

650

ulonglong > 1 and

651

/* bits > b'001' and */

652

(options = 'two' or options = 'three' or options = 'four') and

653

(flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

654

date_field > '1901-01-01' and

655

year_field > '1901' and

656

time_field > '01:01:01' and

657

date_time > '1901-01-01 01:01:01'

658

order by auto;

659

auto

660

661

662

663

explain

664

select auto from t1 where

665

string >= "aaaa" and

666

vstring >= "aaaa" and

667

bin >= 0xAAAA and

668

vbin >= 0xAAAA and

669

tiny <= -1 and

670

short <= -1 and

671

medium <= -1 and

672

long_int <= -1 and

673

longlong <= -1 and

674

real_float >= 1.0 and

675

real_double >= 1.0 and

676

real_decimal >= 1.0 and

677

utiny >= 1 and

678

ushort >= 1 and

679

umedium >= 1 and

680

ulong >= 1 and

681

ulonglong >= 1 and

682

/* bits >= b'001' and */

683

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

684

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

685

date_field >= '1901-01-01' and

686

year_field >= '1901' and

687

time_field >= '01:01:01' and

688

date_time >= '1901-01-01 01:01:01'

689

order by auto;

690

id select_type table type possible_keys key key_len ref rows Extra

691

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

692

select auto from t1 where

693

string >= "aaaa" and

694

vstring >= "aaaa" and

695

bin >= 0xAAAA and

696

vbin >= 0xAAAA and

697

tiny <= -1 and

698

short <= -1 and

699

medium <= -1 and

700

long_int <= -1 and

701

longlong <= -1 and

702

real_float >= 1.0 and

703

real_double >= 1.0 and

704

real_decimal >= 1.0 and

705

utiny >= 1 and

706

ushort >= 1 and

707

umedium >= 1 and

708

ulong >= 1 and

709

ulonglong >= 1 and

710

/* bits >= b'001' and */

711

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

712

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

713

date_field >= '1901-01-01' and

714

year_field >= '1901' and

715

time_field >= '01:01:01' and

716

date_time >= '1901-01-01 01:01:01'

717

order by auto;

718

auto

719

720

721

722

723

explain

724

select auto from t1 where

725

string < "dddd" and

726

vstring < "dddd" and

727

bin < 0xDDDD and

728

vbin < 0xDDDD and

729

tiny > -4 and

730

short > -4 and

731

medium > -4 and

732

long_int > -4 and

733

longlong > -4 and

734

real_float < 4.4 and

735

real_double < 4.4 and

736

real_decimal < 4.4 and

737

utiny < 4 and

738

ushort < 4 and

739

umedium < 4 and

740

ulong < 4 and

741

ulonglong < 4 and

742

/* bits < b'100' and */

743

(options = 'one' or options = 'two' or options = 'three') and

744

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and

745

date_field < '1904-01-01' and

746

year_field < '1904' and

747

time_field < '04:04:04' and

748

date_time < '1904-04-04 04:04:04'

749

order by auto;

750

id select_type table type possible_keys key key_len ref rows Extra

751

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

752

select auto from t1 where

753

string < "dddd" and

754

vstring < "dddd" and

755

bin < 0xDDDD and

756

vbin < 0xDDDD and

757

tiny > -4 and

758

short > -4 and

759

medium > -4 and

760

long_int > -4 and

761

longlong > -4 and

762

real_float < 4.4 and

763

real_double < 4.4 and

764

real_decimal < 4.4 and

765

utiny < 4 and

766

ushort < 4 and

767

umedium < 4 and

768

ulong < 4 and

769

ulonglong < 4 and

770

/* bits < b'100' and */

771

(options = 'one' or options = 'two' or options = 'three') and

772

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and

773

date_field < '1904-01-01' and

774

year_field < '1904' and

775

time_field < '04:04:04' and

776

date_time < '1904-04-04 04:04:04'

777

order by auto;

778

auto

779

780

781

782

explain

783

select auto from t1 where

784

string <= "dddd" and

785

vstring <= "dddd" and

786

bin <= 0xDDDD and

787

vbin <= 0xDDDD and

788

tiny >= -4 and

789

short >= -4 and

790

medium >= -4 and

791

long_int >= -4 and

792

longlong >= -4 and

793

real_float <= 4.5 and

794

real_double <= 4.5 and

795

real_decimal <= 4.5 and

796

utiny <= 4 - 1 + 1 and /* Checking function composition */

797

ushort <= 4 and

798

umedium <= 4 and

799

ulong <= 4 and

800

ulonglong <= 4 and

801

/* bits <= b'100' and */

802

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

803

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

804

date_field <= '1904-04-04' and

805

year_field <= '1904' and

806

time_field <= '04:04:04' and

807

date_time <= '1904-04-04 04:04:04'

808

order by auto;

809

id select_type table type possible_keys key key_len ref rows Extra

810

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

811

select auto from t1 where

812

string <= "dddd" and

813

vstring <= "dddd" and

814

bin <= 0xDDDD and

815

vbin <= 0xDDDD and

816

tiny >= -4 and

817

short >= -4 and

818

medium >= -4 and

819

long_int >= -4 and

820

longlong >= -4 and

821

real_float <= 4.5 and

822

real_double <= 4.5 and

823

real_decimal <= 4.5 and

824

utiny <= 4 - 1 + 1 and /* Checking function composition */

825

ushort <= 4 and

826

umedium <= 4 and

827

ulong <= 4 and

828

ulonglong <= 4 and

829

/* bits <= b'100' and */

830

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

831

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

832

date_field <= '1904-04-04' and

833

year_field <= '1904' and

834

time_field <= '04:04:04' and

835

date_time <= '1904-04-04 04:04:04'

836

order by auto;

837

auto

838

839

840

841

842

create index medium_index on t1(medium);

843

explain

844

select auto from t1 where

845

string = "aaaa" and

846

vstring = "aaaa" and

847

bin = 0xAAAA and

848

vbin = 0xAAAA and

849

tiny = -1 and

850

short = -1 and

851

medium = -1 and

852

long_int = -1 and

853

longlong = -1 and

854

real_float > 1.0 and real_float < 2.0 and

855

real_double > 1.0 and real_double < 2.0 and

856

real_decimal > 1.0 and real_decimal < 2.0 and

857

utiny = 1 and

858

ushort = 1 and

859

umedium = 1 and

860

ulong = 1 and

861

ulonglong = 1 and

862

/* bits = b'001' and */

863

options = 'one' and

864

flags = 'one' and

865

date_field = '1901-01-01' and

866

year_field = '1901' and

867

time_field = '01:01:01' and

868

date_time = '1901-01-01 01:01:01'

869

order by auto;

870

id select_type table type possible_keys key key_len ref rows Extra

871

1 SIMPLE t1 ref medium_index medium_index 3 const # Using where with pushed condition; Using filesort

872

select auto from t1 where

873

string = "aaaa" and

874

vstring = "aaaa" and

875

bin = 0xAAAA and

876

vbin = 0xAAAA and

877

tiny = -1 and

878

short = -1 and

879

medium = -1 and

880

long_int = -1 and

881

longlong = -1 and

882

real_float > 1.0 and real_float < 2.0 and

883

real_double > 1.0 and real_double < 2.0 and

884

real_decimal > 1.0 and real_decimal < 2.0 and

885

utiny = 1 and

886

ushort = 1 and

887

umedium = 1 and

888

ulong = 1 and

889

ulonglong = 1 and

890

/* bits = b'001' and */

891

options = 'one' and

892

flags = 'one' and

893

date_field = '1901-01-01' and

894

year_field = '1901' and

895

time_field = '01:01:01' and

896

date_time = '1901-01-01 01:01:01'

897

order by auto;

898

auto

899

900

explain

901

select auto from t1 where

902

string != "aaaa" and

903

vstring != "aaaa" and

904

bin != 0xAAAA and

905

vbin != 0xAAAA and

906

tiny != -1 and

907

short != -1 and

908

medium != -1 and

909

long_int != -1 and

910

longlong != -1 and

911

(real_float < 1.0 or real_float > 2.0) and

912

(real_double < 1.0 or real_double > 2.0) and

913

(real_decimal < 1.0 or real_decimal > 2.0) and

914

utiny != 1 and

915

ushort != 1 and

916

umedium != 1 and

917

ulong != 1 and

918

ulonglong != 1 and

919

/* bits != b'001' and */

920

options != 'one' and

921

flags != 'one' and

922

date_field != '1901-01-01' and

923

year_field != '1901' and

924

time_field != '01:01:01' and

925

date_time != '1901-01-01 01:01:01'

926

order by auto;

927

id select_type table type possible_keys key key_len ref rows Extra

928

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

929

select auto from t1 where

930

string != "aaaa" and

931

vstring != "aaaa" and

932

bin != 0xAAAA and

933

vbin != 0xAAAA and

934

tiny != -1 and

935

short != -1 and

936

medium != -1 and

937

long_int != -1 and

938

longlong != -1 and

939

(real_float < 1.0 or real_float > 2.0) and

940

(real_double < 1.0 or real_double > 2.0) and

941

(real_decimal < 1.0 or real_decimal > 2.0) and

942

utiny != 1 and

943

ushort != 1 and

944

umedium != 1 and

945

ulong != 1 and

946

ulonglong != 1 and

947

/* bits != b'001' and */

948

options != 'one' and

949

flags != 'one' and

950

date_field != '1901-01-01' and

951

year_field != '1901' and

952

time_field != '01:01:01' and

953

date_time != '1901-01-01 01:01:01'

954

order by auto;

955

auto

956

957

958

959

explain

960

select auto from t1 where

961

string > "aaaa" and

962

vstring > "aaaa" and

963

bin > 0xAAAA and

964

vbin > 0xAAAA and

965

tiny < -1 and

966

short < -1 and

967

medium < -1 and

968

long_int < -1 and

969

longlong < -1 and

970

real_float > 1.1 and

971

real_double > 1.1 and

972

real_decimal > 1.1 and

973

utiny > 1 and

974

ushort > 1 and

975

umedium > 1 and

976

ulong > 1 and

977

ulonglong > 1 and

978

/* bits > b'001' and */

979

(options = 'two' or options = 'three' or options = 'four') and

980

(flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

981

date_field > '1901-01-01' and

982

year_field > '1901' and

983

time_field > '01:01:01' and

984

date_time > '1901-01-01 01:01:01'

985

order by auto;

986

id select_type table type possible_keys key key_len ref rows Extra

987

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

988

select auto from t1 where

989

string > "aaaa" and

990

vstring > "aaaa" and

991

bin > 0xAAAA and

992

vbin > 0xAAAA and

993

tiny < -1 and

994

short < -1 and

995

medium < -1 and

996

long_int < -1 and

997

longlong < -1 and

998

real_float > 1.1 and

999

real_double > 1.1 and

1000

real_decimal > 1.1 and

1001

utiny > 1 and

1002

ushort > 1 and

1003

umedium > 1 and

1004

ulong > 1 and

1005

ulonglong > 1 and

1006

/* bits > b'001' and */

1007

(options = 'two' or options = 'three' or options = 'four') and

1008

(flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

1009

date_field > '1901-01-01' and

1010

year_field > '1901' and

1011

time_field > '01:01:01' and

1012

date_time > '1901-01-01 01:01:01'

1013

order by auto;

1014

auto

1015

1016

1017

1018

explain

1019

select auto from t1 where

1020

string >= "aaaa" and

1021

vstring >= "aaaa" and

1022

bin >= 0xAAAA and

1023

vbin >= 0xAAAA and

1024

tiny <= -1 and

1025

short <= -1 and

1026

medium <= -1 and

1027

long_int <= -1 and

1028

longlong <= -1 and

1029

real_float >= 1.0 and

1030

real_double >= 1.0 and

1031

real_decimal >= 1.0 and

1032

utiny >= 1 and

1033

ushort >= 1 and

1034

umedium >= 1 and

1035

ulong >= 1 and

1036

ulonglong >= 1 and

1037

/* bits >= b'001' and */

1038

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

1039

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

1040

date_field >= '1901-01-01' and

1041

year_field >= '1901' and

1042

time_field >= '01:01:01' and

1043

date_time >= '1901-01-01 01:01:01'

1044

order by auto;

1045

id select_type table type possible_keys key key_len ref rows Extra

1046

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

1047

select auto from t1 where

1048

string >= "aaaa" and

1049

vstring >= "aaaa" and

1050

bin >= 0xAAAA and

1051

vbin >= 0xAAAA and

1052

tiny <= -1 and

1053

short <= -1 and

1054

medium <= -1 and

1055

long_int <= -1 and

1056

longlong <= -1 and

1057

real_float >= 1.0 and

1058

real_double >= 1.0 and

1059

real_decimal >= 1.0 and

1060

utiny >= 1 and

1061

ushort >= 1 and

1062

umedium >= 1 and

1063

ulong >= 1 and

1064

ulonglong >= 1 and

1065

/* bits >= b'001' and */

1066

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

1067

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

1068

date_field >= '1901-01-01' and

1069

year_field >= '1901' and

1070

time_field >= '01:01:01' and

1071

date_time >= '1901-01-01 01:01:01'

1072

order by auto;

1073

auto

1074

1075

1076

1077

1078

explain

1079

select auto from t1 where

1080

string < "dddd" and

1081

vstring < "dddd" and

1082

bin < 0xDDDD and

1083

vbin < 0xDDDD and

1084

tiny > -4 and

1085

short > -4 and

1086

medium > -4 and

1087

long_int > -4 and

1088

longlong > -4 and

1089

real_float < 4.4 and

1090

real_double < 4.4 and

1091

real_decimal < 4.4 and

1092

utiny < 4 and

1093

ushort < 4 and

1094

umedium < 4 and

1095

ulong < 4 and

1096

ulonglong < 4 and

1097

/* bits < b'100' and */

1098

(options = 'one' or options = 'two' or options = 'three') and

1099

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and

1100

date_field < '1904-01-01' and

1101

year_field < '1904' and

1102

time_field < '04:04:04' and

1103

date_time < '1904-04-04 04:04:04'

1104

order by auto;

1105

id select_type table type possible_keys key key_len ref rows Extra

1106

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

1107

select auto from t1 where

1108

string < "dddd" and

1109

vstring < "dddd" and

1110

bin < 0xDDDD and

1111

vbin < 0xDDDD and

1112

tiny > -4 and

1113

short > -4 and

1114

medium > -4 and

1115

long_int > -4 and

1116

longlong > -4 and

1117

real_float < 4.4 and

1118

real_double < 4.4 and

1119

real_decimal < 4.4 and

1120

utiny < 4 and

1121

ushort < 4 and

1122

umedium < 4 and

1123

ulong < 4 and

1124

ulonglong < 4 and

1125

/* bits < b'100' and */

1126

(options = 'one' or options = 'two' or options = 'three') and

1127

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three') and

1128

date_field < '1904-01-01' and

1129

year_field < '1904' and

1130

time_field < '04:04:04' and

1131

date_time < '1904-04-04 04:04:04'

1132

order by auto;

1133

auto

1134

1135

1136

1137

explain

1138

select auto from t1 where

1139

string <= "dddd" and

1140

vstring <= "dddd" and

1141

bin <= 0xDDDD and

1142

vbin <= 0xDDDD and

1143

tiny >= -4 and

1144

short >= -4 and

1145

medium >= -4 and

1146

long_int >= -4 and

1147

longlong >= -4 and

1148

real_float <= 4.5 and

1149

real_double <= 4.5 and

1150

real_decimal <= 4.5 and

1151

utiny <= 4 - 1 + 1 and /* Checking function composition */

1152

ushort <= 4 and

1153

umedium <= 4 and

1154

ulong <= 4 and

1155

ulonglong <= 4 and

1156

/* bits <= b'100' and */

1157

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

1158

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

1159

date_field <= '1904-04-04' and

1160

year_field <= '1904' and

1161

time_field <= '04:04:04' and

1162

date_time <= '1904-04-04 04:04:04'

1163

order by auto;

1164

id select_type table type possible_keys key key_len ref rows Extra

1165

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

1166

select auto from t1 where

1167

string <= "dddd" and

1168

vstring <= "dddd" and

1169

bin <= 0xDDDD and

1170

vbin <= 0xDDDD and

1171

tiny >= -4 and

1172

short >= -4 and

1173

medium >= -4 and

1174

long_int >= -4 and

1175

longlong >= -4 and

1176

real_float <= 4.5 and

1177

real_double <= 4.5 and

1178

real_decimal <= 4.5 and

1179

utiny <= 4 - 1 + 1 and /* Checking function composition */

1180

ushort <= 4 and

1181

umedium <= 4 and

1182

ulong <= 4 and

1183

ulonglong <= 4 and

1184

/* bits <= b'100' and */

1185

(options = 'one' or options = 'two' or options = 'three' or options = 'four') and

1186

(flags = 'one' or flags = 'one,two' or flags = 'one,two,three' or flags = 'one,two,three,four') and

1187

date_field <= '1904-04-04' and

1188

year_field <= '1904' and

1189

time_field <= '04:04:04' and

1190

date_time <= '1904-04-04 04:04:04'

1191

order by auto;

1192

auto

1193

1194

1195

1196

1197

explain

1198

select auto from t1 where

1199

string like "b%" and

1200

vstring like "b%" and

1201

bin like concat(0xBB, '%') and

1202

vbin like concat(0xBB, '%')

1203

order by auto;

1204

id select_type table type possible_keys key key_len ref rows Extra

1205

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

1206

select auto from t1 where

1207

string like "b%" and

1208

vstring like "b%" and

1209

bin like concat(0xBB, '%') and

1210

vbin like concat(0xBB, '%')

1211

order by auto;

1212

auto

1213

1214

explain

1215

select auto from t1 where

1216

string not like "b%" and

1217

vstring not like "b%" and

1218

bin not like concat(0xBB, '%') and

1219

vbin not like concat(0xBB, '%')

1220

order by auto;

1221

id select_type table type possible_keys key key_len ref rows Extra

1222

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

1223

select auto from t1 where

1224

string not like "b%" and

1225

vstring not like "b%" and

1226

bin not like concat(0xBB, '%') and

1227

vbin not like concat(0xBB, '%')

1228

order by auto;

1229

auto

1230

1231

1232

1233

explain

1234

select auto from t1 where

1235

(string between "aaaa" and "cccc") and

1236

(vstring between "aaaa" and "cccc") and

1237

(bin between 0xAAAA and 0xCCCC) and

1238

(vbin between 0xAAAA and 0xCCCC) and

1239

(tiny between -3 and -1) and

1240

(short between -3 and -1) and

1241

(medium between -3 and -1) and

1242

(long_int between -3 and -1) and

1243

(longlong between -3 and -1) and

1244

(utiny between 1 and 3) and

1245

(ushort between 1 and 3) and

1246

(umedium between 1 and 3) and

1247

(ulong between 1 and 3) and

1248

(ulonglong between 1 and 3) and

1249

/* (bits between b'001' and b'011') and */

1250

(options between 'one' and 'three') and

1251

(flags between 'one' and 'one,two,three') and

1252

(date_field between '1901-01-01' and '1903-03-03') and

1253

(year_field between '1901' and '1903') and

1254

(time_field between '01:01:01' and '03:03:03') and

1255

(date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')

1256

order by auto;

1257

id select_type table type possible_keys key key_len ref rows Extra

1258

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

1259

select auto from t1 where

1260

(string between "aaaa" and "cccc") and

1261

(vstring between "aaaa" and "cccc") and

1262

(bin between 0xAAAA and 0xCCCC) and

1263

(vbin between 0xAAAA and 0xCCCC) and

1264

(tiny between -3 and -1) and

1265

(short between -3 and -1) and

1266

(medium between -3 and -1) and

1267

(long_int between -3 and -1) and

1268

(longlong between -3 and -1) and

1269

(utiny between 1 and 3) and

1270

(ushort between 1 and 3) and

1271

(umedium between 1 and 3) and

1272

(ulong between 1 and 3) and

1273

(ulonglong between 1 and 3) and

1274

/* (bits between b'001' and b'011') and */

1275

(options between 'one' and 'three') and

1276

(flags between 'one' and 'one,two,three') and

1277

(date_field between '1901-01-01' and '1903-03-03') and

1278

(year_field between '1901' and '1903') and

1279

(time_field between '01:01:01' and '03:03:03') and

1280

(date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')

1281

order by auto;

1282

auto

1283

1284

1285

explain

1286

select auto from t1 where

1287

("aaaa" between string and string) and

1288

("aaaa" between vstring and vstring) and

1289

(0xAAAA between bin and bin) and

1290

(0xAAAA between vbin and vbin) and

1291

(-1 between tiny and tiny) and

1292

(-1 between short and short) and

1293

(-1 between medium and medium) and

1294

(-1 between long_int and long_int) and

1295

(-1 between longlong and longlong) and

1296

(1 between utiny and utiny) and

1297

(1 between ushort and ushort) and

1298

(1 between umedium and umedium) and

1299

(1 between ulong and ulong) and

1300

(1 between ulonglong and ulonglong) and

1301

/* (b'001' between bits and bits) and */

1302

('one' between options and options) and

1303

('one' between flags and flags) and

1304

('1901-01-01' between date_field and date_field) and

1305

('1901' between year_field and year_field) and

1306

('01:01:01' between time_field and time_field) and

1307

('1901-01-01 01:01:01' between date_time and date_time)

1308

order by auto;

1309

id select_type table type possible_keys key key_len ref rows Extra

1310

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

1311

select auto from t1 where

1312

("aaaa" between string and string) and

1313

("aaaa" between vstring and vstring) and

1314

(0xAAAA between bin and bin) and

1315

(0xAAAA between vbin and vbin) and

1316

(-1 between tiny and tiny) and

1317

(-1 between short and short) and

1318

(-1 between medium and medium) and

1319

(-1 between long_int and long_int) and

1320

(-1 between longlong and longlong) and

1321

(1 between utiny and utiny) and

1322

(1 between ushort and ushort) and

1323

(1 between umedium and umedium) and

1324

(1 between ulong and ulong) and

1325

(1 between ulonglong and ulonglong) and

1326

/* (b'001' between bits and bits) and */

1327

('one' between options and options) and

1328

('one' between flags and flags) and

1329

('1901-01-01' between date_field and date_field) and

1330

('1901' between year_field and year_field) and

1331

('01:01:01' between time_field and time_field) and

1332

('1901-01-01 01:01:01' between date_time and date_time)

1333

order by auto;

1334

auto

1335

1336

explain

1337

select auto from t1 where

1338

(string not between "aaaa" and "cccc") and

1339

(vstring not between "aaaa" and "cccc") and

1340

(bin not between 0xAAAA and 0xCCCC) and

1341

(vbin not between 0xAAAA and 0xCCCC) and

1342

(tiny not between -3 and -1) and

1343

(short not between -3 and -1) and

1344

(medium not between -3 and -1) and

1345

(long_int not between -3 and -1) and

1346

(longlong not between -3 and -1) and

1347

(utiny not between 1 and 3) and

1348

(ushort not between 1 and 3) and

1349

(umedium not between 1 and 3) and

1350

(ulong not between 1 and 3) and

1351

(ulonglong not between 1 and 3) and

1352

/* (bits not between b'001' and b'011') and */

1353

(options not between 'one' and 'three') and

1354

(flags not between 'one' and 'one,two,three') and

1355

(date_field not between '1901-01-01' and '1903-03-03') and

1356

(year_field not between '1901' and '1903') and

1357

(time_field not between '01:01:01' and '03:03:03') and

1358

(date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')

1359

order by auto;

1360

id select_type table type possible_keys key key_len ref rows Extra

1361

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

1362

select auto from t1 where

1363

(string not between "aaaa" and "cccc") and

1364

(vstring not between "aaaa" and "cccc") and

1365

(bin not between 0xAAAA and 0xCCCC) and

1366

(vbin not between 0xAAAA and 0xCCCC) and

1367

(tiny not between -3 and -1) and

1368

(short not between -3 and -1) and

1369

(medium not between -3 and -1) and

1370

(long_int not between -3 and -1) and

1371

(longlong not between -3 and -1) and

1372

(utiny not between 1 and 3) and

1373

(ushort not between 1 and 3) and

1374

(umedium not between 1 and 3) and

1375

(ulong not between 1 and 3) and

1376

(ulonglong not between 1 and 3) and

1377

/* (bits not between b'001' and b'011') and */

1378

(options not between 'one' and 'three') and

1379

(flags not between 'one' and 'one,two,three') and

1380

(date_field not between '1901-01-01' and '1903-03-03') and

1381

(year_field not between '1901' and '1903') and

1382

(time_field not between '01:01:01' and '03:03:03') and

1383

(date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')

1384

order by auto;

1385

auto

1386

1387

explain

1388

select auto from t1 where

1389

("aaaa" not between string and string) and

1390

("aaaa" not between vstring and vstring) and

1391

(0xAAAA not between bin and bin) and

1392

(0xAAAA not between vbin and vbin) and

1393

(-1 not between tiny and tiny) and

1394

(-1 not between short and short) and

1395

(-1 not between medium and medium) and

1396

(-1 not between long_int and long_int) and

1397

(-1 not between longlong and longlong) and

1398

(1 not between utiny and utiny) and

1399

(1 not between ushort and ushort) and

1400

(1 not between umedium and umedium) and

1401

(1 not between ulong and ulong) and

1402

(1 not between ulonglong and ulonglong) and

1403

/* (b'001' not between bits and bits) and */

1404

('one' not between options and options) and

1405

('one' not between flags and flags) and

1406

('1901-01-01' not between date_field and date_field) and

1407

('1901' not between year_field and year_field) and

1408

('01:01:01' not between time_field and time_field) and

1409

('1901-01-01 01:01:01' not between date_time and date_time)

1410

order by auto;

1411

id select_type table type possible_keys key key_len ref rows Extra

1412

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

1413

select auto from t1 where

1414

("aaaa" not between string and string) and

1415

("aaaa" not between vstring and vstring) and

1416

(0xAAAA not between bin and bin) and

1417

(0xAAAA not between vbin and vbin) and

1418

(-1 not between tiny and tiny) and

1419

(-1 not between short and short) and

1420

(-1 not between medium and medium) and

1421

(-1 not between long_int and long_int) and

1422

(-1 not between longlong and longlong) and

1423

(1 not between utiny and utiny) and

1424

(1 not between ushort and ushort) and

1425

(1 not between umedium and umedium) and

1426

(1 not between ulong and ulong) and

1427

(1 not between ulonglong and ulonglong) and

1428

/* (b'001' not between bits and bits) and */

1429

('one' not between options and options) and

1430

('one' not between flags and flags) and

1431

('1901-01-01' not between date_field and date_field) and

1432

('1901' not between year_field and year_field) and

1433

('01:01:01' not between time_field and time_field) and

1434

('1901-01-01 01:01:01' not between date_time and date_time)

1435

order by auto;

1436

auto

1437

1438

1439

1440

explain

1441

select auto from t1 where

1442

string in("aaaa","cccc") and

1443

vstring in("aaaa","cccc") and

1444

bin in(0xAAAA,0xCCCC) and

1445

vbin in(0xAAAA,0xCCCC) and

1446

tiny in(-1,-3) and

1447

short in(-1,-3) and

1448

medium in(-1,-3) and

1449

long_int in(-1,-3) and

1450

longlong in(-1,-3) and

1451

utiny in(1,3) and

1452

ushort in(1,3) and

1453

umedium in(1,3) and

1454

ulong in(1,3) and

1455

ulonglong in(1,3) and

1456

/* bits in(b'001',b'011') and */

1457

options in('one','three') and

1458

flags in('one','one,two,three') and

1459

date_field in('1901-01-01','1903-03-03') and

1460

year_field in('1901','1903') and

1461

time_field in('01:01:01','03:03:03') and

1462

date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03')

1463

order by auto;

1464

id select_type table type possible_keys key key_len ref rows Extra

1465

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

1466

select auto from t1 where

1467

string in("aaaa","cccc") and

1468

vstring in("aaaa","cccc") and

1469

bin in(0xAAAA,0xCCCC) and

1470

vbin in(0xAAAA,0xCCCC) and

1471

tiny in(-1,-3) and

1472

short in(-1,-3) and

1473

medium in(-1,-3) and

1474

long_int in(-1,-3) and

1475

longlong in(-1,-3) and

1476

utiny in(1,3) and

1477

ushort in(1,3) and

1478

umedium in(1,3) and

1479

ulong in(1,3) and

1480

ulonglong in(1,3) and

1481

/* bits in(b'001',b'011') and */

1482

options in('one','three') and

1483

flags in('one','one,two,three') and

1484

date_field in('1901-01-01','1903-03-03') and

1485

year_field in('1901','1903') and

1486

time_field in('01:01:01','03:03:03') and

1487

date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03')

1488

order by auto;

1489

auto

1490

1491

1492

explain

1493

select auto from t1 where

1494

"aaaa" in(string) and

1495

"aaaa" in(vstring) and

1496

0xAAAA in(bin) and

1497

0xAAAA in(vbin) and

1498

(-1 in(tiny)) and

1499

(-1 in (short)) and

1500

(-1 in(medium)) and

1501

(-1 in(long_int)) and

1502

(-1 in(longlong)) and

1503

1 in(utiny) and

1504

1 in(ushort) and

1505

1 in(umedium) and

1506

1 in(ulong) and

1507

1 in(ulonglong) and

1508

/* b'001' in(bits) and */

1509

'one' in(options) and

1510

'one' in(flags) and

1511

'1901-01-01' in(date_field) and

1512

'1901' in(year_field) and

1513

'01:01:01' in(time_field) and

1514

'1901-01-01 01:01:01' in(date_time)

1515

order by auto;

1516

id select_type table type possible_keys key key_len ref rows Extra

1517

1 SIMPLE t1 ref medium_index medium_index 3 const # Using where with pushed condition; Using filesort

1518

select auto from t1 where

1519

"aaaa" in(string) and

1520

"aaaa" in(vstring) and

1521

0xAAAA in(bin) and

1522

0xAAAA in(vbin) and

1523

(-1 in(tiny)) and

1524

(-1 in (short)) and

1525

(-1 in(medium)) and

1526

(-1 in(long_int)) and

1527

(-1 in(longlong)) and

1528

1 in(utiny) and

1529

1 in(ushort) and

1530

1 in(umedium) and

1531

1 in(ulong) and

1532

1 in(ulonglong) and

1533

/* b'001' in(bits) and */

1534

'one' in(options) and

1535

'one' in(flags) and

1536

'1901-01-01' in(date_field) and

1537

'1901' in(year_field) and

1538

'01:01:01' in(time_field) and

1539

'1901-01-01 01:01:01' in(date_time)

1540

order by auto;

1541

auto

1542

1543

explain

1544

select auto from t1 where

1545

string not in("aaaa","cccc") and

1546

vstring not in("aaaa","cccc") and

1547

bin not in(0xAAAA,0xCCCC) and

1548

vbin not in(0xAAAA,0xCCCC) and

1549

tiny not in(-1,-3) and

1550

short not in(-1,-3) and

1551

medium not in(-1,-3) and

1552

long_int not in(-1,-3) and

1553

longlong not in(-1,-3) and

1554

utiny not in(1,3) and

1555

ushort not in(1,3) and

1556

umedium not in(1,3) and

1557

ulong not in(1,3) and

1558

ulonglong not in(1,3) and

1559

/* bits not in(b'001',b'011') and */

1560

options not in('one','three') and

1561

flags not in('one','one,two,three') and

1562

date_field not in('1901-01-01','1903-03-03') and

1563

year_field not in('1901','1903') and

1564

time_field not in('01:01:01','03:03:03') and

1565

date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03')

1566

order by auto;

1567

id select_type table type possible_keys key key_len ref rows Extra

1568

1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort

1569

select auto from t1 where

1570

string not in("aaaa","cccc") and

1571

vstring not in("aaaa","cccc") and

1572

bin not in(0xAAAA,0xCCCC) and

1573

vbin not in(0xAAAA,0xCCCC) and

1574

tiny not in(-1,-3) and

1575

short not in(-1,-3) and

1576

medium not in(-1,-3) and

1577

long_int not in(-1,-3) and

1578

longlong not in(-1,-3) and

1579

utiny not in(1,3) and

1580

ushort not in(1,3) and

1581

umedium not in(1,3) and

1582

ulong not in(1,3) and

1583

ulonglong not in(1,3) and

1584

/* bits not in(b'001',b'011') and */

1585

options not in('one','three') and

1586

flags not in('one','one,two,three') and

1587

date_field not in('1901-01-01','1903-03-03') and

1588

year_field not in('1901','1903') and

1589

time_field not in('01:01:01','03:03:03') and

1590

date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03')

1591

order by auto;

1592

auto

1593

1594

1595

explain

1596

select auto from t1 where

1597

"aaaa" not in(string) and

1598

"aaaa" not in(vstring) and

1599

0xAAAA not in(bin) and

1600

0xAAAA not in(vbin) and

1601

(-1 not in(tiny)) and

1602

(-1 not in(short)) and

1603

(-1 not in(medium)) and

1604

(-1 not in(long_int)) and

1605

(-1 not in(longlong)) and

1606

1 not in(utiny) and

1607

1 not in(ushort) and

1608

1 not in(umedium) and

1609

1 not in(ulong) and

1610

1 not in(ulonglong) and

1611

/* b'001' not in(bits) and */

1612

'one' not in(options) and

1613

'one' not in(flags) and

1614

'1901-01-01' not in(date_field) and

1615

'1901' not in(year_field) and

1616

'01:01:01' not in(time_field) and

1617

'1901-01-01 01:01:01' not in(date_time)

1618

order by auto;

1619

id select_type table type possible_keys key key_len ref rows Extra

1620

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

1621

select auto from t1 where

1622

"aaaa" not in(string) and

1623

"aaaa" not in(vstring) and

1624

0xAAAA not in(bin) and

1625

0xAAAA not in(vbin) and

1626

(-1 not in(tiny)) and

1627

(-1 not in(short)) and

1628

(-1 not in(medium)) and

1629

(-1 not in(long_int)) and

1630

(-1 not in(longlong)) and

1631

1 not in(utiny) and

1632

1 not in(ushort) and

1633

1 not in(umedium) and

1634

1 not in(ulong) and

1635

1 not in(ulonglong) and

1636

/* b'001' not in(bits) and */

1637

'one' not in(options) and

1638

'one' not in(flags) and

1639

'1901-01-01' not in(date_field) and

1640

'1901' not in(year_field) and

1641

'01:01:01' not in(time_field) and

1642

'1901-01-01 01:01:01' not in(date_time)

1643

order by auto;

1644

auto

1645

1646

1647

1648

update t1

1649

set medium = 17

1650

where

1651

string = "aaaa" and

1652

vstring = "aaaa" and

1653

bin = 0xAAAA and

1654

vbin = 0xAAAA and

1655

tiny = -1 and

1656

short = -1 and

1657

medium = -1 and

1658

long_int = -1 and

1659

longlong = -1 and

1660

real_float > 1.0 and real_float < 2.0 and

1661

real_double > 1.0 and real_double < 2.0 and

1662

real_decimal > 1.0 and real_decimal < 2.0 and

1663

utiny = 1 and

1664

ushort = 1 and

1665

umedium = 1 and

1666

ulong = 1 and

1667

ulonglong = 1 and

1668

/* bits = b'001' and */

1669

options = 'one' and

1670

flags = 'one' and

1671

date_field = '1901-01-01' and

1672

year_field = '1901' and

1673

time_field = '01:01:01' and

1674

date_time = '1901-01-01 01:01:01';

1675

delete from t1

1676

where

1677

string = "aaaa" and

1678

vstring = "aaaa" and

1679

bin = 0xAAAA and

1680

vbin = 0xAAAA and

1681

tiny = -1 and

1682

short = -1 and

1683

medium = 17 and

1684

long_int = -1 and

1685

longlong = -1 and

1686

real_float > 1.0 and real_float < 2.0 and

1687

real_double > 1.0 and real_double < 2.0 and

1688

real_decimal > 1.0 and real_decimal < 2.0 and

1689

utiny = 1 and

1690

ushort = 1 and

1691

umedium = 1 and

1692

ulong = 1 and

1693

ulonglong = 1 and

1694

/* bits = b'001' and */

1695

options = 'one' and

1696

flags = 'one' and

1697

date_field = '1901-01-01' and

1698

year_field = '1901' and

1699

time_field = '01:01:01' and

1700

date_time = '1901-01-01 01:01:01';

1701

select count(*) from t1;

1702

count(*)

1703

1704

explain

1705

select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;

1706

id select_type table type possible_keys key key_len ref rows Extra

1707

1 SIMPLE t2 ALL PRIMARY NULL NULL NULL # Using where with pushed condition; Using filesort

1708

select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;

1709

pk1 attr1 attr2 attr3

1710

2 2 NULL NULL

1711

3 3 3 d

1712

explain

1713

select * from t2 where attr3 is not null and attr1 > 2 order by pk1;

1714

id select_type table type possible_keys key key_len ref rows Extra

1715

1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

1716

select * from t2 where attr3 is not null and attr1 > 2 order by pk1;

1717

pk1 attr1 attr2 attr3

1718

3 3 3 d

1719

4 4 4 e

1720

5 5 5 f

1721

explain

1722

select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;

1723

id select_type table type possible_keys key key_len ref rows Extra

1724

1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where with pushed condition; Using filesort

1725

select * from t3 where attr2 > 9223372036854775803 and attr3 != 3 order by pk1;

1726

pk1 attr1 attr2 attr3 attr4

1727

2 2 9223372036854775804 2 c

1728

4 4 9223372036854775806 4 e

1729

5 5 9223372036854775807 5 f

1730

explain

1731

select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;

1732

id select_type table type possible_keys key key_len ref rows Extra

1733

1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where with pushed condition; Using temporary; Using filesort

1734

1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where with pushed condition; Using join buffer

1735

select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;

1736

pk1 attr1 attr2 attr3 pk1 attr1 attr2 attr3 attr4

1737

0 0 0 a 0 0 0 0 a

1738

explain

1739

select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;

1740

id select_type table type possible_keys key key_len ref rows Extra

1741

1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using filesort

1742

select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;

1743

pk1 attr1 attr2 attr3 attr4

1744

2 2 9223372036854775804 2 c

1745

4 4 9223372036854775806 4 e

1746

explain

1747

select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;

1748

id select_type table type possible_keys key key_len ref rows Extra

1749

1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using temporary; Using filesort

1750

1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where; Using join buffer

1751

select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;

1752

pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4

1753

2 2 9223372036854775804 2 c 2 2 9223372036854775804 2 c

1754

3 3 9223372036854775805 3 d 3 3 9223372036854775805 3 d

1755

4 4 9223372036854775806 4 e 4 4 9223372036854775806 4 e

1756

explain

1757

select auto from t1 where string = "aaaa" collate latin1_general_ci order by auto;

1758

id select_type table type possible_keys key key_len ref rows Extra

1759

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where; Using filesort

1760

explain

1761

select * from t2 where (attr1 < 2) = (attr2 < 2) order by pk1;

1762

id select_type table type possible_keys key key_len ref rows Extra

1763

1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where; Using filesort

1764

explain

1765

select * from t3 left join t4 on t4.attr2 = t3.attr2 where t4.attr1 > 1 and t4.attr3 < 5 or t4.attr1 is null order by t4.pk1;

1766

id select_type table type possible_keys key key_len ref rows Extra

1767

1 SIMPLE t3 ALL NULL NULL NULL NULL # Using temporary; Using filesort

1768

1 SIMPLE t4 ALL NULL NULL NULL NULL # Using where

1769

create table t5 (a int primary key auto_increment, b tinytext not null)

1770

engine = ndb;

1771

insert into t5 (b) values ('jonas'), ('jensing'), ('johan');

1772

set engine_condition_pushdown = off;

1773

select * from t5 where b like '%jo%' order by a;

1774

a b

1775

1 jonas

1776

3 johan

1777

set engine_condition_pushdown = on;

1778

explain select * from t5 where b like '%jo%';

1779

id select_type table type possible_keys key key_len ref rows Extra

1780

1 SIMPLE t5 ALL NULL NULL NULL NULL # Using where

1781

select * from t5 where b like '%jo%' order by a;

1782

a b

1783

1 jonas

1784

3 johan

1785

set engine_condition_pushdown = off;

1786

select auto from t1 where date_time like '1902-02-02 %' order by auto;

1787

auto

1788

1789

select auto from t1 where date_time not like '1902-02-02 %' order by auto;

1790

auto

1791

1792

1793

set engine_condition_pushdown = on;

1794

explain select auto from t1 where date_time like '1902-02-02 %';

1795

id select_type table type possible_keys key key_len ref rows Extra

1796

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where

1797

select auto from t1 where date_time like '1902-02-02 %' order by auto;

1798

auto

1799

1800

explain select auto from t1 where date_time not like '1902-02-02 %';

1801

id select_type table type possible_keys key key_len ref rows Extra

1802

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where

1803

select auto from t1 where date_time not like '1902-02-02 %' order by auto;

1804

auto

1805

1806

1807

drop table t1;

1808

create table t1 (a int, b varchar(3), primary key using hash(a))

1809

engine=ndb;

1810

insert into t1 values (1,'a'), (2,'ab'), (3,'abc');

1811

set engine_condition_pushdown = off;

1812

select * from t1 where b like 'ab';

1813

a b

1814

2 ab

1815

select * from t1 where b like 'ab' or b like 'ab';

1816

a b

1817

2 ab

1818

select * from t1 where b like 'abc';

1819

a b

1820

3 abc

1821

select * from t1 where b like 'abc' or b like 'abc';

1822

a b

1823

3 abc

1824

set engine_condition_pushdown = on;

1825

select * from t1 where b like 'ab';

1826

a b

1827

2 ab

1828

select * from t1 where b like 'ab' or b like 'ab';

1829

a b

1830

2 ab

1831

select * from t1 where b like 'abc';

1832

a b

1833

3 abc

1834

select * from t1 where b like 'abc' or b like 'abc';

1835

a b

1836

3 abc

1837

drop table t1;

1838

create table t1 (a int, b char(3), primary key using hash(a))

1839

engine=ndb;

1840

insert into t1 values (1,'a'), (2,'ab'), (3,'abc');

1841

set engine_condition_pushdown = off;

1842

select * from t1 where b like 'ab';

1843

a b

1844

2 ab

1845

select * from t1 where b like 'ab' or b like 'ab';

1846

a b

1847

2 ab

1848

select * from t1 where b like 'abc';

1849

a b

1850

3 abc

1851

select * from t1 where b like 'abc' or b like 'abc';

1852

a b

1853

3 abc

1854

set engine_condition_pushdown = on;

1855

select * from t1 where b like 'ab';

1856

a b

1857

2 ab

1858

select * from t1 where b like 'ab' or b like 'ab';

1859

a b

1860

2 ab

1861

select * from t1 where b like 'abc';

1862

a b

1863

3 abc

1864

select * from t1 where b like 'abc' or b like 'abc';

1865

a b

1866

3 abc

1867

drop table t1;

1868

create table t1 ( fname varchar(255), lname varchar(255) )

1869

engine=ndbcluster;

1870

insert into t1 values ("Young","Foo");

1871

set engine_condition_pushdown = 0;

1872

SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');

1873

fname lname

1874

Young Foo

1875

set engine_condition_pushdown = 1;

1876

SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');

1877

fname lname

1878

Young Foo

1879

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

1880

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

1881

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

1882

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

1883

set engine_condition_pushdown = 0;

1884

SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');

1885

fname lname

1886

Young Foo

1887

set engine_condition_pushdown = 1;

1888

SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');

1889

fname lname

1890

Young Foo

1891

drop table t1;

1892

create table t1 (a int, b int, c int, d int, primary key using hash(a))

1893

engine=ndbcluster;

1894

insert into t1 values (10,1,100,0+0x1111);

1895

insert into t1 values (20,2,200,0+0x2222);

1896

insert into t1 values (30,3,300,0+0x3333);

1897

insert into t1 values (40,4,400,0+0x4444);

1898

insert into t1 values (50,5,500,0+0x5555);

1899

set engine_condition_pushdown = on;

1900

select a,b,d from t1

1901

where b in (0,1,2,5)

1902

order by b;

1903

a b d

1904

10 1 4369

1905

20 2 8738

1906

50 5 21845

1907

-- big filter just below limit

1908

a b d

1909

10 1 4369

1910

20 2 8738

1911

50 5 21845

1912

-- big filter just above limit

1913

a b d

1914

10 1 4369

1915

20 2 8738

1916

50 5 21845

1917

Warnings:

1918

Warning 4294 Scan filter is too large, discarded

1919

set engine_condition_pushdown = @old_ecpd;

1920

DROP TABLE t1,t2,t3,t4,t5;

Older »