~drizzle-trunk/drizzle/development

CREATE TABLE t1 ( USID INTEGER UNSIGNED, ServerID TINYINT UNSIGNED, 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 UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL);

578

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

579

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

580

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;

581

drop table t1,t2;

582

CREATE TABLE t1(

583

Month date NOT NULL,

584

Type tinyint(3) unsigned NOT NULL auto_increment,

585

Field int(10) unsigned NOT NULL,

586

Count int(10) unsigned NOT NULL,

587

UNIQUE KEY Month (Month,Type,Field)

588

);

589

insert into t1 Values

590

(20030901, 1, 1, 100),

591

(20030901, 1, 2, 100),

592

(20030901, 2, 1, 100),

593

(20030901, 2, 2, 100),

594

(20030901, 3, 1, 100);

595

select * from t1;

596

Month Type Field Count

597

2003-09-01 1 1 100

598

2003-09-01 1 2 100

599

2003-09-01 2 1 100

600

2003-09-01 2 2 100

601

2003-09-01 3 1 100

602

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

603

NULL Field Count

604

NULL 1 100

605

NULL 2 100

606

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

607

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

608

select * from t2;

609

No Field Count

610

0 1 100

611

0 2 100

612

drop table t1, t2;

613

CREATE TABLE t1 (

614

ID int(11) NOT NULL auto_increment,

615

NO int(11) NOT NULL default '0',

616

SEQ int(11) NOT NULL default '0',

617

PRIMARY KEY (ID),

618

KEY t1$NO (SEQ,NO)

619

) ENGINE=MyISAM;

620

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

621

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

622

ID NO SEQ

623

1 1 1

624

drop table t1;

625

create table t1 (f1 int);

626

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

627

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

628

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

629

select * from t2;

630

ff1 ff2

631

1 2

632

2 1

633

drop table t1, t2;

634

create table t1 (a int unique);

635

create table t2 (a int, b int);

636

create table t3 (c int, d int);

637

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

638

insert into t2 values (1,2);

639

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

640

select * from t1;

641

642

643

644

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

645

select * from t1;

646

647

648

649

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

650

select * from t1;

651

652

653

654

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

655

select * from t1;

656

657

658

659

660

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

661

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

662

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

663

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

664

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

665

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

666

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

667

drop table t1,t2,t3;

668

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

669

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

670

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

671

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

672

select * from t1;

673

674

2000

675

2001

676

2002

677

drop table t1;

678

create table t1(x int, y int);

679

create table t2(x int, z int);

680

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

681

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

682

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

683

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

684

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

685

drop table t1,t2;

686

CREATE TABLE t1 (a int PRIMARY KEY);

687

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

688

flush status;

689

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

690

show status like 'Handler_read%';

691

Variable_name Value

692

Handler_read_first 1

693

Handler_read_key 0

694

Handler_read_next 0

695

Handler_read_prev 0

696

Handler_read_rnd 0

697

Handler_read_rnd_next 1

698

DROP TABLE t1;

699

CREATE TABLE t1 (

700

f1 int(10) unsigned NOT NULL auto_increment PRIMARY KEY,

701

f2 varchar(100) NOT NULL default ''

702

);

703

CREATE TABLE t2 (

704

f1 varchar(10) NOT NULL default '',

705

f2 char(3) NOT NULL default '',

706

PRIMARY KEY (`f1`),

707

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

708

);

709

INSERT INTO t1 values(NULL, '');

710

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

711

SELECT COUNT(*) FROM t1;

712

COUNT(*)

713

714

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

715

min(t2.f1)

716

INSERT INTO t1 (f2)

717

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

718

SELECT COUNT(*) FROM t1;

719

COUNT(*)

720

721

SELECT * FROM t1;

722

f1 f2

723

724

DROP TABLE t1, t2;

725

CREATE TABLE t1 (x int, y int);

726

CREATE TABLE t2 (z int, y int);

727

CREATE TABLE t3 (a int, b int);

728

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

729

DROP TABLE IF EXISTS t1,t2,t3;

730

CREATE DATABASE bug21774_1;

731

CREATE DATABASE bug21774_2;

732

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

733

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

734

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

735

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

736

use bug21774_1;

737

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

738

DROP DATABASE bug21774_1;

739

DROP DATABASE bug21774_2;

740

USE test;

741

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

742

insert into t1 values (1,1);

743

affected rows: 1

744

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

745

affected rows: 0

746

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

747

affected rows: 2

748

select * from t1;

749

f1 f2

750

1 2

751

drop table t1;

752

CREATE TABLE t1 (f1 INT, f2 INT );

753

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

754

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

755

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

756

INSERT INTO t2 (f1, f2)

757

SELECT f1, f1 FROM t2 src WHERE f1 < 2

758

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

759

SELECT * FROM t2;

760

f1 f2

761

101 1

762

2 2

763

10 10

764

DROP TABLE t1, t2;

765

CREATE TABLE t1 (

766

id INT AUTO_INCREMENT PRIMARY KEY,

767

prev_id INT,

768

join_id INT DEFAULT 0);

769

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

770

SELECT * FROM t1;

771

id prev_id join_id

772

1 NULL 0

773

2 1 0

774

3 2 0

775

CREATE TABLE t2 (join_id INT);

776

INSERT INTO t2 (join_id) VALUES (0);

777

INSERT INTO t1 (prev_id) SELECT id

778

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

779

ORDER BY id DESC LIMIT 1;

780

SELECT * FROM t1;

781

id prev_id join_id

782

1 NULL 0

783

2 1 0

784

3 2 0

785

4 3 0

786

DROP TABLE t1,t2;

787

788

# Bug#30384: Having SQL_BUFFER_RESULT option in the

789

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

790

791

create table t1(f1 int);

792

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

793

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

794

check table t2 extended;

795

Table Op Msg_type Msg_text

796

test.t2 check status OK

797

drop table t1,t2;

798

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

Older »