~drizzle-trunk/drizzle/development

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

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;

284

282

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

285

283

"foo1", "bar1");

286

284

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

311

309

drop table t1,t2;

312

310

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

313

311

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

314

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 t1(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references t2(id) );

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

315

313

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

316

314

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

317

315

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

318

316

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

319

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;

320

drop table t3, t1, t2;

318

drop table t1, t2, t3;

321

319

322

320

323

321

# fix_fields problem

324

322

325

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

326

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

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

327

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

328

326

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

329

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;

334

332

335

333

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

336

334

select * from t1;

337

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

338

335

show create table t1;

339

336

drop table t1;

340

337

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

341

338

select * from t1;

342

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

343

339

show create table t1;

344

340

drop table t1;

345

341

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

346

342

select * from t1;

347

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

348

343

show create table t1;

349

344

drop table t1;

350

345

351

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

352

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

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

353

348

354

349

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

355

350

select * from t1;

356

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

357

351

show create table t1;

358

352

drop table t1;

359

353

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

360

354

select * from t1;

361

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

362

355

show create table t1;

363

356

drop table t1;

364

357

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

365

358

select * from t1;

366

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

367

359

show create table t1;

368

360

drop table t1;

369

361

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

370

362

select * from t1;

371

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

372

363

show create table t1;

373

364

drop table t1;

374

365

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

375

366

select * from t1;

376

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

377

367

show create table t1;

378

368

drop table t1;

379

369

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

380

370

select * from t1;

381

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

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;

382

375

show create table t1;

383

376

drop table t1;

384

377

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

385

378

select * from t1;

386

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

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

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

392

391

show create table t1;

393

392

drop table t1;

394

393

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

395

394

select trim(dt) from t1;

396

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

397

395

show create table t1;

398

396

drop table t1;

399

397

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

400

398

select * from t1;

401

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

402

399

show create table t1;

403

400

drop table t1;

404

401

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

405

402

select * from t1;

406

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

407

403

show create table t1;

408

404

drop table t1;

409

405

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

410

406

select * from t1;

411

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

412

407

show create table t1;

413

408

drop table t1;

414

409

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

415

410

select * from t1;

416

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

417

411

show create table t1;

418

412

drop table t1;

419

413

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

420

414

select * from t1;

421

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

422

415

show create table t1;

423

416

drop table t1;

424

417

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

425

418

select * from t1;

426

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

427

419

show create table t1;

428

420

drop table t1;

429

421

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

430

422

select * from t1;

431

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

432

423

show create table t1;

433

424

drop table t1,t2;

434

425

create table t1 select 1 union select -1;

435

426

select * from t1;

436

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

437

427

show create table t1;

438

428

drop table t1;

439

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

440

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

441

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

442

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

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

443

432

show create table t1;

444

433

drop table t1;

445

434

457

444

insert into t1 select * from t2;

458

445

insert into t2 select * from t1;

459

446

set local tmp_table_size=1024;

460

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

447

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

461

448

select count(*) from t1;

462

449

select count(*) from t2;

463

450

drop table t1,t2;

474

461

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

475

462

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

476

463

FLUSH STATUS;

477

--replace_column 2 #

478

464

show status like 'Slow_queries';

479

465

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

480

--replace_column 2 #

481

466

show status like 'Slow_queries';

482

467

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

483

--replace_column 2 #

484

468

show status like 'Slow_queries';

485

469

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

486

--replace_column 2 #

487

470

show status like 'Slow_queries';

488

471

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

489

--replace_column 2 #

490

472

show status like 'Slow_queries';

491

473

# additional test for examined rows

492

474

flush status;

493

475

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

494

--replace_column 2 #

495

476

show status like 'Slow_queries';

496

477

drop table t1;

497

478

498

479

499

480

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

500

481

501

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

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;

502

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

503

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

504

485

drop table t1;

507

488

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

508

489

# same table)

509

490

510

create table t1 (col1 int, col2 int);

491

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

511

492

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

512

493

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

513

494

alter table t1 add index myindex (col2);

546

527

547

528

# nonexisting column in global ORDER BY

548

529

549

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

550

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

551

--error ER_BAD_FIELD_ERROR

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

552

533

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

553

534

drop table t1, t2;

554

535

555

536

556

537

# length detecting

557

538

558

CREATE TABLE t1 (uid int);

539

CREATE TABLE t1 (uid int(1));

559

540

INSERT INTO t1 SELECT 150;

560

541

SELECT 'a' UNION SELECT uid FROM t1;

561

542

drop table t1;

563

544

564

545

# parser stack overflow

565

546

566

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

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

567

548

568

CREATE TABLE t2 ( ID int NOT NULL DEFAULT '0' , DATA1 timestamp NULL, PRIMARY KEY (ID));

549

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

569

550

