~drizzle-trunk/drizzle/development

SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test";

180

SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test";

181

(SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test");

182

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

183

184

185

# Test insert ... SELECT with UNION

186

187

188

CREATE TABLE t1 (a int not null, b char (10) not null);

189

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

190

CREATE TABLE t2 (a int not null, b char (10) not null);

191

insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');

192

create table t3 select a,b from t1 union select a,b from t2;

193

create table t4 (select a,b from t1) union (select a,b from t2) limit 2;

194

insert into t4 select a,b from t1 union select a,b from t2;

195

insert into t3 (select a,b from t1) union (select a,b from t2) limit 2;

196

select * from t3;

197

select * from t4;

198

drop table t1,t2,t3,t4;

199

200

201

# Test of SQL_CALC_FOUND_ROW handling

202

203

create table t1 (a int);

204

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

205

create table t2 (a int);

206

insert into t2 values (3),(4),(5);

207

208

# Test global limits

209

(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1;

210

select found_rows();

211

(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;

212

select found_rows();

213

214

# Test cases where found_rows() should return number of returned rows

215

(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);

216

select found_rows();

217

(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);

218

select found_rows();

219

# This used to work in 4.0 but not anymore in 4.1

220

--error 1064

221

(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;

222

#select found_rows();

223

224

# In these case found_rows() should work

225

SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;

226

select found_rows();

227

SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;

228

select found_rows();

229

230

# The following examples will not be exact

231

SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;

232

select found_rows();

233

SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;

234

select found_rows();

235

SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;

236

select found_rows();

237

SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;

238

select found_rows();

239

SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;

240

select found_rows();

241

SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;

242

select found_rows();

243

SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;

244

select found_rows();

245

246

# Test some limits with ORDER BY

247

SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;

248

(SELECT * FROM t1 ORDER by a) UNION ALL (SELECT * FROM t2 ORDER BY a) ORDER BY A desc LIMIT 4;

249

250

# Wrong usage

251

--error 1234

252

(SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;

253

254

create temporary table t1 select a from t1 union select a from t2;

255

drop temporary table t1;

256

--error 1093

257

create table t1 select a from t1 union select a from t2;

258

--error 1054

259

select a from t1 union select a from t2 order by t2.a;

260

drop table t1,t2;

261

262

263

# Problem with alias '*' (BUG #1249)

264

265

266

select length(version()) > 1 as `*` UNION select 2;

267

268

269

# Bug #4980: problem with explain

270

271

272

create table t1 (a int);

273

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

274

explain (select * from t1) union (select * from t1) order by a;

275

drop table t1;

276

277

# Test for another bug with UNION and LEFT JOIN

278

279

CREATE TABLE t1 ( id int(3) default '0') ENGINE=MyISAM;

280

INSERT INTO t1 (id) VALUES("1");

281

CREATE TABLE t2 ( id int(3) default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) ENGINE=MyISAM;

282

INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1",

283

"foo1", "bar1");

284

INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1",

285

"foo2", "bar2");

286

INSERT INTO t2 (id, id_master, text1, text2) VALUES("3", "1", NULL,

287

"bar3");

288

INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1",

289

"foo4", "bar4");

290

SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;

291

SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;

292

drop table if exists t1,t2;

293

294

295

# Test of bug when using the same table multiple times

296

297

create table t1 (a int not null primary key auto_increment, b int, key(b));

298

create table t2 (a int not null primary key auto_increment, b int);

299

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

300

insert into t2 (b) values (10),(11),(12),(13);

301

302

explain extended (select * from t1 where a=1) union (select * from t2 where a=1);

303

(select * from t1 where a=5) union (select * from t2 where a=1);

304

(select * from t1 where a=5 and a=6) union (select * from t2 where a=1);

305

(select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1);

306

(select * from t1 where a=1) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);

307

explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t2.a from t1,t2 where t1.a=t2.a);

308

explain (select * from t1 where a=1) union (select * from t1 where b=1);

309

drop table t1,t2;

310

create table t1 ( id int not null auto_increment, primary key (id) ,user_name text );

311

create table t2 ( id int not null auto_increment, primary key (id) ,group_name text );

312

create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references users(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references groups(id) );

313

insert into t1 (user_name) values ('Tester');

314

insert into t2 (group_name) values ('Group A');

315

insert into t2 (group_name) values ('Group B');

316

insert into t3 (user_id, group_id) values (1,1);

317

select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c;

318

drop table t1, t2, t3;

319

320

321

# fix_fields problem

322

323

create table t1 (mat_id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT NULL);

324

create table t2 (mat_id MEDIUMINT NOT NULL, pla_id MEDIUMINT NOT NULL);

