~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

INSERT INTO t1 VALUES (3,NULL,0,50),(3,NULL,0,200),(3,NULL,0,25),(3,NULL,0,84676),(3,NULL,0,235),(3,NULL,0,10),(3,NULL,0,3098),(3,NULL,0,2947),(3,NULL,0,8987),(3,NULL,0,8347654),(3,NULL,0,20398),(3,NULL,0,8976),(3,NULL,0,500),(3,NULL,0,198);

#1st select shows that one record is returned with null entries for the right

#table, when selecting on an id that does not exist in the right table t2

SELECT t1.usr_id,t1.uniq_id,t1.increment,

t2.usr2_id,t2.c_amount,t2.max

FROM t1

LEFT JOIN t2 ON t2.id = t1.uniq_id

WHERE t1.uniq_id = 4

ORDER BY t2.c_amount;

# The same with RIGHT JOIN

SELECT t1.usr_id,t1.uniq_id,t1.increment,

t2.usr2_id,t2.c_amount,t2.max

FROM t2

RIGHT JOIN t1 ON t2.id = t1.uniq_id

WHERE t1.uniq_id = 4

ORDER BY t2.c_amount;

100

101

INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');

102

--error ER_DUP_ENTRY

103

INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');

104

INSERT INTO t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');

105

106

#3rd select should show that one record is returned with null entries for the

107

# right table, when selecting on an id that does not exist in the right table

108

# t2 but this select returns an empty set!!!!

109

SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 ORDER BY t2.c_amount;

110

SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 GROUP BY t2.c_amount;

111

# Removing the ORDER BY works:

112

SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4;

113

114

drop table t1,t2;

115

116

117

# Test of LEFT JOIN with const tables (failed for frankie@etsetb.upc.es)

118

119

120

CREATE TABLE t1 (

121

cod_asig int(11) DEFAULT '0' NOT NULL,

122

desc_larga_cat varchar(80) DEFAULT '' NOT NULL,

123

desc_larga_cas varchar(80) DEFAULT '' NOT NULL,

124

desc_corta_cat varchar(40) DEFAULT '' NOT NULL,

125

desc_corta_cas varchar(40) DEFAULT '' NOT NULL,

126

cred_total double(3,1) DEFAULT '0.0' NOT NULL,

127

pre_requisit int(11),

128

co_requisit int(11),

129

preco_requisit int(11),

130

PRIMARY KEY (cod_asig)

131

);

132

133

INSERT INTO t1 VALUES (10360,'asdfggfg','Introduccion a los Ordenadores I','asdfggfg','Introduccio Ordinadors I',6.0,NULL,NULL,NULL);

134

INSERT INTO t1 VALUES (10361,'Components i Circuits Electronics I','Componentes y Circuitos Electronicos I','Components i Circuits Electronics I','Comp. i Circ. Electr. I',6.0,NULL,NULL,NULL);

135

INSERT INTO t1 VALUES (10362,'Laboratori d`Ordinadors','Laboratorio de Ordenadores','Laboratori d`Ordinadors','Laboratori Ordinadors',4.5,NULL,NULL,NULL);

136

INSERT INTO t1 VALUES (10363,'Tecniques de Comunicacio Oral i Escrita','Tecnicas de Comunicacion Oral y Escrita','Tecniques de Comunicacio Oral i Escrita','Tec. Com. Oral i Escrita',4.5,NULL,NULL,NULL);

137

INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);

138

INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);

139

INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);

140

INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);

141

142

CREATE TABLE t2 (

143

idAssignatura int(11) DEFAULT '0' NOT NULL,

144

Grup int(11) DEFAULT '0' NOT NULL,

145

Places smallint(6) DEFAULT '0' NOT NULL,

146

PlacesOcupades int(11) DEFAULT '0',

147

PRIMARY KEY (idAssignatura,Grup)

148

);

149

150

151

INSERT INTO t2 VALUES (10360,12,333,0);

152

INSERT INTO t2 VALUES (10361,30,2,0);

153

INSERT INTO t2 VALUES (10361,40,3,0);

154

INSERT INTO t2 VALUES (10360,45,10,0);

155

INSERT INTO t2 VALUES (10362,10,12,0);

156

INSERT INTO t2 VALUES (10360,55,2,0);

157

INSERT INTO t2 VALUES (10360,70,0,0);

158

INSERT INTO t2 VALUES (10360,565656,0,0);

159

INSERT INTO t2 VALUES (10360,32767,7,0);

160

INSERT INTO t2 VALUES (10360,33,8,0);

161

INSERT INTO t2 VALUES (10360,7887,85,0);

162

INSERT INTO t2 VALUES (11405,88,8,0);

163

INSERT INTO t2 VALUES (10360,0,55,0);

164

INSERT INTO t2 VALUES (10360,99,0,0);

165

INSERT INTO t2 VALUES (11411,30,10,0);

166

INSERT INTO t2 VALUES (11404,0,0,0);

167

INSERT INTO t2 VALUES (10362,11,111,0);

168

INSERT INTO t2 VALUES (10363,33,333,0);

169

INSERT INTO t2 VALUES (11412,55,0,0);

170

INSERT INTO t2 VALUES (50003,66,6,0);

171

INSERT INTO t2 VALUES (11403,5,0,0);

172

INSERT INTO t2 VALUES (11406,11,11,0);

173

INSERT INTO t2 VALUES (11410,11410,131,0);

174

INSERT INTO t2 VALUES (11416,11416,32767,0);

175

INSERT INTO t2 VALUES (11409,0,0,0);

176

177

CREATE TABLE t3 (

178

id int(11) NOT NULL auto_increment,

179

dni_pasaporte char(16) DEFAULT '' NOT NULL,

180

idPla int(11) DEFAULT '0' NOT NULL,

181

cod_asig int(11) DEFAULT '0' NOT NULL,

182

any smallint(6) DEFAULT '0' NOT NULL,

183

quatrimestre smallint(6) DEFAULT '0' NOT NULL,

184

estat char(1) DEFAULT 'M' NOT NULL,

185

PRIMARY KEY (id),

186

UNIQUE dni_pasaporte (dni_pasaporte,idPla),

187

UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre)

188

);

189

190

INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');

191

192

CREATE TABLE t4 (

193

id int(11) NOT NULL auto_increment,

194

papa int(11) DEFAULT '0' NOT NULL,

195

fill int(11) DEFAULT '0' NOT NULL,

196

idPla int(11) DEFAULT '0' NOT NULL,

197

PRIMARY KEY (id),

198

KEY papa (idPla,papa),

199

UNIQUE papa_2 (idPla,papa,fill)

200

);

