~stewart/drizzle/embedded-innodb-create-select-transaction-arrgh

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) unsigned default '0') ENGINE=MyISAM;

280

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

281

CREATE TABLE t2 ( id int(3) unsigned 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 UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);

324

create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED 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 tinyint, it2 tinyint 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

-- error 1267

430

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

431

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

432

show create table t1;

433

drop table t1;

434

435

436

# conversion memory->disk table

437

438

create table t1 (s char(200));

439

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

440

create table t2 select * from t1;

441

insert into t2 select * from t1;

442

insert into t1 select * from t2;

443

insert into t2 select * from t1;

444

insert into t1 select * from t2;

445

insert into t2 select * from t1;

446

set local tmp_table_size=1024;

447

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

448

select count(*) from t1;

449

select count(*) from t2;

450

drop table t1,t2;

451

set local tmp_table_size=default;

452

453

454

# slow logging

455

456

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

457

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

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

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

463

FLUSH STATUS;

464

show status like 'Slow_queries';

465

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

466

show status like 'Slow_queries';

467

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

468

show status like 'Slow_queries';

469

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

470

show status like 'Slow_queries';

471

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

472

show status like 'Slow_queries';

473

# additional test for examined rows

474

flush status;

475

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

476

show status like 'Slow_queries';

477

drop table t1;

478

479

480

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

481

482

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;

483

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

484

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

485

drop table t1;

486

487

488

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

489

# same table)

490

491

create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);

492

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

493

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

494

alter table t1 add index myindex (col2);

495

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

496

drop table t1;

497

498

499

# Incorrect handling of UNION ALL (Bug #1428)

500

501

create table t1 (i int);

502

insert into t1 values (1);

503

select * from t1 UNION select * from t1;

504

select * from t1 UNION ALL select * from t1;

505

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

506

drop table t1;

507

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

508

set sql_select_limit=1;

509

select 1 union select 2;

510

(select 1) union (select 2);

511

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

512

set sql_select_limit=default;

513

514

515

# ORDER with LIMIT

516

517

create table t1 (a int);

518

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

519

create table t2 (a int);

520

insert into t2 values (100);

521

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

522

SET SQL_SELECT_LIMIT=1;

523

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

524

drop table t1, t2;

525

set sql_select_limit=default;

526

527

528

# nonexisting column in global ORDER BY

529

530

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

531

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

532

--error 1054

533

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

534

drop table t1, t2;

535

536

537

# length detecting

538

539

CREATE TABLE t1 (uid int(1));

540

INSERT INTO t1 SELECT 150;

541

SELECT 'a' UNION SELECT uid FROM t1;

542

drop table t1;

543

544

545

# parser stack overflow

546

547

CREATE TABLE t1 ( ID1 int(10) unsigned 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));

548

549

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

550

