~drizzle-trunk/drizzle/development

create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;

create TEMPORARY table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');

select id, code, name from t1 order by id;

id code name

8 1 Sinisa

12 1 Ralph

drop table t1;

CREATE TABLE t1 (

create TEMPORARY table t1 (

id int NOT NULL auto_increment,

parent_id int DEFAULT '0' NOT NULL,

level int DEFAULT '0' NOT NULL,

174

t1 1 parent_id 1 parent_id A # NULL NULL BTREE

175

t1 1 level 1 level A # NULL NULL BTREE

176

drop table t1;

177

CREATE TABLE t1 (

177

create TEMPORARY table t1 (

178

gesuchnr int DEFAULT '0' NOT NULL,

179

benutzer_id int DEFAULT '0' NOT NULL,

180

PRIMARY KEY (gesuchnr,benutzer_id)

187

1 1

188

2 1

189

drop table t1;

190

create table t1 (a int) engine=MyISAM;

190

create TEMPORARY table t1 (a int) engine=MyISAM;

191

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

192

optimize table t1;

193

Table Op Msg_type Msg_text

200

Table Op Msg_type Msg_text

201

test.t1 check status OK

202

drop table t1;

203

create table t1 (a int,b varchar(20)) engine=MyISAM;

203

create TEMPORARY table t1 (a int,b varchar(20)) engine=MyISAM;

204

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

205

delete from t1 where a = 1;

206

select * from t1;

215

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

216

t1 1 skr 1 a A # NULL NULL YES BTREE

217

drop table t1;

218

create table t1 (a int,b varchar(20),key(a)) engine=MyISAM;

218

create TEMPORARY table t1 (a int,b varchar(20),key(a)) engine=MyISAM;

219

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

220

select * from t1 where a = 1;

221

a b

222

223

drop table t1;

224

CREATE TABLE t1 (

224

create TEMPORARY table t1 (

225

user_id int DEFAULT '0' NOT NULL,

226

name varchar(100),

227

phone varchar(100),

254

user_id name phone ref_email detail

255

10291 sanjeev 29153373 sansh777@hotmail.com xxx

256

drop table t1;

257

CREATE TABLE t1 (a int not null, b int not null,c int not null,

257

create TEMPORARY table t1 (a int not null, b int not null,c int not null,

258

key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM;

259

show index from t1;

260

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

265

t1 1 a 1 a A # NULL NULL BTREE

266

t1 1 a_2 1 a A # NULL NULL BTREE

267

drop table t1;

268

create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY;

268

create TEMPORARY table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY;

269

alter table t1 engine=MyISAM;

270

insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');

271

select * from t1;

300

3 4 0

301

4 7 0

302

drop table t1;

303

create table t1 (a int not null , b int, primary key (a)) engine = MyISAM;

304

create table t2 (a int not null , b int, primary key (a)) engine = MEMORY;

303

create TEMPORARY table t1 (a int not null , b int, primary key (a)) engine = MyISAM;

304

create TEMPORARY table t2 (a int not null , b int, primary key (a)) engine = MEMORY;

305

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

306

select * from t1;

307

a b

329

2 3

330

3 3

331

drop table t1,t2;

332

CREATE TABLE t1 (

332

create TEMPORARY table t1 (

333

id int NOT NULL auto_increment,

334

ggid varchar(32) binary DEFAULT '' NOT NULL,

335

email varchar(64) DEFAULT '' NOT NULL,

366

select * from t1 where id=999;

367

id ggid email passwd

368

drop table t1;

369

CREATE TABLE t1 (

369

create TEMPORARY table t1 (

370

user_name varchar(12),

371

password text,

372

subscribed char(1),

391

user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 2000-09-07 23:06:59 4

392

user_4 somepassword N 4 4 2 2000-09-07 2000-09-07 23:06:59 5

393

drop table t1;

394

CREATE TABLE t1 (

394

create TEMPORARY table t1 (

395

id int NOT NULL auto_increment,

396

parent_id int DEFAULT '0' NOT NULL,

397

level int DEFAULT '0' NOT NULL,

562

1020 103 2

563

1180 105 2

564

drop table t1;

565

CREATE TABLE t1 (

565

create TEMPORARY table t1 (

566

sca_code char(6) NOT NULL,

567

cat_code char(6) NOT NULL,

568

sca_desc varchar(50),

604

update t1 set sca_pic="test" where sca_pic is null;

605

delete from t1 where sca_code='pd';

606

drop table t1;

607

set @a:=now();

608

CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=MyISAM;

609

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

610

select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;

611

612

613

614

615

select a from t1 natural join t1 as t2 where b >= @a order by a;

616

617

618

619

620

update t1 set a=5 where a=1;

621

select a from t1;

622

623

624

625

626

drop table t1;

627

create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM;

607

TEMPORARY

608

create TEMPORARY table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM;

628

609

insert into t1 values("hello",1),("world",2);

629

610

select * from t1 order by b desc;

630

611

a b

637

618

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

638

619

t1 0 PRIMARY 1 a A # NULL NULL BTREE

639

620

drop table t1;

640

create table t1 (i int, j int ) ENGINE=MyISAM;

621

create TEMPORARY table t1 (i int, j int ) ENGINE=MyISAM;

641

622

insert into t1 values (1,2);

642

623

select * from t1 where i=1 and j=2;

643

624

i j

647

628

i j

648

629

1 2

649

630

drop table t1;

650

CREATE TABLE t1 (

631

create TEMPORARY table t1 (

651

632

a int NOT NULL,

652

633

b int NOT NULL,

653

634

UNIQUE (a, b)

657

638

MIN(B) MAX(b)

658

639

1 1

659

640

drop table t1;

660

CREATE TABLE t1 (a int NOT NULL) engine=MyISAM;

641

create TEMPORARY table t1 (a int NOT NULL) engine=MyISAM;

661

642

INSERT INTO t1 VALUES (1);

662

643

SELECT * FROM t1;

663

644

664

645

665

646

DROP TABLE t1;

666

create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = MyISAM;

647

create TEMPORARY table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = MyISAM;

667

648

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

668

649

explain select * from t1 where a > 0 and a < 50;

669

650

id select_type table type possible_keys key key_len ref rows Extra

670

651

1 SIMPLE t1 system PRIMARY NULL NULL NULL #

671

652

drop table t1;

672

create table t1 (a char(20), unique (a(5))) engine=MyISAM;

653

create TEMPORARY table t1 (a char(20), unique (a(5))) engine=MyISAM;

673

654

drop table t1;

674

create table t1 (a char(20), index (a(5))) engine=MyISAM;

655

create TEMPORARY table t1 (a char(20), index (a(5))) engine=MyISAM;

675

656

show create table t1;

676

657

Table Create Table

677

t1 CREATE TABLE `t1` (

658

t1 CREATE TEMPORARY TABLE `t1` (

678

659

`a` varchar(20) DEFAULT NULL,

679

660

KEY `a` (`a`(5))

680

661

) ENGINE=MyISAM

695

676

2 NULL

696

677

4 NULL

697

678

drop table t1;

698

create table t1

679

create TEMPORARY table t1

699

680

(

700

681

id int auto_increment primary key,

701

682

name varchar(32) not null,

716

697

3 three three value 103

717

698

drop table t1;

718

699

create database mysqltest;

719

create table mysqltest.t1 (a int not null) engine= MyISAM;

700

create TEMPORARY table mysqltest.t1 (a int not null) engine= MyISAM;

720

701

insert into mysqltest.t1 values(1);

721

create table mysqltest.t2 (a int not null) engine= MEMORY;

702

create TEMPORARY table mysqltest.t2 (a int not null) engine= MEMORY;

722

703

insert into mysqltest.t2 values(1);

723

create table mysqltest.t3 (a int not null) engine= MEMORY;

704

create TEMPORARY table mysqltest.t3 (a int not null) engine= MEMORY;

724

705

insert into mysqltest.t3 values(1);

725

706

commit;

726

707

drop database mysqltest;

727

708

show tables from mysqltest;

728

709

ERROR 42000: Unknown database 'mysqltest'

729

710

set autocommit=0;

730

create table t1 (a int not null) engine= MyISAM;

711

create TEMPORARY table t1 (a int not null) engine= MyISAM;

731

712

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

732

713

truncate table t1;

733

714

commit;

742

723

commit;

743

724

drop table t1;

744

725

set autocommit=1;

745

create table t1 (a int not null) engine= MyISAM;

726

create TEMPORARY table t1 (a int not null) engine= MyISAM;

746

727

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

747

728

truncate table t1;

748

729

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

756

737

select * from t1;

757

738

758

739

drop table t1;

759

create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=MyISAM;

740

create TEMPORARY table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=MyISAM;

760

741

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

761

742

explain select * from t1 order by a;

762

743

id select_type table type possible_keys key key_len ref rows Extra

783

764

id select_type table type possible_keys key key_len ref rows Extra

784

765

1 SIMPLE t1 ALL NULL NULL NULL NULL #

785

766

drop table t1;

786

create table t1 (t int not null default 1, key (t)) engine=MyISAM;

767

create TEMPORARY table t1 (t int not null default 1, key (t)) engine=MyISAM;

787

768

desc t1;

788

769

Field Type Null Key Default Extra

789

770

t int NO MUL 1

790

771

drop table t1;

791

CREATE TABLE t1 (

772

create TEMPORARY table t1 (

792

773

number bigint NOT NULL default '0',

793

774

cname char(15) NOT NULL default '',

794

775

carrier_id int NOT NULL default '0',

807

788

INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);

808

789

INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);

809

790

INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);

810

CREATE TABLE t2 (

791

create TEMPORARY table t2 (

811

792

number bigint NOT NULL default '0',

812

793

cname char(15) NOT NULL default '',

813

794

carrier_id int NOT NULL default '0',

850

831

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

851

832

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

852

833

drop table t1,t2;

853

create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;

834

create TEMPORARY table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;

854

835

BEGIN;

855

836

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

856

837

SELECT @@tx_isolation,@@global.tx_isolation;

888

869

7 4 Matt

889

870

COMMIT;

890

871

DROP TABLE t1;

891

create table t1 (n int, d int) engine=MyISAM;

892

create table t2 (n int, d int) engine=MyISAM;

872

create TEMPORARY table t1 (n int, d int) engine=MyISAM;

873

create TEMPORARY table t2 (n int, d int) engine=MyISAM;

893

874

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

894

875

insert into t2 values(1,10),(2,20);

895

876

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

902

883

1 30

903

884

2 20

904

885

drop table t1,t2;

905

create table t1 (a int, b int) engine=MyISAM;

906

insert into t1 values(20,null);

907

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

908

t2.b=t3.a;

909

b ifnull(t2.b,"this is null")

910

NULL this is null

911

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

912

t2.b=t3.a order by 1;

913

b ifnull(t2.b,"this is null")

914

NULL this is null

915

insert into t1 values(10,null);

916

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

917

t2.b=t3.a order by 1;

918

b ifnull(t2.b,"this is null")

919

NULL this is null

920

NULL this is null

921

drop table t1;

922

create table t1 (a varchar(10) not null) engine = MEMORY;

923

create table t2 (b varchar(10) not null unique) engine=MyISAM;

886

create TEMPORARY table t1 (a varchar(10) not null) engine = MEMORY;

887

create TEMPORARY table t2 (b varchar(10) not null unique) engine=MyISAM;

924

888

select t1.a from t1,t2 where t1.a=t2.b;

925

889

926

890

drop table t1,t2;

927

create table t1 (a int not null, b int, primary key (a)) engine = MyISAM;

928

create table t2 (a int not null, b int, primary key (a)) engine = MyISAM;

891

create TEMPORARY table t1 (a int not null, b int, primary key (a)) engine = MyISAM;

892

create TEMPORARY table t2 (a int not null, b int, primary key (a)) engine = MyISAM;

929

893

insert into t1 values (10, 20);

930

894

insert into t2 values (10, 20);

931

895

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

932

896

drop table t1,t2;

933

CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM;

897

create TEMPORARY table t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM;

934

898

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

935

899

UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;

936

900

SELECT * from t1;

945

909

8 8

946

910

9 9

947

911

drop table t1;

948

CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=MyISAM;

949

CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=MyISAM;

912

create TEMPORARY table t1 (a int not null primary key, b int not null, key (b)) engine=MyISAM;

913

create TEMPORARY table t2 (a int not null primary key, b int not null, key (b)) engine=MyISAM;

950

914

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);

951

915

INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

952

916

update t1,t2 set t1.a=t1.a+100;

1021

985

8 8

1022

986

9 9

1023

987

drop table t1,t2;

1024

CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY;

1025

CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=MyISAM;

988

create TEMPORARY table t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY;

989

create TEMPORARY table t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=MyISAM;

1026

990

SET AUTOCOMMIT=0;

1027

991

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

1028

992

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

1033

997

B_ID

1034

998

1035

999

drop table t1,t2;

1036

create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = MyISAM;

1000

create TEMPORARY table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = MyISAM;

1037

1001

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

1038

1002

select distinct parent,child from t1 order by parent;

1039

1003

parent child

1042

1006

1 3

1043

1007

2 1

1044

1008

drop table t1;

1045

create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=MyISAM;

1046

create table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY;

1009

create TEMPORARY table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=MyISAM;

1010

create TEMPORARY table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY;

1047

1011

insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);

1048

1012

insert into t2 (a) select b from t1;

1049

1013

insert into t1 (b) select b from t2;

1074

1038

id select_type table type possible_keys key key_len ref rows Extra

1075

1039

1 SIMPLE t1 range c c 5 NULL # Using where; Using MRR

1076

1040

drop table t1,t2;

1077

create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM;

1041

create TEMPORARY table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM;

1078

1042

insert into t1 (id) values (null),(null),(null),(null),(null);

1079

1043

update t1 set fk=69 where fk is null order by id limit 1;

1080

1044

SELECT * from t1;

1085

1049

4 NULL

1086

1050

5 NULL

1087

1051

drop table t1;

1088

create table t1 (a int not null, b int not null, key (a)) engine=MyISAM;

1052

create TEMPORARY table t1 (a int not null, b int not null, key (a)) engine=MyISAM;

1089

1053

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

1090

1054

SET @tmp=0;

1091

1055

update t1 set b=(@tmp:=@tmp+1) order by a;

1107

1071

111 100

1108

1072

111 100

1109

1073

drop table t1;

1110

create table t1 ( c char(8) not null ) engine=MyISAM;

1074

create TEMPORARY table t1 ( c char(8) not null ) engine=MyISAM;

1111

1075

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

1112

1076

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

1113

1077

alter table t1 add b char(8) not null;

1114

1078

alter table t1 add a char(8) not null;

1115

1079

alter table t1 add primary key (a,b,c);

1116

1080

update t1 set a=c, b=c;

1117

create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=MyISAM;

1081

create TEMPORARY table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=MyISAM;

1118

1082

insert into t2 select * from t1;

1119

1083

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

1120

1084

drop table t1,t2;

1121

1085

SET AUTOCOMMIT=1;

1122

create table t1 (a integer auto_increment primary key) engine=MyISAM;

1086

create TEMPORARY table t1 (a integer auto_increment primary key) engine=MyISAM;

1123

1087

insert into t1 (a) values (NULL),(NULL);

1124

1088

truncate table t1;

1125

1089

insert into t1 (a) values (NULL),(NULL);

1126

1090

SELECT * from t1;

1127

1091

1128

1129

1092

1093

1130

1094

drop table t1;

1131

CREATE TABLE t1 (col1 int)ENGINE=MyISAM;

1132

CREATE TABLE t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx

1095

create TEMPORARY table t1 (col1 int)ENGINE=MyISAM;

1096

create TEMPORARY table t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx

1133

1097

(stamp))ENGINE=MyISAM;

1134

1098

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

1135

1099

insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);

1141

1105

1142

1106

1143

1107

drop table t1,t2;

1144

CREATE TABLE t1 (

1145

`id` int NOT NULL auto_increment,

1146

`id_object` int default '0',

1147

`id_version` int NOT NULL default '1',

1148

`label` varchar(100) NOT NULL default '',

1149

`description` text,

1150

PRIMARY KEY (`id`),

1151

KEY `id_object` (`id_object`),

1152

KEY `id_version` (`id_version`)

1153

) ENGINE=MyISAM;

1154

INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);

1155

CREATE TABLE t2 (

1156

`id` int NOT NULL auto_increment,

1157

`id_version` int NOT NULL default '1',

1158

PRIMARY KEY (`id`),

1159

KEY `id_version` (`id_version`)

1160

) ENGINE=MyISAM;

1161

INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");

1162

SELECT t2.id, t1.`label` FROM t2 INNER JOIN

1163

(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl

1164

ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);

1165

id label

1166

3382 Test

1167

102 Le Pekin (Test)

1168

1794 Test de resto

1169

1822 Test 3

1170

3524 Societe Test

1171

3525 Fournisseur Test

1172

drop table t1,t2;

1173

create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM;

1174

create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM;

1175

create table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=MEMORY;

1176

create table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=MEMORY;

1177

create table t5 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM;

1178

create table t6 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM;

1108

create TEMPORARY table t1 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM;

1109

create TEMPORARY table t2 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM;

1110

create TEMPORARY table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=MEMORY;

1111

create TEMPORARY table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=MEMORY;

1112

create TEMPORARY table t5 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM;

1113

create TEMPORARY table t6 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM;

1179

1114

insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");

1180

1115

insert t2 select * from t1;

1181

1116

insert t3 select * from t1;

1216

1151

Warnings:

1217

1152

Error 1146 Table 'test.t7' doesn't exist

1218

1153

drop table t1,t2,t3, t4, t5, t6;

1219

create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=MyISAM;

1220

insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');

1221

select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;

1222

trim(name2)

1223

fff

1224

sss

1225

ttt

1226

first

1227

second

1228

third

1229

1230

1231

1232

drop table t1;

1233

create table t1 (a int) engine=MyISAM;

1154

create TEMPORARY table t1 (a int) engine=MyISAM;

1234

1155

create table t2 like t1;

1235

1156

show create table t2;

1236

1157

Table Create Table

1243

1164

Variable_name Value

1244

1165

show status like "binlog_cache_disk_use";

1245

1166

Variable_name Value

1246

create table t1 (a int) engine=MyISAM;

1167

create TEMPORARY table t1 (a int) engine=MyISAM;

1247

1168

show status like "binlog_cache_use";

1248

1169

Variable_name Value

1249

1170

show status like "binlog_cache_disk_use";

1256

1177

show status like "binlog_cache_disk_use";

1257

1178

Variable_name Value

1258

1179

drop table t1;

1259

create table t1 (c char(10), index (c,c)) engine=MyISAM;

1180

create TEMPORARY table t1 (c char(10), index (c,c)) engine=MyISAM;

1260

1181

ERROR 42S21: Duplicate column name 'c'

1261

create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM;

1262

ERROR 42S21: Duplicate column name 'c1'

1263

create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM;

1264

ERROR 42S21: Duplicate column name 'c1'

1265

create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM;

1266

ERROR 42S21: Duplicate column name 'c1'

1267

create table t1 (c1 char(10), c2 char(10)) engine=MyISAM;

1182

create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM;

1183

ERROR 42S21: Duplicate column name 'c1'

1184

create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM;

1185

ERROR 42S21: Duplicate column name 'c1'

1186

create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM;

1187

ERROR 42S21: Duplicate column name 'c1'

1188

create TEMPORARY table t1 (c1 char(10), c2 char(10)) engine=MyISAM;

1268

1189

alter table t1 add key (c1,c1);

1269

1190

ERROR 42S21: Duplicate column name 'c1'

1270

1191

alter table t1 add key (c2,c1,c1);

1274

1195

alter table t1 add key (c1,c1,c2);

1275

1196

ERROR 42S21: Duplicate column name 'c1'

1276

1197

drop table t1;

1277

create table t1(a int, b int) engine=MyISAM;

1198

create TEMPORARY table t1(a int, b int) engine=MyISAM;

1278

1199

insert into t1 values ('1111', '3333');

1279

1200

select distinct concat(a, b) from t1;

1280

1201

concat(a, b)

1288

1209

1289

1210

1290

1211

drop table t1;

1291

create table t1 (a int) engine=MyISAM;

1212

create TEMPORARY table t1 (a int) engine=MyISAM;

1292

1213

insert into t1 values (4711);

1293

1214

truncate t1;

1294

1215

insert into t1 values (42);

1296

1217

1297

1218

1298

1219

drop table t1;

1299

create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=MyISAM;

1220

create TEMPORARY table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=MyISAM;

1300

1221

insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);

1301

1222

select * from t1 order by a,b,c,d;

1302

1223

a b c d e

1307

1228

id select_type table type possible_keys key key_len ref rows Extra

1308

1229

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort

1309

1230

drop table t1;

1310

create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

1231

create TEMPORARY table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

1311

1232

insert into t1 values ('8', '6'), ('4', '7');

1312

1233

select min(a) from t1;

1313

1234

min(a)

1316

1237

min(b)

1317

1238

1318

1239

drop table t1;

1319

create table t1 (x bigint not null primary key) engine=MyISAM;

1240

create TEMPORARY table t1 (x bigint not null primary key) engine=MyISAM;

1320

1241

insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);

1321

1242

ERROR 22003: Out of range value for column 'x' at row 1

1322

1243

select * from t1;

1352

1273

drop table if exists t1,t2,t3;

1353

1274

--- Testing varchar ---

1354

1275

--- Testing varchar ---

1355

create table t1 (v varchar(10), c char(10), t text);

1276

create TEMPORARY table t1 (v varchar(10), c char(10), t text);

1356

1277

insert into t1 values('+ ', '+ ', '+ ');

1357

1278

set @a=repeat(' ',20);

1358

1279

insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));

1365

1286

*+ *+ *+ *

1366

1287

show create table t1;

1367

1288

Table Create Table

1368

t1 CREATE TABLE `t1` (

1289

t1 CREATE TEMPORARY TABLE `t1` (

1369

1290

`v` varchar(10) DEFAULT NULL,

1370

1291

`c` varchar(10) DEFAULT NULL,

1371

1292

`t` text

1372

1293

) ENGINE=MyISAM

1373

create table t2 like t1;

1294

create TEMPORARY table t2 like t1;

1374

1295

show create table t2;

1375

1296

Table Create Table

1376

t2 CREATE TABLE `t2` (

1297

t2 CREATE TEMPORARY TABLE `t2` (

1377

1298

`v` varchar(10) DEFAULT NULL,

1378

1299

`c` varchar(10) DEFAULT NULL,

1379

1300

`t` text

1380

1301

) ENGINE=MyISAM

1381

create table t3 select * from t1;

1302

create TEMPORARY table t3 select * from t1;

1382

1303

show create table t3;

1383

1304

Table Create Table

1384

t3 CREATE TABLE `t3` (

1305

t3 CREATE TEMPORARY TABLE `t3` (

1385

1306

`v` varchar(10) DEFAULT NULL,

1386

1307

`c` varchar(10) DEFAULT NULL,

1387

1308

`t` text

1389

1310

alter table t1 modify c varchar(10);

1390

1311

show create table t1;

1391

1312

Table Create Table

1392

t1 CREATE TABLE `t1` (

1313

t1 CREATE TEMPORARY TABLE `t1` (

1393

1314

`v` varchar(10) DEFAULT NULL,

1394

1315

`c` varchar(10) DEFAULT NULL,

1395

1316

`t` text

1397

1318

alter table t1 modify v char(10);

1398

1319

show create table t1;

1399

1320

Table Create Table

1400

t1 CREATE TABLE `t1` (

1321

t1 CREATE TEMPORARY TABLE `t1` (

1401

1322

`v` varchar(10) DEFAULT NULL,

1402

1323

`c` varchar(10) DEFAULT NULL,

1403

1324

`t` text

1407

1328

Note 1265 Data truncated for column 't' at row 2

1408

1329

show create table t1;

1409

1330

Table Create Table

1410

t1 CREATE TABLE `t1` (

1331

t1 CREATE TEMPORARY TABLE `t1` (

1411

1332

`v` varchar(10) DEFAULT NULL,

1412

1333

`c` varchar(10) DEFAULT NULL,

1413

1334

`t` varchar(10) DEFAULT NULL

1417

1338

*+ *+ *+ *

1418

1339

*+ *+ *+ *

1419

1340

drop table t1,t2,t3;

1420

create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));

1341

create TEMPORARY table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));

1421

1342

show create table t1;

1422

1343

Table Create Table

1423

t1 CREATE TABLE `t1` (

1344

t1 CREATE TEMPORARY TABLE `t1` (

1424

1345

`v` varchar(10) DEFAULT NULL,

1425

1346

`c` varchar(10) DEFAULT NULL,

1426

1347

`t` text,

1639

1560

alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);

1640

1561

show create table t1;

1641

1562

Table Create Table

1642

t1 CREATE TABLE `t1` (

1563

t1 CREATE TEMPORARY TABLE `t1` (

1643

1564

`v` varchar(300) DEFAULT NULL,

1644

1565

`c` varchar(10) DEFAULT NULL,

1645

1566

`t` text,

1719

1640

alter table t1 drop key v, add key v (v(30));

1720

1641

show create table t1;

1721

1642

Table Create Table

1722

t1 CREATE TABLE `t1` (

1643

t1 CREATE TEMPORARY TABLE `t1` (

1723

1644

`v` varchar(300) DEFAULT NULL,

1724

1645

`c` varchar(10) DEFAULT NULL,

1725

1646

`t` text,

1801

1722

Warning 1071 Specified key was too long; max key length is 1332 bytes

1802

1723

show create table t1;

1803

1724

Table Create Table

1804

t1 CREATE TABLE `t1` (

1725

t1 CREATE TEMPORARY TABLE `t1` (

1805

1726

`v` varchar(600) DEFAULT NULL,

1806

1727

`c` varchar(10) DEFAULT NULL,

1807

1728

`t` text,

1846

1767

h 10

1847

1768

i 10

1848

1769

drop table t1;

1849

create table t1 (a char(10), unique (a));

1770

create TEMPORARY table t1 (a char(10), unique (a));

1850

1771

insert into t1 values ('a ');

1851

1772

insert into t1 values ('a ');

1852

1773

ERROR 23000: Duplicate entry 'a ' for key 'a'

1876

1797

concat(a,'.')

1877

1798

a .

1878

1799

drop table t1;

1879

create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));

1800

create TEMPORARY table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));

1880

1801

show create table t1;

1881

1802

Table Create Table

1882

t1 CREATE TABLE `t1` (

1803

t1 CREATE TEMPORARY TABLE `t1` (

1883

1804

`v` varchar(10) DEFAULT NULL,

1884

1805

`c` varchar(10) DEFAULT NULL,

1885

1806

`t` text,

1888

1809

KEY `t` (`t`(5))

1889

1810

) ENGINE=MyISAM

1890

1811

drop table t1;

1891

create table t1 (v char(10));

1812

create TEMPORARY table t1 (v char(10));

1892

1813

show create table t1;

1893

1814

Table Create Table

1894

t1 CREATE TABLE `t1` (

1815

t1 CREATE TEMPORARY TABLE `t1` (

1895

1816

`v` varchar(10) DEFAULT NULL

1896

1817

) ENGINE=MyISAM

1897

1818

drop table t1;

1898

create table t1 (v varchar(10), c char(10)) row_format=fixed;

1819

create TEMPORARY table t1 (v varchar(10), c char(10)) row_format=fixed;

1899

1820

show create table t1;

1900

1821

Table Create Table

1901

t1 CREATE TABLE `t1` (

1822

t1 CREATE TEMPORARY TABLE `t1` (

1902

1823

`v` varchar(10) DEFAULT NULL,

1903

1824

`c` varchar(10) DEFAULT NULL

1904

1825

) ENGINE=MyISAM ROW_FORMAT=FIXED

1908

1829

*a*a*

1909

1830

*a *a *

1910

1831

drop table t1;

1911

create table t1(a int, b varchar(12), key ba(b, a));

1832

create TEMPORARY table t1(a int, b varchar(12), key ba(b, a));

1912

1833

insert into t1 values (1, 'A'), (20, NULL);

1913

1834

explain select * from t1 where a=20 and b is null;

1914

1835

id select_type table type possible_keys key key_len ref rows Extra

1917

1838

a b

1918

1839

20 NULL

1919

1840

drop table t1;

1920

create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

1841

create TEMPORARY table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

1921

1842

insert into t1 values ('8', '6'), ('4', '7');

1922

1843

select min(a) from t1;

1923

1844

min(a)

1926

1847

min(b)

1927

1848

1928

1849

drop table t1;

1929

CREATE TABLE t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM;

1850

create TEMPORARY table t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM;

1930

1851

insert into t1 (b) values (1);

1931

1852

replace into t1 (b) values (2), (1), (3);

1932

1853

select * from t1;

1941

1862

replace into t1 (b) values (3);

1942

1863

select * from t1;

1943

1864

a b

1944

7 1

1945

6 2

1946

8 3

1865

3 1

1866

2 2

1867

4 3

1947

1868

drop table t1;

1948

create table t1 (rowid int not null auto_increment, val int not null,primary

1869

create TEMPORARY table t1 (rowid int not null auto_increment, val int not null,primary

1949

1870

key (rowid), unique(val)) engine=MyISAM;

1950

1871

replace into t1 (val) values ('1'),('2');

1951

1872

replace into t1 (val) values ('1'),('2');

1956

1877

3 1

1957

1878

4 2

1958

1879

drop table t1;

1959

CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM;

1880

create TEMPORARY table t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM;

1960

1881

INSERT INTO t1 (GRADE) VALUES (151),(252),(343);

1961

1882

SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;

1962

1883

GRADE

1965

1886

GRADE

1966

1887

151

1967

1888

DROP TABLE t1;

1968

create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=MyISAM;

1969

create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=MyISAM;

1889

create TEMPORARY table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=MyISAM;

1890

create TEMPORARY table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=MyISAM;

1970

1891

insert into t2 values ('aa','cc');

1971

1892

insert into t1 values ('aa','bb'),('aa','cc');

1972

1893

delete t1 from t1,t2 where f1=f3 and f4='cc';

1973

1894

select * from t1;

1974

1895

f1 f2

1975

1896

drop table t1,t2;

1976

create table t1(a date) engine=MyISAM;

1977

create table t2(a date, key(a)) engine=MyISAM;

1897

create TEMPORARY table t1(a date) engine=MyISAM;

1898

create TEMPORARY table t2(a date, key(a)) engine=MyISAM;

1978

1899

insert into t1 values('2005-10-01');

1979

1900

insert into t2 values('2005-10-01');

1980

1901

select * from t1, t2

1982

1903

a a

1983

1904

2005-10-01 2005-10-01

1984

1905

drop table t1, t2;

1985

create table t1 (id int not null, f_id int not null, f int not null,

1906

create TEMPORARY table t1 (id int not null, f_id int not null, f int not null,

1986

1907

primary key(f_id, id)) engine=MyISAM;

1987

create table t2 (id int not null,s_id int not null,s varchar(200),

1908

create TEMPORARY table t2 (id int not null,s_id int not null,s varchar(200),

1988

1909

primary key(id)) engine=MyISAM;

1989

1910

INSERT INTO t1 VALUES (8, 1, 3);

1990

1911

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

1997

1918

where mm.id is null lock in share mode;

1998

1919

id f_id f

1999

1920

drop table t1,t2;

2000

create table t1(a int not null, b int, primary key(a)) engine=MyISAM;

2001

insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);

2002

commit;

2003

set autocommit = 0;

2004

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2005

update t1 set b = 5 where b = 1;

2006

set autocommit = 0;

2007

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2008

select * from t1 where a = 7 and b = 3 for update;

2009

a b

2010

7 3

2011

commit;

2012

commit;

2013

drop table t1;

2014

CREATE TABLE t1 ( a int ) ENGINE=MyISAM;

1921

create TEMPORARY table t1 ( a int ) ENGINE=MyISAM;

2015

1922

BEGIN;

2016

1923

INSERT INTO t1 VALUES (1);

2017

1924

OPTIMIZE TABLE t1;

Older »