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

1793

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;

1794

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

1795

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;

1795

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;

1796

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

1797

--error 1686 # bad datetime

1798

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;

1863

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

1864

drop table t1;

1865

1866

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;

1866

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;

1867

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

1868

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

1868

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

1869

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

1870

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

1871

drop table t1,t2;

1923

# Covering index is mentioned in EXPLAIN output for const tables (bug #5333)

1924

1925

1926

CREATE TABLE t1 (

1926

CREATE TEMPORARY TABLE t1 (

1927

i int NOT NULL default '0',

1928

c char(10) NOT NULL default '',

1929

PRIMARY KEY (i),

2335

# Test for bug #6474

2336

2337

2338

CREATE TABLE t1 (

2338

CREATE TEMPORARY TABLE t1 (

2339

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

2340

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

2341

F2I4 int NOT NULL default '0'

2388

# Bug#9799

2389

2390

2391

create table t1 (s1 int) engine=myisam;

2391

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

2392

insert into t1 values (0);

2393

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

2394

drop table t1;

Older »