~drizzle-trunk/drizzle/development

CREATE TABLE t1 ( USID INTEGER, ServerID int, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '<UNKNOWN>', NASAddr INTEGER, NASPort INTEGER, NASPortType INTEGER, ConnectSpeed INTEGER, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER, SessionTime INTEGER, PacketsIn INTEGER, OctetsIn INTEGER, PacketsOut INTEGER, OctetsOut INTEGER, TerminateCause INTEGER, UnauthTime int, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL);

577

CREATE TABLE t2 ( USID INTEGER AUTO_INCREMENT, ServerID int, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER, NASPort INTEGER, NASPortType INTEGER, ConnectSpeed INTEGER, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER, SessionTime INTEGER, PacketsIn INTEGER, OctetsIn INTEGER, PacketsOut INTEGER, OctetsOut INTEGER, TerminateCause INTEGER, UnauthTime int, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr));

578

INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121);

579

INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1;

580

drop table t1,t2;

581

CREATE TABLE t1(

582

Month date NOT NULL,

583

Type int NOT NULL auto_increment,

584

Field int NOT NULL,

585

Count int NOT NULL,

586

PRIMARY KEY (Type),

587

UNIQUE KEY Month (Month,Type,Field)

588

);

589

insert into t1 Values (20030901, 1, 1, 100);

590

insert into t1 Values (20030901, 2, 2, 100);

591

insert into t1 Values (20030901, 3, 3, 100);

592

insert into t1 Values (20030901, 4, 4, 100);

593

insert into t1 Values (20030901, 5, 5, 100);

594

select * from t1;

595

Month Type Field Count

596

2003-09-01 1 1 100

597

2003-09-01 2 2 100

598

2003-09-01 3 3 100

599

2003-09-01 4 4 100

600

2003-09-01 5 5 100

601

Select null, Field, Count From t1 Where Month=20030901 and Type=2;

602

NULL Field Count

603

NULL 2 100

604

create table t2(No int not null, Field int not null, Count int not null);

605

insert into t2 Select 0, Field, Count From t1 Where Month=20030901 and Type=2;

606

select * from t2;

607

No Field Count

608

0 2 100

609

drop table t1, t2;

610

CREATE TABLE t1 (

611

ID int NOT NULL auto_increment,

612

NO int NOT NULL default '0',

613

SEQ int NOT NULL default '0',

614

PRIMARY KEY (ID),

615

KEY t1$NO (SEQ,NO)

616

) ENGINE=MyISAM;

617

INSERT INTO t1 (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 1);

618

select SQL_BUFFER_RESULT * from t1 WHERE (SEQ = 1);

619

ID NO SEQ

620

1 1 1

621

drop table t1;

622

create table t1 (f1 int);

623

create table t2 (ff1 int unique, ff2 int default 1);

624

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

625

insert into t2(ff1) select f1 from t1 on duplicate key update ff2=ff2+1;

626

select * from t2;

627

ff1 ff2

628

1 2

629

2 1

630

drop table t1, t2;

631

create table t1 (a int unique);

632

create table t2 (a int, b int);

633

create table t3 (c int, d int);

634

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

635

insert into t2 values (1,2);

636

insert into t3 values (1,6),(3,7);

637

select * from t1;

638

639

640

641

insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b;

642

select * from t1;

643

644

645

646

insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1;

647

select * from t1;

648

649

650

651

insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d;

652

select * from t1;

653

654

655

656

657

insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;

658

insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;

659

ERROR 23000: Column 'a' in field list is ambiguous

660

insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;

661

ERROR 42S22: Unknown column 't2.a' in 'field list'

662

insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;

663

ERROR 42S22: Unknown column 't2.b' in 'field list'

664

drop table t1,t2,t3;

665

create table t1(f1 varchar(5) key);

666

insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;

667

insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;

668

insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;

669

select * from t1;

670

671

2000

672

2001

673

2002

674

drop table t1;

675

create table t1(x int, y int);

676

create table t2(x int, z int);

677

insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x);

678

insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z);

679

ERROR 42S22: Unknown column 'z' in 'field list'

680

insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x);

681