201

202

INSERT INTO t4 VALUES (1,-1,10360,1);

203

INSERT INTO t4 VALUES (2,-1,10361,1);

204

INSERT INTO t4 VALUES (3,-1,10362,1);

205

206

SELECT DISTINCT fill,desc_larga_cat,cred_total,Grup,Places,PlacesOcupades FROM t4 LEFT JOIN t3 ON t3.cod_asig=fill AND estat='S' AND dni_pasaporte='11111111' AND t3.idPla=1 , t2,t1 WHERE fill=t1.cod_asig AND Places>PlacesOcupades AND fill=idAssignatura AND t4.idPla=1 AND papa=-1;

207

208

SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ;

209

210

INSERT INTO t3 VALUES (3,'1234',1,10360,98,1,'S');

211

SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ;

212

213

drop table t1,t2,t3,test.t4;

214

215

216

# Test of IS NULL on AUTO_INCREMENT with LEFT JOIN

217

218

219

CREATE TABLE t1 (

220

id smallint(5) unsigned NOT NULL auto_increment,

221

name char(60) DEFAULT '' NOT NULL,

222

PRIMARY KEY (id)

223

);

224

INSERT INTO t1 VALUES (1,'Antonio Paz');

225

INSERT INTO t1 VALUES (2,'Lilliana Angelovska');

226

INSERT INTO t1 VALUES (3,'Thimble Smith');

227

228

CREATE TABLE t2 (

229

id smallint(5) unsigned NOT NULL auto_increment,

230

owner smallint(5) unsigned DEFAULT '0' NOT NULL,

231

name char(60),

232

PRIMARY KEY (id)

233

);

234

INSERT INTO t2 VALUES (1,1,'El Gato');

235

INSERT INTO t2 VALUES (2,1,'Perrito');

236

INSERT INTO t2 VALUES (3,3,'Happy');

237

238

--sorted_result

239

select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner);

240

select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;

241

explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;

242

explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;

243

select count(*) from t1 left join t2 on (t1.id = t2.owner);

244

245

--sorted_result

246

select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner);

247

select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;

248

explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;

249

explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;

250

select count(*) from t2 right join t1 on (t1.id = t2.owner);

251

252

--sorted_result

253

select t1.name, t2.name, t2.id,t3.id from t2 right join t1 on (t1.id = t2.owner) left join t1 as t3 on t3.id=t2.owner;

254

select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;

255

select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;

256

257

drop table t1,t2;

258

259

create table t1 (id int not null, str char(10), index(str));

260

insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");

261

select * from t1 where str is not null order by id;

262

select * from t1 where str is null;

263

drop table t1;

264

265

266

# Test wrong LEFT JOIN query

267

268

269

CREATE TABLE t1 (

270

t1_id bigint(21) NOT NULL auto_increment,

271

PRIMARY KEY (t1_id)

272

);

273

CREATE TABLE t2 (

274

t2_id bigint(21) NOT NULL auto_increment,

275

PRIMARY KEY (t2_id)

276

);

277

CREATE TABLE t3 (

278

t3_id bigint(21) NOT NULL auto_increment,

279

PRIMARY KEY (t3_id)

280

);

281

CREATE TABLE t4 (

282

seq_0_id bigint(21) DEFAULT '0' NOT NULL,

283

seq_1_id bigint(21) DEFAULT '0' NOT NULL,

284

KEY seq_0_id (seq_0_id),

285

KEY seq_1_id (seq_1_id)

286

);

287

CREATE TABLE t5 (

288

seq_0_id bigint(21) DEFAULT '0' NOT NULL,

289

seq_1_id bigint(21) DEFAULT '0' NOT NULL,

290

KEY seq_1_id (seq_1_id),

291

KEY seq_0_id (seq_0_id)

292

);

293

294

insert into t1 values (1);

295

insert into t2 values (1);

296

insert into t3 values (1);

297

insert into t4 values (1,1);

298

insert into t5 values (1,1);

299

300

--error 1054

301

explain select * from t3 left join t4 on t4.seq_1_id = t2.t2_id left join t1 on t1.t1_id = t4.seq_0_id left join t5 on t5.seq_0_id = t1.t1_id left join t2 on t2.t2_id = t5.seq_1_id where t3.t3_id = 23;

302

303

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

304

305

306

# Another LEFT JOIN problem

307

# (The problem was that the result changed when we added ORDER BY)

308

309

310

create table t1 (n int, m int, o int, key(n));

311

create table t2 (n int not null, m int, o int, primary key(n));

312

insert into t1 values (1, 2, 11), (1, 2, 7), (2, 2, 8), (1,2,9),(1,3,9);

313

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

314

select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and

315

t1.m = t2.m where t1.n = 1;

316

select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and

317

t1.m = t2.m where t1.n = 1 order by t1.o,t1.m;

318

drop table t1,t2;

319

320

# Test bug with NATURAL join:

321

322

CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);

323

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

324

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

325

INSERT INTO t1 VALUES (3,'c',2);

326

327

CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));

328

INSERT INTO t2 VALUES (1,'x');

329

INSERT INTO t2 VALUES (2,'y');

330

INSERT INTO t2 VALUES (3,'z');

331

332

SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;

333

SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;

334

335

drop table t1,t2;

336

337

create table t1 ( color varchar(20), name varchar(20) );

338

insert into t1 values ( 'red', 'apple' );

339

insert into t1 values ( 'yellow', 'banana' );

340

insert into t1 values ( 'green', 'lime' );

341

insert into t1 values ( 'black', 'grape' );

342

insert into t1 values ( 'blue', 'blueberry' );

343

create table t2 ( count int, color varchar(20) );

344

insert into t2 values (10, 'green');

345

insert into t2 values (5, 'black');

346

insert into t2 values (15, 'white');

347

insert into t2 values (7, 'green');

348

select * from t1;

349

select * from t2;

350

select * from t2 natural join t1;

351

select t2.count, t1.name from t2 natural join t1;

352

select t2.count, t1.name from t2 inner join t1 using (color);

353

drop table t1;

354

drop table t2;

355

356

357

# Test of LEFT JOIN + GROUP FUNCTIONS within functions:

358

359

360

CREATE TABLE t1 (

361

pcode varchar(8) DEFAULT '' NOT NULL

362

);

363

