~drizzle-trunk/drizzle/development

select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price;

1173

1169

fld3 period price price2

1174

1170

admonishing 1002 28357832 8723648

1237

1233

18801 inch 1001 5987435 234724

1238

1234

18811 repetitions 1001 5987435 234724

1239

1235

create table t4 (

1240

companynr tinyint unsigned NOT NULL default '0',

1236

companynr int NOT NULL default '0',

1241

1237

companyname char(30) NOT NULL default '',

1242

1238

PRIMARY KEY (companynr),

1243

1239

UNIQUE KEY companyname(companyname)

1941

1937

show full columns from t2;

1942

1938

Field Type Collation Null Key Default Extra Privileges Comment

1943

1939

auto int NULL NO PRI NULL auto_increment #

1944

fld1 int unsigned NULL NO UNI NULL #

1945

companynr tinyint(3) unsigned NULL NO NULL #

1940

fld1 int NULL NO UNI NULL #

1941

companynr int NULL NO NULL #

1946

1942

fld3 varchar(30) utf8_general_ci NO MUL NULL #

1947

1943

fld4 varchar(35) utf8_general_ci NO NULL #

1948

1944

fld5 varchar(35) utf8_general_ci NO NULL #

1949

1945

fld6 varchar(4) utf8_general_ci NO NULL #

1950

1946

show full columns from t2 from test like 'f%';

1951

1947

Field Type Collation Null Key Default Extra Privileges Comment

1952

fld1 int unsigned NULL NO UNI NULL #

1948

fld1 int NULL NO UNI NULL #

1953

1949

fld3 varchar(30) utf8_general_ci NO MUL NULL #

1954

1950

fld4 varchar(35) utf8_general_ci NO NULL #

1955

1951

fld5 varchar(35) utf8_general_ci NO NULL #

1958

1954

Field Type Collation Null Key Default Extra Privileges Comment

1959

1955

drop table t4, t3, t2, t1;

1960

1956

CREATE TABLE t1 (

1961

id bigint unsigned NOT NULL auto_increment,

1957

id bigint NOT NULL auto_increment,

1962

1958

pseudo varchar(35) NOT NULL default '',

1963

1959

PRIMARY KEY (id),

1964

1960

UNIQUE KEY pseudo (pseudo)

1968

1964

SELECT 1 as rnd1 from t1 where rand() > 2;

1969

1965

rnd1

1970

1966

DROP TABLE t1;

1971

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;

1967

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;

1972

1968

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

1973

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;

1969

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;

1974

1970

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

1975

1971

SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;

1976

1972

gvid the_success the_fail the_size the_time

2270

2266

city

2271

2267

London

2272

2268

DROP TABLE t1;

2273

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

2269

create table t1 (a int, b int);

2274

2270

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

2275

2271

select a-b from t1 order by 1;

2276

2272

a-b

2286

2282

d (a-b >= 0) b

2287

2283

1 1 0

2288

2284

0 1 1

2289

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

2290

cast((a - b) as unsigned)

2285

select a - b from t1 order by 1;

2286

a - b

2287

-1

2291

2288

2292

2289

2293

18446744073709551615

2294

2290

drop table t1;

2295

2291

create table t1 (a int);

2296

2292

select all all * from t1;

2303

2299

ERROR HY000: Incorrect usage of ALL and DISTINCT

2304

2300

drop table t1;

2305

2301

CREATE TABLE t1 (

2306

kunde_intern_id int unsigned NOT NULL default '0',

2307

kunde_id int unsigned NOT NULL default '0',

2308

FK_firma_id int unsigned NOT NULL default '0',

2302

kunde_intern_id int NOT NULL default '0',

2303

kunde_id int NOT NULL default '0',

2304

FK_firma_id int NOT NULL default '0',

2309

2305

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

2310

2306

vorname varchar(128) NOT NULL default '',

2311

2307

nachname varchar(128) NOT NULL default '',

2356

2352

COUNT(*)

2357

2353

2358

2354

drop table t1;

2359

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

2360

INSERT INTO t1 VALUES (0x8000000000000000);

2361

SELECT b FROM t1 WHERE b=0x8000000000000000;

2355

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

2356

INSERT INTO t1 VALUES (0x4000000000000000);

2357

SELECT b FROM t1 WHERE b=0x4000000000000000;

2362

2358

2363

9223372036854775808

2359

4611686018427387904

2364

2360

DROP TABLE t1;

2365

2361

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

2366

2362

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

2373

2369

drop table t1,t2,t3;

2374

2370

CREATE TABLE t1 (

2375

2371

acct_id int NOT NULL default '0',

2376

profile_id smallint default NULL,

2372

profile_id int default NULL,

2377

2373

UNIQUE KEY t1$acct_id (acct_id),

2378

2374

KEY t1$profile_id (profile_id)

2379

2375

);

2380

2376

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

2381

2377

CREATE TABLE t2 (

2382

profile_id smallint default NULL,

2378

profile_id int default NULL,

2383

2379

queue_id int default NULL,

2384

2380

seq int default NULL,

2385

2381

KEY t2$queue_id (queue_id)

2391

2387

seq int default NULL,

2392

2388

warn_lvl int default NULL,

2393

2389

crit_lvl int default NULL,

2394

rr1 tinyint NOT NULL default '0',

2390

rr1 int NOT NULL default '0',

2395

2391

rr2 int default NULL,

2396

default_queue tinyint NOT NULL default '0',

2392

default_queue int NOT NULL default '0',

2397

2393

KEY t3$qtype (qtype),

2398

2394

KEY t3$id (id)

2399

2395

);

2417

2413

2418

2414

NULL

2419

2415

drop table t1,t2;

2420

create table t2 (a tinyint unsigned);

2416

create table t2 (a int);

2421

2417

create index t2i on t2(a);

2422

2418

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

2423

2419

select * from t2 where a > -1;

2584

2580

min(key1)

2585

2581

0.3762

2586

2582

DROP TABLE t1,t2;

2587

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2583

CREATE TABLE t1 (i BIGINT NOT NULL);

2588

2584

INSERT INTO t1 VALUES (10);

2589

2585

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

2590

2586

i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01')

2723

2719

2724

2720

2725

2721

drop table t1, t2;

2726

CREATE TABLE t1 (`id` TINYINT);

2727

CREATE TABLE t2 (`id` TINYINT);

2728

CREATE TABLE t3 (`id` TINYINT);

2722

CREATE TABLE t1 (`id` int);

2723

CREATE TABLE t2 (`id` int);

2724

CREATE TABLE t3 (`id` int);

2729

2725

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

2730

2726

INSERT INTO t2 VALUES (2);

2731

2727

INSERT INTO t3 VALUES (3);

2987

2983

1 SIMPLE t2 ref c c 5 test.t1.a 1

2988

2984

DROP TABLE t1, t2;

2989

2985

create table t1 (

2990

a int unsigned not null auto_increment primary key,

2991

b bit not null,

2992

c bit not null

2986

a int not null auto_increment primary key,

2987

b int not null,

2988

c int not null

2993

2989

);

2994

2990

create table t2 (

2995

a int unsigned not null auto_increment primary key,

2996

b bit not null,

2997

c int unsigned not null,

2991

a int not null auto_increment primary key,

2992

b int not null,

2993

c int not null,

2998

2994

d varchar(50)

2999

2995

);

3000

2996

insert into t1 (b,c) values (0,1), (0,1);

3108

3104

t3.a=t2.a AND t3.c IN ('bb','ee');

3109

3105

id select_type table type possible_keys key key_len ref rows Extra

3110

3106

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3111

1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR

3107

1 SIMPLE t2 range si si 5 NULL 4 Using where; Using MRR

3112

3108

1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where

3113

3109

EXPLAIN

3114

3110

SELECT t3.a FROM t1,t2,t3

3124

3120

t3.c IN ('bb','ee');

3125

3121

id select_type table type possible_keys key key_len ref rows Extra

3126

3122

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3127

1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR

3123

1 SIMPLE t2 range si si 5 NULL 2 Using where; Using MRR

3128

3124

1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where

3129

3125

EXPLAIN

3130

3126

SELECT t3.a FROM t1,t2,t3

3132

3128

t3.c IN ('bb','ee');

3133

3129

id select_type table type possible_keys key key_len ref rows Extra

3134

3130

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3135

1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using MRR

3131

1 SIMPLE t2 range si,ai si 5 NULL 2 Using where; Using MRR

3136

3132

1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where

3137

3133

DROP TABLE t1,t2,t3;

3138

3134

CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);

3206

3202

EXPLAIN SELECT * FROM t1

3207

3203

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

3208

3204

id select_type table type possible_keys key key_len ref rows Extra

3209

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3205

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL # Using intersect(idx2,idx1); Using where; Using index

3210

3206

EXPLAIN SELECT * FROM t1

3211

3207

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

3212

3208

id select_type table type possible_keys key key_len ref rows Extra

3213

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3209

1 SIMPLE t1 ref idx1,idx2 idx2 4 const # Using where

3214

3210

EXPLAIN SELECT * FROM t1

3215

3211

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

3216

3212

id select_type table type possible_keys key key_len ref rows Extra

3217

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3213

1 SIMPLE t1 ref idx1,idx2 idx2 4 const # Using where

3218

3214

DROP INDEX idx1 ON t1;

3219

3215

CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);

3220

3216

EXPLAIN SELECT * FROM t1

3252

3248

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

3253

3249

id select_type table type possible_keys key key_len ref rows Extra

3254

3250

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

3255

1 SIMPLE t1 range ts ts 4 NULL 1 Using index condition; Using where; Using MRR

3251

1 SIMPLE t1 range ts ts 4 NULL 1 Using where; Using MRR

3256

3252

Warnings:

3257

3253

Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1

3258

3254

SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30

3263

3259

Warnings:

3264

3260

Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1

3265

3261

DROP TABLE t1,t2;

3266

create table t1 (a bigint unsigned);

3262

create table t1 (a bigint);

3267

3263

insert into t1 values

3268

(if(1, 9223372036854775808, 1)),

3269

(case when 1 then 9223372036854775808 else 1 end),

3270

(coalesce(9223372036854775808, 1));

3264

(if(1, 92233720368547758, 1)),

3265

(case when 1 then 92233720368547758 else 1 end),

3266

(coalesce(92233720368547758, 1));

3271

3267

select * from t1;

3272

3268

3273

9223372036854775808

3274

9223372036854775808

3275

9223372036854775808

3269

92233720368547758

3270

92233720368547758

3271

92233720368547758

3276

3272

drop table t1;

3277

3273

create table t1 select

3278

3274

if(1, 9223372036854775808, 1) i,

3287

3283

) ENGINE=InnoDB

3288

3284

drop table t1;

3289

3285

select

3290

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

3291

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

3292

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

3286

if(1, 1111111111111111111, 1) i,

3287

case when 1 then 1111111111111111111 else 1 end c,

3288

coalesce(1111111111111111111, 1) co;

3293

3289

i c co

3294

3290

1111111111111111111 1111111111111111111 1111111111111111111

3295

3291

CREATE TABLE t1 (name varchar(255));

3576

3572

3577

3573

Warnings:

3578

3574

Warning 1466 Leading spaces are removed from name ' x'

3579

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

3580

CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,

3575

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

3576

CREATE TABLE t2 (c21 INT NOT NULL,

3581

3577

c22 INT DEFAULT NULL,

3582

3578

KEY(c21, c22));

3583

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

3579

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3584

3580

c32 INT DEFAULT NULL,

3585

3581

c33 INT NOT NULL,

3586

c34 INT UNSIGNED DEFAULT 0,

3582

c34 INT DEFAULT 0,

3587

3583

KEY (c33, c34, c32));

3588

3584

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

3589

3585

INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;

Older »