~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 default '0', id_master int 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",

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

346

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

347

insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, '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/

382

371

show create table t1;

383

372

drop table t1;

384

373

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

385

374

select * from t1;

386

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

387

375

show create table t1;

388

376

drop table t1;

389

377

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

390

378

select * from t1;

391

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

392

379

show create table t1;

393

380

drop table t1;

394

381

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

395

382

select trim(dt) from t1;

396

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

397

383

show create table t1;

398

384

drop table t1;

399

385

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

400

386

select * from t1;

401

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

402

387

show create table t1;

403

388

drop table t1;

404

389

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

405

390

select * from t1;

406

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

407

391

show create table t1;

408

392

drop table t1;

409

393

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

410

394

select * from t1;

411

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

412

395

show create table t1;

413

396

drop table t1;

414

397

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

415

398

select * from t1;

416

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

417

399

show create table t1;

418

400

drop table t1;

419

401

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

420

402

select * from t1;

421

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

422

403

show create table t1;

423

404

drop table t1;

424

405

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

425

406

select * from t1;

426

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

427

407

show create table t1;

428

408

drop table t1;

429

409

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

430

410

select * from t1;

431

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

432

411

show create table t1;

433

412

drop table t1,t2;

434

413

create table t1 select 1 union select -1;

435

414

select * from t1;

436

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

437

415

show create table t1;

438

416

drop table t1;

439

417

-- error 1054

440

418

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

441

419

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

442

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

443

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

444

420

show create table t1;

445

421

drop table t1;

446

422

474

449

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

475

450

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

476

451

FLUSH STATUS;

477

--replace_column 2 #

478

452

show status like 'Slow_queries';

479

453

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

480

--replace_column 2 #

481

454

show status like 'Slow_queries';

482

455

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

483

--replace_column 2 #

484

456

show status like 'Slow_queries';

485

457

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

486

--replace_column 2 #

487

458

show status like 'Slow_queries';

488

459

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

489

--replace_column 2 #

490

460

show status like 'Slow_queries';

491

461

# additional test for examined rows

492

462

flush status;

493

463

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

464

show status like 'Slow_queries';

496

465

drop table t1;

497

466

498

467

499

468

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

500

469

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

470

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) engine=MyISAM;

502

471

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

472

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

473

drop table t1;

563

532

564

533

# parser stack overflow

565

534

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

535

CREATE TABLE t1 ( ID1 int 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

536

568

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

537

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

569

538

(SELECT * FROM t1 AS PARTITIONED, t2 AS

570

539

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

571

540

(SELECT * FROM t1 AS PARTITIONED, t2 AS

602

571

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

603

572

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

604

573

insert into t3 values (1);

605

--error 1691 # Bad enum

574

--error 1265

606

575

insert into t4 values (3);

607

576

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

608

577

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

617

586

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

618

587

619

588

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/

589

(select _utf8'test') union

590

(select _utf8'TEST') union

591

(select _utf8'TeST');

592

show create table t1;

593

select count(*) from t1;

594

drop table t1;

595

596

create table t1 as

597

(select _utf8'test' collate utf8_bin) union

598

(select _utf8'TEST') union

599

(select _utf8'TeST');

600

show create table t1;

601

select count(*) from t1;

602

drop table t1;

603

604

create table t1 as

605

(select _utf8'test') union

606

(select _utf8'TEST' collate utf8_bin) union

607

(select _utf8'TeST');

608

show create table t1;

609

select count(*) from t1;

610

drop table t1;

611

612

create table t1 as

613

(select _utf8'test') union

614

(select _utf8'TEST') union

615

(select _utf8'TeST' collate utf8_bin);

651

616

show create table t1;

652

617

select count(*) from t1;

653

618

drop table t1;

676

641

create table t1 as

677

642

(select a from t2) union

678

643

(select b collate utf8_swedish_ci from t2);

679

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

680

644

show create table t1;

681

645

drop table t1;

682

646

create table t1 as

683

647

(select a from t2) union

684

648

(select b from t2) union

685

649

(select 'c' collate utf8_spanish_ci from t2);

686

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

687

650

show create table t1;

688

651

drop table t1;

689

652

drop table t2;

745

708

746

709

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

747

710

748

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

711

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

749

712

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

750

713

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

751

714

--error 1146

763

726

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

764

727

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

765

728

#);

766

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

767

729

#show create table t1;

768

730

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

769

731

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

779

741

#drop table t2;

780

742

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

781

743

#show columns from t2;

782

#drop table t2, t1;`

744

#drop table t2, t1;

783

745

784

746

785

747

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

805

767

806

768

create table t1 (a varchar(5));

807

769

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

808

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

809

770

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/

771

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

772

alter table t2 ROW_FORMAT=fixed;

812

773

show create table t2;

813

774

drop table t1,t2;

814

775

821

782

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

822

783

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

823

784

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

824

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

825

785

show create table t3;

826

786

drop tables t1,t2,t3;

827

787

836

796

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

837

797

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

838

798

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

839

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

840

799

show create table t3;

841

800

drop tables t1,t2,t3;

842

801

851

810

CREATE TABLE t2 (b varchar(20));

852

811

INSERT INTO t1 VALUES ('a');

853

812

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

813

SHOW CREATE TABLE t3;

856

814

DROP TABLES t1,t3;

857

815

CREATE TABLE t1 (a tinytext);

858

816

INSERT INTO t1 VALUES ('a');

859

817

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

818

SHOW CREATE TABLE t3;

862

819

DROP TABLES t1,t3;

863

820

CREATE TABLE t1 (a mediumtext);

864

821

INSERT INTO t1 VALUES ('a');

865

822

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

823

SHOW CREATE TABLE t3;

868

824

DROP TABLES t1,t3;

869

825

CREATE TABLE t1 (a tinyblob);

870

826

INSERT INTO t1 VALUES ('a');

871

827

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

828

SHOW CREATE TABLE t3;

874

829

DROP TABLES t1,t2,t3;

875

830

SET max_allowed_packet:= @tmp_max;

891

846

892

847

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

893

848

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

849

show create table t2;

896

850

drop table t1, t2;

897

851

946

900

# Bug #16881: password() and union select

947

901

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

948

902

949

select '12' union select '12345';

903

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

950

904

951

905

952

906

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

Older »