ERROR 42S22: Unknown column 't2.x' in 'field list'

682

drop table t1,t2;

683

CREATE TABLE t1 (a int PRIMARY KEY);

684

INSERT INTO t1 values (1), (2);

685

flush status;

686

INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;

687

show status like 'Handler_read%';

688

Variable_name Value

689

Handler_read_first 1

690

Handler_read_key 2

691

Handler_read_next 0

692

Handler_read_prev 0

693

Handler_read_rnd 0

694

Handler_read_rnd_next 1

695

DROP TABLE t1;

696

CREATE TABLE t1 (

697

f1 int NOT NULL auto_increment PRIMARY KEY,

698

f2 varchar(100) NOT NULL default ''

699

);

700

CREATE TABLE t2 (

701

f1 varchar(10) NOT NULL default '',

702

f2 char(3) NOT NULL default '',

703

PRIMARY KEY (`f1`),

704

KEY `k1` (`f2`, `f1`)

705

);

706

INSERT INTO t1 values(NULL, '');

707

INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');

708

SELECT COUNT(*) FROM t1;

709

COUNT(*)

710

711

SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;

712

min(t2.f1)

713

INSERT INTO t1 (f2)

714

SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;

715

SELECT COUNT(*) FROM t1;

716

COUNT(*)

717

718

SELECT * FROM t1;

719

f1 f2

720

721

DROP TABLE t1, t2;

722

CREATE TABLE t1 (x int, y int);

723

CREATE TABLE t2 (z int, y int);

724

CREATE TABLE t3 (a int, b int);

725

INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);

726

DROP TABLE IF EXISTS t1,t2,t3;

727

CREATE DATABASE bug21774_1;

728

CREATE DATABASE bug21774_2;

729

CREATE TABLE bug21774_1.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));

730

CREATE TABLE bug21774_2.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));

731

CREATE TABLE bug21774_1.t2(id VARCHAR(10) NOT NULL,label VARCHAR(255));

732

INSERT INTO bug21774_2.t1 SELECT t1.* FROM bug21774_1.t1;

733

use bug21774_1;

734

INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;

735

DROP DATABASE bug21774_1;

736

DROP DATABASE bug21774_2;

737

USE test;

738

create table t1(f1 int primary key, f2 int);

739

insert into t1 values (1,1);

740

affected rows: 1

741

insert into t1 values (1,1) on duplicate key update f2=1;

742

affected rows: 0

743

insert into t1 values (1,1) on duplicate key update f2=2;

744

affected rows: 2

745

select * from t1;

746

f1 f2

747

1 2

748

drop table t1;

749

CREATE TABLE t1 (f1 INT, f2 INT );

750

CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT);

751

INSERT INTO t1 VALUES (1,1),(2,2),(10,10);

752

INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;

753

INSERT INTO t2 (f1, f2)

754

SELECT f1, f1 FROM t2 src WHERE f1 < 2

755

ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;

756

SELECT * FROM t2;

757

f1 f2

758

2 2

759

10 10

760

101 1

761

DROP TABLE t1, t2;

762

CREATE TABLE t1 (

763

id INT AUTO_INCREMENT PRIMARY KEY,

764

prev_id INT,

765

join_id INT DEFAULT 0);

766

INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2);

767

SELECT * FROM t1;

768

id prev_id join_id

769

1 NULL 0

770

2 1 0

771

3 2 0

772

CREATE TABLE t2 (join_id INT);

773

INSERT INTO t2 (join_id) VALUES (0);

774

INSERT INTO t1 (prev_id) SELECT id

775

FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id

776

ORDER BY id DESC LIMIT 1;

777

SELECT * FROM t1;

778

id prev_id join_id

779

1 NULL 0

780

2 1 0

781

3 2 0

782

4 3 0

783

DROP TABLE t1,t2;

784

785

# Bug#30384: Having SQL_BUFFER_RESULT option in the

786

# CREATE .. KEY(..) .. SELECT led to creating corrupted index.

787

788

create table t1(f1 int);

789

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

790

create table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1;

791

check table t2 extended;

792

Table Op Msg_type Msg_text

793

test.t2 check status OK

794

drop table t1,t2;

795

##################################################################

Older »