~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(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) 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;

1796

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

1797

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(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

1798

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

1799

1792

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

1800

1793

--disable_ps_protocol

1808

1801

# Test of bigint comparision

1809

1802

1810

1803

1811

create table t1 ( A_Id bigint NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint NOT NULL default '0', A_UpdateSerial int NOT NULL default '0', other_types bigint NOT NULL default '0', wss_type bigint NOT NULL default '0');

1804

create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0');

1812

1805

INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);

1813

1806

select wss_type from t1 where wss_type ='102935229216544106';

1814

1807

select wss_type from t1 where wss_type ='102935229216544105';

1838

1831

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

1839

1832

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

1840

1833

# inner join on

1841

--sorted_result

1842

1834

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

1843

1835

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

1844

1836

# inner join using

1867

1859

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

1868

1860

drop table t1;

1869

1861

1870

CREATE TABLE t1 ( aa char(2), id int NOT NULL auto_increment, t2_id int NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;

1862

CREATE TABLE t1 ( aa char(2), id int(11) NOT NULL auto_increment, t2_id int(11) NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;

1871

1863

INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);

1872

CREATE TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

1864

CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

1873

1865

INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);

1874

1866

select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5;

1875

1867

drop table t1,t2;

1928

1920

1929

1921

1930

1922

CREATE TABLE t1 (

1931

i int NOT NULL default '0',

1923

i int(11) NOT NULL default '0',

1932

1924

c char(10) NOT NULL default '',

1933

1925

PRIMARY KEY (i),

1934

1926

UNIQUE KEY c (c)

1975

1967

DROP TABLE t1;

1976

1968

1977

1969

1978

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

1970

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

1979

1971

1980

1972

1981

create table t1 (a int, b int);

1973

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

1982

1974

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

1983

1975

select a-b from t1 order by 1;

1984

1976

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

1985

1977

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

1986

select a - b from t1 order by 1;

1978

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

1987

1979

drop table t1;

1988

1980

1989

1981

1990

1982

1991

1983

# Bug#8733 server accepts malformed query (multiply mentioned distinct)

1992

1984

1993

create table t1 (a int);

1985

create table t1 (a int(11));

1994

1986

select all all * from t1;

1995

1987

select distinct distinct * from t1;

1996

1988

--error 1221

2003

1995

# Test for BUG#10095

2004

1996

2005

1997

CREATE TABLE t1 (

2006

kunde_intern_id int NOT NULL default '0',

2007

kunde_id int NOT NULL default '0',

2008

FK_firma_id int NOT NULL default '0',

1998

kunde_intern_id int(10) unsigned NOT NULL default '0',

1999

kunde_id int(10) unsigned NOT NULL default '0',

2000

FK_firma_id int(10) unsigned NOT NULL default '0',

2009

2001

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

2010

2002

vorname varchar(128) NOT NULL default '',

2011

2003

nachname varchar(128) NOT NULL default '',

2061

2053

2062

2054

2063

2055

2064

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

2056

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

2065

2057

2066

2058

2067

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

2068

INSERT INTO t1 VALUES (0x4000000000000000);

2069

SELECT b FROM t1 WHERE b=0x4000000000000000;

2059

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

2060

INSERT INTO t1 VALUES (0x8000000000000000);

2061

SELECT b FROM t1 WHERE b=0x8000000000000000;

2070

2062

DROP TABLE t1;

2071

2063

2072

2064

2073

2065

# IN with outer join condition (BUG#9393)

2074

2066

2075

CREATE TABLE `t1` ( `gid` int default NULL, `uid` int default NULL);

2067

CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL);

2076

2068

2077

CREATE TABLE `t2` ( `ident` int default NULL, `level` char(16) default NULL);

2069

CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL);

2078

2070

INSERT INTO `t2` VALUES (0,'READ');

2079

2071

2080

CREATE TABLE `t3` ( `id` int default NULL, `name` char(16) default NULL);

2072

CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL);

2081

2073

INSERT INTO `t3` VALUES (1,'fs');