325

insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);

326

insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);

327

SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id union SELECT 0, 0;

328

drop table t1, t2;

329

330

331

# types conversions

332

333

create table t1 SELECT "a" as a UNION select "aa" as a;

334

select * from t1;

335

show create table t1;

336

drop table t1;

337

create table t1 SELECT 12 as a UNION select "aa" as a;

338

select * from t1;

339

show create table t1;

340

drop table t1;

341

create table t1 SELECT 12 as a UNION select 12.2 as a;

342

select * from t1;

343

show create table t1;

344

drop table t1;

345

346

create table t2 (it1 int, it2 int not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text);

347

insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, 1972, '1972-10-22', '1972-10-22 11:50', 'testc', 'testv', 'tetetetetest', 'teeeeeeeeeeeest');

348

349

create table t1 SELECT it2 from t2 UNION select it1 from t2;

350

select * from t1;

351

show create table t1;

352

drop table t1;

353

create table t1 SELECT it2 from t2 UNION select i from t2;

354

select * from t1;

355

show create table t1;

356

drop table t1;

357

create table t1 SELECT i from t2 UNION select f from t2;

358

select * from t1;

359

show create table t1;

360

drop table t1;

361

create table t1 SELECT f from t2 UNION select d from t2;

362

select * from t1;

363

show create table t1;

364

drop table t1;

365

create table t1 SELECT ib from t2 UNION select f from t2;

366

select * from t1;

367

show create table t1;

368

drop table t1;

369

create table t1 SELECT ib from t2 UNION select d from t2;

370

select * from t1;

371

show create table t1;

372

drop table t1;

373

create table t1 SELECT f from t2 UNION select y from t2;

374

select * from t1;

375

show create table t1;

376

drop table t1;

377

create table t1 SELECT f from t2 UNION select da from t2;

378

select * from t1;

379

show create table t1;

380

drop table t1;

381

create table t1 SELECT y from t2 UNION select da from t2;

382

select * from t1;

383

show create table t1;

384

drop table t1;

385

create table t1 SELECT y from t2 UNION select dt from t2;

386

select * from t1;

387

show create table t1;

388

drop table t1;

389

create table t1 SELECT da from t2 UNION select dt from t2;

390

select * from t1;

391

show create table t1;

392

drop table t1;

393

create table t1 SELECT dt from t2 UNION select trim(sc) from t2;

394

select trim(dt) from t1;

395

show create table t1;

396

drop table t1;

397

create table t1 SELECT dt from t2 UNION select sv from t2;

398

select * from t1;

399

show create table t1;

400

drop table t1;

401

create table t1 SELECT sc from t2 UNION select sv from t2;

402

select * from t1;

403

show create table t1;

404

drop table t1;

405

create table t1 SELECT dt from t2 UNION select b from t2;

406

select * from t1;

407

show create table t1;

408

drop table t1;

409

create table t1 SELECT sv from t2 UNION select b from t2;

410

select * from t1;

411

show create table t1;

412

drop table t1;

413

create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;

414

select * from t1;

415

show create table t1;

416

drop table t1;

417

create table t1 SELECT sv from t2 UNION select tx from t2;

418

select * from t1;

419

show create table t1;

420

drop table t1;

421

create table t1 SELECT b from t2 UNION select tx from t2;

422

select * from t1;

423

show create table t1;

424

drop table t1,t2;

425

create table t1 select 1 union select -1;

426

select * from t1;

427

show create table t1;

428

drop table t1;

429

create table t1 select _latin1"test" union select _latin2"testt" ;

430

create table t1 select _latin2"test" union select _latin2"testt" ;

431

show create table t1;

432

drop table t1;

433

434

435

# conversion memory->disk table

436

437

create table t1 (s char(200));

438

insert into t1 values (repeat("1",200));

439

create table t2 select * from t1;

440

insert into t2 select * from t1;

441

insert into t1 select * from t2;

442

insert into t2 select * from t1;

443

insert into t1 select * from t2;

444

insert into t2 select * from t1;

445

set local tmp_table_size=1024;

446

select count(*) from (select * from t1 union all select * from t2 order by 1) b;

447

select count(*) from t1;

448

select count(*) from t2;

449

drop table t1,t2;

450

set local tmp_table_size=default;

451

452

453

# slow logging

454

455

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

456

insert t1 values (1,1),(2,2),(3,3),(4,4),(5,5);

457

insert t1 select a+1, a+b from t1;

458

insert t1 select a+1, a+b from t1;

459

insert t1 select a+1, a+b from t1;

460

insert t1 select a+1, a+b from t1;

461

