~drizzle-trunk/drizzle/development

CREATE TEMPORARY 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) default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;

1966

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

1977

1967

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

1978

1968

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

1979

1969

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

2014

2004

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

2015

2005

a a

2016

2006

1 1

2007

2 1

2008

3 1

2017

2009

1 2

2010

2 2

2011

3 2

2018

2012

1 3

2019

2 1

2020

2 2

2021

2013

2 3

2022

3 1

2023

3 2

2024

2014

3 3

2025

2015

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

2026

2016

a a

2458

2448

create table t11 like t1;

2459

2449

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

2460

2450

show table status like 't1%';

2461

Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment

2462

# test t1 STANDARD InnoDB # # # # #

2451

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

2452

t1 InnoDB X Compact 2 8192 X X X X X X X X utf8_general_ci NULL

2453

t11 InnoDB X Compact 0 0 X X X X X X X X utf8_general_ci NULL

2463

2454

select 123 as a from t1 where f1 is null;

2464

2455

2465

2456

drop table t1,t11;

2593

2584

DROP TABLE t1;

2594

2585

CREATE TABLE t1 (c0 int);

2595

2586

CREATE TABLE t2 (c0 int);

2596

INSERT INTO t1 VALUES(@@server_id);

2597

INSERT INTO t2 VALUES(@@server_id);

2598

SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@server_id);

2587

INSERT INTO t1 VALUES(@@connect_timeout);

2588

INSERT INTO t2 VALUES(@@connect_timeout);

2589

SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout);

2599

2590

c0 c0

2600

2591

X X

2601

2592

DROP TABLE t1, t2;

2818

2809

insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),

2819

2810

(4,'2005-10-01'),(5,'2005-12-30');

2820

2811

select * from t1 where f2 >= 0 order by f2;

2821

ERROR HY000: Received an invalid DATE value '0'.

2812

ERROR HY000: Received an invalid datetime value '0'.

2822

2813

select * from t1 where f2 >= '0000-00-00' order by f2;

2823

2814

ERROR HY000: Received an invalid datetime value '0000-00-00'.

2824

2815

select * from t1 where f2 >= '2005-09-31' order by f2;

2932

2923

create table t1 (a int);

2933

2924

insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

2934

2925

create table t2 (a int, b int, c int, e int, primary key(a,b,c));

2935

insert into t2 select A.a, B.a, C.a, C.a from t1 A CROSS JOIN t1 B CROSS JOIN t1 C;

2926

insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C;

2936

2927

analyze table t2;

2937

2928

Table Op Msg_type Msg_text

2938

2929

test.t2 analyze status OK

2952

2943

SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;

2953

2944

id select_type table type possible_keys key key_len ref rows Extra

2954

2945

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where; Using index

2955

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

2946

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

2956

2947

EXPLAIN

2957

2948

SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;

2958

2949

id select_type table type possible_keys key key_len ref rows Extra

2959

2950

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where; Using index

2960

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

2951

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

2961

2952

DROP TABLE t1, t2;

2962

2953

create table t1 (

2963

2954

a int not null auto_increment primary key,

2993

2984

EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;

2994

2985

id select_type table type possible_keys key key_len ref rows Extra

2995

2986

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

2996

1 SIMPLE t2 ref idx idx 4 const 8 Using index

2987

1 SIMPLE t2 ref idx idx 4 const 7 Using index

2997

2988

EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;

2998

2989

id select_type table type possible_keys key key_len ref rows Extra

2999

2990

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3011

3002

id select_type table type possible_keys key key_len ref rows Extra

3012

3003

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3013

3004

1 SIMPLE t2 const idx1 NULL NULL NULL 1

3014

1 SIMPLE t3 ALL idx1 NULL NULL NULL 5 Using where

3005

1 SIMPLE t3 ref idx1 idx1 5 const 2

3015

3006

SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id

3016

3007

WHERE t1.id=2;

3017

3008

id a b c d e

3225

3216

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

3226

3217

id select_type table type possible_keys key key_len ref rows Extra

3227

3218

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

3228

1 SIMPLE t1 range ts ts 9 NULL 2 Using where

3219

1 SIMPLE t1 range ts ts 5 NULL 1 Using where

3229

3220

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

3230

3221

AND t1.ts BETWEEN t2.dt1 AND t2.dt2

3231

3222

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

3250

3241

show create table t1;

3251

3242

Table Create Table

3252

3243

t1 CREATE TABLE `t1` (

3253

`i` DECIMAL(19,0) NOT NULL,

3254

`c` DECIMAL(19,0) NOT NULL,

3255

`co` DECIMAL(19,0) NOT NULL

3256

) ENGINE=InnoDB COLLATE = utf8_general_ci

3244

`i` decimal(19,0) NOT NULL,

3245

`c` decimal(19,0) NOT NULL,

3246

`co` decimal(19,0) NOT NULL

3247

) ENGINE=InnoDB

3257

3248

drop table t1;

3258

3249

select

3259

3250

if(1, 1111111111111111111, 1) i,

3555

3546

c34 INT DEFAULT 0,

3556

3547

KEY (c33, c34, c32));

3557

3548

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

3558

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

3549

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

3559

3550

INSERT INTO t3 VALUES (1, 1, 1, 0),

3560

3551

(2, 2, 0, 0),

3561

3552

(3, 3, 1, 0),

3670

3661

3671

3662

3672

3663

DROP TABLE t1;

3673

CREATE TABLE t1 (a INT);

3674

INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

3675

CREATE TABLE t2 (b INT);

3676

INSERT INTO t2 VALUES (2);

3677

SELECT * FROM t1 WHERE a = 1 + 1;

3678

3679

3680

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1;

3681

id select_type table type possible_keys key key_len ref rows filtered Extra

3682

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where

3683

Warnings:

3684

Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1)))

3685

SELECT * FROM t1 HAVING a = 1 + 1;

3686

3687

3688

EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1;

3689

id select_type table type possible_keys key key_len ref rows filtered Extra

3690

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00

3691

Warnings:

3692

Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1)))

3693

SELECT * FROM t1, t2 WHERE a = b + (1 + 1);

3694

a b

3695

4 2

3696

EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1);

3697

id select_type table type possible_keys key key_len ref rows filtered Extra

3698

1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00

3699

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer

3700

Warnings:

3701

Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = (`test`.`t2`.`b` + <cache>((1 + 1))))

3702

SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;

3703

b a

3704

2 3

3705

EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;

3706

id select_type table type possible_keys key key_len ref rows filtered Extra

3707

1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00

3708

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00

3709

Warnings:

3710

Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = (`test`.`t2`.`b` + 1))) where 1

3711

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');

3712

id select_type table type possible_keys key key_len ref rows filtered Extra

3713

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where

3714

Warnings:

3715

Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00')))

3716

DROP TABLE t1, t2;

Older »