~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

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 null, Field, Count From t1 Where Month=20030901 and Type=2;

608

Warnings:

609

Warning 1048 Column 'No' cannot be null

610

Warning 1048 Column 'No' cannot be null

611

select * from t2;

612

No Field Count

613

0 1 100

614

0 2 100

615

drop table t1, t2;

616

CREATE TABLE t1 (

617

ID int(11) NOT NULL auto_increment,

618

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

619

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

620

PRIMARY KEY (ID),

621

KEY t1$NO (SEQ,NO)

622

) ENGINE=MyISAM;

623

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

624

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

625

ID NO SEQ

626

1 1 1

627

drop table t1;

628

create table t1 (f1 int);

629

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

630

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

631

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

632

select * from t2;

633

ff1 ff2

634

1 2

635

2 1

636

drop table t1, t2;

637

create table t1 (a int unique);

638

create table t2 (a int, b int);

639

create table t3 (c int, d int);

640

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

641

insert into t2 values (1,2);

642

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

643

select * from t1;

644

645

646

647

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

648

select * from t1;

649

650

651

652

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

653

select * from t1;

654

655

656

657

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

658

select * from t1;

659

660

661

662

663

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

664

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

665

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

666

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

667

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

668

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

669

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

670

drop table t1,t2,t3;

671

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

672

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

673

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

674

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

675

select * from t1;

676

677

2000

678

2001

679

2002

680

drop table t1;

681

create table t1(x int, y int);

682

create table t2(x int, z int);

683

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

684

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

685

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

686

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

687

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

688

drop table t1,t2;

689

CREATE TABLE t1 (a int PRIMARY KEY);

690

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

691

flush status;

692

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

693

show status like 'Handler_read%';

694

Variable_name Value

695

Handler_read_first 1

696

Handler_read_key 0

697

Handler_read_next 0

698

Handler_read_prev 0

699

Handler_read_rnd 0

700

Handler_read_rnd_next 1

701

DROP TABLE t1;

702

CREATE TABLE t1 (

703

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

704

f2 varchar(100) NOT NULL default ''

705

);

706

CREATE TABLE t2 (

707

f1 varchar(10) NOT NULL default '',

708

f2 char(3) NOT NULL default '',

709

PRIMARY KEY (`f1`),

710

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

711

);

712

INSERT INTO t1 values(NULL, '');

713

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

714

SELECT COUNT(*) FROM t1;

715

COUNT(*)

716

717

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

718

min(t2.f1)

719

INSERT INTO t1 (f2)

720

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

721

SELECT COUNT(*) FROM t1;

722

COUNT(*)

723

724

SELECT * FROM t1;

725

f1 f2

726

727

DROP TABLE t1, t2;

728

CREATE TABLE t1 (x int, y int);

729

CREATE TABLE t2 (z int, y int);

730

CREATE TABLE t3 (a int, b int);

731

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

732

DROP TABLE IF EXISTS t1,t2,t3;

733

CREATE DATABASE bug21774_1;

734

CREATE DATABASE bug21774_2;

735

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

736

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

737

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

738

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

739

use bug21774_1;

740

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

741

DROP DATABASE bug21774_1;

742

DROP DATABASE bug21774_2;

743

USE test;

744

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

745

insert into t1 values (1,1);

746

affected rows: 1

747

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

748

affected rows: 0

749

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

750

affected rows: 2

751

select * from t1;

752

f1 f2

753

1 2

754

drop table t1;

755

CREATE TABLE t1 (f1 INT, f2 INT );

756

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

757

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

758

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

759

INSERT INTO t2 (f1, f2)

760

SELECT f1, f1 FROM t2 src WHERE f1 < 2

761

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

762

SELECT * FROM t2;

763

f1 f2

764

101 1

765

2 2

766

10 10

767

DROP TABLE t1, t2;

768

CREATE TABLE t1 ( a INT KEY, b INT );

769

INSERT INTO t1 VALUES ( 0, 1 );

770

INSERT INTO t1 ( b ) SELECT MAX( b ) FROM t1 WHERE b = 2;

771

ERROR 23000: Duplicate entry '0' for key 'PRIMARY'

772

DROP TABLE t1;

773

SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

774

CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10)));

775

CREATE TABLE t2 (d VARCHAR(10));

776

INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters');

777

EXPLAIN

778

SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;

779

id select_type table type possible_keys key key_len ref rows Extra

780

1 PRIMARY t1 ALL NULL NULL NULL NULL 2

781

2 SUBQUERY t1 ref ix_c ix_c 13 const 1 Using where

782

SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;

783

(SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters')

784

785

786

INSERT INTO t2 (d)

787

SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;

788

INSERT INTO t2 (d)

789

SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1;

790

INSERT INTO t2 (d)

791

SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1))

792

FROM t1;

793

SELECT * FROM t2;

794

795

796

797

798

799

800

801

DROP TABLE t1,t2;

802

CREATE TABLE t1 (

803

id INT AUTO_INCREMENT PRIMARY KEY,

804

prev_id INT,

805

join_id INT DEFAULT 0);

806

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

807

SELECT * FROM t1;

808

id prev_id join_id

809

1 NULL 0

810

2 1 0

811

3 2 0

812

CREATE TABLE t2 (join_id INT);

813

INSERT INTO t2 (join_id) VALUES (0);

814

INSERT INTO t1 (prev_id) SELECT id

815

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

816

ORDER BY id DESC LIMIT 1;

817

SELECT * FROM t1;

818

id prev_id join_id

819

1 NULL 0

820

2 1 0

821

3 2 0

822

4 3 0

823

DROP TABLE t1,t2;

824

825

# Bug#30384: Having SQL_BUFFER_RESULT option in the

826

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

827

828

create table t1(f1 int);

829

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

830

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

831

check table t2 extended;

832

Table Op Msg_type Msg_text

833

test.t2 check status OK

834

drop table t1,t2;

835

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

836

837

# Bug #46075: Assertion failed: 0, file .\protocol.cc, line 416

838

839

CREATE TABLE t1(a INT);

840

SET max_heap_table_size = 16384;

841

SET @old_myisam_data_pointer_size = @@myisam_data_pointer_size;

842

SET GLOBAL myisam_data_pointer_size = 2;

843

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

844

call mtr.add_suppression("mysqld: The table '.*#sql.*' is full");

845

INSERT IGNORE INTO t1 SELECT t1.a FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7;

846

Got one of the listed errors

847

SET GLOBAL myisam_data_pointer_size = @old_myisam_data_pointer_size;

848

DROP TABLE t1;

849

End of 5.0 tests

Older »