INSERT INTO t1 VALUES ('kvw2000'),('kvw2001'),('kvw3000'),('kvw3001'),('kvw3002'),('kvw3500'),('kvw3501'),('kvw3502'),('kvw3800'),('kvw3801'),('kvw3802'),('kvw3900'),('kvw3901'),('kvw3902'),('kvw4000'),('kvw4001'),('kvw4002'),('kvw4200'),('kvw4500'),('kvw5000'),('kvw5001'),('kvw5500'),('kvw5510'),('kvw5600'),('kvw5601'),('kvw6000'),('klw1000'),('klw1020'),('klw1500'),('klw2000'),('klw2001'),('klw2002'),('kld2000'),('klw2500'),('kmw1000'),('kmw1500'),('kmw2000'),('kmw2001'),('kmw2100'),('kmw3000'),('kmw3200');

364

CREATE TABLE t2 (

365

pcode varchar(8) DEFAULT '' NOT NULL,

366

KEY pcode (pcode)

367

);

368

INSERT INTO t2 VALUES ('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw6000'),('kvw6000'),('kld2000');

369

370

SELECT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1

371

LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;

372

SELECT SQL_BIG_RESULT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1 LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;

373

drop table t1,t2;

374

375

376

# Another left join problem

377

378

379

CREATE TABLE t1 (

380

id int(11),

381

pid int(11),

382

rep_del tinyint(4),

383

KEY id (id),

384

KEY pid (pid)

385

);

386

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

387

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

388

select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;

389

create index rep_del ON t1(rep_del);

390

select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;

391

drop table t1;

392

393

CREATE TABLE t1 (

394

id int(11) DEFAULT '0' NOT NULL,

395

name tinytext DEFAULT '' NOT NULL,

396

UNIQUE id (id)

397

);

398

INSERT INTO t1 VALUES (1,'yes'),(2,'no');

399

CREATE TABLE t2 (

400

id int(11) DEFAULT '0' NOT NULL,

401

idx int(11) DEFAULT '0' NOT NULL,

402

UNIQUE id (id,idx)

403

);

404

INSERT INTO t2 VALUES (1,1);

405

explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;

406

SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;

407

drop table t1,t2;

408

409

410

# Test problem with using key_column= constant in ON and WHERE

411

412

create table t1 (bug_id mediumint, reporter mediumint);

413

create table t2 (bug_id mediumint, who mediumint, index(who));

414

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

415

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

416

SELECT * FROM t1 LEFT JOIN t2 ON (t1.bug_id = t2.bug_id AND t2.who = 2) WHERE (t1.reporter = 2 OR t2.who = 2);

417

drop table t1,t2;

418

419

420

# Test problem with LEFT JOIN

421

422

create table t1 (fooID smallint unsigned auto_increment, primary key (fooID));

423

create table t2 (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID));

424

insert into t1 (fooID) values (10),(20),(30);

425

insert into t2 values (10,1),(20,2),(30,3);

426

explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;

427

select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;

428

--sorted_result

429

select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;

430

drop table t1,t2;

431

432

create table t1 (i int);

433

create table t2 (i int);

434

create table t3 (i int);

435

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

436

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

437

insert into t3 values(2),(4);

438

--sorted_result

439

select * from t1 natural left join t2 natural left join t3;

440

select * from t1 natural left join t2 where (t2.i is not null)=0;

441

--sorted_result

442

select * from t1 natural left join t2 where (t2.i is not null) is not null;

443

select * from t1 natural left join t2 where (i is not null)=0;

444

--sorted_result

445

select * from t1 natural left join t2 where (i is not null) is not null;

446

drop table t1,t2,t3;

447

448

449

# Test of USING

450

451

create table t1 (f1 integer,f2 integer,f3 integer);

452

create table t2 (f2 integer,f4 integer);

453

create table t3 (f3 integer,f5 integer);

454

select * from t1

455

left outer join t2 using (f2)

456

left outer join t3 using (f3);

457

drop table t1,t2,t3;

458

459

create table t1 (a1 int, a2 int);

460

create table t2 (b1 int not null, b2 int);

461

create table t3 (c1 int, c2 int);

462

463

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

464

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

465

insert into t3 values (2,4), (3,4);

466

467

select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;

468

explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;

469

470

drop table t1, t2, t3;

471

472

# Test for BUG#8711 '<=>' was considered to be a NULL-rejecting predicate.

473

create table t1 (

474

a int(11),

475

b char(10),

476

key (a)

477

);

478

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

479

create table t2 (a int);

480

481

select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a);

482

select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a);

483

drop table t1,t2;

484

485

# Test for BUG#5088

486

487

create table t1 (

488

match_id tinyint(3) unsigned not null auto_increment,

489

home tinyint(3) unsigned default '0',

490

unique key match_id (match_id),

491

key match_id_2 (match_id)

492

);

493

494

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

495

496

create table t2 (

497

player_id tinyint(3) unsigned default '0',

498

match_1_h tinyint(3) unsigned default '0',

499

key player_id (player_id)

500

);

501

502

insert into t2 values("1", "5");

503

insert into t2 values("2", "9");

504

insert into t2 values("3", "3");

505

insert into t2 values("4", "7");

506

insert into t2 values("5", "6");

507

insert into t2 values("6", "8");

508

insert into t2 values("7", "4");

509

insert into t2 values("8", "12");

510

insert into t2 values("9", "11");

511

insert into t2 values("10", "10");

512

513

explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from

514

(t2 s left join t1 m on m.match_id = 1)

515

order by m.match_id desc;

516

517

explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from

518

(t2 s left join t1 m on m.match_id = 1)

519

order by UUX desc;

520

521

select s.*, '*', m.*, (s.match_1_h - m.home) UUX from

522

(t2 s left join t1 m on m.match_id = 1)

523

order by UUX desc;

524

525

explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from

526

t2 s straight_join t1 m where m.match_id = 1

527

order by UUX desc;

528

529

select s.*, '*', m.*, (s.match_1_h - m.home) UUX from

530

t2 s straight_join t1 m where m.match_id = 1

531

order by UUX desc;

532

533

drop table t1, t2;

534

535

# Tests for bugs #6307 and 6460

536

537

create table t1 (a int, b int, unique index idx (a, b));

538

create table t2 (a int, b int, c int, unique index idx (a, b));

539

540

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

541

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

542

543

select t1.a, t1.b, t2.c from t1 left join t2

544

on t1.a=t2.a and t1.b=t2.b and t2.c=3

545

where t1.a=1 and t2.c is null;

546

547

drop table t1, t2;

548

549