2082

2074

2083

2075

select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);

2086

2078

2087

2079

# Test for BUG#11700

2088

2080

CREATE TABLE t1 (

2089

acct_id int NOT NULL default '0',

2090

profile_id int default NULL,

2081

acct_id int(11) NOT NULL default '0',

2082

profile_id smallint(6) default NULL,

2091

2083

UNIQUE KEY t1$acct_id (acct_id),

2092

2084

KEY t1$profile_id (profile_id)

2093

2085

);

2094

2086

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

2095

2087

2096

2088

CREATE TABLE t2 (

2097

profile_id int default NULL,

2098

queue_id int default NULL,

2099

seq int default NULL,

2089

profile_id smallint(6) default NULL,

2090

queue_id int(11) default NULL,

2091

seq int(11) default NULL,

2100

2092

KEY t2$queue_id (queue_id)

2101

2093

);

2102

2094

INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);

2103

2095

2104

2096

CREATE TABLE t3 (

2105

id int NOT NULL default '0',

2106

qtype int default NULL,

2107

seq int default NULL,

2108

warn_lvl int default NULL,

2109

crit_lvl int default NULL,

2110

rr1 int NOT NULL default '0',

2111

rr2 int default NULL,

2112

default_queue int NOT NULL default '0',

2097

id int(11) NOT NULL default '0',

2098

qtype int(11) default NULL,

2099

seq int(11) default NULL,

2100

warn_lvl int(11) default NULL,

2101

crit_lvl int(11) default NULL,

2102

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

2103

rr2 int(11) default NULL,

2104

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

2113

2105

KEY t3$qtype (qtype),

2114

2106

KEY t3$id (id)

2115

2107

);

2140

2132

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

2141

2133

# range analysis.

2142

2134

2143

create table t2 (a int);

2135

create table t2 (a tinyint unsigned);

2144

2136

create index t2i on t2(a);

2145

2137

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

2146

2138

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

2307

2299

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

2308

2300

# 4.1 and 5.0

2309

2301

2310

CREATE TABLE t1 (i BIGINT NOT NULL);

2302

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2311

2303

INSERT INTO t1 VALUES (10);

2312

2304

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

2313

2305

DROP TABLE t1;

2340

2332

2341

2333

2342

2334

CREATE TABLE t1 (

2343

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

2344

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

2345

F2I4 int NOT NULL default '0'

2346

) ENGINE=MyISAM;

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(11) NOT NULL default '0'

2338

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2347

2339

2348

2340

INSERT INTO t1 VALUES

2349

2341

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

2432

2424

2433

2425

# Bug #11398 Bug in field_conv() results in wrong result of join with index

2434

2426

2435

create table t1 (f1 varchar(6) default NULL, f2 int primary key not null);

2427

create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);

2436

2428

create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));

2437

2429

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

2438

2430

insert into t2 values (" 2", " one "),(" 2", " two ");

2454

2446

# incorrect transformation to join ... on.

2455

2447

2456

2448

2457

create table t1 (a int, t1_val int);

2458

create table t2 (b int, t2_val int);

2459

create table t3 (a int, b int);

2449

create table t1 (a int(10), t1_val int(10));

2450

create table t2 (b int(10), t2_val int(10));

2451

create table t3 (a int(10), b int(10));

2460

2452

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

2461

2453

insert into t2 values (1,1),(2,2),(3,3);

2462

2454

insert into t3 values (1,1),(2,1),(3,1),(4,1);

2483

2475

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

2484

2476

2485

2477

2486

CREATE TABLE t1 (`id` int);

2487

CREATE TABLE t2 (`id` int);

2488

CREATE TABLE t3 (`id` int);

2478

CREATE TABLE t1 (`id` TINYINT);

2479

CREATE TABLE t2 (`id` TINYINT);

2480

CREATE TABLE t3 (`id` TINYINT);

2489

2481

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

2490

2482

INSERT INTO t2 VALUES (2);

2491

2483

INSERT INTO t3 VALUES (3);

2504

