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

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;

1968

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

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;

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;

1970

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

1971

1970

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;

1972

1971

ERROR HY000: Received an invalid datetime value 'wrong-date-value'.

2157

2156

2158

2157

2159

2158

drop table t1;

2160

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;

2159

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

2161

2160

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

2162

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

2161

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

2163

2162

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

2164

2163

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

2165

2164

aa id t2_id id

2215

2214

3 2 3 4

2216

2215

4 2 4 4

2217

2216

drop table t1, t2;

2218

CREATE TABLE t1 (

2217

CREATE TEMPORARY TABLE t1 (

2219

2218

i int NOT NULL default '0',

2220

2219

c char(10) NOT NULL default '',

2221

2220

PRIMARY KEY (i),

2592

2591

X X

2593

2592

DROP TABLE t1, t2;

2594

2593

End of 4.1 tests

2595

CREATE TABLE t1 (

2594

CREATE TEMPORARY TABLE t1 (

2596

2595

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

2597

2596

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

2598

2597

F2I4 int NOT NULL default '0'

2641

2640

Wall

2642

2641

Wall

2643

2642

drop table t1;

2644

create table t1 (s1 int) engine=myisam;

2643

create temporary table t1 (s1 int) engine=myisam;

2645

2644

insert into t1 values (0);

2646

2645

select avg(distinct s1) from t1 group by s1 with rollup;

2647

2646

avg(distinct s1)

Older »