CREATE TABLE t1 (

550

ts_id bigint(20) default NULL,

551

inst_id tinyint(4) default NULL,

552

flag_name varchar(64) default NULL,

553

flag_value text,

554

UNIQUE KEY ts_id (ts_id,inst_id,flag_name)

555

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

556

557

CREATE TABLE t2 (

558

ts_id bigint(20) default NULL,

559

inst_id tinyint(4) default NULL,

560

flag_name varchar(64) default NULL,

561

flag_value text,

562

UNIQUE KEY ts_id (ts_id,inst_id,flag_name)

563

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

564

565

INSERT INTO t1 VALUES

566

(111056548820001, 0, 'flag1', NULL),

567

(111056548820001, 0, 'flag2', NULL),

568

(2, 0, 'other_flag', NULL);

569

570

INSERT INTO t2 VALUES

571

(111056548820001, 3, 'flag1', 'sss');

572

573

SELECT t1.flag_name,t2.flag_value

574

FROM t1 LEFT JOIN t2

575

ON (t1.ts_id = t2.ts_id AND t1.flag_name = t2.flag_name AND

576

t2.inst_id = 3)

577

WHERE t1.inst_id = 0 AND t1.ts_id=111056548820001 AND

578

t2.flag_value IS NULL;

579

580

DROP TABLE t1,t2;

581

582

CREATE TABLE t1 (

583

id int(11) unsigned NOT NULL auto_increment,

584

text_id int(10) unsigned default NULL,

585

PRIMARY KEY (id)

586

);

587

588

INSERT INTO t1 VALUES("1", "0");

589

INSERT INTO t1 VALUES("2", "10");

590

591

CREATE TABLE t2 (

592

text_id char(3) NOT NULL default '',

593

language_id char(3) NOT NULL default '',

594

text_data text,

595

PRIMARY KEY (text_id,language_id)

596

);

597

598

INSERT INTO t2 VALUES("0", "EN", "0-EN");

599

INSERT INTO t2 VALUES("0", "SV", "0-SV");

600

INSERT INTO t2 VALUES("10", "EN", "10-EN");

601

INSERT INTO t2 VALUES("10", "SV", "10-SV");

602

SELECT t1.id, t1.text_id, t2.text_data

603

FROM t1 LEFT JOIN t2

604

ON t1.text_id = t2.text_id

605

AND t2.language_id = 'SV'

606

WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');

607

608

DROP TABLE t1, t2;

609

610

# Test for bug #5896

611

612

CREATE TABLE t0 (a0 int PRIMARY KEY);

613

CREATE TABLE t1 (a1 int PRIMARY KEY);

614

CREATE TABLE t2 (a2 int);

615

CREATE TABLE t3 (a3 int);

616

INSERT INTO t0 VALUES (1);

617

INSERT INTO t1 VALUES (1);

618

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

619

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

620

621

SELECT * FROM t1 LEFT JOIN t2 ON a1=0;

622

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;

623

SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;

624

EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;

625

SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;

626

EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;

627

628

INSERT INTO t0 VALUES (0);

629

INSERT INTO t1 VALUES (0);

630

SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;

631

EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;

632

633

# Test for BUG#4480

634

drop table t1,t2;

635

create table t1 (a int, b int);

636

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

637

create table t2 (a int, b int);

638

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

639

640

select * from t2 right join t1 on t2.a=t1.a;

641

select straight_join * from t2 right join t1 on t2.a=t1.a;

642

643

DROP TABLE t0,t1,t2,t3;

644

645

646

# Test for bug #9017: left join mistakingly converted to inner join

647

648

649

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

650

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

651

652

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

653

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

654

655

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

656

SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;

657

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

658

WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);

659

SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));

660

661

DROP TABLE t1,t2;

662

663

# Bug #8681: Bad warning message when group_concat() exceeds max length

664

set group_concat_max_len=5;

665

create table t1 (a int, b varchar(20));

666

create table t2 (a int, c varchar(20));

667

insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");

668

insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");

669

select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;

670

select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;

671

select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;

672

select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;

673

drop table t1, t2;

674

set group_concat_max_len=default;

675

676

# End of 4.1 tests

677

678

679

# BUG#10162 - ON is merged with WHERE, left join is convered to a regular join

680

681

create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, art int(11) not null, primary key (gid,x,y));

682

insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);

683

create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, id int(11) not null, primary key (gid,id,x,y), key id (id));

684

insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);

685

create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null, name char(12) not null, primary key (id,set_id));

686

insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4, 'f'), (1, 5, 'g'), (1, 6, 'h');

687

explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y

688

left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8

689

and t1.gid =1 and t2.gid =1 and t3.set_id =1;

690

drop tables t1,t2,t3;

691

692

693

# Test for bug #9938: invalid conversion from outer join to inner join

694

# for queries containing indirect reference in WHERE clause

695

696

697

CREATE TABLE t1 (EMPNUM INT, GRP INT);

698

INSERT INTO t1 VALUES (0, 10);

699

INSERT INTO t1 VALUES (2, 30);

700

701

CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));

702

INSERT INTO t2 VALUES (0, 'KERI');

703

INSERT INTO t2 VALUES (9, 'BARRY');

704

705

CREATE VIEW v1 AS

706

SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP

707

FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;

708

709

SELECT * FROM v1;

710

SELECT * FROM v1 WHERE EMPNUM < 10;

711

712

DROP VIEW v1;

713

DROP TABLE t1,t2;

714

715

716

# Test for bug #11285: false Item_equal on expression in outer join

717

718

719

CREATE TABLE t1 (c11 int);

720

CREATE TABLE t2 (c21 int);

721

INSERT INTO t1 VALUES (30), (40), (50);

722

INSERT INTO t2 VALUES (300), (400), (500);

723

SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;

724

DROP TABLE t1, t2;

725

726

# Test for bugs

727

# #12101: erroneously applied outer join elimination in case of WHERE NOT BETWEEN

728

# #12102: erroneously missing outer join elimination in case of WHERE IN/IF

729

730

731

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

732

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

733

734

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

735

INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);

736

737

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;

738

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;

739

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);

740

741

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;

742

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;

743

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);

744

745

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b;

746

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b);

747

748

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b);

749

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b);

750

751

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;

752

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);

753

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));

754

755

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;

756

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);

757

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));

758

759

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b);

760

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b));

761

762

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b;

763

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b));

764

765

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;

766

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);

767

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1);

768

769

DROP TABLE t1,t2;

770

771

772

# Test for bug #17164: ORed FALSE blocked conversion of outer join into join

773

774

