~ubuntu-branches/ubuntu/trusty/mariadb-5.5/trusty-proposed

Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`))

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

FROM t2

LEFT JOIN

(t3, t4)

ON t2.b=t4.b

WHERE t3.a=1 OR t3.c IS NULL;

100

a b a b a b

101

4 2 1 2 3 2

102

4 2 1 2 4 2

103

3 3 NULL NULL NULL NULL

104

5 3 NULL NULL NULL NULL

105

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

106

FROM t2

107

LEFT JOIN

108

(t3, t4)

109

ON t2.b=t4.b

110

WHERE t3.a>1 OR t3.c IS NULL;

111

a b a b a b

112

4 2 2 2 3 2

113

4 2 2 2 4 2

114

3 3 NULL NULL NULL NULL

115

5 3 NULL NULL NULL NULL

116

SELECT t5.a,t5.b

117

FROM t5;

118

a b

119

3 1

120

2 2

121

3 3

122

SELECT t3.a,t3.b,t4.a,t4.b,t5.a,t5.b

123

FROM t3,t4,t5;

124

a b a b a b

125

1 2 3 2 3 1

126

2 2 3 2 3 1

127

1 2 4 2 3 1

128

2 2 4 2 3 1

129

1 2 3 2 2 2

130

2 2 3 2 2 2

131

1 2 4 2 2 2

132

2 2 4 2 2 2

133

1 2 3 2 3 3

134

2 2 3 2 3 3

135

1 2 4 2 3 3

136

2 2 4 2 3 3

137

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b

138

FROM t2

139

LEFT JOIN

140

(t3, t4, t5)

141

ON t2.b=t4.b;

142

a b a b a b a b

143

4 2 1 2 3 2 3 1

144

4 2 2 2 3 2 3 1

145

4 2 1 2 4 2 3 1

146

4 2 2 2 4 2 3 1

147

4 2 1 2 3 2 2 2

148

4 2 2 2 3 2 2 2

149

4 2 1 2 4 2 2 2

150

4 2 2 2 4 2 2 2

151

4 2 1 2 3 2 3 3

152

4 2 2 2 3 2 3 3

153

4 2 1 2 4 2 3 3

154

4 2 2 2 4 2 3 3

155

3 3 NULL NULL NULL NULL NULL NULL

156

5 3 NULL NULL NULL NULL NULL NULL

157

EXPLAIN EXTENDED

158

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b

159

FROM t2

160

LEFT JOIN

161

(t3, t4, t5)

162

ON t2.b=t4.b

163

WHERE t3.a>1 OR t3.c IS NULL;

164

id select_type table type possible_keys key key_len ref rows filtered Extra

165

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00

166

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)

167

1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

168

1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join)

169

Warnings:

170

Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`))

171

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b

172

FROM t2

173

LEFT JOIN

174

(t3, t4, t5)

175

ON t2.b=t4.b

176

WHERE t3.a>1 OR t3.c IS NULL;

177

a b a b a b a b

178

4 2 2 2 3 2 3 1

179

4 2 2 2 4 2 3 1

180

4 2 2 2 3 2 2 2

181

4 2 2 2 4 2 2 2

182

4 2 2 2 3 2 3 3

183

4 2 2 2 4 2 3 3

184

3 3 NULL NULL NULL NULL NULL NULL

185

5 3 NULL NULL NULL NULL NULL NULL

186

EXPLAIN EXTENDED

187

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b

188

FROM t2

189

LEFT JOIN

190

(t3, t4, t5)

191

ON t2.b=t4.b

192

WHERE (t3.a>1 OR t3.c IS NULL) AND

193

(t5.a<3 OR t5.c IS NULL);

194

id select_type table type possible_keys key key_len ref rows filtered Extra

195

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00

196

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)

197

1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

198

1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

199

Warnings:

200

201

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b

202

FROM t2

203

LEFT JOIN

204

(t3, t4, t5)

205

ON t2.b=t4.b

206

WHERE (t3.a>1 OR t3.c IS NULL) AND

207

(t5.a<3 OR t5.c IS NULL);

208

a b a b a b a b

209

4 2 2 2 3 2 2 2

210

4 2 2 2 4 2 2 2

211

3 3 NULL NULL NULL NULL NULL NULL

212

5 3 NULL NULL NULL NULL NULL NULL

213

SELECT t6.a,t6.b

214

FROM t6;

215

a b

216

3 2

217

6 2

218

6 1

219

SELECT t7.a,t7.b

220

FROM t7;

221

a b

222

1 1

223

2 2

224

SELECT t6.a,t6.b,t7.a,t7.b

225

FROM t6,t7;

226

a b a b

227

3 2 1 1

228

3 2 2 2

229

6 2 1 1

230

6 2 2 2

231

6 1 1 1

232

6 1 2 2

233

SELECT t8.a,t8.b

234

FROM t8;

235

a b

236

0 2

237

1 2

238

EXPLAIN EXTENDED

239

SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

240

FROM (t6, t7)

241

LEFT JOIN

242

243

ON t7.b=t8.b AND t6.b < 10;

244

id select_type table type possible_keys key key_len ref rows filtered Extra

245

1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00

246

1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)

247

1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t7.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

248

Warnings:

249

