~drizzle-trunk/drizzle/development

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

a b a b a b

3 3 NULL NULL NULL NULL

4 2 1 2 3 2

4 2 1 2 4 2

5 3 NULL NULL NULL NULL

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;

a b a b a b

3 3 NULL NULL NULL NULL

4 2 2 2 3 2

100

4 2 2 2 4 2

101

5 3 NULL NULL NULL NULL

102

SELECT t5.a,t5.b

103

FROM t5;

104

a b

105

3 1

106

2 2

107

3 3

108

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

109

FROM t3,t4,t5;

110

a b a b a b

111

1 2 3 2 3 1

112

2 2 3 2 3 1

113

1 2 4 2 3 1

114

2 2 4 2 3 1

115

1 2 3 2 2 2

116

2 2 3 2 2 2

117

1 2 4 2 2 2

118

2 2 4 2 2 2

119

1 2 3 2 3 3

120

2 2 3 2 3 3

121

1 2 4 2 3 3

122

2 2 4 2 3 3

123

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

124

FROM t2

125

LEFT JOIN

126

(t3, t4, t5)

127

ON t2.b=t4.b;

128

a b a b a b a b

129

3 3 NULL NULL NULL NULL NULL NULL

130

4 2 1 2 3 2 3 1

131

4 2 1 2 3 2 2 2

132

4 2 1 2 3 2 3 3

133

4 2 1 2 4 2 3 1

134

4 2 1 2 4 2 2 2

135

4 2 1 2 4 2 3 3

136

4 2 2 2 3 2 3 1

137

4 2 2 2 3 2 2 2

138

4 2 2 2 3 2 3 3

139

4 2 2 2 4 2 3 1

140

4 2 2 2 4 2 2 2

141

4 2 2 2 4 2 3 3

142

5 3 NULL NULL NULL NULL NULL NULL

143

EXPLAIN EXTENDED

144

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

145

FROM t2

146

LEFT JOIN

147

(t3, t4, t5)

148

ON t2.b=t4.b

149

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

150

id select_type table type possible_keys key key_len ref rows filtered Extra

151

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00

152

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where

153

1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00

154

1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00

155

Warnings:

156

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`)) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`))

157

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

158

FROM t2

159

LEFT JOIN

160

(t3, t4, t5)

161

ON t2.b=t4.b

162

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

163

a b a b a b a b

164

3 3 NULL NULL NULL NULL NULL NULL

165

4 2 2 2 3 2 3 1

166

4 2 2 2 3 2 2 2

167

4 2 2 2 3 2 3 3

168

4 2 2 2 4 2 3 1

169

4 2 2 2 4 2 2 2

170

4 2 2 2 4 2 3 3

171

5 3 NULL NULL NULL NULL NULL NULL

172

EXPLAIN EXTENDED

173

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

174

FROM t2

175

LEFT JOIN

176

(t3, t4, t5)

177

ON t2.b=t4.b

178

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

179

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

180

id select_type table type possible_keys key key_len ref rows filtered Extra

181

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00

182

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where

183

1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00

184

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

185

Warnings:

186

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

a b a b a b a b

195

3 3 NULL NULL NULL NULL NULL NULL

196

4 2 2 2 3 2 2 2

197

4 2 2 2 4 2 2 2

198

5 3 NULL NULL NULL NULL NULL NULL

199

SELECT t6.a,t6.b

200

FROM t6;

201

a b

202

3 2

203

6 2

204

6 1

205

SELECT t7.a,t7.b

206

FROM t7;

207

a b

208

1 1

209

2 2

210

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

211

FROM t6,t7;

212

a b a b

213

3 2 1 1

214

3 2 2 2

215

6 2 1 1

216

6 2 2 2

217

6 1 1 1

218

6 1 2 2

219

SELECT t8.a,t8.b

220

FROM t8;

221

a b

222

0 2

223

1 2

224

EXPLAIN EXTENDED

225

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

226

FROM (t6, t7)

227

LEFT JOIN

228

229

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

230

id select_type table type possible_keys key key_len ref rows filtered Extra

231

1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00

232

1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer

