~drizzle-trunk/drizzle/development

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;

1791

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;

1789

1792

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

1793

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;

1791

1794

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

1795

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

1793

1796

--disable_ps_protocol

1831

1834

select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;

1832

1835

select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));

1833

1836

# inner join on

1837

--sorted_result

1834

1838

select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;

1835

1839

select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;

1836

1840

# inner join using

1967

1971

DROP TABLE t1;

1968

1972

1969

1973

1970

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

1974

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

1971

1975

1972

1976

1973

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

1977

create table t1 (a int, b int);

1974

1978

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

1975

1979

select a-b from t1 order by 1;

1976

1980

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

1977

1981

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

1978

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

1982

select a - b from t1 order by 1;

1979

1983

drop table t1;

1980

1984

1981

1985

1995

1999

# Test for BUG#10095

1996

2000

1997

2001

CREATE TABLE t1 (

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',

2002

kunde_intern_id int NOT NULL default '0',

2003

kunde_id int NOT NULL default '0',

2004

FK_firma_id int NOT NULL default '0',

2001

2005

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

2002

2006

vorname varchar(128) NOT NULL default '',

2003

2007

nachname varchar(128) NOT NULL default '',

2053

2057

2054

2058

2055

2059

2056

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

2060

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

2057

2061

2058

2062

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;

2063

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

2064

INSERT INTO t1 VALUES (0x4000000000000000);

2065

SELECT b FROM t1 WHERE b=0x4000000000000000;

2062

2066

DROP TABLE t1;

2063

2067

2064

2068

2079

2083

# Test for BUG#11700

2080

2084

CREATE TABLE t1 (

2081

2085

acct_id int NOT NULL default '0',

2082

profile_id smallint default NULL,

2086

profile_id int default NULL,

2083

2087

UNIQUE KEY t1$acct_id (acct_id),

2084

2088

KEY t1$profile_id (profile_id)

2085

2089

);

2086

2090

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

2087

2091

2088

2092

CREATE TABLE t2 (

2089

profile_id smallint default NULL,

2093

profile_id int default NULL,

2090

2094

queue_id int default NULL,

2091

2095

seq int default NULL,

2092

2096

KEY t2$queue_id (queue_id)

2099

2103

seq int default NULL,

2100

2104

warn_lvl int default NULL,

2101

2105

crit_lvl int default NULL,

2102

rr1 tinyint NOT NULL default '0',

2106

rr1 int NOT NULL default '0',

2103

2107

rr2 int default NULL,

2104

default_queue tinyint NOT NULL default '0',

2108

default_queue int NOT NULL default '0',

2105

2109

KEY t3$qtype (qtype),

2106

2110

KEY t3$id (id)

2107

2111

);

2132

2136

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

2133

2137

# range analysis.

2134

2138

2135

create table t2 (a tinyint unsigned);

2139

create table t2 (a int);

2136

2140

create index t2i on t2(a);

2137

2141

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

2138

2142

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

2169

2173

create table t1 (f1 int not null auto_increment primary key, f2 varchar(10));

2170

2174

create table t11 like t1;

2171

2175

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

2172

--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X

2176

--replace_column 3 X 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X

2173

2177

show table status like 't1%';

2174

2178

select 123 as a from t1 where f1 is null;

2175

2179

drop table t1,t11;

2299

2303

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

2300

2304

# 4.1 and 5.0

2301

2305

2302

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2306

CREATE TABLE t1 (i BIGINT NOT NULL);

2303

2307

INSERT INTO t1 VALUES (10);

2304

2308

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

2305

2309

DROP TABLE t1;

2332

2336

2333

2337

2334

2338

CREATE TABLE t1 (

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',

2339

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

2340

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

2337

2341

F2I4 int NOT NULL default '0'

2338

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2342

) ENGINE=MyISAM;

2339

2343

2340

2344

INSERT INTO t1 VALUES

2341

2345

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

2475

2479

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

2476

2480

2477

2481

2478

CREATE TABLE t1 (`id` TINYINT);

2479

CREATE TABLE t2 (`id` TINYINT);

2480

CREATE TABLE t3 (`id` TINYINT);

2482

CREATE TABLE t1 (`id` int);

2483

CREATE TABLE t2 (`id` int);

2484

CREATE TABLE t3 (`id` int);

2481

2485

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

2482

2486

INSERT INTO t2 VALUES (2);

2483

2487

INSERT INTO t3 VALUES (3);

2547

2551

2548

2552

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

2549

2553

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

2550

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

2554

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

2551

2555

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

2552

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

2556

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

2553

2557

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

2554

2558

2555

2559

# both queries are equivalent

2704

2708

2705

2709

DROP TABLE t1,t2;

2706

2710

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

2722

2711

# BUG#17379

2723

2712

2724

2713

create table t1 (a int);

2753

2742

DROP TABLE t1, t2;

2754

2743

2755

2744

2756

# Bug #18895: BIT values cause joins to fail

2745

# Bug #18895: Now tests for int

2757

2746

2758

2747

create table t1 (

2759

a int unsigned not null auto_increment primary key,

2760

b bit not null,

2761

c bit not null

2748

a int not null auto_increment primary key,

2749

b int not null,

2750

c int not null

2762

2751

);

2763

2752

2764

2753

create table t2 (

2765

a int unsigned not null auto_increment primary key,

2766

b bit not null,

2767

c int unsigned not null,

2754

a int not null auto_increment primary key,

2755

b int not null,

2756

c int not null,

2768

2757

d varchar(50)

2769

2758

);

2770

2759

2990

2979

SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;

2991

2980

SELECT COUNT(*) FROM t1 WHERE ID_better=1;

2992

2981

2982

# PBXT: does not return a consistent row count

2983

--replace_column 9 #

2993

2984

EXPLAIN SELECT * FROM t1

2994

2985

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;

2986

--replace_column 9 #

2995

2987

EXPLAIN SELECT * FROM t1

2996

2988

WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;

2989

--replace_column 9 #

2997

2990

EXPLAIN SELECT * FROM t1

2998

2991

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;

2999

2992

3034

3027

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

3035

3028

3036

3029

DROP TABLE t1,t2;

3037

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

3030

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

3038

3031

3039

3032

3040

create table t1 (a bigint unsigned);

3033

create table t1 (a bigint);

3041

3034

insert into t1 values

3042

(if(1, 9223372036854775808, 1)),

3043

(case when 1 then 9223372036854775808 else 1 end),

3044

(coalesce(9223372036854775808, 1));

3035

(if(1, 92233720368547758, 1)),

3036

(case when 1 then 92233720368547758 else 1 end),

3037

(coalesce(92233720368547758, 1));

3045

3038

select * from t1;

3046

3039

drop table t1;

3047

3040

create table t1 select

3052

3045

drop table t1;

3053

3046

# Ensure we handle big values properly

3054

3047

select

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;

3048

if(1, 1111111111111111111, 1) i,

3049

case when 1 then 1111111111111111111 else 1 end c,

3050

coalesce(1111111111111111111, 1) co;

3058

3051

3059

3052

3060

3053

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

3267

3260

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

3268

3261

3269

3262

3270

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

3271

CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,

3263

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

3264

CREATE TABLE t2 (c21 INT NOT NULL,

3272

3265

c22 INT DEFAULT NULL,

3273

3266

KEY(c21, c22));

3274

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

3267

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3275

3268

c32 INT DEFAULT NULL,

3276

3269

c33 INT NOT NULL,

3277

c34 INT UNSIGNED DEFAULT 0,

3270

c34 INT DEFAULT 0,

3278

3271

KEY (c33, c34, c32));

3279

3272

3280

3273

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

Older »