Note 1003 select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t7`.`b`) and (`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` is not null))) where 1

250

SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

251

FROM (t6, t7)

252

LEFT JOIN

253

254

ON t7.b=t8.b AND t6.b < 10;

255

a b a b a b

256

3 2 2 2 0 2

257

6 2 2 2 0 2

258

6 1 2 2 0 2

259

3 2 2 2 1 2

260

6 2 2 2 1 2

261

6 1 2 2 1 2

262

3 2 1 1 NULL NULL

263

6 2 1 1 NULL NULL

264

6 1 1 1 NULL NULL

265

SELECT t5.a,t5.b

266

FROM t5;

267

a b

268

3 1

269

2 2

270

3 3

271

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

272

FROM t5

273

LEFT JOIN

274

(

275

(t6, t7)

276

LEFT JOIN

277

278

ON t7.b=t8.b AND t6.b < 10

279

)

280

ON t6.b >= 2 AND t5.b=t7.b;

281

a b a b a b a b

282

2 2 3 2 2 2 0 2

283

2 2 6 2 2 2 0 2

284

2 2 3 2 2 2 1 2

285

2 2 6 2 2 2 1 2

286

3 1 3 2 1 1 NULL NULL

287

3 1 6 2 1 1 NULL NULL

288

3 3 NULL NULL NULL NULL NULL NULL

289

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

290

FROM t5

291

LEFT JOIN

292

(

293

(t6, t7)

294

LEFT JOIN

295

296

ON t7.b=t8.b AND t6.b < 10

297

)

298

ON t6.b >= 2 AND t5.b=t7.b AND

299

(t8.a < 1 OR t8.c IS NULL);

300

a b a b a b a b

301

2 2 3 2 2 2 0 2

302

2 2 6 2 2 2 0 2

303

3 1 3 2 1 1 NULL NULL

304

3 1 6 2 1 1 NULL NULL

305

3 3 NULL NULL NULL NULL NULL NULL

306

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

307

FROM t2

308

LEFT JOIN

309

(t3, t4)

310

ON t3.a=1 AND t2.b=t4.b;

311

a b a b a b

312

4 2 1 2 3 2

313

4 2 1 2 4 2

314

3 3 NULL NULL NULL NULL

315

5 3 NULL NULL NULL NULL

316

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

317

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

318

FROM t2

319

LEFT JOIN

320

(t3, t4)

321

ON t3.a=1 AND t2.b=t4.b,

322

323

LEFT JOIN

324

(

325

(t6, t7)

326

LEFT JOIN

327

328

ON t7.b=t8.b AND t6.b < 10

329

)

330

ON t6.b >= 2 AND t5.b=t7.b;

331

a b a b a b a b a b a b a b

332

4 2 1 2 3 2 2 2 3 2 2 2 0 2

333

4 2 1 2 4 2 2 2 3 2 2 2 0 2

334

4 2 1 2 3 2 2 2 6 2 2 2 0 2

335

4 2 1 2 4 2 2 2 6 2 2 2 0 2

336

4 2 1 2 3 2 2 2 3 2 2 2 1 2

337

4 2 1 2 4 2 2 2 3 2 2 2 1 2

338

4 2 1 2 3 2 2 2 6 2 2 2 1 2

339

4 2 1 2 4 2 2 2 6 2 2 2 1 2

340

4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL

341

4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL

342

4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL

343

4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL

344

4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL

345

4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL

346

3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

347

5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

348

3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2

349

5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2

350

3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

351

5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

352

3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2

353

5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2

354

3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

355

5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

356

3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL

357

5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL

358

3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

359

5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

360

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

361

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

362

FROM t2

363

LEFT JOIN

364

(t3, t4)

365

ON t3.a=1 AND t2.b=t4.b,

366

367

LEFT JOIN

368

(

369

(t6, t7)

370

LEFT JOIN

371

372

ON t7.b=t8.b AND t6.b < 10

373

)

374

ON t6.b >= 2 AND t5.b=t7.b

375

WHERE t2.a > 3 AND

376

(t6.a < 6 OR t6.c IS NULL);

377

a b a b a b a b a b a b a b

378

4 2 1 2 3 2 2 2 3 2 2 2 0 2

379

4 2 1 2 4 2 2 2 3 2 2 2 0 2

380

4 2 1 2 3 2 2 2 3 2 2 2 1 2

381

4 2 1 2 4 2 2 2 3 2 2 2 1 2

382

4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL

383

4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL

384

4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL

385

4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL

386

5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

387

5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

388

5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

389

5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

390

SELECT t1.a,t1.b

391

FROM t1;

392

a b

393

1 3

394

2 2

395

3 2

396

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

397

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

398

FROM t1

399

LEFT JOIN

400

(

401

402

LEFT JOIN

403

(t3, t4)

404

ON t3.a=1 AND t2.b=t4.b,

405

406

LEFT JOIN

407

(

408

(t6, t7)

409

LEFT JOIN

410

411

ON t7.b=t8.b AND t6.b < 10

412

)

413

ON t6.b >= 2 AND t5.b=t7.b

414

)

415

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

416

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

417

(t1.a != 2);

418

a b a b a b a b a b a b a b a b

419

3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2

420

3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2

421

3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2

422

3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2

423

3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2

424

3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2

425

3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2

426

3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2

427

1 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL

428

3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL

429

1 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL

430

3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL

431

1 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL

432

3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL

433

1 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL

434

3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL

435

1 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL

436

3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL

437

1 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL

438

3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL

439

1 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

440

3 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

441

1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

442

3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

443

1 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2

444

3 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2

445

1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2

446

3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2

447

1 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

448

3 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

449

1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

450

3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

451

1 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2

452

3 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2

453

1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2

454

3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2

455

1 3 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

456

3 2 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

457

1 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

458

3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

459

1 3 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL

460

3 2 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL

461

1 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL

462

3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL

463

1 3 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

464

3 2 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

465

1 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

466

3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

467

2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

468

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

469

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

470

FROM t1

471

LEFT JOIN

472

(

473

474

LEFT JOIN

475

(t3, t4)

476

ON t3.a=1 AND t2.b=t4.b,

477

478

LEFT JOIN

479

(

480

(t6, t7)

481

LEFT JOIN

482

483

ON t7.b=t8.b AND t6.b < 10

484

)

485

ON t6.b >= 2 AND t5.b=t7.b

486

)

487

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

488

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

489

(t1.a != 2)

490

WHERE (t2.a >= 4 OR t2.c IS NULL);

491

a b a b a b a b a b a b a b a b

492

3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2

493

3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2

494

3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2

495

3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2

496

3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2

497

3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2

498

3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2

499

3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2

500

1 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL

501

3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL

502

1 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL

503

3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL

504

1 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL

505

3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL

506

1 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL

507

3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL

508

1 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL

509

3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL

510

1 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL

511

3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL

512

1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

513

3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

514

1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2

515

3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2

516

1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

517

3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

518

1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2

519

3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2

520

1 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

521

3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

522

1 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL

523

3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL

524

1 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

525

3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

526

2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

527

SELECT t0.a,t0.b

528

FROM t0;

529

a b

530

1 1

531

1 2

532

2 2

533

EXPLAIN EXTENDED

534

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

535

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

536

FROM t0,t1

537

LEFT JOIN

538

(

539

540

LEFT JOIN

541

(t3, t4)

542

ON t3.a=1 AND t2.b=t4.b,

543

544

LEFT JOIN

545

(

546

(t6, t7)

547

LEFT JOIN

548

549

ON t7.b=t8.b AND t6.b < 10

550

)

551

ON t6.b >= 2 AND t5.b=t7.b

552

)

553

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

554

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

555

(t1.a != 2)

556

WHERE t0.a=1 AND

557

t0.b=t1.b AND

558

(t2.a >= 4 OR t2.c IS NULL);

559

id select_type table type possible_keys key key_len ref rows filtered Extra

560

1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where

561

1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join)

562

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

563

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)

564

1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

565

1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join)

566

1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

567

1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

568

1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

569

Warnings:

570

571

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

572

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

573

FROM t0,t1

574

LEFT JOIN

575

(

576

577

LEFT JOIN

578

(t3, t4)

579

ON t3.a=1 AND t2.b=t4.b,

580

581

LEFT JOIN

582

(

583

(t6, t7)

584

LEFT JOIN

585

586

ON t7.b=t8.b AND t6.b < 10

587

)

588

ON t6.b >= 2 AND t5.b=t7.b

589

)

590

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

591

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

592

(t1.a != 2)

593

WHERE t0.a=1 AND

594

t0.b=t1.b AND

595

(t2.a >= 4 OR t2.c IS NULL);

596

a b a b a b a b a b a b a b a b a b

597

1 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2

598

1 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2

599

1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2

600

1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2

601

1 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2

602

1 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2

603

1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2

604

1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2

605

1 2 3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL

606

1 2 3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL

607

1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL

608

1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL

609

1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL

610

1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL

611

1 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2

612

1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2

613

1 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2

614

1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2

615

1 2 3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL

616

1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL

617

1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL

618

1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

619

EXPLAIN EXTENDED

620

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

621

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b

622

FROM t0,t1

623

LEFT JOIN

624

(

625

626

LEFT JOIN

627

(t3, t4)

628

ON t3.a=1 AND t2.b=t4.b,

629

630

LEFT JOIN

631

(

632

(t6, t7)

633

LEFT JOIN

634

635

ON t7.b=t8.b AND t6.b < 10

636

)

637

ON t6.b >= 2 AND t5.b=t7.b

638

)

639

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

640

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

641

(t1.a != 2),

642

643

WHERE t0.a=1 AND

644

t0.b=t1.b AND

645

(t2.a >= 4 OR t2.c IS NULL) AND

646

(t3.a < 5 OR t3.c IS NULL) AND

647

(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND

648

(t5.a >=2 OR t5.c IS NULL) AND

649

(t6.a >=4 OR t6.c IS NULL) AND

650

(t7.a <= 2 OR t7.c IS NULL) AND

651

(t8.a < 1 OR t8.c IS NULL) AND

652

(t8.b=t9.b OR t8.c IS NULL) AND

653

(t9.a=1);

654

id select_type table type possible_keys key key_len ref rows filtered Extra

655

1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where

656

1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join)

657

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

658

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)

659

1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

660

1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

661

1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

662

1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

663

1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

664

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

665

Warnings:

666

Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t0`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t9`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))

667

SELECT t9.a,t9.b

668

FROM t9;

669

a b

670

1 1

671

1 2

672

3 3

673

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

674

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b

675

FROM t0,t1

676

LEFT JOIN

677

(

678

679

LEFT JOIN

680

(t3, t4)

681

ON t3.a=1 AND t2.b=t4.b,

682

683

LEFT JOIN

684

(

685

(t6, t7)

686

LEFT JOIN

687

688

ON t7.b=t8.b AND t6.b < 10

689

)

690

ON t6.b >= 2 AND t5.b=t7.b

691

)

692

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

693

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

694

(t1.a != 2),

695

696

WHERE t0.a=1 AND

697

t0.b=t1.b AND

698

(t2.a >= 4 OR t2.c IS NULL) AND

699

(t3.a < 5 OR t3.c IS NULL) AND

700

(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND

701

(t5.a >=2 OR t5.c IS NULL) AND

702

(t6.a >=4 OR t6.c IS NULL) AND

703

(t7.a <= 2 OR t7.c IS NULL) AND

704

(t8.a < 1 OR t8.c IS NULL) AND

705

(t8.b=t9.b OR t8.c IS NULL) AND

706

(t9.a=1);

707

a b a b a b a b a b a b a b a b a b a b

708

1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2

709

1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2

710

1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1

711

1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1

712

1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2

713

1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2

714

1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1

715

1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1

716

1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2

717

1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2

718

1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2

719

1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1

720

1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2

721

1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1

722

1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2

723

1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1

724

1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2

725

SELECT t1.a,t1.b

726

FROM t1;

727

a b

728

1 3

729

2 2

730

3 2

731

SELECT t2.a,t2.b

732

FROM t2;

733

a b

734

3 3

735

4 2

736

5 3

737

SELECT t3.a,t3.b

738

FROM t3;

739

a b

740

1 2

741

2 2

742

SELECT t2.a,t2.b,t3.a,t3.b

743

FROM t2

744

LEFT JOIN

745

746

ON t2.b=t3.b;

747

a b a b

748

4 2 1 2

749

4 2 2 2

750

3 3 NULL NULL

751

5 3 NULL NULL

752

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b

753

FROM t1, t2

754

LEFT JOIN

755

756

ON t2.b=t3.b

757

WHERE t1.a <= 2;

758

a b a b a b

759

1 3 4 2 1 2

760

2 2 4 2 1 2

761

1 3 4 2 2 2

762

2 2 4 2 2 2

763

1 3 3 3 NULL NULL

764

2 2 3 3 NULL NULL

765

1 3 5 3 NULL NULL

766

2 2 5 3 NULL NULL

767

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b

768

FROM t1, t3

769

RIGHT JOIN

770

771

ON t2.b=t3.b

772

WHERE t1.a <= 2;

773

a b a b a b

774

1 3 4 2 1 2

775

2 2 4 2 1 2

776

1 3 4 2 2 2

777

2 2 4 2 2 2

778

1 3 3 3 NULL NULL

779

2 2 3 3 NULL NULL

780

1 3 5 3 NULL NULL

781

2 2 5 3 NULL NULL

782

SELECT t3.a,t3.b,t4.a,t4.b

783

FROM t3,t4;

784

a b a b

785

1 2 3 2

786

2 2 3 2

787

1 2 4 2

788

2 2 4 2

789

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

790

FROM t2

791

LEFT JOIN

792

(t3, t4)

793

ON t3.a=1 AND t2.b=t4.b;

794

a b a b a b

795

4 2 1 2 3 2

796

4 2 1 2 4 2

797

3 3 NULL NULL NULL NULL

798

5 3 NULL NULL NULL NULL

799

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

800

FROM t1, t2

801

LEFT JOIN

802

(t3, t4)

803

ON t3.a=1 AND t2.b=t4.b

804

WHERE t1.a <= 2;

805

a b a b a b a b

806

1 3 4 2 1 2 3 2

807

2 2 4 2 1 2 3 2

808

1 3 4 2 1 2 4 2

809

2 2 4 2 1 2 4 2

810

1 3 3 3 NULL NULL NULL NULL

811

2 2 3 3 NULL NULL NULL NULL

812

1 3 5 3 NULL NULL NULL NULL

813

2 2 5 3 NULL NULL NULL NULL

814

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

815

FROM t1, (t3, t4)

816

RIGHT JOIN

817

818

ON t3.a=1 AND t2.b=t4.b

819

WHERE t1.a <= 2;

820

a b a b a b a b

821

1 3 4 2 1 2 3 2

822

2 2 4 2 1 2 3 2

823

1 3 4 2 1 2 4 2

824

2 2 4 2 1 2 4 2

825

1 3 3 3 NULL NULL NULL NULL

826

2 2 3 3 NULL NULL NULL NULL

827

1 3 5 3 NULL NULL NULL NULL

828

2 2 5 3 NULL NULL NULL NULL

829

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

830

FROM t1, (t3, t4)

831

RIGHT JOIN

832

833

ON t3.a=1 AND t2.b=t4.b

834

WHERE t1.a <= 2;

835

a b a b a b a b

836

1 3 4 2 1 2 3 2

837

2 2 4 2 1 2 3 2

838

1 3 4 2 1 2 4 2

839

2 2 4 2 1 2 4 2

840

1 3 3 3 NULL NULL NULL NULL

841

2 2 3 3 NULL NULL NULL NULL

842

1 3 5 3 NULL NULL NULL NULL

843

2 2 5 3 NULL NULL NULL NULL

844

EXPLAIN EXTENDED

845

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

846

FROM t1, (t3, t4)

847

RIGHT JOIN

848

849

ON t3.a=1 AND t2.b=t4.b

850

WHERE t1.a <= 2;

851

id select_type table type possible_keys key key_len ref rows filtered Extra

852

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where

853

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)

854

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)

855

1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

856

Warnings:

857

Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where (`test`.`t1`.`a` <= 2)

858

INSERT INTO t2 VALUES (-1,9,0), (-3,10,0), (-2,8,0), (-4,11,0), (-5,15,0);

859

CREATE INDEX idx_b ON t2(b);

860

EXPLAIN EXTENDED

861

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

862

FROM (t3,t4)

863

LEFT JOIN

864

(t1,t2)

865

ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0;

866

id select_type table type possible_keys key key_len ref rows filtered Extra

867

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00

868

1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)

869

1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

870

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join)

871

Warnings:

872

Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`a` > 0) and (`test`.`t3`.`b` is not null))) where 1

873

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

874

FROM (t3,t4)

875

LEFT JOIN

876

(t1,t2)

877

ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0;

878

a b a b a b

879

4 2 1 2 3 2

880

4 2 1 2 4 2

881

4 2 1 2 3 2

882

4 2 1 2 4 2

883

4 2 1 2 3 2

884

4 2 1 2 4 2

885

NULL NULL 2 2 3 2

886

NULL NULL 2 2 4 2

887

EXPLAIN EXTENDED

888

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

889

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b

890

FROM t0,t1

891

LEFT JOIN

892

(

893

894

LEFT JOIN

895

(t3, t4)

896

ON t3.a=1 AND t2.b=t4.b AND t2.a>0,

897

898

LEFT JOIN

899

(

900

(t6, t7)

901

LEFT JOIN

902

903

ON t7.b=t8.b AND t6.b < 10

904

)

905

ON t6.b >= 2 AND t5.b=t7.b

906

)

907

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

908

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

909

(t1.a != 2),

910

911

WHERE t0.a=1 AND

912

t0.b=t1.b AND

913

(t2.a >= 4 OR t2.c IS NULL) AND

914

(t3.a < 5 OR t3.c IS NULL) AND

915

(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND

916

(t5.a >=2 OR t5.c IS NULL) AND

917

(t6.a >=4 OR t6.c IS NULL) AND

918

(t7.a <= 2 OR t7.c IS NULL) AND

919

(t8.a < 1 OR t8.c IS NULL) AND

920

(t8.b=t9.b OR t8.c IS NULL) AND

921

(t9.a=1);

922

id select_type table type possible_keys key key_len ref rows filtered Extra

923

1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where

924

1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join)

925

1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

926

1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

927

1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

928

1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

929

1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join)

930

1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

931

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)

932

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

933

Warnings:

934

Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`a` > 0) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t0`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t9`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))

935

INSERT INTO t4 VALUES (-3,12,0), (-4,13,0), (-1,11,0), (-3,11,0), (-5,15,0);

936

INSERT INTO t5 VALUES (-3,11,0), (-2,12,0), (-3,13,0), (-4,12,0);

937

CREATE INDEX idx_b ON t4(b);

938

CREATE INDEX idx_b ON t5(b);

939

EXPLAIN EXTENDED

940

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

941

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b

942

FROM t0,t1

943

LEFT JOIN

944

(

945

946

LEFT JOIN

947

(t3, t4)

948

ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0,

949

950

LEFT JOIN

951

(

952

(t6, t7)

953

LEFT JOIN

954

955

ON t7.b=t8.b AND t6.b < 10

956

)

957

ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0

958

)

959

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

960

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

961

(t1.a != 2),

962

963

WHERE t0.a=1 AND

964

t0.b=t1.b AND

965

(t2.a >= 4 OR t2.c IS NULL) AND

966

(t3.a < 5 OR t3.c IS NULL) AND

967

(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND

968

(t5.a >=2 OR t5.c IS NULL) AND

969

(t6.a >=4 OR t6.c IS NULL) AND

970

(t7.a <= 2 OR t7.c IS NULL) AND

971

(t8.a < 1 OR t8.c IS NULL) AND

972

(t8.b=t9.b OR t8.c IS NULL) AND

973

(t9.a=1);

974

id select_type table type possible_keys key key_len ref rows filtered Extra

975

1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where

976

1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join)

977

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

978

1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join)

979

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)

980

1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

981

1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join)

982

1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

983

1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

984

1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

985

Warnings:

986

Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`a` > 0) and (`test`.`t4`.`a` > 0) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2) and (`test`.`t5`.`a` > 0) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t0`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t9`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)))

987

INSERT INTO t8 VALUES (-3,12,0), (-1,14,0), (-5,15,0), (-1,11,0), (-4,13,0);

988

CREATE INDEX idx_b ON t8(b);

989

EXPLAIN EXTENDED

990

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

991

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b

992

FROM t0,t1

993

LEFT JOIN

994

(

995

996

LEFT JOIN

997

(t3, t4)

998

ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0,

999

1000

LEFT JOIN

1001

(

1002

(t6, t7)

1003

LEFT JOIN

1004

1005

ON t7.b=t8.b AND t6.b < 10 AND t8.a>=0

1006

)

1007

ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0

1008

)

1009

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

1010

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

1011

(t1.a != 2),

1012

1013

WHERE t0.a=1 AND

1014

t0.b=t1.b AND

1015

(t2.a >= 4 OR t2.c IS NULL) AND

1016

(t3.a < 5 OR t3.c IS NULL) AND

1017

(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND

1018

(t5.a >=2 OR t5.c IS NULL) AND

1019

(t6.a >=4 OR t6.c IS NULL) AND

1020

(t7.a <= 2 OR t7.c IS NULL) AND

1021

(t8.a < 1 OR t8.c IS NULL) AND

1022

(t8.b=t9.b OR t8.c IS NULL) AND

1023

(t9.a=1);

1024

id select_type table type possible_keys key key_len ref rows filtered Extra

1025

1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where

1026

1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join)

1027

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

1028

1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join)

1029

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)

1030

1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1031

1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join)

1032

1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

1033

1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

1034

1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1035

Warnings:

1036

Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`a` > 0) and (`test`.`t4`.`a` > 0) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10) and (`test`.`t8`.`a` >= 0) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2) and (`test`.`t5`.`a` > 0) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t0`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t9`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)))

1037

INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0);

1038

CREATE INDEX idx_b ON t1(b);

1039

CREATE INDEX idx_a ON t0(a);

1040

EXPLAIN EXTENDED

1041

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

1042

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b

1043

FROM t0,t1

1044

LEFT JOIN

1045

(

1046

1047

LEFT JOIN

1048

(t3, t4)

1049

ON t3.a=1 AND t2.b=t4.b,

1050

1051

LEFT JOIN

1052

(

1053

(t6, t7)

1054

LEFT JOIN

1055

1056

ON t7.b=t8.b AND t6.b < 10

1057

)

1058

ON t6.b >= 2 AND t5.b=t7.b

1059

)

1060

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

1061

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

1062

(t1.a != 2) AND t1.a>0,

1063

1064

WHERE t0.a=1 AND

1065

t0.b=t1.b AND

1066

(t2.a >= 4 OR t2.c IS NULL) AND

1067

(t3.a < 5 OR t3.c IS NULL) AND

1068

(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND

1069

(t5.a >=2 OR t5.c IS NULL) AND

1070

(t6.a >=4 OR t6.c IS NULL) AND

1071

(t7.a <= 2 OR t7.c IS NULL) AND

1072

(t8.a < 1 OR t8.c IS NULL) AND

1073

(t8.b=t9.b OR t8.c IS NULL) AND

1074

(t9.a=1);

1075

id select_type table type possible_keys key key_len ref rows filtered Extra

1076

1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where

1077

1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

1078

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

1079

1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join)

1080

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)

1081

1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1082

1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join)

1083

1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join)

1084

1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)

1085

1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1086

Warnings:

1087

Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2) and (`test`.`t1`.`a` > 0))) join `test`.`t9` where ((`test`.`t0`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t9`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)))