2496

# Bug #13067 JOIN xxx USING is case sensitive

2505

2497

2506

2498

2507

create table t1 (a int,b int);

2508

create table t2 (a int,b int);

2499

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

2500

create table t2 (a int(10),b int(10));

2509

2501

insert into t1 values (1,10),(2,20),(3,30);

2510

2502

insert into t2 values (1,10);

2511

2503

# both queries should produce the same result

2553

2545

# nested right join.

2554

2546

2555

2547

2556

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

2548

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

2557

2549

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

2558

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

2550

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

2559

2551

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

2560

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

2552

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

2561

2553

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

2562

2554

2563

2555

# both queries are equivalent

2712

2704

2713

2705

DROP TABLE t1,t2;

2714

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

2715

2722

# BUG#17379

2716

2723

2717

2724

create table t1 (a int);

2746

2753

DROP TABLE t1, t2;

2747

2754

2748

2755

2749

# Bug #18895: Now tests for int

2756

# Bug #18895: BIT values cause joins to fail

2750

2757

2751

2758

create table t1 (

2752

a int not null auto_increment primary key,

2753

b int not null,

2754

c int not null

2759

a int unsigned not null auto_increment primary key,

2760

b bit not null,

2761

c bit not null

2755

2762

);

2756

2763

2757

2764

create table t2 (

2758

a int not null auto_increment primary key,

2759

b int not null,

2760

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,

2761

2768

d varchar(50)

2762

2769

);

2763

2770

2983

2990

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

2984

2991

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

2985

2992

2986

# PBXT: does not return a consistent row count

2987

--replace_column 9 #

2988

2993

EXPLAIN SELECT * FROM t1

2989

2994

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

2990

--replace_column 9 #

2991

2995

EXPLAIN SELECT * FROM t1

2992

2996

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

2993

--replace_column 9 #

2994

2997

EXPLAIN SELECT * FROM t1

2995

2998

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

2996

2999

3031

3034

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

3032

3035

3033

3036

DROP TABLE t1,t2;

3034

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

3037

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

3035

3038

3036

3039

3037

create table t1 (a bigint);

3040

create table t1 (a bigint unsigned);

3038

3041

insert into t1 values

3039

(if(1, 92233720368547758, 1)),

3040

(case when 1 then 92233720368547758 else 1 end),

3041

(coalesce(92233720368547758, 1));

3042

(if(1, 9223372036854775808, 1)),

3043

(case when 1 then 9223372036854775808 else 1 end),

3044

(coalesce(9223372036854775808, 1));

3042

3045

select * from t1;

3043

3046

drop table t1;

3044

3047

create table t1 select

3049

3052

drop table t1;

3050

3053

# Ensure we handle big values properly

3051

3054

select

3052

if(1, 1111111111111111111, 1) i,

3053

case when 1 then 1111111111111111111 else 1 end c,

3054

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;

3055

3058

3056

3059

3057

3060

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

3195

3198

3196

3199

3197

3200

CREATE TABLE t1 (

3198

c1 int NOT NULL AUTO_INCREMENT,

3201

c1 int(11) NOT NULL AUTO_INCREMENT,

3199

3202

c2 varchar(1000) DEFAULT NULL,

3200

c3 bigint DEFAULT NULL,

3201

c4 bigint DEFAULT NULL,

3203

c3 bigint(20) DEFAULT NULL,

3204

c4 bigint(20) DEFAULT NULL,

3202

3205

PRIMARY KEY (c1)

3203

3206

);

3204

3207

3264

3267

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

3265

3268

3266

3269

3267

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

3268

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,

3269

3272

c22 INT DEFAULT NULL,

3270

3273

KEY(c21, c22));

3271

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3274

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

3272

3275

c32 INT DEFAULT NULL,

3273

3276

c33 INT NOT NULL,

3274

c34 INT DEFAULT 0,

3277

c34 INT UNSIGNED DEFAULT 0,

3275

3278

KEY (c33, c34, c32));

3276

3279

3277

3280

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

Older »