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

1792

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

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;

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;

1794

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

1795

1792

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

1796

1793

--disable_ps_protocol

1834

1831

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

1835

1832

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

1836

1833

# inner join on

1837

--sorted_result

1838

1834

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

1839

1835

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

1840

1836

# inner join using

1971

1967

DROP TABLE t1;

1972

1968

1973

1969

1974

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

1970

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

1975

1971

1976

1972

1977

create table t1 (a int, b int);

1973

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

1978

1974

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

1979

1975

select a-b from t1 order by 1;

1980

1976

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

1981

1977

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

1982

select a - b from t1 order by 1;

1978

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

1983

1979

drop table t1;

1984

1980

1985

1981

1999

1995

# Test for BUG#10095

2000

1996

2001

1997

CREATE TABLE t1 (

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

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

2005

2001

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

2006

2002

vorname varchar(128) NOT NULL default '',

2007

2003

nachname varchar(128) NOT NULL default '',

2057

2053

2058

2054

2059

2055

2060

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

2056

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

2061

2057

2062

2058

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;

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;

2066

2062

DROP TABLE t1;

2067

2063

2068

2064

2083

2079

# Test for BUG#11700

2084

2080

CREATE TABLE t1 (

2085

2081

acct_id int NOT NULL default '0',

2086

profile_id int default NULL,

2082

profile_id smallint default NULL,

2087

2083

UNIQUE KEY t1$acct_id (acct_id),

2088

2084

KEY t1$profile_id (profile_id)

2089

2085

);

2090

2086

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

2091

2087

2092

2088

CREATE TABLE t2 (

2093

profile_id int default NULL,

2089

profile_id smallint default NULL,

2094

2090

queue_id int default NULL,

2095

2091

seq int default NULL,

2096

2092

KEY t2$queue_id (queue_id)

2103

2099

seq int default NULL,

2104

2100

warn_lvl int default NULL,

2105

2101

crit_lvl int default NULL,

2106

rr1 int NOT NULL default '0',

2102

rr1 tinyint NOT NULL default '0',

2107

2103

rr2 int default NULL,

2108

default_queue int NOT NULL default '0',

2104

default_queue tinyint NOT NULL default '0',

2109

2105

KEY t3$qtype (qtype),

2110

2106

KEY t3$id (id)

2111

2107

);

2136

2132

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

2137

2133

# range analysis.

2138

2134

2139

create table t2 (a int);

2135

create table t2 (a tinyint unsigned);

2140

2136

create index t2i on t2(a);

2141

2137

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

2142

2138

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

2303

2299

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

2304

2300

# 4.1 and 5.0

2305

2301

2306

CREATE TABLE t1 (i BIGINT NOT NULL);

2302

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2307

2303

INSERT INTO t1 VALUES (10);

2308

2304

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

2309

2305

DROP TABLE t1;

2479

2475

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

2480

2476

2481

2477

2482

CREATE TABLE t1 (`id` int);

2483

CREATE TABLE t2 (`id` int);

2484

CREATE TABLE t3 (`id` int);

2478

CREATE TABLE t1 (`id` TINYINT);

2479

CREATE TABLE t2 (`id` TINYINT);

2480

CREATE TABLE t3 (`id` TINYINT);

2485

2481

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

2486

2482

INSERT INTO t2 VALUES (2);

2487

2483

INSERT INTO t3 VALUES (3);

2742

2738

DROP TABLE t1, t2;

2743

2739

2744

2740

2745

# Bug #18895: Now tests for int

2741

# Bug #18895: BIT values cause joins to fail

2746

2742

2747

2743

create table t1 (

2748

a int not null auto_increment primary key,

2749

b int not null,

2750

c int not null

2744

a int unsigned not null auto_increment primary key,

2745

b bit not null,

2746

c bit not null

2751

2747

);

2752

2748

2753

2749

create table t2 (

2754

a int not null auto_increment primary key,

2755

b int not null,

2756

c int not null,

2750

a int unsigned not null auto_increment primary key,

2751

b bit not null,

2752

c int unsigned not null,

2757

2753

d varchar(50)

2758

2754

);

2759

2755

2979

2975

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

2980

2976

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

2981

2977

2982

# PBXT: does not return a consistent row count

2983

--replace_column 9 #

2984

2978

EXPLAIN SELECT * FROM t1

2985

2979

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

2986

--replace_column 9 #

2987

2980

EXPLAIN SELECT * FROM t1

2988

2981

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

2989

--replace_column 9 #

2990

2982

EXPLAIN SELECT * FROM t1

2991

2983

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

2992

2984

3027

3019

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

3028

3020

3029

3021

DROP TABLE t1,t2;

3030

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

3022

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

3031

3023

3032

3024

3033

create table t1 (a bigint);

3025

create table t1 (a bigint unsigned);

3034

3026

insert into t1 values

3035

(if(1, 92233720368547758, 1)),

3036

(case when 1 then 92233720368547758 else 1 end),

3037

(coalesce(92233720368547758, 1));

3027

(if(1, 9223372036854775808, 1)),

3028

(case when 1 then 9223372036854775808 else 1 end),

3029

(coalesce(9223372036854775808, 1));

3038

3030

select * from t1;

3039

3031

drop table t1;

3040

3032

create table t1 select

3045

3037

drop table t1;

3046

3038

# Ensure we handle big values properly

3047

3039

select

3048

if(1, 1111111111111111111, 1) i,

3049

case when 1 then 1111111111111111111 else 1 end c,

3050

coalesce(1111111111111111111, 1) co;

3040

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

3041

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

3042

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

3051

3043

3052

3044

3053

3045

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

3260

3252

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

3261

3253

3262

3254

3263

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

3264

CREATE TABLE t2 (c21 INT NOT NULL,

3255

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

3256

CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,

3265

3257

c22 INT DEFAULT NULL,

3266

3258

KEY(c21, c22));

3267

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3259

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

3268

3260

c32 INT DEFAULT NULL,

3269

3261

c33 INT NOT NULL,

3270

c34 INT DEFAULT 0,

3262

c34 INT UNSIGNED DEFAULT 0,

3271

3263

KEY (c33, c34, c32));

3272

3264

3273

3265

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

Older »