1088

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

1089

t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b

1090

FROM t0,t1

1091

LEFT JOIN

1092

(

1093

1094

LEFT JOIN

1095

(t3, t4)

1096

ON t3.a=1 AND t2.b=t4.b,

1097

1098

LEFT JOIN

1099

(

1100

(t6, t7)

1101

LEFT JOIN

1102

1103

ON t7.b=t8.b AND t6.b < 10

1104

)

1105

ON t6.b >= 2 AND t5.b=t7.b

1106

)

1107

ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND

1108

(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND

1109

(t1.a != 2) AND t1.a>0,

1110

1111

WHERE t0.a=1 AND

1112

t0.b=t1.b AND

1113

(t2.a >= 4 OR t2.c IS NULL) AND

1114

(t3.a < 5 OR t3.c IS NULL) AND

1115

(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND

1116

(t5.a >=2 OR t5.c IS NULL) AND

1117

(t6.a >=4 OR t6.c IS NULL) AND

1118

(t7.a <= 2 OR t7.c IS NULL) AND

1119

(t8.a < 1 OR t8.c IS NULL) AND

1120

(t8.b=t9.b OR t8.c IS NULL) AND

1121

(t9.a=1);

1122

a b a b a b a b a b a b a b a b a b a b

1123

1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2

1124

1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2

1125

1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1

1126

1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2

1127

1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1

1128

1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2

1129

1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1

1130

1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2

1131

1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1

1132

1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2

1133

1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2

1134

1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1

1135

1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2

1136

1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1

1137

1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2

1138

1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1

1139

1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2

1140

SELECT t2.a,t2.b

1141

FROM t2;

1142

a b

1143

3 3

1144

4 2

1145

5 3

1146

-1 9

1147

-3 10

1148

-2 8

1149

-4 11

1150

-5 15

1151

SELECT t3.a,t3.b

1152

FROM t3;

1153

a b

1154

1 2

1155

2 2

1156

SELECT t2.a,t2.b,t3.a,t3.b

1157

FROM t2 LEFT JOIN t3 ON t2.b=t3.b

1158

WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);

1159

a b a b

1160

4 2 1 2

1161

4 2 2 2

1162

5 3 NULL NULL

1163

SELECT t2.a,t2.b,t3.a,t3.b

1164

FROM t2 LEFT JOIN (t3) ON t2.b=t3.b

1165

WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);