insert t1 select a+1, a+b from t1;

462

FLUSH STATUS;

463

show status like 'Slow_queries';

464

select count(*) from t1 where a=7;

465

show status like 'Slow_queries';

466

select count(*) from t1 where b=13;

467

show status like 'Slow_queries';

468

select count(*) from t1 where b=13 union select count(*) from t1 where a=7;

469

show status like 'Slow_queries';

470

select count(*) from t1 where a=7 union select count(*) from t1 where b=13;

471

show status like 'Slow_queries';

472

# additional test for examined rows

473

flush status;

474

select a from t1 where b not in (1,2,3) union select a from t1 where b not in (4,5,6);

475

show status like 'Slow_queries';

476

drop table t1;

477

478

479

# Column 'name' cannot be null (error with union and left join) (bug #2508)

480

481

create table t1 ( RID int(11) not null default '0', IID int(11) not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM;

482

insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');

483

select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);

484

drop table t1;

485

486

487

# Bug #2809 (UNION fails on MyIsam tables when index on second column from

488

# same table)

489

490

create table t1 (col1 int, col2 int);

491

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

492

select col1 n from t1 union select col2 n from t1 order by n;

493

alter table t1 add index myindex (col2);

494

select col1 n from t1 union select col2 n from t1 order by n;

495

drop table t1;

496

497

498

# Incorrect handling of UNION ALL (Bug #1428)

499

500

create table t1 (i int);

501

insert into t1 values (1);

502

select * from t1 UNION select * from t1;

503

select * from t1 UNION ALL select * from t1;

504

select * from t1 UNION select * from t1 UNION ALL select * from t1;

505

drop table t1;

506

select 1 as a union all select 1 union all select 2 union select 1 union all select 2;

507

set sql_select_limit=1;

508

select 1 union select 2;

509

(select 1) union (select 2);

510

(select 1) union (select 2) union (select 3) limit 2;

511

set sql_select_limit=default;

512

513

514

# ORDER with LIMIT

515

516

create table t1 (a int);

517

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

518

create table t2 (a int);

519

insert into t2 values (100);

520

select a from t1 union select a from t2 order by a;

521

SET SQL_SELECT_LIMIT=1;

522

select a from t1 union select a from t2 order by a;

523

drop table t1, t2;

524

set sql_select_limit=default;

525

526

527

# nonexisting column in global ORDER BY

528

529

CREATE TABLE t1 (i int(11) default NULL,c char(1) default NULL,KEY i (i));

530

CREATE TABLE t2 (i int(11) default NULL,c char(1) default NULL,KEY i (i));

531

--error 1054

532

explain (select * from t1) union (select * from t2) order by not_existing_column;

533

drop table t1, t2;

534

535

536

# length detecting

537

538

CREATE TABLE t1 (uid int(1));

539

INSERT INTO t1 SELECT 150;

540

SELECT 'a' UNION SELECT uid FROM t1;

541

drop table t1;

542

543

544

# parser stack overflow

545

546

CREATE TABLE t1 ( ID1 int(10) NOT NULL DEFAULT '0' , ID2 datetime NOT NULL DEFAULT '0000-00-00 00:00:00' , DATA1 varchar(10) , DATA2 double(5,4) , DATA3 datetime , PRIMARY KEY (ID1,ID2));

547

548

CREATE TABLE t2 ( ID int(3) NOT NULL DEFAULT '0' , DATA1 timestamp DEFAULT '0000-00-00 00:00:00' , PRIMARY KEY (ID));

549