233

1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00

234

Warnings:

235

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`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10))) where 1

236

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

237

FROM (t6, t7)

238

LEFT JOIN

239

240

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

241

a b a b a b

242

3 2 1 1 NULL NULL

243

3 2 2 2 0 2

244

3 2 2 2 1 2

245

6 2 1 1 NULL NULL

246

6 2 2 2 0 2

247

6 2 2 2 1 2

248

6 1 1 1 NULL NULL

249

6 1 2 2 0 2

250

6 1 2 2 1 2

251

SELECT t5.a,t5.b

252

FROM t5;

253

a b

254

3 1

255

2 2

256

3 3

257

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

258

FROM t5

259

LEFT JOIN

260

(

261

(t6, t7)

262

LEFT JOIN

263

264

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

265

)

266

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

267

a b a b a b a b

268

3 1 3 2 1 1 NULL NULL

269

3 1 6 2 1 1 NULL NULL

270

2 2 3 2 2 2 0 2

271

2 2 3 2 2 2 1 2

272

2 2 6 2 2 2 0 2

273

2 2 6 2 2 2 1 2

274

3 3 NULL NULL NULL NULL NULL NULL

275

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

276

FROM t5

277

LEFT JOIN

278

(

279

(t6, t7)

280

LEFT JOIN

281

282

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

283

)

284

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

285

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

286

a b a b a b a b

287

3 1 3 2 1 1 NULL NULL

288

3 1 6 2 1 1 NULL NULL

289

2 2 3 2 2 2 0 2

290

2 2 6 2 2 2 0 2

291

3 3 NULL NULL NULL NULL NULL NULL

292

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

293

FROM t2

294

LEFT JOIN

295

(t3, t4)

296

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

297

a b a b a b

298

3 3 NULL NULL NULL NULL

299

4 2 1 2 3 2

300

4 2 1 2 4 2

301

5 3 NULL NULL NULL NULL

302

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

303

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

304

FROM t2

305

LEFT JOIN

306

(t3, t4)

307

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

308

309

LEFT JOIN

310

(

311

(t6, t7)

312

LEFT JOIN

313

314

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

315

)

316

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

317

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

318

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

319

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

320

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

321

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

322

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

323

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

324

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

325

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

326

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

327

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

328

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

329

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

330

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

331

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

332

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

333

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

334

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

335

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

336

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

337

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

338

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

339

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

340

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

341

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

342

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

343

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

344

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

345

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

346

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

347

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

348

FROM t2

349

LEFT JOIN

350

(t3, t4)

351

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

352

353

LEFT JOIN

354

(

355

(t6, t7)

356

LEFT JOIN

357

358

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

359

)

360

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

361

WHERE t2.a > 3 AND

362

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

363

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

364

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

365

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

366

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

367

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

368

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

369

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

370

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

371

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

372

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

373

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

374

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

375

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

376

SELECT t1.a,t1.b

377

FROM t1;

378

a b

379

1 3

380

2 2

381

3 2

382

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

383

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

384

FROM t1

385

LEFT JOIN

386

(

387

388

LEFT JOIN

389

(t3, t4)

390

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

391

392

LEFT JOIN

393

(

394

(t6, t7)

395

LEFT JOIN

396

397

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

398

)

399

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

400

)

401

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

402

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

403

(t1.a != 2);

404

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

405

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

406

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

407

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

408

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

409

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

410

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

411

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

412

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

413

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

414

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

415

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

416

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

417

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

418

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

419

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

420

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

421

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

422

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

423

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

424

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

425

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

426

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

427

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

428

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

429

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

430

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

431

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

432

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

433

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

434

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

435

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

436

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

437

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

438

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

439

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

440

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

441

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

442

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

443

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

444

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

445

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

446

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

447

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

448

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

449

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

450

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

451

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

452

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

453

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

454

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

455

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

456

FROM t1

457

LEFT JOIN

458

(

459

460

LEFT JOIN

461

(t3, t4)

462

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

463

464

LEFT JOIN

465

(

466

(t6, t7)

467

LEFT JOIN

468

469

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

470

)

471

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

472

)

473

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

474

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

475

(t1.a != 2)

476

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

477

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

478

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

479

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

480

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

481

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

482

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

483

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

484

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

485

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

486

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

487

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

488

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

489

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

490

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

491

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

492

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

493

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

494

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

495

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

496

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

497

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

498

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

499

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

500

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

501

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

502

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

503

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

504

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

505

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

506

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

507

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

508

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

509

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

510

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

511

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

512

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

513

SELECT t0.a,t0.b

514

FROM t0;

515

a b

516

1 1

517

1 2

518

2 2

519

EXPLAIN EXTENDED

520

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

521

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

522

FROM t0,t1

523

LEFT JOIN

524

(

525

526

LEFT JOIN

527

(t3, t4)

528

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

529

530

LEFT JOIN

531

(

532

(t6, t7)

533

LEFT JOIN

534

535

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

536

)

537

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

538

)

539

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

540

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

541

(t1.a != 2)

542

WHERE t0.a=1 AND

543

t0.b=t1.b AND

544

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

545

id select_type table type possible_keys key key_len ref rows filtered Extra

546

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

547

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

548

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

549

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00

550

1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00

551

1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00

552

1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00

553

1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00

554

1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00

555

Warnings:

556

557

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

558

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

559

FROM t0,t1

560

LEFT JOIN

561

(

562

563

LEFT JOIN

564

(t3, t4)

565

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

566

567

LEFT JOIN

568

(

569

(t6, t7)

570

LEFT JOIN

571

572

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

573

)

574

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

575

)

576

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

577

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

578

(t1.a != 2)

579

WHERE t0.a=1 AND

580

t0.b=t1.b AND

581

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

582

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

583

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

584

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

585

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

586

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

587

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

588

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

589

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

590

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

591

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

592

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

593

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

594

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

595

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

596

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

597

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

598

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

599

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

600

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

601

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

602

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

603

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

604

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

605

EXPLAIN EXTENDED

606

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

607

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

608

FROM t0,t1

609

LEFT JOIN

610

(

611

612

LEFT JOIN

613

(t3, t4)

614

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

615

616

LEFT JOIN

617

(

618

(t6, t7)

619

LEFT JOIN

620

621

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

622

)

623

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

624

)

625

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

626

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

627

(t1.a != 2),

628

629

WHERE t0.a=1 AND

630

t0.b=t1.b AND

631

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

632

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

633

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

634

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

635

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

636

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

637

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

638

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

639

(t9.a=1);

640

id select_type table type possible_keys key key_len ref rows filtered Extra

641

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

642

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

643

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

644

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where

645

1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where

646

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

647

1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where

648

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

649

1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where

650

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

651

Warnings:

652

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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) 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)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) 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`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`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`)))