775

# Test case moved to join_outer_innodb

776

777

778

# Bug 19396: LEFT OUTER JOIN over views in curly braces

779

780

--disable_warnings

781

DROP VIEW IF EXISTS v1,v2;

782

DROP TABLE IF EXISTS t1,t2;

783

--enable_warnings

784

785

CREATE TABLE t1 (a int);

786

CREATE table t2 (b int);

787

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

788

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

789

790

CREATE VIEW v1 AS SELECT a FROM t1 JOIN t2 ON t1.a=t2.b;

791

CREATE VIEW v2 AS SELECT b FROM t2 JOIN t1 ON t2.b=t1.a;

792

793

SELECT v1.a, v2. b

794

FROM v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3)

795

GROUP BY v1.a;

796

SELECT v1.a, v2. b

797

FROM { OJ v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3) }

798

GROUP BY v1.a;

799

800

DROP VIEW v1,v2;

801

DROP TABLE t1,t2;

802

803

804

# Bug 19816: LEFT OUTER JOIN with constant ORed predicates in WHERE clause

805

806

807

CREATE TABLE t1 (a int);

808

CREATE TABLE t2 (b int);

809

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

810

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

811

812

--sorted_result

813

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);

814

815

--sorted_result

816

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);

817

--sorted_result

818

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);

819

--sorted_result

820

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);

821

--sorted_result

822

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);

823

824

DROP TABLE t1,t2;

825

826

827

# Bug 26017: LEFT OUTER JOIN over two constant tables and

828

# a case-insensitive comparison predicate field=const

829

830

831

CREATE TABLE t1 (

832

f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,

833

f2 varchar(16) collate latin1_swedish_ci

834

);

835

CREATE TABLE t2 (

836

f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,

837

f3 varchar(16) collate latin1_swedish_ci

838

);

839

840

INSERT INTO t1 VALUES ('bla','blah');

841

INSERT INTO t2 VALUES ('bla','sheep');

842

843

SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';

844

SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';

845

SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';

846

847

DROP TABLE t1,t2;

848

849

850

# Bug 28188: 'not exists' optimization for outer joins

851

852

853

CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));

854

CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));

855

INSERT INTO t1 VALUES

856

(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');

857

INSERT INTO t2 VALUES

858

(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);

859

860

EXPLAIN

861

SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;

862

863

flush status;

864

SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;

865

show status like 'Handler_read%';

866

867

DROP TABLE t1,t2;

868

869

870

# Bug 28571: outer join with false on condition over constant tables

871

872

873

CREATE TABLE t1 (c int PRIMARY KEY, e int NOT NULL);

874

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

875

CREATE TABLE t2 (d int PRIMARY KEY);

876

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

877

878

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;

879

SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;

880

SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;

881

882

DROP TABLE t1,t2;

883

884

--echo #

885

--echo # Bug#47650: using group by with rollup without indexes returns incorrect

886

--echo # results with where

887

--echo #

888

CREATE TABLE t1 ( a INT );

889

INSERT INTO t1 VALUES (1);

890

891

CREATE TABLE t2 ( a INT, b INT );

892

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

893

894

EXPLAIN

895

SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )

896

FROM t1 LEFT JOIN t2 USING( a )

897

GROUP BY t1.a WITH ROLLUP;

898

899

SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )

900

FROM t1 LEFT JOIN t2 USING( a )

901

GROUP BY t1.a WITH ROLLUP;

902

903

EXPLAIN

904

SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )

905

FROM t1 JOIN t2 USING( a )

906

GROUP BY t1.a WITH ROLLUP;

907

908

SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )

909

FROM t1 JOIN t2 USING( a )

910

GROUP BY t1.a WITH ROLLUP;

911

912

DROP TABLE t1, t2;

913

914

--echo #

915

--echo # Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison

916

--echo #

917

CREATE TABLE t1(f1 INT, f2 INT, f3 INT);

918

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

919

CREATE TABLE t2(f1 INT, f2 INT);

920

INSERT INTO t2 VALUES (2, 1);

921

922

EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2

923

WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));

924

925

SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2

926

WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));

927

928

DROP TABLE t1, t2;

929

930

--echo #

931

--echo # Bug#52357: Assertion failed: join->best_read in greedy_search

932

--echo # optimizer_search_depth=0

933

--echo #

934

CREATE TABLE t1( a INT );

935

936

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

937

SET optimizer_search_depth = 0;

938

939

--echo # Should not core dump on query preparation

940

EXPLAIN

941

SELECT 1

942

FROM t1 tt3 LEFT OUTER JOIN t1 tt4 ON 1

943

LEFT OUTER JOIN t1 tt5 ON 1

944

LEFT OUTER JOIN t1 tt6 ON 1

945

LEFT OUTER JOIN t1 tt7 ON 1

946

LEFT OUTER JOIN t1 tt8 ON 1

947

RIGHT OUTER JOIN t1 tt2 ON 1

948

RIGHT OUTER JOIN t1 tt1 ON 1

949

STRAIGHT_JOIN t1 tt9 ON 1;

950

951

SET optimizer_search_depth = DEFAULT;

952

DROP TABLE t1;

953

954

--echo #

955

--echo # Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result

956

--echo #

957

CREATE TABLE t1 (f1 INT NOT NULL);

958

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

959

960

CREATE TABLE t2 (f1 INT NOT NULL);

961

INSERT INTO t2 VALUES

962

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

963

964

SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1

965

RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;

966

967

EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1

968

RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;

969

970

DROP TABLE t1, t2;

971

972

--echo #

973

--echo # Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990

974

--echo #

975

CREATE TABLE t1(f1 INT, PRIMARY KEY (f1));

976

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

977

978

EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1

979

LEFT JOIN t1 AS jt2

980

RIGHT JOIN t1 AS jt3

981

JOIN t1 AS jt4 ON 1

982

LEFT JOIN t1 AS jt5 ON 1

983

ON 1

984

RIGHT JOIN t1 AS jt6 ON jt6.f1

985

ON 1;

986

987

EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1

988

RIGHT JOIN t1 AS jt2

989

RIGHT JOIN t1 AS jt3

990

JOIN t1 AS jt4 ON 1

991

LEFT JOIN t1 AS jt5 ON 1

992

ON 1

993

RIGHT JOIN t1 AS jt6 ON jt6.f1

994

ON 1;

995

996

DROP TABLE t1;

997

998

--echo #

999

--echo # Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field

1000

--echo #

1001

1002

CREATE TABLE t1 (f1 INT NOT NULL, PRIMARY KEY (f1));

