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

1795

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

1796

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;

1797

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

1798

--error ER_INVALID_DATETIME_VALUE # bad datetime

1797

--error 1686 # bad datetime

1799

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;

1800

1799

# Testing the same select with NULL's instead of invalid datetime values

1801

1800

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 >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;

1829

1828

insert into t1 values ();

1830

1829

insert into t1 values ();

1831

1830

# ,

1832

--sorted_result

1833

1831

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

1834

1832

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

1835

1833

# stright_join

1865

1863

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

1866

1864

drop table t1;

1867

1865

1868

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;

1869

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

1870

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;

1871

1869

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

1872

1870

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

1873

1871

drop table t1,t2;

1925

1923

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

1926

1924

1927

1925

1928

CREATE TEMPORARY TABLE t1 (

1926

CREATE TABLE t1 (

1929

1927

i int NOT NULL default '0',

1930

1928

c char(10) NOT NULL default '',

1931

1929

PRIMARY KEY (i),

1991

1989

create table t1 (a int);

1992

1990

select all all * from t1;

1993

1991

select distinct distinct * from t1;

1994

--error ER_WRONG_USAGE

1992

--error 1221

1995

1993

select all distinct * from t1;

1996

--error ER_WRONG_USAGE

1994

--error 1221

1997

1995

select distinct all * from t1;

1998

1996

drop table t1;

1999

1997

2175

2173

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

2176

2174

create table t11 like t1;

2177

2175

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

2178

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

2179

2177

show table status like 't1%';

2180

2178

select 123 as a from t1 where f1 is null;

2181

2179

drop table t1,t11;

2239

2237

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

2240

2238

2241

2239

EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);

2242

--error ER_KEY_DOES_NOT_EXITS

2240

--error 1176

2243

2241

EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);

2244

--error ER_KEY_DOES_NOT_EXITS

2242

--error 1176

2245

2243

EXPLAIN SELECT * FROM t1 FORCE INDEX (a);

2246

2244

2247

2245

DROP TABLE t1;

2322

2320

# 1. has integer type,

2323

2321

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

2324

2322

# 3. available in every server build

2325

INSERT INTO t1 VALUES(@@server_id);

2326

INSERT INTO t2 VALUES(@@server_id);

2323

INSERT INTO t1 VALUES(@@connect_timeout);

2324

INSERT INTO t2 VALUES(@@connect_timeout);

2327

2325

2328

2326

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

2329

2327

--replace_column 1 X 2 X

2330

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

2331

2329

2332

2330

DROP TABLE t1, t2;

2333

2331

2337

2335

# Test for bug #6474

2338

2336

2339

2337

2340

CREATE TEMPORARY TABLE t1 (

2338

CREATE TABLE t1 (

2341

2339

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

2342

2340

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

2343

2341

F2I4 int NOT NULL default '0'

2390

2388

# Bug#9799

2391

2389

2392

2390

2393

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

2391

create table t1 (s1 int) engine=myisam;

2394

2392

insert into t1 values (0);

2395

2393

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

2396

2394

drop table t1;

2589

2587

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

2590

2588

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

2591

2589

# should return all records

2592

--error ER_INVALID_DATETIME_VALUE # Bad date

2590

--error 1686 # Bad date

2593

2591

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

2594

--error ER_INVALID_DATETIME_VALUE # Bad date

2592

--error 1686 # Bad date

2595

2593

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

2596

2594

# should return 4,5

2597

--error ER_INVALID_DATETIME_VALUE # Bad date

2595

--error 1686 # Bad date

2598

2596

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

2599

--error ER_INVALID_DATETIME_VALUE # Bad date

2597

--error 1686 # Bad date

2600

2598

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

2601

--error ER_INVALID_DATETIME_VALUE # Bad date

2599

--error 1686 # Bad date

2602

2600

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

2603

--error ER_INVALID_DATETIME_VALUE # Bad date

2601

--error 1686 # Bad date

2604

2602

select * from t1 where f2 <= '2005-09-3a' order by f2;

2605

2603

drop table t1;

2606

2604

2689

2687

2690

2688

# Bug#15538 unchecked table absense caused server crash.

2691

2689

2692

--error ER_PARSE_ERROR

2690

--error 1064

2693

2691

select * from (select * left join t on f1=f2) tt;

2694

2692

2695

2693

3456

3454

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

3457

3455

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

3458

3456

DROP TABLE t1;

3459

3460

3461

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

3462

3463

CREATE TABLE t1 (a INT);

3464

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

3465

CREATE TABLE t2 (b INT);

3466

INSERT INTO t2 VALUES (2);

3467

SELECT * FROM t1 WHERE a = 1 + 1;

3468

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

3469

SELECT * FROM t1 HAVING a = 1 + 1;

3470

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

3471

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

3472

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

3473

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

3474

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

3475

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

3476

3477

DROP TABLE t1, t2;

Older »