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

282

284

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

283

285

"foo1", "bar1");

284

286

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

309

311

drop table t1,t2;

310

312

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

311

313

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

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

313

315

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

314

316

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

315

317

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

316

318

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

drop table t3, t1, t2;

319

321

320

322

321

323

# fix_fields problem

322

324

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

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

325

327

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

328

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

327

329

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;

332

334

333

335

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

334

336

select * from t1;

337

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

335

338

show create table t1;

336

339

drop table t1;

337

340

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

338

341

select * from t1;

342

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

339

343

show create table t1;

340

344

drop table t1;

341

345

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

342

346

select * from t1;

347

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

343

348

show create table t1;

344

349

drop table t1;

345

350

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

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

348

353

349

354

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

350

355

select * from t1;

356

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

351

357

show create table t1;

352

358

drop table t1;

353

359

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

354

360

select * from t1;

361

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

355

362

show create table t1;

356

363

drop table t1;

357

364

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

358

365

select * from t1;

366

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

359

367

show create table t1;

360

368

drop table t1;

361

369

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

362

370

select * from t1;

371

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

363

372

show create table t1;

364

373

drop table t1;

365

374

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

366

375

select * from t1;

376

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

367

377

show create table t1;

368

378

drop table t1;

369

379

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

370

380

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;

381

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

375

382

show create table t1;

376

383

drop table t1;

377

384

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

378

385

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;

386

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

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/

391

392

show create table t1;

392

393

drop table t1;

393

394

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

394

395

select trim(dt) from t1;

396

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

395

397

show create table t1;

396

398

drop table t1;

397

399

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

398

400

select * from t1;

401

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

399

402

show create table t1;

400

403

drop table t1;

401

404

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

402

405

select * from t1;

406

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

403

407

show create table t1;

404

408

drop table t1;

405

409

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

406

410

select * from t1;

411

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

407

412

show create table t1;

408

413

drop table t1;

409

414

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

410

415

select * from t1;

416

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

411

417

show create table t1;

412

418

drop table t1;

413

419

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

414

420

select * from t1;

421

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

415

422

show create table t1;

416

423

drop table t1;

417

424

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

418

425

select * from t1;

426

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

419

427

show create table t1;

420

428

drop table t1;

421

429

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

422

430

select * from t1;

431

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

423

432

show create table t1;

424

433

drop table t1,t2;

425

434

create table t1 select 1 union select -1;

426

435

select * from t1;

436

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

427

437

show create table t1;

428

438

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

439

--error ER_BAD_FIELD_ERROR

440

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

441

--error ER_BAD_FIELD_ERROR

442

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

443

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

444

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

431

445

show create table t1;

432

446

drop table t1;

433

447

444

457

insert into t1 select * from t2;

445

458

insert into t2 select * from t1;

446

459

set local tmp_table_size=1024;

447

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

460

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

448

461

select count(*) from t1;

449

462

select count(*) from t2;

450

463

drop table t1,t2;

461

474

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

462

475

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

463

476

FLUSH STATUS;

477

--replace_column 2 #

464

478

show status like 'Slow_queries';

465

479

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

480

--replace_column 2 #

466

481

show status like 'Slow_queries';

467

482

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

483

--replace_column 2 #

468

484

show status like 'Slow_queries';

469

485

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

486

--replace_column 2 #

470

487

show status like 'Slow_queries';

471

488

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

489

--replace_column 2 #

472

490

show status like 'Slow_queries';

473

491

# additional test for examined rows

474

492

flush status;

475

493

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 #

476

495

show status like 'Slow_queries';

477

496

drop table t1;

478

497

479

498

480

499

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

481

500

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;

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

483

502

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

503

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

504

drop table t1;

527

546

528

547

# nonexisting column in global ORDER BY

529

548

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

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

533

552

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

534

553

drop table t1, t2;

535

554

536

555

537

556

# length detecting

538

557

539

CREATE TABLE t1 (uid int(1));

558

CREATE TABLE t1 (uid int);

540

559

INSERT INTO t1 SELECT 150;

541

560

SELECT 'a' UNION SELECT uid FROM t1;

542

561

drop table t1;

544

563

545

564

# parser stack overflow

546

565

547

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

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

548

567

549

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

568

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

550

569

(SELECT * FROM t1 AS PARTITIONED, t2 AS

551

570

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

552

571

(SELECT * FROM t1 AS PARTITIONED, t2 AS

580

599

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

581

600

insert into t1 values ('No');

582

601

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

602

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

603

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

585

604

insert into t3 values (1);

605

--error ER_INVALID_ENUM_VALUE # Bad enum

586

606

insert into t4 values (3);

587

607

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

588

608

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

597

617

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

598

618

599

619

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

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/

646

680

show create table t1;

647

681

drop table t1;

648

682

create table t1 as

649

683

(select a from t2) union

650

684

(select b from t2) union

651

(select 'c' collate latin1_german1_ci from t2);

685

(select 'c' collate utf8_spanish_ci from t2);

686

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

652

687

show create table t1;

653

688

drop table t1;

654

689

drop table t2;

710

745

711

746

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

712

747

713

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

714

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

748

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

749

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

715

750

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

751

--error ER_TABLE_UNKNOWN

716

752

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

753

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

717

754

drop table t1;

718

755

756

757

# Commenting out this test until Bug 308841 is fixed

719

758

720

759

# Enum merging test

721

760

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;

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

743

783

744

784

745

785

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

765

805

766

806

create table t1 (a varchar(5));

767

807

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

808

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

768

809

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;

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/

771

812

show create table t2;

772

813

drop table t1,t2;

773

814

780

821

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

781

822

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

782

823

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

824

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

783

825

show create table t3;

784

826

drop tables t1,t2,t3;

785

827

794

836

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

795

837

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

796

838

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

839

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

797

840

show create table t3;

798

841

drop tables t1,t2,t3;

799

842

808

851

CREATE TABLE t2 (b varchar(20));

809

852

INSERT INTO t1 VALUES ('a');

810

853

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/

811

855

SHOW CREATE TABLE t3;

812

856

DROP TABLES t1,t3;

813

857

CREATE TABLE t1 (a tinytext);

814

858

INSERT INTO t1 VALUES ('a');

815

859

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/

816

861

SHOW CREATE TABLE t3;

817

862

DROP TABLES t1,t3;

818

863

CREATE TABLE t1 (a mediumtext);

819

864

INSERT INTO t1 VALUES ('a');

820

865

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/

821

867

SHOW CREATE TABLE t3;

822

868

DROP TABLES t1,t3;

823

869

CREATE TABLE t1 (a tinyblob);

824

870

INSERT INTO t1 VALUES ('a');

825

871

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/

826

873

SHOW CREATE TABLE t3;

827

874

DROP TABLES t1,t2,t3;

828

875

SET max_allowed_packet:= @tmp_max;

842

889

843

890

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

844

891

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

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

846

893

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

894

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

847

895

show create table t2;

848

896

drop table t1, t2;

849

897

898

946

# Bug #16881: password() and union select

899

947

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

900

948

901

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

949

select '12' union select '12345';

902

950

903

951

904

952

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

908

956

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

909

957

910

958

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

911

--error 1054

959

--error ER_BAD_FIELD_ERROR

912

960

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

913

961

914

962

DROP TABLE t1;

916

964

917

965

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

918

966

919

--error 1221

967

--error ER_WRONG_USAGE

920

968

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

921

969

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

922

970

select @var;

923

--error 1172

971

--error ER_TOO_MANY_ROWS

924

972

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

925

973

926

974

Older »