1003

CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (f1, f2));

1004

1005

INSERT INTO t1 VALUES (4);

1006

INSERT INTO t2 VALUES (3, 3);

1007

INSERT INTO t2 VALUES (7, 7);

1008

1009

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1

1010

WHERE t1.f1 = 4

1011

GROUP BY t2.f1, t2.f2;

1012

1013

SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1

1014

WHERE t1.f1 = 4

1015

GROUP BY t2.f1, t2.f2;

1016

1017

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1

1018

WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL

1019

GROUP BY t2.f1, t2.f2;

1020

1021

SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1

1022

WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL

1023

GROUP BY t2.f1, t2.f2;

1024

1025

DROP TABLE t1,t2;

1026

1027

--echo #

1028

--echo # Bug#57034 incorrect OUTER JOIN result when joined on unique key

1029

--echo #

1030

1031

CREATE TABLE t1 (pk INT PRIMARY KEY,

1032

col_int INT,

1033

col_int_unique INT UNIQUE KEY);

1034

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

1035

1036

CREATE TABLE t2 (pk INT PRIMARY KEY,

1037

col_int INT,

1038

col_int_unique INT UNIQUE KEY);

1039

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

1040

1041

EXPLAIN

1042

SELECT * FROM t1 LEFT JOIN t2

1043

ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int

1044

WHERE t1.pk=1;

1045

1046

SELECT * FROM t1 LEFT JOIN t2

1047

ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int

1048

WHERE t1.pk=1;

1049

1050

DROP TABLE t1,t2;

1051

1052

--echo #

1053

--echo # Bug#48046 Server incorrectly processing JOINs on NULL values

1054

--echo #

1055

1056

# bug#48046 is a duplicate of bug#57034

1057

1058

CREATE TABLE `BB` (

1059

`pk` int(11) NOT NULL AUTO_INCREMENT,

1060

`time_key` time DEFAULT NULL,

1061

`varchar_key` varchar(1) DEFAULT NULL,

1062

`varchar_nokey` varchar(1) DEFAULT NULL,

1063

PRIMARY KEY (`pk`),

1064

KEY `time_key` (`time_key`),

1065

KEY `varchar_key` (`varchar_key`)

1066

) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

1067

1068

INSERT INTO `BB` VALUES (10,'18:27:58',NULL,NULL);

1069

1070

SELECT table1.time_key AS field1, table2.pk

1071

FROM BB table1 LEFT JOIN BB table2

1072

ON table2.varchar_nokey = table1.varchar_key

1073

HAVING field1;

1074

1075

DROP TABLE BB;

1076

1077

--echo #

1078

--echo # Bug#49600 Server incorrectly processing RIGHT JOIN with

1079

--echo # constant WHERE clause and no index

1080

--echo #

1081

1082

# bug#49600 is a duplicate of bug#57034

1083

1084

CREATE TABLE `BB` (

1085

`col_datetime_key` datetime DEFAULT NULL,

1086

`col_varchar_key` varchar(1) DEFAULT NULL,

1087

`col_varchar_nokey` varchar(1) DEFAULT NULL,

1088

KEY `col_datetime_key` (`col_datetime_key`),

1089

KEY `col_varchar_key` (`col_varchar_key`)

1090

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

1091

1092

INSERT INTO `BB` VALUES ('1900-01-01 00:00:00',NULL,NULL);

1093

1094

SELECT table1.col_datetime_key

1095

FROM BB table1 RIGHT JOIN BB table2

1096

ON table2 .col_varchar_nokey = table1.col_varchar_key

1097

WHERE 7;

1098

1099

# Disable keys, and we get incorrect result for the same query

1100

ALTER TABLE BB DISABLE KEYS;

1101

1102

SELECT table1.col_datetime_key

1103

FROM BB table1 RIGHT JOIN BB table2

1104

ON table2 .col_varchar_nokey = table1.col_varchar_key

1105

WHERE 7;

1106

1107

DROP TABLE BB;

1108

1109

1110

--echo #

1111

--echo # Bug#58490: Incorrect result in multi level OUTER JOIN

1112

--echo # in combination with IS NULL

1113

--echo #

1114

1115

CREATE TABLE t1 (i INT NOT NULL);

1116

INSERT INTO t1 VALUES (0), (2),(3),(4);

1117

CREATE TABLE t2 (i INT NOT NULL);

1118

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

1119

CREATE TABLE t3 (i INT NOT NULL);

1120

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

1121

CREATE TABLE t4 (i INT NOT NULL);

1122

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

1123

1124

--sorted_result

1125

SELECT * FROM

1126

t1 LEFT JOIN

1127

( t2 LEFT JOIN

1128

( t3 LEFT JOIN

1129

1130

ON t4.i = t3.i

1131

)

1132

ON t3.i = t2.i

1133

)

1134

ON t2.i = t1.i

1135

;

1136

1137

--sorted_result

1138

SELECT * FROM

1139

t1 LEFT JOIN

1140

( t2 LEFT JOIN

1141

( t3 LEFT JOIN

1142

1143

ON t4.i = t3.i

1144

)

1145

ON t3.i = t2.i

1146

)

1147

ON t2.i = t1.i

1148

WHERE t4.i IS NULL;

1149

1150

1151

# Most simplified testcase to reproduce the bug.

1152

# (Has to be at least a two level nested outer join)

1153

--sorted_result

1154

SELECT * FROM

1155

t1 LEFT JOIN

1156

( ( t2 LEFT JOIN

1157

1158

ON t3.i = t2.i

1159

)

1160

)

1161

ON t2.i = t1.i

1162

WHERE t3.i IS NULL;

1163

1164

1165

# Extended testing:

1166

# We then add some equi-join inside the query above:

1167

# (There Used to be some problems here with first

1168

# proposed patch for this bug)

1169

--sorted_result

1170

SELECT * FROM

1171

t1 LEFT JOIN

1172

( ( t2 LEFT JOIN

1173

1174

ON t3.i = t2.i

1175

)

1176

JOIN t4

1177

ON t4.i=t2.i

1178

)

1179

ON t2.i = t1.i

1180

WHERE t3.i IS NULL;

1181

1182

--sorted_result

1183

SELECT * FROM

1184

t1 LEFT JOIN

1185

( ( t2 LEFT JOIN

1186

1187

ON t3.i = t2.i

1188

)

1189

JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)

1190

ON t4a.i=t2.i

1191

)

1192

ON t2.i = t1.i

1193

WHERE t3.i IS NULL;

1194

1195

--sorted_result

