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

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;

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

2738

2742

DROP TABLE t1, t2;

2739

2743

2740

2744

2741

# Bug #18895: BIT values cause joins to fail

2745

# Bug #18895: Now tests for int

2742

2746

2743

2747

create table t1 (

2744

a int unsigned not null auto_increment primary key,

2745

b bit not null,

2746

c bit not null

2748

a int not null auto_increment primary key,

2749

b int not null,

2750

c int not null

2747

2751

);

2748

2752

2749

2753

create table t2 (

2750

a int unsigned not null auto_increment primary key,

2751

b bit not null,

2752

c int unsigned not null,

2754

a int not null auto_increment primary key,

2755

b int not null,

2756

c int not null,

2753

2757

d varchar(50)

2754

2758

);

2755

2759

2975

2979

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

2976

2980

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

2977

2981

2982

# PBXT: does not return a consistent row count

2983

--replace_column 9 #

2978

2984

EXPLAIN SELECT * FROM t1

2979

2985

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

2986

--replace_column 9 #

2980

2987

EXPLAIN SELECT * FROM t1

2981

2988

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

2989

--replace_column 9 #

2982

2990

EXPLAIN SELECT * FROM t1

2983

2991

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

2984

2992

3019

3027

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

3020

3028

3021

3029

DROP TABLE t1,t2;

3022

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

3030

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

3023

3031

3024

3032

3025

create table t1 (a bigint unsigned);

3033

create table t1 (a bigint);

3026

3034

insert into t1 values

3027

(if(1, 9223372036854775808, 1)),

3028

(case when 1 then 9223372036854775808 else 1 end),

3029

(coalesce(9223372036854775808, 1));

3035

(if(1, 92233720368547758, 1)),

3036

(case when 1 then 92233720368547758 else 1 end),

3037

(coalesce(92233720368547758, 1));

3030

3038

select * from t1;

3031

3039

drop table t1;

3032

3040

create table t1 select

3037

3045

drop table t1;

3038

3046

# Ensure we handle big values properly

3039

3047

select

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;

3048

if(1, 1111111111111111111, 1) i,

3049

case when 1 then 1111111111111111111 else 1 end c,

3050

coalesce(1111111111111111111, 1) co;

3043

3051

3044

3052

3045

3053

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

3252

3260

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

3253

3261

3254

3262

3255

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

3256

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,

3257

3265

c22 INT DEFAULT NULL,

3258

3266

KEY(c21, c22));

3259

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

3267

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3260

3268

c32 INT DEFAULT NULL,

3261

3269

c33 INT NOT NULL,

3262

c34 INT UNSIGNED DEFAULT 0,

3270

c34 INT DEFAULT 0,

3263

3271

KEY (c33, c34, c32));

3264

3272

3265

3273

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

Older »