~drizzle-trunk/drizzle/development

CREATE TABLE t1 (gvid int default NULL, hmid int default NULL, volid int default NULL, mmid int default NULL, hdid int default NULL, fsid int default NULL, ctid int default NULL, dtid int default NULL, cost int default NULL, performance int default NULL, serialnumber bigint default NULL, monitored int default '1', removed int default '0', target int default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;

1788

CREATE TABLE t1 (gvid int unsigned default NULL, hmid int unsigned default NULL, volid int unsigned default NULL, mmid int unsigned default NULL, hdid int unsigned default NULL, fsid int unsigned default NULL, ctid int unsigned default NULL, dtid int unsigned default NULL, cost int unsigned default NULL, performance int unsigned default NULL, serialnumber bigint unsigned default NULL, monitored tinyint unsigned default '1', removed tinyint unsigned default '0', target tinyint unsigned default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;

1789

INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);

1790

CREATE TABLE t2 ( hmid int default NULL, volid int default NULL, sampletid int default NULL, sampletime datetime default NULL, samplevalue bigint default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

1790

CREATE TABLE t2 ( hmid int unsigned default NULL, volid int unsigned default NULL, sampletid smallint unsigned default NULL, sampletime datetime default NULL, samplevalue bigint unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

1791

INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);

1792

# Disable PS becasue we get more warnings from PS than from normal execution

1793

--disable_ps_protocol

1967

DROP TABLE t1;

1968

1969

1970

# Bug#7425 inconsistent sort order on columns result of substraction

1970

# Bug#7425 inconsistent sort order on unsigned columns result of substraction

1971

1972

1973

create table t1 (a int, b int);

1973

create table t1 (a int unsigned, b int unsigned);

1974

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

1975

select a-b from t1 order by 1;

1976

select a-b , (a-b < 0) from t1 order by 1;

1977

select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;

1978

select a - b from t1 order by 1;

1978

select cast((a - b) as unsigned) from t1 order by 1;

1979

drop table t1;

1980

1981

1995

# Test for BUG#10095

1996

1997

CREATE TABLE t1 (

1998

kunde_intern_id int NOT NULL default '0',

1999

kunde_id int NOT NULL default '0',

2000

FK_firma_id int NOT NULL default '0',

1998

kunde_intern_id int unsigned NOT NULL default '0',

1999

kunde_id int unsigned NOT NULL default '0',

2000

FK_firma_id int unsigned NOT NULL default '0',

2001

aktuell enum('Ja','Nein') NOT NULL default 'Ja',

2002

vorname varchar(128) NOT NULL default '',

2003

nachname varchar(128) NOT NULL default '',

2053

2054

2055

2056

# Test for Bug#8009, SELECT failed on bigint when using HEX

2056

# Test for Bug#8009, SELECT failed on bigint unsigned when using HEX

2057

2058

2059

CREATE TABLE t1 (b BIGINT NOT NULL, PRIMARY KEY (b));

2060

INSERT INTO t1 VALUES (0x4000000000000000);

2061

SELECT b FROM t1 WHERE b=0x4000000000000000;

2059

CREATE TABLE t1 (b BIGINT UNSIGNED NOT NULL, PRIMARY KEY (b));

2060

INSERT INTO t1 VALUES (0x8000000000000000);

2061

SELECT b FROM t1 WHERE b=0x8000000000000000;

2062

DROP TABLE t1;

2063

2064

2079

# Test for BUG#11700

2080

CREATE TABLE t1 (

2081

acct_id int NOT NULL default '0',

2082

profile_id int default NULL,

2082

profile_id smallint default NULL,

2083

UNIQUE KEY t1$acct_id (acct_id),

2084

KEY t1$profile_id (profile_id)

2085

);

2086

INSERT INTO t1 VALUES (132,17),(133,18);

2087

2088

CREATE TABLE t2 (

2089

profile_id int default NULL,

2089

profile_id smallint default NULL,

2090

queue_id int default NULL,

2091

seq int default NULL,

2092

KEY t2$queue_id (queue_id)

2099

seq int default NULL,

2100

warn_lvl int default NULL,

2101

crit_lvl int default NULL,

2102

rr1 int NOT NULL default '0',

2102

rr1 tinyint NOT NULL default '0',

2103

rr2 int default NULL,

2104

default_queue int NOT NULL default '0',

2104

default_queue tinyint NOT NULL default '0',

2105

KEY t3$qtype (qtype),

2106

KEY t3$id (id)

2107

);

2132

# Bug #11521 Negative integer keys incorrectly substituted for 0 during

2133

# range analysis.

2134

2135

create table t2 (a int);

2135

create table t2 (a tinyint unsigned);

2136

create index t2i on t2(a);

2137

insert into t2 values (0), (254), (255);

2138

#explain select * from t2 where a > -1;

2299

# This test is here only to make sure that behavior is not changed in

2300

# 4.1 and 5.0

2301

2302

CREATE TABLE t1 (i BIGINT NOT NULL);

2302

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2303

INSERT INTO t1 VALUES (10);

2304

SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;

2305

DROP TABLE t1;

2332

2333

2334

CREATE TABLE t1 (

2335

K2C4 varchar(4) collate utf8_bin NOT NULL default '',

2336

K4N4 varchar(4) collate utf8_bin NOT NULL default '0000',

2335

K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '',

2336

K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000',

2337

F2I4 int NOT NULL default '0'

2338

) ENGINE=MyISAM;

2338

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2339

2340

INSERT INTO t1 VALUES

2341

('W%RT', '0100', 1),

2475

# for base tables, search all nested join operands of natural joins.

2476

2477

2478

CREATE TABLE t1 (`id` int);

2479

CREATE TABLE t2 (`id` int);

2480

CREATE TABLE t3 (`id` int);

2478

CREATE TABLE t1 (`id` TINYINT);

2479

CREATE TABLE t2 (`id` TINYINT);

2480

CREATE TABLE t3 (`id` TINYINT);

2481

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

2482

INSERT INTO t2 VALUES (2);

2483

INSERT INTO t3 VALUES (3);

2547

2548

create table t1 (id int not null default '0');

2549

insert into t1 values (123),(191),(192);

2550

create table t2 (id char(16) not null);

2550

create table t2 (id char(16) character set utf8 not null);

2551

insert into t2 values ('58013'),('58014'),('58015'),('58016');

2552

create table t3 (a_id int not null, b_id char(16));

2552

create table t3 (a_id int not null, b_id char(16) character set utf8);

2553

insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');

2554

2555

# both queries are equivalent

2704

2705

DROP TABLE t1,t2;

2706

2707

2708

# Bug#18712: Truncation problem (needs just documenting and test

2709

# cases to prevent fixing this accidently. It is intended behaviour)

2710

2711

2712

CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);

2713

INSERT t1 SET i = 0;

2714

UPDATE t1 SET i = -1;

2715

SELECT * FROM t1;

2716

UPDATE t1 SET i = CAST(i - 1 AS SIGNED);

2717

SELECT * FROM t1;

2718

UPDATE t1 SET i = i - 1;

2719

SELECT * FROM t1;

2720

DROP TABLE t1;

2721

2707

2722

# BUG#17379

2708

2723

2709

2724

create table t1 (a int);

2738

2753

DROP TABLE t1, t2;

2739

2754

2740

2755

2741

# Bug #18895: Now tests for int

2756

# Bug #18895: BIT values cause joins to fail

2742

2757

2743

2758

create table t1 (

2744

a int not null auto_increment primary key,

2745

b int not null,

2746

c int not null

2759

a int unsigned not null auto_increment primary key,

2760

b bit not null,

2761

c bit not null

2747

2762

);

2748

2763

2749

2764

create table t2 (

2750

a int not null auto_increment primary key,

2751

b int not null,

2752

c int not null,

2765

a int unsigned not null auto_increment primary key,

2766

b bit not null,

2767

c int unsigned not null,

2753

2768

d varchar(50)

2754

2769

);

2755

2770

3019

3034

AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";

3020

3035

3021

3036

DROP TABLE t1,t2;

3022

# Bug #22026: Warning when using IF statement and large bigint

3037

# Bug #22026: Warning when using IF statement and large unsigned bigint

3023

3038

3024

3039

3025

create table t1 (a bigint);

3040

create table t1 (a bigint unsigned);

3026

3041

insert into t1 values

3027

(if(1, 92233720368547758, 1)),

3028

(case when 1 then 92233720368547758 else 1 end),

3029

(coalesce(92233720368547758, 1));

3042

(if(1, 9223372036854775808, 1)),

3043

(case when 1 then 9223372036854775808 else 1 end),

3044

(coalesce(9223372036854775808, 1));

3030

3045

select * from t1;

3031

3046

drop table t1;

3032

3047

create table t1 select

3037

3052

drop table t1;

3038

3053

# Ensure we handle big values properly

3039

3054

select

3040

if(1, 1111111111111111111, 1) i,

3041

case when 1 then 1111111111111111111 else 1 end c,

3042

coalesce(1111111111111111111, 1) co;

3055

if(1, cast(1111111111111111111 as unsigned), 1) i,

3056

case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,

3057

coalesce(cast(1111111111111111111 as unsigned), 1) co;

3043

3058

3044

3059

3045

3060

# Bug #22971: indexes on text columns are ignored for ref accesses

3252

3267

# Bug #30666: Incorrect order when using range conditions on 2 tables or more

3253

3268

3254

3269

3255

CREATE TABLE t1 (c11 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

3256

CREATE TABLE t2 (c21 INT NOT NULL,

3270

CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

3271

CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,

3257

3272

c22 INT DEFAULT NULL,

3258

3273

KEY(c21, c22));

3259

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3274

CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0,

3260

3275

c32 INT DEFAULT NULL,

3261

3276

c33 INT NOT NULL,

3262

c34 INT DEFAULT 0,

3277

c34 INT UNSIGNED DEFAULT 0,

3263

3278

KEY (c33, c34, c32));

3264

3279

3265

3280

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

Older »