1196

SELECT * FROM

1197

t1 LEFT JOIN

1198

( ( t2 LEFT JOIN

1199

1200

ON t3.i = t2.i

1201

)

1202

JOIN (t4 AS t4a, t4 AS t4b)

1203

ON t4a.i=t2.i

1204

)

1205

ON t2.i = t1.i

1206

WHERE t3.i IS NULL;

1207

1208

1209

DROP TABLE t1,t2,t3,t4;

1210

1211

## Bug#49322 & bug#58490 are duplicates. However, we include testcases

1212

## for both.

1213

--echo #

1214

--echo # Bug#49322(Duplicate): Server is adding extra NULL row

1215

--echo # on processing a WHERE clause

1216

--echo #

1217

1218

CREATE TABLE h (pk INT NOT NULL, col_int_key INT);

1219

INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);

1220

1221

CREATE TABLE m (pk INT NOT NULL, col_int_key INT);

1222

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

1223

CREATE TABLE k (pk INT NOT NULL, col_int_key INT);

1224

INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);

1225

1226

# Baseline query wo/ 'WHERE ... IS NULL' - was correct

1227

--sorted_result

1228

SELECT TABLE1.pk FROM k TABLE1

1229

RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key

1230

RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;

1231

1232

# Adding 'WHERE ... IS NULL' -> incorrect result

1233

--sorted_result

1234

SELECT TABLE1.pk FROM k TABLE1

1235

RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key

1236

RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key

1237

WHERE TABLE1.pk IS NULL;

1238

1239

DROP TABLE h,m,k;

1240

1241

--echo #

1242

--echo # Bug #11765810 58813: SERVER THREAD HANGS WHEN JOIN + WHERE + GROUP BY

1243

--echo # IS EXECUTED TWICE FROM P

1244

--echo #

1245

CREATE TABLE t1 ( a INT ) ENGINE = MYISAM;

1246

INSERT INTO t1 VALUES (1);

1247

PREPARE prep_stmt FROM '

1248

SELECT 1 AS f FROM t1

1249

LEFT JOIN t1 t2

1250

RIGHT JOIN t1 t3

1251

JOIN t1 t4

1252

ON 1

1253

ON 1

1254

ON 1

1255

GROUP BY f';

1256

EXECUTE prep_stmt;

1257

EXECUTE prep_stmt;

1258

1259

DROP TABLE t1;

1260

1261

1262

--echo End of 5.1 tests

1263

1264

--echo #

1265

--echo # Bug#54235 Extra rows with join_cache_level=4,6,8 and two LEFT JOIN

1266

--echo #

1267

1268

CREATE TABLE t1 (a int);

1269

CREATE TABLE t2 (a int);

1270

CREATE TABLE t3 (a int);

1271

CREATE TABLE t4 (a int);

1272

1273

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

1274

1275

let $query = SELECT t1.a FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a)

1276

ON 0 WHERE t1.a OR t3.a;

1277

1278

eval explain $query;

1279

1280

eval $query;

1281

1282

let $query = SELECT t1.a FROM t1 LEFT JOIN

1283

(t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a)

1284

ON 0 WHERE t1.a OR t4.a;

1285

1286

eval explain $query;

1287

1288

eval $query;

1289

1290

DROP TABLE t1,t2,t3,t4;

1291

1292

--echo #

1293

--echo # Bug#56254 Assertion tab->ref.use_count fails in

1294

--echo # join_read_key_unlock_row() on 4-way JOIN

1295

--echo #

1296

1297

CREATE TABLE t1 (

1298

pk INT NOT NULL,

1299

col_int_key INT,

1300

col_int INT,

1301

PRIMARY KEY (pk),

1302

KEY col_int_key (col_int_key)

1303

);

1304

1305

INSERT INTO t1 VALUES (6, -448724992, NULL);

1306

1307

CREATE TABLE t2 (

1308

col_int INT,

1309

col_varchar_10 VARCHAR(10)

1310

);

1311

1312

INSERT INTO t2 VALUES (6,'afasdkiyum');

1313

1314

CREATE TABLE t3 (

1315

col_varchar_10 VARCHAR(10),

1316

col_int INT

1317

);

1318

1319

CREATE TABLE t4 (

1320

pk INT NOT NULL,

1321

PRIMARY KEY (pk)

1322

);

1323

1324

INSERT INTO t4 VALUES (1);

1325

INSERT INTO t4 VALUES (2);

1326

1327

SELECT t1.col_int

1328

FROM t1

1329

LEFT JOIN t2

1330

LEFT JOIN t3

1331

JOIN t4

1332

ON t3.col_int = t4.pk

1333

ON t2.col_varchar_10 = t3.col_varchar_10

1334

ON t2.col_int = t1.pk

1335

WHERE t1.col_int_key IS NULL OR t4.pk < t3.col_int;

1336

1337

DROP TABLE t1,t2,t3,t4;

1338

1339

--echo

1340

--echo # BUG#12567331 - INFINITE LOOP WHEN RESOLVING AN ALIASED COLUMN

1341

--echo # USED IN GROUP BY

1342

--echo

1343

CREATE TABLE t1 (pk int(11));

1344

1345

PREPARE prep_stmt_9846 FROM '

1346

SELECT alias1.pk AS field1 FROM

1347

t1 AS alias1

1348

LEFT JOIN

1349

(

1350

t1 AS alias2

1351

RIGHT JOIN

1352

(

1353

t1 AS alias3

1354

JOIN t1 AS alias4

1355

ON 1

1356

)

1357

ON 1

1358

)

1359

ON 1

1360

GROUP BY field1';

1361

execute prep_stmt_9846;

1362

execute prep_stmt_9846;

1363

deallocate prepare prep_stmt_9846;

1364

drop table t1;

1365

1366

--echo #

1367

--echo # Bug#13040136 - ASSERT IN PLAN_CHANGE_WATCHDOG::~PLAN_CHANGE_WATCHDOG()

1368

--echo #

1369

CREATE TABLE t1 (

1370

col_varchar_10 VARCHAR(10),

1371

col_int_key INTEGER,

1372

col_varchar_10_key VARCHAR(10),

1373

pk INTEGER NOT NULL,

1374

PRIMARY KEY (pk),

1375

KEY (col_int_key),

1376

KEY (col_varchar_10_key)

1377

);

1378

INSERT INTO t1 VALUES ('q',NULL,'o',1);

1379

1380