653

SELECT t9.a,t9.b

654

FROM t9;

655

a b

656

1 1

657

1 2

658

3 3

659

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

660

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

661

FROM t0,t1

662

LEFT JOIN

663

(

664

665

LEFT JOIN

666

(t3, t4)

667

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

668

669

LEFT JOIN

670

(

671

(t6, t7)

672

LEFT JOIN

673

674

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

675

)

676

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

677

)

678

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

679

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

680

(t1.a != 2),

681

682

WHERE t0.a=1 AND

683

t0.b=t1.b AND

684

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

685

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

686

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

687

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

688

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

689

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

690

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

691

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

692

(t9.a=1);

693

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

694

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

695

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

696

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

697

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

698

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

699

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

700

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

701

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

702

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

703

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

704

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

705

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

706

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

707

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

708

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

709

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

710

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

711

SELECT t1.a,t1.b

712

FROM t1;

713

a b

714

1 3

715

2 2

716

3 2

717

SELECT t2.a,t2.b

718

FROM t2;

719

a b

720

3 3

721

4 2

722

5 3

723

SELECT t3.a,t3.b

724

FROM t3;

725

a b

726

1 2

727

2 2

728

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

729

FROM t2

730

LEFT JOIN

731

732

ON t2.b=t3.b;

733

a b a b

734

3 3 NULL NULL

735