1166

a b a b

1167

4 2 1 2

1168

4 2 2 2

1169

5 3 NULL NULL

1170

ALTER TABLE t3

1171

CHANGE COLUMN a a1 int,

1172

CHANGE COLUMN c c1 int;

1173

SELECT t2.a,t2.b,t3.a1,t3.b

1174

FROM t2 LEFT JOIN t3 ON t2.b=t3.b

1175

WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);

1176

a b a1 b

1177

4 2 1 2

1178

4 2 2 2

1179

5 3 NULL NULL

1180

SELECT t2.a,t2.b,t3.a1,t3.b

1181

FROM t2 NATURAL LEFT JOIN t3

1182

WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);

1183

a b a1 b

1184

4 2 1 2

1185

4 2 2 2

1186

5 3 NULL NULL

1187

DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;

1188

CREATE TABLE t1 (a int);

1189

CREATE TABLE t2 (a int);

1190

CREATE TABLE t3 (a int);

1191

INSERT INTO t1 VALUES (1);

1192

INSERT INTO t2 VALUES (2);

1193

INSERT INTO t3 VALUES (2);

1194

INSERT INTO t1 VALUES (2);

1195

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a;

1196

a a a

1197

2 2 2

1198

1 NULL NULL

1199

SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;

1200

a a a

1201

2 2 2

1202

1 NULL NULL

1203

DELETE FROM t1 WHERE a=2;

1204

SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;

1205

a a a

1206

1 NULL NULL

1207

DELETE FROM t2;

1208

SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;

1209

a a a

1210

1 NULL NULL

1211

DROP TABLE t1,t2,t3;

1212

CREATE TABLE t1(a int, key (a));

1213

CREATE TABLE t2(b int, key (b));

1214

CREATE TABLE t3(c int, key (c));

1215

INSERT INTO t1 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),

1216

(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);

1217

INSERT INTO t2 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),

1218

(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);

1219

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

1220

EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c;

1221

id select_type table type possible_keys key key_len ref rows Extra

1222

1 SIMPLE t1 index NULL a 5 NULL 21 Using index

1223