(SELECT * FROM t1 AS PARTITIONED, t2 AS

570

551

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

571

552

(SELECT * FROM t1 AS PARTITIONED, t2 AS

599

580

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

600

581

insert into t1 values ('No');

601

582

insert into t2 values ('bbb');

602

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

603

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

583

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

584

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

604

585

insert into t3 values (1);

605

--error ER_INVALID_ENUM_VALUE # Bad enum

606

586

insert into t4 values (3);

607

587

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

608

588

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

617

597

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

618

598

619

599

create table t1 as

620

(select 'test') union

621

(select 'TEST') union

622

(select 'TeST');

623

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

624

show create table t1;

625

select count(*) from t1;

626

drop table t1;

627

628

create table t1 as

629

(select 'test' collate utf8_bin) union

630

(select 'TEST') union

631

(select 'TeST');

632

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

633

show create table t1;

634

select count(*) from t1;

635

drop table t1;

636

637

create table t1 as

638

(select 'test') union

639

(select 'TEST' collate utf8_bin) union

640

(select 'TeST');

641

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

642

show create table t1;

643

select count(*) from t1;

644

drop table t1;

645

646

create table t1 as

647

(select 'test') union

648

(select 'TEST') union

649

(select 'TeST' collate utf8_bin);

650

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

651

show create table t1;

652

select count(*) from t1;

653

drop table t1;

654

655

# Drizzle doesn't support specifying character set, it is all UTF8

656

--error ER_PARSE_ERROR

657

create table t2 (

658

a char character set utf8 collate utf8_swedish_ci,

659

b char character set utf8 collate utf8_spanish_ci);

660

661

create table t2 (

662

a char collate utf8_swedish_ci,

663

b char collate utf8_spanish_ci);

664

--error ER_CANT_AGGREGATE_NCOLLATIONS

665

create table t1 as

666

(select a from t2) union

667

(select b from t2);

668

669

--error ER_WRONG_COLUMN_NAME

670

create table t1 as

671

(select a collate utf8_swedish_ci from t2) union

672

(select b from t2);

673

#show create table t1;

674

#drop table t1;

675

676

create table t1 as

677

(select a from t2) union

678

(select b collate utf8_swedish_ci from t2);

679

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

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

680

646

show create table t1;

681

647

drop table t1;

682

648

create table t1 as

683

649

(select a from t2) union

684

650

(select b from t2) union

685

(select 'c' collate utf8_spanish_ci from t2);

686

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

651

(select 'c' collate latin1_german1_ci from t2);

687

652

show create table t1;

688

653

drop table t1;

689

654

drop table t2;

745

710

746

711

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

747

712

748

select concat('a', 'b' collate utf8_bin);

749

create table t1 (foo varchar(100)) collate=utf8_bin;

713

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

714

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

750

715

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

751

--error ER_NO_SUCH_TABLE

752

716

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

753

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

754

717

drop table t1;

755

718

756

757

# Commenting out this test until Bug 308841 is fixed

758

719

759

720

# Enum merging test

760

721

761

#CREATE TABLE t1 (

762

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

763

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

764

# c ENUM("one", "two")

765

#);

766

#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

767

#show create table t1;

768

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

769

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

770

#show columns from t2;

771

#drop table t2;

772

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

773

#show columns from t2;

774

#drop table t2;

775

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

776

#show columns from t2;

777

#drop table t2;

778

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

779

#drop table t2;

780

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

781

#show columns from t2;

782

#drop table t2, t1;`

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;

783

743

784

744

785

745

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

805

765

806

766

create table t1 (a varchar(5));

807

767

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

808

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

809

768

show create table t2;

810

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

811

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

769

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

770

alter table t2 ROW_FORMAT=fixed;

812

771

show create table t2;

813

772

drop table t1,t2;

814

773

821

780

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

822

781

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

823

782

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

824

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

825

783

show create table t3;

826

784

drop tables t1,t2,t3;

827

785

836

794

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

837

795

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

838

796

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

839

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

840

797

show create table t3;

841

798

drop tables t1,t2,t3;

842

799

851

808

CREATE TABLE t2 (b varchar(20));

852

809

INSERT INTO t1 VALUES ('a');

853

810

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

854

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

855

811

SHOW CREATE TABLE t3;

856

812

DROP TABLES t1,t3;

857

813

CREATE TABLE t1 (a tinytext);

858

814

INSERT INTO t1 VALUES ('a');

859

815

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

860

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

861

816

SHOW CREATE TABLE t3;

862

817

DROP TABLES t1,t3;

863

818

CREATE TABLE t1 (a mediumtext);

864

819

INSERT INTO t1 VALUES ('a');

865

820

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

866

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

867

821

SHOW CREATE TABLE t3;

868

822

DROP TABLES t1,t3;

869

823

CREATE TABLE t1 (a tinyblob);

870

824

INSERT INTO t1 VALUES ('a');

871

825

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

872

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

873

826

SHOW CREATE TABLE t3;

874

827

DROP TABLES t1,t2,t3;

875

828

SET max_allowed_packet:= @tmp_max;

889

842

890

843

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

891

844

892

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

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

893

846

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

894

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

895

847

show create table t2;

896

848

drop table t1, t2;

897

849

946

898

# Bug #16881: password() and union select

947

899

# (The issue was poor handling of character set aggregation.)

948

900

949

select '12' union select '12345';

901

select _utf8'12' union select _latin1'12345';

950

902

951

903

952

904

# Bug #26661: UNION with ORDER BY undefined column in FROM list

956

908

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

957

909

958

910

SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a) AS test;

959

--error ER_BAD_FIELD_ERROR

911

--error 1054

960

912

SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test;

961

913

962

914

DROP TABLE t1;

964

916

965

917

# Bug#23345: Wrongly allowed INTO in a non-last select of a UNION.

966

918

967

--error ER_WRONG_USAGE

919

--error 1221

968

920

(select 1 into @var) union (select 1);

969

921

(select 1) union (select 1 into @var);

970

922

select @var;

971

--error ER_TOO_MANY_ROWS

923

--error 1172

972

924

(select 2) union (select 1 into @var);

973

925

974

926

Older »