~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=innodb;

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

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;

CREATE TABLE t1 (

id int(11) NOT NULL auto_increment,

parent_id int(11) DEFAULT '0' NOT NULL,

level int(4) DEFAULT '0' NOT NULL,

level tinyint(4) DEFAULT '0' NOT NULL,

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

358

CREATE TABLE t1 (

359

id int(11) NOT NULL auto_increment,

360

parent_id int(11) DEFAULT '0' NOT NULL,

361

level int(4) DEFAULT '0' NOT NULL,

361

level tinyint(4) DEFAULT '0' NOT NULL,

362

KEY (id),

363

KEY parent_id (parent_id),

364

KEY level (level)

451

452

453

CREATE TABLE t1 (

454

a int3 NOT NULL,

455

b int1 NOT NULL,

454

a int3 unsigned NOT NULL,

455

b int1 unsigned NOT NULL,

456

UNIQUE (a, b)

457

) ENGINE = innodb;

458

464

# Test INSERT DELAYED

465

466

467

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

467

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

468

# Can't test this in 3.23

469

# INSERT DELAYED INTO t1 VALUES (1);

470

INSERT INTO t1 VALUES (1);

638

CREATE TABLE t1 (

639

number bigint(20) NOT NULL default '0',

640

cname char(15) NOT NULL default '',

641

carrier_id int(6) NOT NULL default '0',

642

privacy int(4) NOT NULL default '0',

641

carrier_id smallint(6) NOT NULL default '0',

642

privacy tinyint(4) NOT NULL default '0',

643

last_mod_date timestamp NOT NULL,

644

last_mod_id int(6) NOT NULL default '0',

644

last_mod_id smallint(6) NOT NULL default '0',

645

last_app_date timestamp NOT NULL,

646

last_app_id int(6) default '-1',

647

version int(6) NOT NULL default '0',

646

last_app_id smallint(6) default '-1',

647

version smallint(6) NOT NULL default '0',

648

assigned_scps int(11) default '0',

649

status int(4) default '0'

649

status tinyint(4) default '0'

650

) ENGINE=InnoDB;

651

INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);

652

INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);

657

CREATE TABLE t2 (

658

number bigint(20) NOT NULL default '0',

659

cname char(15) NOT NULL default '',

660

carrier_id int(6) NOT NULL default '0',

661

privacy int(4) NOT NULL default '0',

660

carrier_id smallint(6) NOT NULL default '0',

661

privacy tinyint(4) NOT NULL default '0',

662

last_mod_date timestamp NOT NULL,

663

last_mod_id int(6) NOT NULL default '0',

663

last_mod_id smallint(6) NOT NULL default '0',

664

last_app_date timestamp NOT NULL,

665

last_app_id int(6) default '-1',

666

version int(6) NOT NULL default '0',

665

last_app_id smallint(6) default '-1',

666

version smallint(6) NOT NULL default '0',

667

assigned_scps int(11) default '0',

668

status int(4) default '0'

668

status tinyint(4) default '0'

669

) ENGINE=InnoDB;

670

INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);

671

INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);

685

# this works.

686

687

688

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=innodb;

688

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

689

690

BEGIN;

691

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

1025

1026

1027

CREATE TABLE t1 (

1028

`id` int(10) NOT NULL auto_increment,

1029

`id_object` int(10) default '0',

1030

`id_version` int(10) NOT NULL default '1',

1028

`id` int(10) unsigned NOT NULL auto_increment,

1029

`id_object` int(10) unsigned default '0',

1030

`id_version` int(10) unsigned NOT NULL default '1',

1031

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

1032

`description` text,

1033

PRIMARY KEY (`id`),

1038

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

1039

1040

CREATE TABLE t2 (

1041

`id` int(10) NOT NULL auto_increment,

1042

`id_version` int(10) NOT NULL default '1',

1041

`id` int(10) unsigned NOT NULL auto_increment,

1042

`id_version` int(10) unsigned NOT NULL default '1',

1043

PRIMARY KEY (`id`),

1044

KEY `id_version` (`id_version`)

1045

) ENGINE=InnoDB;

1189

1190

1191

--disable_warnings

1192

CREATE TABLE t1 (a_id int(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1192

CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1193

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

1194

CREATE TABLE t2 (b_id int(4) NOT NULL default '0',b_a int(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1194

CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1195

CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1196

--enable_warnings

1197

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

1243

# range optimizer problem

1244

1245

1246

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

1246

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

1247

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

1248

select * from t1;

1249

select count(*) from t1 where x>0;

Older »