1 SIMPLE t3 index c c 5 NULL 6 Using where; Using index

1224

1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index

1225

EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;

1226

id select_type table type possible_keys key key_len ref rows Extra

1227

1 SIMPLE t1 index NULL a 5 NULL 21 Using index

1228

1 SIMPLE t3 index c c 5 NULL 6 Using where; Using index

1229

1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index

1230

SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;

1231

a b c

1232

NULL 0 0

1233

NULL 1 1

1234

NULL 2 2

1235

0 0 0

1236

0 1 1

1237

0 2 2

1238

1 0 0

1239

1 1 1

1240

1 2 2

1241

2 0 0

1242

2 1 1

1243

2 2 2

1244

3 0 0

1245

3 1 1

1246

3 2 2

1247

4 0 0

1248

4 1 1

1249

4 2 2

1250

5 0 0

1251

5 1 1

1252

5 2 2

1253

6 0 0

1254

6 1 1

1255

6 2 2

1256

7 0 0

1257

7 1 1

1258

7 2 2

1259

8 0 0

1260

8 1 1

1261

8 2 2

1262

9 0 0

1263

9 1 1

1264

9 2 2

1265

10 0 0

1266

10 1 1

1267

10 2 2

1268

11 0 0

1269

11 1 1

1270

11 2 2

1271

12 0 0

1272

12 1 1

1273

12 2 2

1274

13 0 0

1275

13 1 1

1276

13 2 2

1277

14 0 0

1278

14 1 1

1279

14 2 2

1280

15 0 0

1281

15 1 1

1282

15 2 2

1283

16 0 0

1284

16 1 1

1285

16 2 2

1286

17 0 0

1287

17 1 1

1288

17 2 2

1289

18 0 0

1290

18 1 1

1291

18 2 2

1292

19 0 0

1293

19 1 1

1294

19 2 2

1295

DELETE FROM t3;

1296

EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;

1297

id select_type table type possible_keys key key_len ref rows Extra

1298

1 SIMPLE t1 index NULL a 5 NULL 21 Using index

1299

1 SIMPLE t3 index c c 5 NULL 0 Using where; Using index

1300

1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index

1301

SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;

1302

a b c

1303

NULL NULL NULL

1304

0 NULL NULL

1305

1 NULL NULL

1306

2 NULL NULL

1307

3 NULL NULL

1308

4 NULL NULL

1309

5 NULL NULL

1310

6 NULL NULL

1311

7 NULL NULL

1312

8 NULL NULL

1313

9 NULL NULL

1314

10 NULL NULL

1315

11 NULL NULL

1316

12 NULL NULL

1317

13 NULL NULL

1318

14 NULL NULL

1319

15 NULL NULL

1320

16 NULL NULL

1321

17 NULL NULL

1322

18 NULL NULL

1323

19 NULL NULL

1324

DROP TABLE t1,t2,t3;

1325

CREATE TABLE t1 (c11 int);

1326

CREATE TABLE t2 (c21 int);

1327

CREATE TABLE t3 (c31 int);

1328

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

1329

SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;

1330

c11 c21

1331

4 NULL

1332

5 NULL

1333

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;

1334

id select_type table type possible_keys key key_len ref rows Extra

1335

1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found

1336

1 SIMPLE t1 ALL NULL NULL NULL NULL 2

1337

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;

1338

c11 c21 c31

1339

4 NULL NULL

1340

5 NULL NULL

1341

EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;

1342

id select_type table type possible_keys key key_len ref rows Extra

1343

1 SIMPLE t1 ALL NULL NULL NULL NULL 2

1344

1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.c11 0 Using where; Using join buffer (flat, BNLH join)

1345

1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join)

1346

DROP TABLE t1,t2,t3;

1347

CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);

1348

INSERT INTO t1 VALUES (23, 2340), (26, 9900);

1349

CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));

1350

INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');

1351

create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);

1352

INSERT INTO t3 VALUES (3,23), (6,26);

1353

CREATE TABLE t4 (groupid int(12));

1354

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

1355

SELECT * FROM

1356

(SELECT DISTINCT gl.groupid, gp.price

1357

FROM t4 gl

1358

LEFT JOIN

1359

(t3 g INNER JOIN t2 p ON g.goodsid = p.goods

1360

INNER JOIN t1 gp ON p.goods = gp.goods)

1361

ON gl.groupid = g.groupid and p.shop = 'fr') t;

1362

groupid price

1363

3 2340

1364

6 9900

1365

1 NULL

1366

2 NULL

1367

4 NULL

1368

5 NULL

1369

CREATE VIEW v1 AS

1370

SELECT g.groupid groupid, p.goods goods,

1371

p.name name, p.shop shop,

1372

gp.price price

1373

FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods

1374

INNER JOIN t1 gp on p.goods = gp.goods;

1375

CREATE VIEW v2 AS

1376

SELECT DISTINCT g.groupid, fr.price

1377

FROM t4 g

1378

LEFT JOIN

1379

v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';

1380

SELECT * FROM v2;

1381

groupid price

1382

3 2340

1383

6 9900

1384

1 NULL

1385

2 NULL

1386

4 NULL

1387

5 NULL

1388

SELECT * FROM

1389

(SELECT DISTINCT g.groupid, fr.price

1390

FROM t4 g

1391

LEFT JOIN

1392

v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;

1393

groupid price

1394

3 2340

1395

6 9900

1396

1 NULL

1397

2 NULL

1398

4 NULL

1399

5 NULL

1400

DROP VIEW v1,v2;

1401

DROP TABLE t1,t2,t3,t4;

1402

CREATE TABLE t1(a int);

1403

CREATE TABLE t2(b int);

1404

CREATE TABLE t3(c int, d int);

1405

CREATE TABLE t4(d int);

1406

CREATE TABLE t5(e int, f int);

1407

CREATE TABLE t6(f int);

1408

CREATE VIEW v1 AS

1409

SELECT e FROM t5 JOIN t6 ON t5.e=t6.f;

1410

CREATE VIEW v2 AS

1411

SELECT e FROM t5 NATURAL JOIN t6;

1412

SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d);

1413

1414

SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d);

1415

ERROR 42S22: Unknown column 't1.x' in 'field list'

1416

SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4;

1417

1418

SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4;

1419

ERROR 42S22: Unknown column 't1.x' in 'field list'

1420

SELECT v1.e FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);

1421

1422

SELECT v1.x FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);

1423

ERROR 42S22: Unknown column 'v1.x' in 'field list'

1424

SELECT v2.e FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);

1425

1426

SELECT v2.x FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);

1427

ERROR 42S22: Unknown column 'v2.x' in 'field list'

1428

DROP VIEW v1, v2;

1429

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

1430

create table t1 (id1 int(11) not null);

1431

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

1432

create table t2 (id2 int(11) not null);

1433

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

1434

create table t3 (id3 char(16) not null);

1435

insert into t3 values ('100');

1436

create table t4 (id2 int(11) not null, id3 char(16));

1437

create table t5 (id1 int(11) not null, key (id1));

1438

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

1439

create view v1 as

1440

select t4.id3 from t4 join t2 on t4.id2 = t2.id2;

1441

select t1.id1 from t1 inner join (t3 left join v1 on t3.id3 = v1.id3);

1442

id1

1443

1444

1445

drop view v1;

1446

drop table t1, t2, t3, t4, t5;

1447

create table t0 (a int);

1448

insert into t0 values (0),(1),(2),(3);

1449

create table t1(a int);

1450

insert into t1 select A.a + 10*(B.a) from t0 A, t0 B;

1451

create table t2 (a int, b int);

1452

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

1453

create table t3(a int, b int, filler char(200), key(a));

1454

insert into t3 select a,a,'filler' from t1;

1455

insert into t3 select a,a,'filler' from t1;

1456

create table t4 like t3;

1457

insert into t4 select * from t3;

1458

insert into t4 select * from t3;

1459

create table t5 like t4;

1460

insert into t5 select * from t4;

1461

insert into t5 select * from t4;

1462

create table t6 like t5;

1463

insert into t6 select * from t5;

1464

insert into t6 select * from t5;

1465

create table t7 like t6;

1466

insert into t7 select * from t6;

1467

insert into t7 select * from t6;

1468

explain select * from t4 join

1469

t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b;

1470

id select_type table type possible_keys key key_len ref rows Extra

1471

1 SIMPLE t2 ALL NULL NULL NULL NULL X

1472