CREATE TABLE t2 (

1381

pk INTEGER NOT NULL AUTO_INCREMENT,

1382

col_varchar_10_key VARCHAR(10),

1383

col_int_key INTEGER,

1384

col_varchar_10 VARCHAR(10),

1385

PRIMARY KEY (pk),

1386

KEY (col_varchar_10_key),

1387

KEY col_int_key (col_int_key)

1388

);

1389

INSERT INTO t2 VALUES

1390

(1,'r',NULL,'would'),(2,'tell',-655032320,'t'),

1391

(3,'d',9,'a'),(4,'gvafasdkiy',6,'ugvafasdki'),

1392

(5,'that\'s',NULL,'she'),(6,'bwftwugvaf',7,'cbwftwugva'),

1393

(7,'f',-700055552,'mkacbwftwu'),(8,'a',9,'be'),

1394

(9,'d',NULL,'u'),(10,'ckiixcsxmk',NULL,'o');

1395

1396

SELECT DISTINCT t2.col_int_key

1397

FROM

1398

1399

LEFT JOIN t2

1400

ON t1.col_varchar_10 = t2.col_varchar_10_key

1401

WHERE t2.pk

1402

ORDER BY t2.col_int_key;

1403

1404

DROP TABLE t1,t2;

1405

1406

--echo #

1407

--echo # Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS WRONG RESULT

1408

--echo #

1409

CREATE TABLE t1 (i1 int);

1410

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

1411

1412

CREATE TABLE t2 (i2 int, i3 int);

1413

INSERT INTO t2 VALUES (20,1),(10,2);

1414

1415

CREATE TABLE t3 (i4 int(11));

1416

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

1417

1418

let $query= SELECT (

1419

SELECT MAX( t2.i2 )

1420

FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )

1421

WHERE t2.i3 <> t1.i1

1422

) AS field1

1423

FROM t1;

1424

1425

--echo

1426

--eval $query;

1427

--echo

1428

--eval $query GROUP BY field1;

1429

1430

--echo

1431

drop table t1,t2,t3;

1432

1433

--echo # Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN

1434

1435

CREATE TABLE t1 (

1436

pk int(11) NOT NULL,

1437

col_varchar_10_latin1_key varchar(10) DEFAULT NULL

1438

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1439

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

1440

CREATE TABLE t2 (

1441

pk int(11) NOT NULL

1442

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1443

INSERT INTO t2 VALUES (1);

1444

CREATE TABLE t3 (

1445

pk int(11) NOT NULL

1446

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1447

INSERT INTO t3 VALUES (1);

1448

CREATE TABLE t4 (

1449

pk int(11) NOT NULL,

1450

col_int int(11) DEFAULT NULL,

1451

col_int_key int(11) DEFAULT NULL,

1452

col_varchar_10_latin1_key varchar(10) DEFAULT NULL

1453

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1454

INSERT INTO t4 VALUES (1,1,1,'1');

1455

CREATE TABLE t5 (

1456

col_int int(11) DEFAULT NULL,

1457

col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL

1458

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1459

INSERT INTO t5 VALUES (1,'1');

1460

CREATE TABLE t6 (

1461

col_int_key int(11) DEFAULT NULL,

1462

col_varchar_10_latin1_key varchar(10) DEFAULT NULL,

1463

pk int(11) NOT NULL

1464

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1465

INSERT INTO t6 VALUES (1,'1',1);

1466

1467

# EXPLAIN of query above (t2 is before t5 in plan)

1468

1469

let $rest_of_query=t6a.pk, t2.pk

1470

FROM

1471

t6 AS t6a

1472

LEFT JOIN

1473

(

1474

1475

RIGHT JOIN

1476

(

1477

(

1478

1479

LEFT JOIN

1480

(

1481

1482

JOIN

1483

1484

ON t4.col_int

1485

)

1486

ON t4.col_int_key = t1.pk

1487

)

1488

LEFT JOIN

1489

(

1490

1491

JOIN

1492

t6 AS t6b

1493

ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key

1494

)

1495

ON t1.pk = t5.col_int

1496

)

1497

ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key

1498

AND t5.col_varchar_10_utf8_key = 0

1499

)

1500

ON t6a.pk IS TRUE

1501

WHERE t6b.col_int_key IS TRUE

1502

;

1503

1504

eval SELECT STRAIGHT_JOIN $rest_of_query;

1505

eval EXPLAIN SELECT STRAIGHT_JOIN $rest_of_query;

1506

1507

# right result (same query, just remove STRAIGHT_JOIN):

1508

1509

eval SELECT $rest_of_query;

1510

eval EXPLAIN SELECT $rest_of_query;

1511

1512

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

1513

1514

--echo #

1515

--echo # Verify that the "not exists" optimization works.

1516

--echo #

1517

CREATE TABLE t1(a INT);

1518

CREATE TABLE t2(a INT NOT NULL);

1519

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

1520

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

1521

let $query=SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.a IS NULL;

1522

eval EXPLAIN $query;

1523

FLUSH STATUS;

1524

eval $query;

1525

# Without the "not exists" optimization, there would be more read_rnd_next

1526

SHOW STATUS LIKE 'HANDLER_READ%';

1527

DROP TABLE t1,t2;

1528

1529

--echo #

1530

--echo # Bug#13464334 SAME QUERY PRODUCES DIFFERENT RESULTS WHEN USED WITH AND

1531

--echo # WITHOUT UNION ALL

1532

--echo #

1533

1534

CREATE TABLE t1 (p1 INT PRIMARY KEY, a CHAR(1));

1535

CREATE TABLE t2 (p2 INT PRIMARY KEY, b CHAR(1));

1536

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

1537

INSERT INTO t2 VALUES (1,'h'),(2,'i'),(3,'j'),(4,'k');

1538

CREATE VIEW v1 AS SELECT * FROM t1;

1539

CREATE VIEW v2 AS SELECT * FROM t2;

1540

(SELECT p1 FROM v2 LEFT JOIN v1 ON b = a WHERE p2 = 1 GROUP BY p1 ORDER BY p1)

1541

UNION (SELECT NULL LIMIT 0);

1542

DROP VIEW v1, v2;

1543

DROP TABLE t1, t2;

1544

1545

--echo #

1546

--echo # Bug#13980954 Missing data on left join + null value + where..in

1547

--echo #

1548

1549

CREATE TABLE t1 (ik INT, vc varchar(1)) ENGINE=Innodb;

1550

1551

let $query=

1552

SELECT straight_join t1.vc, t1.ik

1553

FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc;

1554

1555

eval explain format=json $query;

1556

eval $query;

1557

1558

DROP TABLE t1;

Older »