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

1793

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;

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

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;

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

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;

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 TEMPORARY TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

1868

CREATE 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 TEMPORARY TABLE t1 (

1926

CREATE TABLE t1 (

1927

i int NOT NULL default '0',

1928

c char(10) NOT NULL default '',

1929

PRIMARY KEY (i),

2173

create table t1 (f1 int not null auto_increment primary key, f2 varchar(10));

2174

create table t11 like t1;

2175

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

2176

--replace_column 1 # 6 # 7 # 8 # 9 # 10 #

2176

--replace_column 3 X 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X

2177

show table status like 't1%';

2178

select 123 as a from t1 where f1 is null;

2179

drop table t1,t11;

2320

# 1. has integer type,

2321

# 2. can be used with the "@@name" syntax

2322

# 3. available in every server build

2323

INSERT INTO t1 VALUES(@@server_id);

2324

INSERT INTO t2 VALUES(@@server_id);

2323

INSERT INTO t1 VALUES(@@connect_timeout);

2324

INSERT INTO t2 VALUES(@@connect_timeout);

2325

2326

# We only need to ensure 1 row is returned to validate the results

2327

--replace_column 1 X 2 X

2328

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

2328

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

2329

2330

DROP TABLE t1, t2;

2331

2335

# Test for bug #6474

2336

2337

2338

CREATE TEMPORARY TABLE t1 (

2338

CREATE 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 temporary table t1 (s1 int) engine=myisam;

2391

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

3454

SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;

3455

SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;

3456

DROP TABLE t1;

3457

3458

3459

# MySQL Bug#33546: Slowdown on re-evaluation of constant expressions.

3460

3461

CREATE TABLE t1 (a INT);

3462

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

3463

CREATE TABLE t2 (b INT);

3464

INSERT INTO t2 VALUES (2);

3465

SELECT * FROM t1 WHERE a = 1 + 1;

3466

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

3467

SELECT * FROM t1 HAVING a = 1 + 1;

3468

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

3469

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

3470

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

3471

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

3472

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

3473

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

3474

3475

DROP TABLE t1, t2;

Older »