1 SIMPLE t3 ref a a 5 test.t2.b X Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

1473

1 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1474

1 SIMPLE t4 ref a a 5 test.t3.b X Using index condition(BKA); Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1475

explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b

1476

join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b;

1477

id select_type table type possible_keys key key_len ref rows Extra

1478

1 SIMPLE t2 ALL NULL NULL NULL NULL X

1479

1 SIMPLE t3 ref a a 5 test.t2.b X Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

1480

1 SIMPLE t4 ref a a 5 test.t3.b X Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1481

1 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1482

1 SIMPLE t5 ref a a 5 test.t2.b X Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1483

1 SIMPLE t7 ref a a 5 test.t5.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1484

explain select * from t2 left join

1485

(t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b

1486

join t5 on t5.a=t3.b) on t3.a=t2.b;

1487

id select_type table type possible_keys key key_len ref rows Extra

1488

1 SIMPLE t2 ALL NULL NULL NULL NULL X

1489

1 SIMPLE t3 ref a a 5 test.t2.b X Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

1490

1 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1491

1 SIMPLE t4 ref a a 5 test.t5.a X Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1492

1 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1493

drop table t0, t1, t2, t3, t4, t5, t6, t7;

1494

create table t1 (a int);

1495

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

1496

create table t2 (a int, filler char(100), key(a));

1497

insert into t2 select A.a + 10*B.a, '' from t1 A, t1 B;

1498

create table t3 like t2;

1499

insert into t3 select * from t2;

1500

explain select * from t1 left join

1501

(t2 left join t3 on (t2.a = t3.a))

1502

on (t1.a = t2.a);

1503

id select_type table type possible_keys key key_len ref rows Extra

1504

1 SIMPLE t1 ALL NULL NULL NULL NULL 10

1505

1 SIMPLE t2 ref a a 5 test.t1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

1506

1 SIMPLE t3 ref a a 5 test.t1.a 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1507

drop table t1, t2, t3;

1508

CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));

1509

CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));

1510

CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY,

1511

id int NOT NULL,

1512

pid int NOT NULL);

1513

INSERT INTO t1 VALUES (1, 'A'), (3, 'C');

1514

INSERT INTO t2 VALUES (1, 'A'), (3, 'C');

1515

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

1516

SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1)

1517

ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id)

1518

LEFT JOIN t2 ON (t3.pid=t2.pid)

1519

WHERE p.id=1;

1520

id type cid id pid id type pid type

1521

1 A NULL NULL NULL NULL NULL NULL NULL

1522

CREATE VIEW v1 AS

1523

SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B';

1524

SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id

1525

LEFT JOIN t2 ON v1.pid=t2.pid

1526

WHERE p.id=1;

1527

id type cid id pid pid type

1528

1 A NULL NULL NULL NULL NULL

1529

DROP VIEW v1;

1530

DROP TABLE t1,t2,t3;

1531

CREATE TABLE t1 (id1 int PRIMARY KEY, id2 int);

1532

CREATE TABLE t2 (id1 int PRIMARY KEY, id2 int);

1533

CREATE TABLE t3 (id1 int PRIMARY KEY, id2 int);

1534

CREATE TABLE t4 (id1 int PRIMARY KEY, id2 int);

1535

CREATE TABLE t5 (id1 int PRIMARY KEY, id2 int);

1536

SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa

1537

FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1

1538

LEFT OUTER JOIN

1539

(t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)

1540

ON t3.id2 IS NOT NULL

1541

WHERE t1.id1=2;

1542

id ngroupbynsa

1543

PREPARE stmt FROM

1544

"SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa

1545

FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1

1546

LEFT OUTER JOIN

1547

(t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)

1548

ON t3.id2 IS NOT NULL

1549

WHERE t1.id1=2";

1550

EXECUTE stmt;

1551

id ngroupbynsa

1552

EXECUTE stmt;

1553

id ngroupbynsa

1554

EXECUTE stmt;

1555

id ngroupbynsa

1556

EXECUTE stmt;

1557

id ngroupbynsa

1558

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

1559

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

1560

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

1561

INSERT INTO t4 VALUES (1,1), (2,1), (3,3);

1562

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

1563

EXECUTE stmt;

1564

id ngroupbynsa

1565

2 1

1566

2 1

1567

EXECUTE stmt;

1568

id ngroupbynsa

1569

2 1

1570

2 1

1571

EXECUTE stmt;

1572

id ngroupbynsa

1573

2 1

1574

2 1

1575

EXECUTE stmt;

1576

id ngroupbynsa

1577

2 1

1578

2 1

1579

SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa

1580

FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1

1581

LEFT OUTER JOIN

1582

(t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)

1583

ON t3.id2 IS NOT NULL

1584

WHERE t1.id1=2;

1585

id ngroupbynsa

1586

2 1

1587

2 1

1588

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

1589

CREATE TABLE t1 (

1590

id int NOT NULL PRIMARY KEY,

1591

ct int DEFAULT NULL,

1592

pc int DEFAULT NULL,

1593

INDEX idx_ct (ct),

1594

INDEX idx_pc (pc)

1595

);

1596

INSERT INTO t1 VALUES

1597

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

1598

CREATE TABLE t2 (

1599

id int NOT NULL PRIMARY KEY,

1600

sr int NOT NULL,

1601

nm varchar(255) NOT NULL,

1602

INDEX idx_sr (sr)

1603

);

1604

INSERT INTO t2 VALUES

1605

(2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand');

1606

CREATE TABLE t3 (

1607

id int NOT NULL PRIMARY KEY,

1608

ct int NOT NULL,

1609

ln int NOT NULL,

1610

INDEX idx_ct (ct),

1611

INDEX idx_ln (ln)

1612

);

1613

CREATE TABLE t4 (

1614

id int NOT NULL PRIMARY KEY,

1615

nm varchar(255) NOT NULL

1616

);

1617

INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique');

1618

SELECT t1.*

1619

FROM t1 LEFT JOIN

1620

(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id

1621

WHERE t1.id='5';

1622

id ct pc

1623

5 NULL NULL

1624

SELECT t1.*, t4.nm

1625

FROM t1 LEFT JOIN

1626

(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id

1627

LEFT JOIN t4 ON t2.sr=t4.id

1628

WHERE t1.id='5';

1629

id ct pc nm

1630

5 NULL NULL NULL

1631

DROP TABLE t1,t2,t3,t4;

1632

CREATE TABLE t1 (a INT, b INT);

1633

CREATE TABLE t2 (a INT);

1634

CREATE TABLE t3 (a INT, c INT);

1635

CREATE TABLE t4 (a INT, c INT);

1636

CREATE TABLE t5 (a INT, c INT);

1637

SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)

1638

LEFT JOIN t5 USING (a)) USING (a);

1639

1640

SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)

1641

LEFT JOIN t5 USING (a)) USING (a);

1642

ERROR 23000: Column 'c' in field list is ambiguous

1643

SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)

1644

JOIN t5 USING (a)) USING (a);

1645

1646

SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)

1647

JOIN t5 USING (a)) USING (a);

1648

ERROR 23000: Column 'c' in field list is ambiguous

1649

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

1650

CREATE TABLE t1 (a INT, b INT);

1651

CREATE TABLE t2 (a INT, b INT);

1652

CREATE TABLE t3 (a INT, b INT);

1653

INSERT INTO t1 VALUES (1,1);

1654

INSERT INTO t2 VALUES (1,1);

1655

INSERT INTO t3 VALUES (1,1);

1656

SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a);

1657

ERROR 23000: Column 'a' in from clause is ambiguous

1658

DROP TABLE t1,t2,t3;

1659

CREATE TABLE t1 (

1660

carrier char(2) default NULL,

1661

id int NOT NULL auto_increment PRIMARY KEY

1662

);

1663

INSERT INTO t1 VALUES

1664

('CO',235371754),('CO',235376554),('CO',235376884),('CO',235377874),

1665

('CO',231060394),('CO',231059224),('CO',231059314),('CO',231060484),

1666

('CO',231060274),('CO',231060124),('CO',231060244),('CO',231058594),

1667

('CO',231058924),('CO',231058504),('CO',231059344),('CO',231060424),

1668

('CO',231059554),('CO',231060304),('CO',231059644),('CO',231059464),

1669

('CO',231059764),('CO',231058294),('CO',231058624),('CO',231058864),

1670

('CO',231059374),('CO',231059584),('CO',231059734),('CO',231059014),