(SELECT * FROM t1 AS PARTITIONED, t2 AS

550

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

551

(SELECT * FROM t1 AS PARTITIONED, t2 AS

552

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

553

(SELECT * FROM t1 AS PARTITIONED, t2 AS

554

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

555

(SELECT * FROM t1 AS PARTITIONED, t2 AS

556

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

557

(SELECT * FROM t1 AS PARTITIONED, t2 AS

558

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

559

(SELECT * FROM t1 AS PARTITIONED, t2 AS

560

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

561

(SELECT * FROM t1 AS PARTITIONED, t2 AS

562

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

563

(SELECT * FROM t1 AS PARTITIONED, t2 AS

564

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

565

(SELECT * FROM t1 AS PARTITIONED, t2 AS

566

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

567

(SELECT * FROM t1 AS PARTITIONED, t2 AS

568

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

569

(SELECT * FROM t1 AS PARTITIONED, t2 AS

570

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

571

(SELECT * FROM t1 AS PARTITIONED, t2 AS

572

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1);

573

drop table t1,t2;

574

575

576

# merging ENUM and SET fields in one UNION

577

578

create table t1 (a ENUM('Yes', 'No') NOT NULL);

579

create table t2 (a ENUM('aaa', 'bbb') NOT NULL);

580

insert into t1 values ('No');

581

insert into t2 values ('bbb');

582

create table t3 (a SET('Yes', 'No') NOT NULL);

583

create table t4 (a SET('aaa', 'bbb') NOT NULL);

584

insert into t3 values (1);

585

insert into t4 values (3);

586

select "1" as a union select a from t1;

587

select a as a from t1 union select "1";

588

select a as a from t2 union select a from t1;

589

select "1" as a union select a from t3;

590

select a as a from t3 union select "1";

591

select a as a from t4 union select a from t3;

592

select a as a from t1 union select a from t4;

593

drop table t1,t2,t3,t4;

594

595

596

# Bug #6139 UNION doesn't understand collate in the column of second select

597

598

create table t1 as

599

(select _latin1'test') union

600

(select _latin1'TEST') union

601

(select _latin1'TeST');

602

show create table t1;

603

select count(*) from t1;

604

drop table t1;

605

606

create table t1 as

607

(select _latin1'test' collate latin1_bin) union

608

(select _latin1'TEST') union

609

(select _latin1'TeST');

610

show create table t1;

611

select count(*) from t1;

612

drop table t1;

613

614

create table t1 as

615

(select _latin1'test') union

616

(select _latin1'TEST' collate latin1_bin) union

617

(select _latin1'TeST');

618

show create table t1;

619

select count(*) from t1;

620

drop table t1;

621

622

create table t1 as

623

(select _latin1'test') union

624

(select _latin1'TEST') union

625

(select _latin1'TeST' collate latin1_bin);

626

show create table t1;

627

select count(*) from t1;

628

drop table t1;

629

630

create table t2 (

631

a char character set latin1 collate latin1_swedish_ci,

632

b char character set latin1 collate latin1_german1_ci);

633

--error 1271

634

create table t1 as

635

(select a from t2) union

636

(select b from t2);

637

create table t1 as

638

(select a collate latin1_german1_ci from t2) union

639

(select b from t2);

640

show create table t1;

641

drop table t1;

642

create table t1 as

643

(select a from t2) union

644

(select b collate latin1_german1_ci from t2);

645

show create table t1;

646

drop table t1;

647

create table t1 as

648

(select a from t2) union

649

(select b from t2) union

650

(select 'c' collate latin1_german1_ci from t2);

651

show create table t1;

652

drop table t1;

653

drop table t2;

654

655

656

# Bug 6931: Date Type column problem when using UNION-Table.

657

658

create table t1(a1 int, f1 char(10));

659

create table t2

660

select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a

661

union

662

select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a

663

order by f2, a1;

664

show columns from t2;

665

drop table t1, t2;

666

667

create table t1 (f1 int);

668

create table t2 (f1 int, f2 int ,f3 date);

669

create table t3 (f1 int, f2 char(10));

670

create table t4

671

(

672

select t2.f3 as sdate

673

from t1

674

left outer join t2 on (t1.f1 = t2.f1)

675

inner join t3 on (t2.f2 = t3.f1)

676

order by t1.f1, t3.f1, t2.f3

677

)

678

union

679

(

680

select cast('2004-12-31' as date) as sdate

681

from t1

682

left outer join t2 on (t1.f1 = t2.f1)

683

inner join t3 on (t2.f2 = t3.f1)

684

group by t1.f1

685

order by t1.f1, t3.f1, t2.f3

686

)

687

order by sdate;

688

show columns from t4;

689

drop table t1, t2, t3, t4;

690

691

692

# Bug #2435 UNION with parentheses not supported

693

694

create table t1 (a int not null, b char (10) not null);

695

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

696

select * from ((select * from t1 limit 1)) a;

697

select * from ((select * from t1 limit 1) union (select * from t1 limit 1)) a;

698

select * from ((select * from t1 limit 1) union (select * from t1 limit 1) union (select * from t1 limit 1)) a;

699

select * from ((((select * from t1))) union (select * from t1) union (select * from t1)) a;

700

select * from ((select * from t1) union (((select * from t1))) union (select * from t1)) a;

701

drop table t1;

702

703

704

# Bugs#6519 UNION with collation binary and latin1_swedish_ci fails

705

706

set @val:=6;

707

select concat('value is: ', @val) union select 'some text';

708

709

710

# Bug#15949 union + illegal mix of collations (IMPLICIT + COERCIBLE)

711

712

select concat(_latin1'a', _ascii'b' collate ascii_bin);

713

create table t1 (foo varchar(100)) collate ascii_bin;

714

insert into t1 (foo) values ("foo");

715

select foo from t1 union select 'bar' as foo from dual;

716

drop table t1;

717

718

719

# Enum merging test

720

721

CREATE TABLE t1 (

722

a ENUM('�','�','�') character set utf8 not null default '�',

723

b ENUM("one", "two") character set utf8,

724

c ENUM("one", "two")

725

);

726

show create table t1;

727

insert into t1 values ('�', 'one', 'one'), ('�', 'two', 'one'), ('�', NULL, NULL);

728

create table t2 select NULL union select a from t1;

729

show columns from t2;

730

drop table t2;

731

create table t2 select a from t1 union select NULL;

732

show columns from t2;

733

drop table t2;

734

create table t2 select a from t1 union select a from t1;

735

show columns from t2;

736

drop table t2;

737

create table t2 select a from t1 union select c from t1;

738

drop table t2;

739

create table t2 select a from t1 union select b from t1;

740

show columns from t2;

741

drop table t2, t1;

742

743

744

# Bug #14216: UNION + DECIMAL wrong values in result

745

746

create table t1 (f1 decimal(60,25), f2 decimal(60,25));

747

insert into t1 values (0.0,0.0);

748

select f1 from t1 union all select f2 from t1;

749

select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1

750

union all

751

select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;

752

drop table t1;

753

create table t1 (f1 decimal(60,24), f2 decimal(60,24));

754

insert into t1 values (0.0,0.0);

755

select f1 from t1 union all select f2 from t1;

756

select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1

757

union all

758

select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;

759

drop table t1;

760

761

762

# Test that union with VARCHAR produces dynamic row tables

763

764

765

create table t1 (a varchar(5));

766

create table t2 select * from t1 union select 'abcdefghijkl';

767

show create table t2;

768

select row_format from information_schema.TABLES where table_schema="test" and table_name="t2";

769

alter table t2 ROW_FORMAT=fixed;

770

show create table t2;

771

drop table t1,t2;

772

773

774

# correct conversion long string to TEXT (BUG#10025)

775

776

777

CREATE TABLE t1 (a mediumtext);

778

CREATE TABLE t2 (b varchar(20));

779

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

780

SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;

781

create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;

782

show create table t3;

783

drop tables t1,t2,t3;

784

785

786

# Extended fix to Bug#10025 - the test above should result to mediumtext

787

# and the one below to longtext. Earlier above test resulted to longtext

788

# type also.

789

790

791

CREATE TABLE t1 (a longtext);

792

CREATE TABLE t2 (b varchar(20));

793

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

794

SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;

795

create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;

796

show create table t3;

797

drop tables t1,t2,t3;

798

799

800

# Testing here that mediumtext converts into longtext if the result

801

# exceeds mediumtext maximum length

802

803

804

SELECT @tmp_max:= @@max_allowed_packet;

805

SET max_allowed_packet=25000000;

806

CREATE TABLE t1 (a mediumtext);

807

CREATE TABLE t2 (b varchar(20));

808

INSERT INTO t1 VALUES ('a');

809

CREATE TABLE t3 SELECT REPEAT(a,20000000) AS a FROM t1 UNION SELECT b FROM t2;

810

SHOW CREATE TABLE t3;

811

DROP TABLES t1,t3;

812

CREATE TABLE t1 (a tinytext);

813

INSERT INTO t1 VALUES ('a');

814

CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;

815

SHOW CREATE TABLE t3;

816

DROP TABLES t1,t3;

817

CREATE TABLE t1 (a mediumtext);

818

INSERT INTO t1 VALUES ('a');

819

CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;

820

SHOW CREATE TABLE t3;

821

DROP TABLES t1,t3;

822

CREATE TABLE t1 (a tinyblob);

823

INSERT INTO t1 VALUES ('a');

824

CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;

825

SHOW CREATE TABLE t3;

826

DROP TABLES t1,t2,t3;

827

SET max_allowed_packet:= @tmp_max;

828

829

830

# Bug #10032 Bug in parsing UNION with ORDER BY when one node does not use FROM

831

832

833

create table t1 ( id int not null auto_increment, primary key (id), col1 int);

834

insert into t1 (col1) values (2),(3),(4),(5),(6);

835

select 99 union all select id from t1 order by 1;

836

select id from t1 union all select 99 order by 1;

837

drop table t1;

838

839

# End of 4.1 tests

840

841

842

# Bug#12185: Data type aggregation may produce wrong result

843

844

create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp, f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text);

845

create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1;

846

show create table t2;

847

drop table t1, t2;

848

849

850

# Bug#18175: Union select over 129 tables with a sum function fails.

851

852