(SELECT * FROM t1 AS PARTITIONED, t2 AS

551

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

552

(SELECT * FROM t1 AS PARTITIONED, t2 AS

553

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

554

(SELECT * FROM t1 AS PARTITIONED, t2 AS

555

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

556

(SELECT * FROM t1 AS PARTITIONED, t2 AS

557

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

558

(SELECT * FROM t1 AS PARTITIONED, t2 AS

559

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

560

(SELECT * FROM t1 AS PARTITIONED, t2 AS

561

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

562

(SELECT * FROM t1 AS PARTITIONED, t2 AS

563

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

564

(SELECT * FROM t1 AS PARTITIONED, t2 AS

565

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

566

(SELECT * FROM t1 AS PARTITIONED, t2 AS

567

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

568

(SELECT * FROM t1 AS PARTITIONED, t2 AS

569

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

570

(SELECT * FROM t1 AS PARTITIONED, t2 AS

571

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

572

(SELECT * FROM t1 AS PARTITIONED, t2 AS

573

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1);

574

drop table t1,t2;

575

576

577

# merging ENUM and SET fields in one UNION

578

579

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

580

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

581

insert into t1 values ('No');

582

insert into t2 values ('bbb');

583

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

584

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

585

insert into t3 values (1);

586

insert into t4 values (3);

587

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

588

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

589

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

590

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

591

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

592

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

593

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

594

drop table t1,t2,t3,t4;

595

596

597

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

598

599

create table t1 as

600

(select _latin1'test') union

601

(select _latin1'TEST') union

602

(select _latin1'TeST');

603

show create table t1;

604

select count(*) from t1;

605

drop table t1;

606

607

create table t1 as

608

(select _latin1'test' collate latin1_bin) union

609

(select _latin1'TEST') union

610

(select _latin1'TeST');

611

show create table t1;

612

select count(*) from t1;

613

drop table t1;

614

615

create table t1 as

616

(select _latin1'test') union

617

(select _latin1'TEST' collate latin1_bin) union

618

(select _latin1'TeST');

619

show create table t1;

620

select count(*) from t1;

621

drop table t1;

622

623

create table t1 as

624

(select _latin1'test') union

625

(select _latin1'TEST') union

626

(select _latin1'TeST' collate latin1_bin);

627

show create table t1;

628

select count(*) from t1;

629

drop table t1;

630

631

create table t2 (

632

a char character set latin1 collate latin1_swedish_ci,

633

b char character set latin1 collate latin1_german1_ci);

634

--error 1271

635

create table t1 as

636

(select a from t2) union

637

(select b from t2);

638

create table t1 as

639

(select a collate latin1_german1_ci from t2) union

640

(select b from t2);

641

show create table t1;

642

drop table t1;

643

create table t1 as

644

(select a from t2) union

645

(select b collate latin1_german1_ci from t2);

646

show create table t1;

647

drop table t1;

648

create table t1 as

649

(select a from t2) union

650

(select b from t2) union

651

(select 'c' collate latin1_german1_ci from t2);

652

show create table t1;

653

drop table t1;

654

drop table t2;

655

656

657

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

658

659

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

660

create table t2

661

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

662

union

663

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

664

order by f2, a1;

665

show columns from t2;

666

drop table t1, t2;

667

668

create table t1 (f1 int);

669

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

670

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

671

create table t4

672

(

673

select t2.f3 as sdate

674

from t1

675

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

676

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

677

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

678

)

679

union

680

(

681

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

682

from t1

683

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

684

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

685

group by t1.f1

686

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

687

)

688

order by sdate;

689

show columns from t4;

690

drop table t1, t2, t3, t4;

691

692

693

# Bug #2435 UNION with parentheses not supported

694

695

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

696

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

697

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

698

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

699

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

700

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

701

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

702

drop table t1;

703

704

705

# Bugs#6519 UNION with collation binary and latin1_swedish_ci fails

706

707

set @val:=6;

708

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

709

710

711

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

712

713

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

714

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

715

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

716

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

717

drop table t1;

718

719

720

# Enum merging test

721

722

CREATE TABLE t1 (

723

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

724

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

725

c ENUM("one", "two")

726

);

727

show create table t1;

728

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

729

create table t2 select NULL union select a from t1;

730

show columns from t2;

731

drop table t2;

732

create table t2 select a from t1 union select NULL;

733

show columns from t2;

734

drop table t2;

735

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

736

show columns from t2;

737

drop table t2;

738

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

739

drop table t2;

740

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

741

show columns from t2;

742

drop table t2, t1;

743

744

745

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

746

747

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

748

insert into t1 values (0.0,0.0);

749

select f1 from t1 union all select f2 from t1;

750

select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1

751

union all

752

select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;

753

drop table t1;

754

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

755

insert into t1 values (0.0,0.0);

756

select f1 from t1 union all select f2 from t1;

757

select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1

758

union all

759

select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;

760

drop table t1;

761

762

763

# Test that union with VARCHAR produces dynamic row tables

764

765

766

create table t1 (a varchar(5));

767

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

768

show create table t2;

769

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

770

alter table t2 ROW_FORMAT=fixed;

771

show create table t2;

772

drop table t1,t2;

773

774

775

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

776

777

778

CREATE TABLE t1 (a mediumtext);

779

CREATE TABLE t2 (b varchar(20));

780

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

781

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

782

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

783

show create table t3;

784

drop tables t1,t2,t3;

785

786

787

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

788

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

789

# type also.

790

791

792

CREATE TABLE t1 (a longtext);

793

CREATE TABLE t2 (b varchar(20));

794

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

795

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

796

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

797

show create table t3;

798

drop tables t1,t2,t3;

799

800

801

# Testing here that mediumtext converts into longtext if the result

802

# exceeds mediumtext maximum length

803

804

805

SELECT @tmp_max:= @@max_allowed_packet;

806

SET max_allowed_packet=25000000;

807

CREATE TABLE t1 (a mediumtext);

808

CREATE TABLE t2 (b varchar(20));

809

INSERT INTO t1 VALUES ('a');

810

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

811

SHOW CREATE TABLE t3;

812

DROP TABLES t1,t3;

813

CREATE TABLE t1 (a tinytext);

814

INSERT INTO t1 VALUES ('a');

815

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

816

SHOW CREATE TABLE t3;

817

DROP TABLES t1,t3;

818

CREATE TABLE t1 (a mediumtext);

819

INSERT INTO t1 VALUES ('a');

820

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

821

SHOW CREATE TABLE t3;

822

DROP TABLES t1,t3;

823

CREATE TABLE t1 (a tinyblob);

824

INSERT INTO t1 VALUES ('a');

825

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

826

SHOW CREATE TABLE t3;

827

DROP TABLES t1,t2,t3;

828

SET max_allowed_packet:= @tmp_max;

829

830

831

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

832

833

834

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

835

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

836

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

837

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

838

drop table t1;

839

840

# End of 4.1 tests

841

842

843

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

844

845

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

846

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

847

show create table t2;

848

drop table t1, t2;

849

850

851

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

852

853