1671

('CO',231059854),('CO',231059494),('CO',231059794),('CO',231058534),

1672

('CO',231058324),('CO',231058684),('CO',231059524),('CO',231059974);

1673

CREATE TABLE t2 (

1674

scan_date date default NULL,

1675

package_id int default NULL,

1676

INDEX scan_date(scan_date),

1677

INDEX package_id(package_id)

1678

);

1679

INSERT INTO t2 VALUES

1680

('2008-12-29',231062944),('2008-12-29',231065764),('2008-12-29',231066124),

1681

('2008-12-29',231060094),('2008-12-29',231061054),('2008-12-29',231065644),

1682

('2008-12-29',231064384),('2008-12-29',231064444),('2008-12-29',231073774),

1683

('2008-12-29',231058594),('2008-12-29',231059374),('2008-12-29',231066004),

1684

('2008-12-29',231068494),('2008-12-29',231070174),('2008-12-29',231071884),

1685

('2008-12-29',231063274),('2008-12-29',231063754),('2008-12-29',231064144),

1686

('2008-12-29',231069424),('2008-12-29',231073714),('2008-12-29',231058414),

1687

('2008-12-29',231060994),('2008-12-29',231069154),('2008-12-29',231068614),

1688

('2008-12-29',231071464),('2008-12-29',231074014),('2008-12-29',231059614),

1689

('2008-12-29',231059074),('2008-12-29',231059464),('2008-12-29',231069094),

1690

('2008-12-29',231067294),('2008-12-29',231070144),('2008-12-29',231073804),

1691

('2008-12-29',231072634),('2008-12-29',231058294),('2008-12-29',231065344),

1692

('2008-12-29',231066094),('2008-12-29',231069034),('2008-12-29',231058594),

1693

('2008-12-29',231059854),('2008-12-29',231059884),('2008-12-29',231059914),

1694

('2008-12-29',231063664),('2008-12-29',231063814),('2008-12-29',231063904);

1695

CREATE TABLE t3 (

1696

package_id int default NULL,

1697

INDEX package_id(package_id)

1698

);

1699

INSERT INTO t3 VALUES

1700

(231058294),(231058324),(231058354),(231058384),(231058414),(231058444),

1701

(231058474),(231058504),(231058534),(231058564),(231058594),(231058624),

1702

(231058684),(231058744),(231058804),(231058864),(231058924),(231058954),

1703

(231059014),(231059074),(231059104),(231059134),(231059164),(231059194),

1704

(231059224),(231059254),(231059284),(231059314),(231059344),(231059374),

1705

(231059404),(231059434),(231059464),(231059494),(231059524),(231059554),

1706

(231059584),(231059614),(231059644),(231059674),(231059704),(231059734),

1707

(231059764),(231059794),(231059824),(231059854),(231059884),(231059914),

1708

(231059944),(231059974),(231060004),(231060034),(231060064),(231060094),

1709

(231060124),(231060154),(231060184),(231060214),(231060244),(231060274),

1710

(231060304),(231060334),(231060364),(231060394),(231060424),(231060454),

1711

(231060484),(231060514),(231060544),(231060574),(231060604),(231060634),

1712

(231060664),(231060694),(231060724),(231060754),(231060784),(231060814),

1713

(231060844),(231060874),(231060904),(231060934),(231060964),(231060994),

1714

(231061024),(231061054),(231061084),(231061144),(231061174),(231061204),

1715

(231061234),(231061294),(231061354),(231061384),(231061414),(231061474),

1716

(231061564),(231061594),(231061624),(231061684),(231061714),(231061774),

1717

(231061804),(231061894),(231061984),(231062074),(231062134),(231062224),

1718

(231062254),(231062314),(231062374),(231062434),(231062494),(231062554),

1719

(231062584),(231062614),(231062644),(231062704),(231062734),(231062794),

1720

(231062854),(231062884),(231062944),(231063004),(231063034),(231063064),

1721

(231063124),(231063154),(231063184),(231063214),(231063274),(231063334),

1722

(231063394),(231063424),(231063454),(231063514),(231063574),(231063664);

1723

CREATE TABLE t4 (

1724

carrier char(2) NOT NULL default '' PRIMARY KEY,

1725

id int(11) default NULL,

1726

INDEX id(id)

1727

);

1728

INSERT INTO t4 VALUES

1729

('99',6),('SK',456),('UA',486),('AI',1081),('OS',1111),('VS',1510);

1730

CREATE TABLE t5 (

1731

carrier_id int default NULL,

1732

INDEX carrier_id(carrier_id)

1733

);

1734

INSERT INTO t5 VALUES

1735

(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),

1736

(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),

1737

(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),

1738

(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),

1739

(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),

1740

(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(456),(456),(456),

1741

(456),(456),(456),(456),(456),(456),(456),(456),(456),(456),(456),(456),

1742

(456),(486),(1081),(1111),(1111),(1111),(1111),(1510);

1743

SELECT COUNT(*)

1744

FROM((t2 JOIN t1 ON t2.package_id = t1.id)

1745

JOIN t3 ON t3.package_id = t1.id);

1746

COUNT(*)

1747

1748

EXPLAIN

1749

SELECT COUNT(*)

1750

FROM ((t2 JOIN t1 ON t2.package_id = t1.id)

1751

JOIN t3 ON t3.package_id = t1.id)

1752

LEFT JOIN

1753

(t5 JOIN t4 ON t5.carrier_id = t4.id)

1754

ON t4.carrier = t1.carrier;

1755

id select_type table type possible_keys key key_len ref rows Extra

1756

1 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using index

1757

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

1758

1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1 Using where

1759

1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index

1760

1 SIMPLE t3 ref package_id package_id 5 test.t2.package_id 1 Using index

1761

SELECT COUNT(*)

1762

FROM ((t2 JOIN t1 ON t2.package_id = t1.id)

1763

JOIN t3 ON t3.package_id = t1.id)

1764

LEFT JOIN

1765

(t5 JOIN t4 ON t5.carrier_id = t4.id)

1766

ON t4.carrier = t1.carrier;

1767

COUNT(*)

1768

1769

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

1770

CREATE TABLE t1 (

1771

pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,

1772

a int DEFAULT NULL,

1773

KEY idx(a)

1774

);

1775

CREATE TABLE t2 (

1776

pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,

1777

a int DEFAULT NULL,

1778

KEY idx(a)

1779

);

1780

CREATE TABLE t3 (

1781

pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,

1782

a int DEFAULT NULL,

1783

KEY idx(a)

1784

);

1785

INSERT INTO t1 VALUES

1786

(1,2), (2,7), (3,5), (4,7), (5,5), (6,NULL), (7,NULL), (8,9);

1787

INSERT INTO t2 VALUES

1788

(1,NULL), (4,2), (5,2), (3,4), (2,8);

1789

INSERT INTO t3 VALUES

1790

(1,9), (2,2), (3,5), (4,2), (5,7), (6,0), (7,5);

1791

SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a

1792

FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a;

1793

pk a pk a pk a

1794

1 2 4 2 2 2

1795

1 2 5 2 2 2

1796

1 2 4 2 4 2

1797

1 2 5 2 4 2

1798

2 7 NULL NULL NULL NULL

1799

3 5 NULL NULL NULL NULL

1800

4 7 NULL NULL NULL NULL

1801

5 5 NULL NULL NULL NULL

1802

6 NULL NULL NULL NULL NULL

1803

7 NULL NULL NULL NULL NULL

1804

8 9 NULL NULL NULL NULL

1805

SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a

1806

FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a

1807

WHERE t2.pk IS NULL;

1808

pk a pk a pk a

1809

2 7 NULL NULL NULL NULL

1810

3 5 NULL NULL NULL NULL

1811

4 7 NULL NULL NULL NULL

1812

5 5 NULL NULL NULL NULL

1813

6 NULL NULL NULL NULL NULL

1814

7 NULL NULL NULL NULL NULL

1815

8 9 NULL NULL NULL NULL

1816

SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a

1817

FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a

1818

WHERE t3.pk IS NULL;

1819

pk a pk a pk a

1820

2 7 NULL NULL NULL NULL

1821

3 5 NULL NULL NULL NULL

1822

4 7 NULL NULL NULL NULL

1823

5 5 NULL NULL NULL NULL

1824

6 NULL NULL NULL NULL NULL

1825

7 NULL NULL NULL NULL NULL

1826

8 9 NULL NULL NULL NULL

1827

DROP TABLE t1, t2, t3;

1828

CREATE TABLE t1 (a int NOT NULL );

1829

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

1830

CREATE TABLE t2 (a int NOT NULL );

1831

INSERT INTO t2 VALUES (9);

1832

CREATE TABLE t3 (a int NOT NULL, b int);

1833

INSERT INTO t3 VALUES (19,9);

1834

CREATE TABLE t4 (b int) ;

1835

SELECT * FROM t1 LEFT JOIN

1836

((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b)

1837

ON t1.a=t2.a;

1838

a a a b b

1839

9 9 19 9 NULL

1840

9 9 19 9 NULL

1841

SELECT * FROM t1 LEFT JOIN

1842

((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b)

1843

ON t1.a=t2.a

1844

WHERE t3.a IS NULL;

1845

a a a b b

1846

EXPLAIN EXTENDED

1847

SELECT * FROM t1 LEFT JOIN

1848

((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b)

1849

ON t1.a=t2.a

1850

WHERE t3.a IS NULL;

1851

id select_type table type possible_keys key key_len ref rows filtered Extra

1852

1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00

1853

1 SIMPLE t2 hash_ALL NULL #hash#$hj 4 test.t1.a 1 100.00 Using where; Using join buffer (flat, BNLH join)

1854

1 SIMPLE t3 hash_ALL NULL #hash#$hj 5 test.t1.a 1 100.00 Using where; Not exists; Using join buffer (incremental, BNLH join)

1855

1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t3.a 0 0.00 Using where; Using join buffer (incremental, BNLH join)

1856

Warnings:

1857

Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`b` AS `b` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on((`test`.`t3`.`b` = `test`.`t1`.`a`)) left join `test`.`t4` on(((`test`.`t4`.`b` = `test`.`t3`.`a`) and (`test`.`t3`.`a` is not null)))) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where isnull(`test`.`t3`.`a`)

1858

DROP TABLE t1,t2,t3,t4;

1859

SET optimizer_switch=@save_optimizer_switch;

1860

End of 5.0 tests

1861

1862

# MDEV-621: LP:693329 - Assertion `!is_interleave_error' failed on low optimizer_search_depth

1863

1864

set @tmp_mdev621= @@optimizer_search_depth;

1865

SET SESSION optimizer_search_depth = 4;

1866

CREATE TABLE t1 (f1 int,f2 int,f3 int,f4 int) ;

1867

INSERT IGNORE INTO t1 VALUES (0,0,2,0),(NULL,0,2,0);

1868

CREATE TABLE t2 (f1 int) ;

1869

CREATE TABLE t3 (f3 int,PRIMARY KEY (f3)) ;

1870

CREATE TABLE t4 (f5 int) ;

1871

CREATE TABLE t5 (f2 int) ;

1872

SELECT alias2.f4 FROM t1 AS alias1

1873

LEFT JOIN t1 AS alias2

1874

LEFT JOIN t2 AS alias3

1875

LEFT JOIN t3 AS alias4 ON alias3.f1 = alias4.f3

1876

ON alias2.f1

1877

LEFT JOIN t4 AS alias5

1878

JOIN t5 ON alias5.f5

1879

ON alias2.f3 ON alias1.f2;

1880

1881

NULL

1882

NULL

1883

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

1884

set optimizer_search_depth= @tmp_mdev621;

1885

CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));

1886

CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));