4 2 1 2

736

4 2 2 2

737

5 3 NULL NULL

738

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

739

FROM t1, t2

740

LEFT JOIN

741

742

ON t2.b=t3.b

743

WHERE t1.a <= 2;

744

a b a b a b

745

1 3 3 3 NULL NULL

746

2 2 3 3 NULL NULL

747

1 3 4 2 1 2

748

1 3 4 2 2 2

749

2 2 4 2 1 2

750

2 2 4 2 2 2

751

1 3 5 3 NULL NULL

752

2 2 5 3 NULL NULL

753

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

754

FROM t1, t3

755

RIGHT JOIN

756

757

ON t2.b=t3.b

758

WHERE t1.a <= 2;

759

a b a b a b

760

1 3 3 3 NULL NULL

761

2 2 3 3 NULL NULL

762

1 3 4 2 1 2

763

1 3 4 2 2 2

764

2 2 4 2 1 2

765

2 2 4 2 2 2

766

1 3 5 3 NULL NULL

767

2 2 5 3 NULL NULL

768

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

769

FROM t3,t4;

770

a b a b

771

1 2 3 2

772

2 2 3 2

773

1 2 4 2

774

2 2 4 2

775

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

776

FROM t2

777

LEFT JOIN

778

(t3, t4)

779

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

780

a b a b a b

781

3 3 NULL NULL NULL NULL

782

4 2 1 2 3 2

783

4 2 1 2 4 2

784

5 3 NULL NULL NULL NULL

785

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

786

FROM t1, t2

787

LEFT JOIN

788

(t3, t4)

789

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

790

WHERE t1.a <= 2;

791

a b a b a b a b

792

1 3 3 3 NULL NULL NULL NULL

793

2 2 3 3 NULL NULL NULL NULL

794

1 3 4 2 1 2 3 2

795

1 3 4 2 1 2 4 2

796

2 2 4 2 1 2 3 2

797

2 2 4 2 1 2 4 2

798

1 3 5 3 NULL NULL NULL NULL

799

2 2 5 3 NULL NULL NULL NULL

800

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

801

FROM t1, (t3, t4)

802

RIGHT JOIN

803

804

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

805

WHERE t1.a <= 2;

806

a b a b a b a b

807

1 3 3 3 NULL NULL NULL NULL

808

2 2 3 3 NULL NULL NULL NULL

809

1 3 4 2 1 2 3 2

810

1 3 4 2 1 2 4 2

811

2 2 4 2 1 2 3 2

812

2 2 4 2 1 2 4 2

813

1 3 5 3 NULL NULL NULL NULL

814

2 2 5 3 NULL NULL NULL NULL

815

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

816

FROM t1, (t3, t4)

817

RIGHT JOIN

818

819

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

820

WHERE t1.a <= 2;

821

a b a b a b a b

822

1 3 3 3 NULL NULL NULL NULL

823

2 2 3 3 NULL NULL NULL NULL

824

1 3 4 2 1 2 3 2

825

1 3 4 2 1 2 4 2

826

2 2 4 2 1 2 3 2

827

2 2 4 2 1 2 4 2

828

1 3 5 3 NULL NULL NULL NULL

829

2 2 5 3 NULL NULL NULL NULL

830

EXPLAIN EXTENDED

831

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

832

FROM t1, (t3, t4)

833

RIGHT JOIN

834

835

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

836

WHERE t1.a <= 2;

837

id select_type table type possible_keys key key_len ref rows filtered Extra

838

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

839

1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer

840

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00

841

1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00

842

Warnings:

843

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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) where (`test`.`t1`.`a` <= 2)

844

CREATE INDEX idx_b ON t2(b);

845

EXPLAIN EXTENDED

846

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

847

FROM (t3,t4)

848

LEFT JOIN

849

(t1,t2)

850

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

851

id select_type table type possible_keys key key_len ref rows filtered Extra

852

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00

853

1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer

854

1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00

855

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00

856

Warnings:

857

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`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1

858

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

859

FROM (t3,t4)

860

LEFT JOIN

861

(t1,t2)

862

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

863

a b a b a b

864

4 2 1 2 3 2

865

4 2 1 2 3 2

866

4 2 1 2 3 2

867

NULL NULL 2 2 3 2

868

4 2 1 2 4 2

869

4 2 1 2 4 2

870

4 2 1 2 4 2

871

NULL NULL 2 2 4 2

872

EXPLAIN EXTENDED

873

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

874

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

875

FROM t0,t1

876

LEFT JOIN

877

(

878

879

LEFT JOIN

880

(t3, t4)

881

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

882

883

LEFT JOIN

884

(

885

(t6, t7)

886

LEFT JOIN

887

888

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

889

)

890

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

891

)

892

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

893

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

894

(t1.a != 2),

895

896

WHERE t0.a=1 AND

897

t0.b=t1.b AND

898

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

899

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

900

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

901

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

902

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

903

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

904

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

905

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

906

(t9.a=1);

907

id select_type table type possible_keys key key_len ref rows filtered Extra

908

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

909

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

910

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

911

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where

912

1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where

913

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

914

1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where

915

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

916

1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where

917

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

918

Warnings:

919

920

CREATE INDEX idx_b ON t4(b);

921

CREATE INDEX idx_b ON t5(b);

922

EXPLAIN EXTENDED

923

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

924

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

925

FROM t0,t1

926

LEFT JOIN

927

(

928

929

LEFT JOIN

930

(t3, t4)

931

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

932

933

LEFT JOIN

934

(

935

(t6, t7)

936

LEFT JOIN

937

938

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

939

)

940

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

941

)

942

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

943

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

944

(t1.a != 2),

945

946

WHERE t0.a=1 AND

947

t0.b=t1.b AND

948

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

949

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

950

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

951

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

952

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

953

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

954

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

955

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

956

(t9.a=1);

957

id select_type table type possible_keys key key_len ref rows filtered Extra

958

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

959

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

960

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

961

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where

962

1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where

963

1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where

964

1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where

965

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

966

1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where

967

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

968

Warnings:

969

970

CREATE INDEX idx_b ON t8(b);

971

EXPLAIN EXTENDED

972

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

973

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

974

FROM t0,t1

975

LEFT JOIN

976

(

977

978

LEFT JOIN

979

(t3, t4)

980

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

981

982

LEFT JOIN

983

(

984

(t6, t7)

985

LEFT JOIN

986

987

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

988

)

989

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

990

)

991

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

992

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

993

(t1.a != 2),

994

995

WHERE t0.a=1 AND

996

t0.b=t1.b AND

997

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

998

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

999

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

1000

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

1001

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

1002

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

1003

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

1004

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

1005

(t9.a=1);

1006

id select_type table type possible_keys key key_len ref rows filtered Extra

1007

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

1008

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

1009

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

1010

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where

1011

1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where

1012

1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where

1013

1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where

1014

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

1015

1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where

1016

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

1017

Warnings:

1018

1019

CREATE INDEX idx_b ON t1(b);

1020

CREATE INDEX idx_a ON t0(a);

1021

EXPLAIN EXTENDED

1022

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

1023

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

1024

FROM t0,t1

1025

LEFT JOIN

1026

(

1027

1028

LEFT JOIN

1029

(t3, t4)

1030

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

1031

1032

LEFT JOIN

1033

(

1034

(t6, t7)

1035

LEFT JOIN

1036

1037

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

1038

)

1039

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

1040

)

1041

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

1042

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

1043

(t1.a != 2),

1044

1045

WHERE t0.a=1 AND

1046

t0.b=t1.b AND

1047

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

1048

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

1049

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

1050

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

1051

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

1052

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

1053

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

1054

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

1055

(t9.a=1);

1056

id select_type table type possible_keys key key_len ref rows filtered Extra

1057

1 SIMPLE t0 ref idx_a idx_a 5 const 2 100.00

1058

1 SIMPLE t1 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer

1059

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

1060

1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where

1061

1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where

1062

1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where

1063

1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where

1064

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

1065

1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where

1066

1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

1067

Warnings:

1068

1069

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

1070

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

1071

FROM t0,t1

1072

LEFT JOIN

1073

(

1074

1075

LEFT JOIN

1076

(t3, t4)

1077

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

1078

1079

LEFT JOIN

1080

(

1081

(t6, t7)

1082

LEFT JOIN

1083

1084

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

1085

)

1086

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

1087

)

1088

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

1089

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

1090

(t1.a != 2),

1091

1092

WHERE t0.a=1 AND

1093

t0.b=t1.b AND

1094

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

1095

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

1096

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

1097

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

1098

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

1099

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

1100

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

1101

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

1102

(t9.a=1);

1103

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

1104

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

1105

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

1106

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

1107

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

1108

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

1109

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

1110

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

1111

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

1112

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

1113

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

1114

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

1115

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

1116

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

1117

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

1118

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

1119

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

1120

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

1121

SELECT t2.a,t2.b

1122

FROM t2;

1123

a b

1124

3 3

1125

4 2

1126

5 3

1127

SELECT t3.a,t3.b

1128

FROM t3;

1129

a b

1130

1 2

1131

2 2

1132

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

1133

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

1134

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

1135

a b a b

1136

4 2 1 2

1137

4 2 2 2

1138

5 3 NULL NULL

1139

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

1140

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

1141

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

1142

a b a b

1143

4 2 1 2

1144

4 2 2 2

1145

5 3 NULL NULL

1146

ALTER TABLE t3

1147

CHANGE COLUMN a a1 int,

1148

CHANGE COLUMN c c1 int;

1149

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

1150

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

1151

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

1152

a b a1 b

1153

4 2 1 2

1154

4 2 2 2

1155

5 3 NULL NULL

1156

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

1157

FROM t2 NATURAL LEFT JOIN t3

1158

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

1159

a b a1 b

1160

4 2 1 2

1161

4 2 2 2

1162

5 3 NULL NULL

1163

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

1164

CREATE TABLE t1 (a int);

1165

CREATE TABLE t2 (a int);

1166

CREATE TABLE t3 (a int);

1167

INSERT INTO t1 VALUES (1);

1168

INSERT INTO t2 VALUES (2);

1169

INSERT INTO t3 VALUES (2);

1170

INSERT INTO t1 VALUES (2);

1171

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

1172

a a a

1173

1 NULL NULL

1174

2 2 2

1175

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

1176

a a a

1177

1 NULL NULL

1178

2 2 2

1179

DELETE FROM t1 WHERE a=2;

1180

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

1181

a a a

1182

1 NULL NULL

1183

DELETE FROM t2;

1184

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

1185

a a a

1186

1 NULL NULL

1187

DROP TABLE t1,t2,t3;

1188

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

1189

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

1190

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

1191

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

1192

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

1193

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

1194

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

1195

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

1196

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

1197

id select_type table type possible_keys key key_len ref rows Extra

1198

1 SIMPLE t1 index NULL a 5 NULL 21 Using index

1199

1 SIMPLE t3 index c c 5 NULL 6 Using index

1200

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

1201

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

1202

id select_type table type possible_keys key key_len ref rows Extra

1203

1 SIMPLE t1 index NULL a 5 NULL 21 Using index

1204

1 SIMPLE t3 index c c 5 NULL 6 Using index

1205

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

1206

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

1207

a b c

1208

NULL 0 0

1209

NULL 1 1

1210

NULL 2 2

1211

0 0 0

1212

0 1 1

1213

0 2 2

1214

1 0 0

1215

1 1 1

1216

1 2 2

1217

2 0 0

1218

2 1 1

1219

2 2 2

1220

3 0 0

1221

3 1 1

1222

3 2 2

1223

4 0 0

1224

4 1 1

1225

4 2 2

1226

5 0 0

1227

5 1 1

1228

5 2 2

1229

6 0 0

1230

6 1 1

1231

6 2 2

1232

7 0 0

1233

7 1 1

1234

7 2 2

1235

8 0 0

1236

8 1 1

1237

8 2 2

1238

9 0 0

1239

9 1 1

1240

9 2 2

1241

10 0 0

1242

10 1 1

1243

10 2 2

1244

11 0 0

1245

11 1 1

1246

11 2 2

1247

12 0 0

1248

12 1 1

1249

12 2 2

1250

13 0 0

1251

13 1 1

1252

13 2 2

1253

14 0 0

1254

14 1 1

1255

14 2 2

1256

15 0 0

1257

15 1 1

1258

15 2 2

1259

16 0 0

1260

16 1 1

1261

16 2 2

1262

17 0 0

1263

17 1 1

1264

17 2 2

1265

18 0 0

1266

18 1 1

1267

18 2 2

1268

19 0 0

1269

19 1 1

1270

19 2 2

1271

DELETE FROM t3;

1272

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

1273

id select_type table type possible_keys key key_len ref rows Extra

1274

1 SIMPLE t1 index NULL a 5 NULL 21 Using index

1275

1 SIMPLE t3 index c c 5 NULL 6 Using index

1276

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

1277

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

1278

a b c

1279

NULL NULL NULL

1280

0 NULL NULL

1281

1 NULL NULL

1282

2 NULL NULL

1283

3 NULL NULL

1284

4 NULL NULL

1285

5 NULL NULL

1286

6 NULL NULL

1287

7 NULL NULL

1288

8 NULL NULL

1289

9 NULL NULL

1290

10 NULL NULL

1291

11 NULL NULL

1292

12 NULL NULL

1293

13 NULL NULL

1294

14 NULL NULL

1295

15 NULL NULL

1296

16 NULL NULL

1297

17 NULL NULL

1298

18 NULL NULL

1299

19 NULL NULL

1300

DROP TABLE t1,t2,t3;

1301

CREATE TABLE t1 (c11 int);

1302

CREATE TABLE t2 (c21 int);

1303

CREATE TABLE t3 (c31 int);

1304

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

1305

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

1306

c11 c21

1307

4 NULL

1308

5 NULL

1309

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

1310

id select_type table type possible_keys key key_len ref rows Extra

1311

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

1312

1 SIMPLE t1 ALL NULL NULL NULL NULL 2

1313

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

1314

c11 c21 c31

1315

4 NULL NULL

1316

5 NULL NULL

1317

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

1318

id select_type table type possible_keys key key_len ref rows Extra

1319

1 SIMPLE t1 ALL NULL NULL NULL NULL 2

1320

1 SIMPLE t2 ALL NULL NULL NULL NULL 0

1321

1 SIMPLE t3 ALL NULL NULL NULL NULL 0

1322

DROP TABLE t1,t2,t3;

1323

create table t1 (a int);

1324

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

1325

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

1326

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

1327

create table t3 like t2;

1328

insert into t3 select * from t2;

1329

explain select * from t1 left join

1330

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

1331

on (t1.a = t2.a);

1332

id select_type table type possible_keys key key_len ref rows Extra

1333

1 SIMPLE t1 ALL NULL NULL NULL NULL 10

1334

1 SIMPLE t2 ref a a 5 test.t1.a 11

1335

1 SIMPLE t3 ref a a 5 test.t2.a 11

1336

drop table t1, t2, t3;

1337

CREATE TABLE t1 (

1338

id int NOT NULL PRIMARY KEY,

1339

ct int DEFAULT NULL,

1340

pc int DEFAULT NULL,

1341

INDEX idx_ct (ct),

1342

INDEX idx_pc (pc)

1343

);

1344

INSERT INTO t1 VALUES

1345

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

1346

CREATE TABLE t2 (

1347

id int NOT NULL PRIMARY KEY,

1348

sr int NOT NULL,

1349

nm varchar(255) NOT NULL,

1350

INDEX idx_sr (sr)

1351

);

1352

INSERT INTO t2 VALUES

1353

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

1354

CREATE TABLE t3 (

1355

id int NOT NULL PRIMARY KEY,

1356

ct int NOT NULL,

1357

ln int NOT NULL,

1358

INDEX idx_ct (ct),

1359

INDEX idx_ln (ln)

1360

);

1361

CREATE TABLE t4 (

1362

id int NOT NULL PRIMARY KEY,

1363

nm varchar(255) NOT NULL

1364

);

1365

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

1366

SELECT t1.*

1367

FROM t1 LEFT JOIN

1368

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

1369

WHERE t1.id='5';

1370

id ct pc

1371

5 NULL NULL

1372

SELECT t1.*, t4.nm

1373

FROM t1 LEFT JOIN

1374

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

1375

LEFT JOIN t4 ON t2.sr=t4.id

1376

WHERE t1.id='5';

1377

id ct pc nm

1378

5 NULL NULL NULL

1379

DROP TABLE t1,t2,t3,t4;

1380

CREATE TABLE t1 (a INT, b INT);

1381

CREATE TABLE t2 (a INT);

1382

CREATE TABLE t3 (a INT, c INT);

1383

CREATE TABLE t4 (a INT, c INT);

1384

CREATE TABLE t5 (a INT, c INT);

1385

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

1386

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

1387

1388

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

1389

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

1390

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

1391

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

1392

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

1393

1394

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

1395

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

1396

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

1397

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

1398

CREATE TABLE t1 (a INT, b INT);

1399

CREATE TABLE t2 (a INT, b INT);

1400

CREATE TABLE t3 (a INT, b INT);

1401

INSERT INTO t1 VALUES (1,1);

1402

INSERT INTO t2 VALUES (1,1);

1403

INSERT INTO t3 VALUES (1,1);

1404

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

1405

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

1406

DROP TABLE t1,t2,t3;

1407

CREATE TABLE t1 (

1408

carrier char(2) default NULL,

1409

id int NOT NULL auto_increment PRIMARY KEY

1410

);

1411

INSERT INTO t1 VALUES

1412

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

1413

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

1414

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

1415

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

1416

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

1417

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

1418

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

1419

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

1420

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

1421

CREATE TABLE t2 (

1422

scan_date date default NULL,

1423

package_id int default NULL,

1424

INDEX scan_date(scan_date),

1425

INDEX package_id(package_id)

1426

);

1427

INSERT INTO t2 VALUES

1428

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

1429

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

1430

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

1431

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

1432

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

1433

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

1434

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

1435

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

1436

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

1437

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

1438

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

1439

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

1440

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

1441

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

1442

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

1443

CREATE TABLE t3 (

1444

package_id int default NULL,

1445

INDEX package_id(package_id)

1446

);

1447

INSERT INTO t3 VALUES

1448

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

1449

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

1450

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

1451

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

1452

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

1453

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

1454

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

1455

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

1456

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

1457

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

1458

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

1459

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

1460

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

1461

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

1462

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

1463

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

1464

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

1465

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

1466

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

1467

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

1468

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

1469

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

1470

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

1471

CREATE TABLE t4 (

1472

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

1473

id int default NULL,

1474

INDEX id(id)

1475

);

1476

INSERT INTO t4 VALUES

1477

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

1478

CREATE TABLE t5 (

1479

carrier_id int default NULL,

1480

INDEX carrier_id(carrier_id)

1481

);

1482

INSERT INTO t5 VALUES

1483

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

1484

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

1485

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

1486

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

1487

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

1488

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

1489

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

1490

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

1491

SELECT COUNT(*)

1492

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

1493

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

1494

COUNT(*)

1495

1496

EXPLAIN

1497

SELECT COUNT(*)

1498

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

1499

JOIN t3 ON t3.package_id = t1.id)

1500

LEFT JOIN

1501

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

1502

ON t4.carrier = t1.carrier;

1503

id select_type table type possible_keys key key_len ref rows Extra

1504

1 SIMPLE t2 index package_id package_id 5 NULL 45 Using index

1505

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1

1506

1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 10 test.t1.carrier 1

1507

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

1508

1 SIMPLE t3 ref package_id package_id 5 test.t1.id 12 Using where; Using index

1509

SELECT COUNT(*)

1510

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

1511

JOIN t3 ON t3.package_id = t1.id)

1512

LEFT JOIN

1513

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

1514

ON t4.carrier = t1.carrier;

1515

COUNT(*)

1516

1517

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

1518

End of 5.0 tests

Older »