1887

CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));

1888

CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));

1889

INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);

1890

INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);

1891

INSERT INTO t7 VALUES (1,1,0), (2,2,0);

1892

INSERT INTO t8 VALUES (0,2,0), (1,2,0);

1893

INSERT INTO t6 VALUES (-1,12,0), (-3,13,0), (-6,11,0), (-4,14,0);

1894

INSERT INTO t7 VALUES (-1,11,0), (-2,12,0), (-3,13,0), (-4,14,0), (-5,15,0);

1895

INSERT INTO t8 VALUES (-3,13,0), (-1,12,0), (-2,14,0), (-5,15,0), (-4,16,0);

1896

EXPLAIN

1897

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

1898

FROM t5

1899

LEFT JOIN

1900

(

1901

(t6, t7)

1902

LEFT JOIN

1903

1904

ON t7.b=t8.b AND t6.b < 10

1905

)

1906

ON t6.b >= 2 AND t5.b=t7.b AND

1907

(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;

1908

id select_type table type possible_keys key key_len ref rows Extra

1909

1 SIMPLE t5 ALL NULL NULL NULL NULL 3

1910

1 SIMPLE t7 ref PRIMARY,b_i b_i 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

1911

1 SIMPLE t6 ALL PRIMARY,b_i NULL NULL NULL 7 Using where; Using join buffer (incremental, BNL join)

1912

1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1913

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

1914

FROM t5

1915

LEFT JOIN

1916

(

1917

(t6, t7)

1918

LEFT JOIN

1919

1920

ON t7.b=t8.b AND t6.b < 10

1921

)

1922

ON t6.b >= 2 AND t5.b=t7.b AND

1923

(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;

1924

a b a b a b a b

1925

2 2 1 2 2 2 1 2

1926

2 2 3 2 2 2 1 2

1927

1 1 1 2 1 1 NULL NULL

1928

1 1 3 2 1 1 NULL NULL

1929

3 3 NULL NULL NULL NULL NULL NULL

1930

DELETE FROM t5;

1931

DELETE FROM t6;

1932

DELETE FROM t7;

1933

DELETE FROM t8;

1934

INSERT INTO t5 VALUES (1,3,0), (3,2,0);

1935

INSERT INTO t6 VALUES (3,3,0);

1936

INSERT INTO t7 VALUES (1,2,0);

1937

INSERT INTO t8 VALUES (1,1,0);

1938

EXPLAIN

1939

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

1940

FROM t5 LEFT JOIN

1941

(t6 LEFT JOIN t7 ON t7.a=1, t8)

1942

ON (t5.b=t8.b);

1943

id select_type table type possible_keys key key_len ref rows Extra

1944

1 SIMPLE t5 ALL NULL NULL NULL NULL 2

1945

1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join)

1946

1 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1947

1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)

1948

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

1949

FROM t5 LEFT JOIN

1950

(t6 LEFT JOIN t7 ON t7.a=1, t8)

1951

ON (t5.b=t8.b);

1952

a b a b a b a b

1953

1 3 NULL NULL NULL NULL NULL NULL

1954

3 2 NULL NULL NULL NULL NULL NULL

1955

EXPLAIN

1956

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

1957

FROM t5 LEFT JOIN

1958

(t6 LEFT JOIN t7 ON t7.b=2, t8)

1959

ON (t5.b=t8.b);

1960

id select_type table type possible_keys key key_len ref rows Extra

1961

1 SIMPLE t5 ALL NULL NULL NULL NULL 2

1962

1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join)

1963

1 SIMPLE t7 ref b_i b_i 5 const 0 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1964

1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)

1965

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

1966

FROM t5 LEFT JOIN

1967

(t6 LEFT JOIN t7 ON t7.b=2, t8)

1968

ON (t5.b=t8.b);

1969

a b a b a b a b

1970

1 3 NULL NULL NULL NULL NULL NULL

1971

3 2 NULL NULL NULL NULL NULL NULL

1972

EXPLAIN

1973

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

1974

FROM t5 LEFT JOIN

1975

(t8, t6 LEFT JOIN t7 ON t7.a=1)

1976

ON (t5.b=t8.b);

1977

id select_type table type possible_keys key key_len ref rows Extra

1978

1 SIMPLE t5 ALL NULL NULL NULL NULL 2

1979

1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)

1980

1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join)

1981

1 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

1982

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b

1983

FROM t5 LEFT JOIN

1984

(t8, t6 LEFT JOIN t7 ON t7.a=1)

1985

ON (t5.b=t8.b);

1986

a b a b a b a b

1987

1 3 NULL NULL NULL NULL NULL NULL

1988

3 2 NULL NULL NULL NULL NULL NULL

1989

DROP TABLE t5,t6,t7,t8;

1990

set join_cache_level=default;

1991

show variables like 'join_cache_level';

1992

Variable_name Value

1993

join_cache_level 2

1994

set @@optimizer_switch=@save_optimizer_switch_jcl6;

1995

set @optimizer_switch_for_join_nested_test=NULL;

1996

set @join_cache_level_for_join_nested_test=NULL;

Older »