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

1791

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

1792

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;

1793

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

1794

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

1795

--error 1686 # bad datetime

1799

1796

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

1797

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

1801

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

1829

1826

insert into t1 values ();

1830

1827

insert into t1 values ();

1831

1828

# ,

1832

--sorted_result

1833

1829

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

1834

1830

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

1835

1831

# stright_join

1865

1861

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

1866

1862

drop table t1;

1867

1863

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;

1864

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

1865

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;

1866

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

1871

1867

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

1872

1868

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

1873

1869

drop table t1,t2;

1925

1921

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

1926

1922

1927

1923

1928

CREATE TEMPORARY TABLE t1 (

1924

CREATE TABLE t1 (

1929

1925

i int NOT NULL default '0',

1930

1926

c char(10) NOT NULL default '',

1931

1927

PRIMARY KEY (i),

1991

1987

create table t1 (a int);

1992

1988

select all all * from t1;

1993

1989

select distinct distinct * from t1;

1994

--error ER_WRONG_USAGE

1990

--error 1221

1995

1991

select all distinct * from t1;

1996

--error ER_WRONG_USAGE

1992

--error 1221

1997

1993

select distinct all * from t1;

1998

1994

drop table t1;

1999

1995

2175

2171

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

2176

2172

create table t11 like t1;

2177

2173

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

2178

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

2174

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

2179

2175

show table status like 't1%';

2180

2176

select 123 as a from t1 where f1 is null;

2181

2177

drop table t1,t11;

2239

2235

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

2240

2236

2241

2237

EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);

2242

--error ER_KEY_DOES_NOT_EXITS

2238

--error 1176

2243

2239

EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);

2244

--error ER_KEY_DOES_NOT_EXITS

2240

--error 1176

2245

2241

EXPLAIN SELECT * FROM t1 FORCE INDEX (a);

2246

2242

2247

2243

DROP TABLE t1;

2322

2318

# 1. has integer type,

2323

2319

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

2324

2320

# 3. available in every server build

2325

INSERT INTO t1 VALUES(@@server_id);

2326

INSERT INTO t2 VALUES(@@server_id);

2321

INSERT INTO t1 VALUES(@@connect_timeout);

2322

INSERT INTO t2 VALUES(@@connect_timeout);

2327

2323

2328

2324

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

2329

2325

--replace_column 1 X 2 X

2330

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

2326

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

2331

2327

2332

2328

DROP TABLE t1, t2;

2333

2329

2337

2333

# Test for bug #6474

2338

2334

2339

2335

2340

CREATE TEMPORARY TABLE t1 (

2336

CREATE TABLE t1 (

2341

2337

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

2342

2338

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

2343

2339

F2I4 int NOT NULL default '0'

2390

2386

# Bug#9799

2391

2387

2392

2388

2393

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

2389

create table t1 (s1 int) engine=myisam;

2394

2390

insert into t1 values (0);

2395

2391

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

2396

2392

drop table t1;

2487

2483

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

2488

2484

INSERT INTO t2 VALUES (2);

2489

2485

INSERT INTO t3 VALUES (3);

2490

--error ER_NON_UNIQ_ERROR

2486

-- error 1052

2491

2487

SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);

2492

--error ER_NON_UNIQ_ERROR

2488

-- error 1052

2493

2489

SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);

2494

--error ER_NON_UNIQ_ERROR

2490

-- error 1052

2495

2491

SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);

2496

--error ER_NON_UNIQ_ERROR

2492

-- error 1052

2497

2493

SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);

2498

2494

2499

2495

drop table t1, t2, t3;

2528

2524

select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a);

2529

2525

# Notice that ',' has lower priority than 'join', thus we have that:

2530

2526

# t1, t2 join t3 <==> t1, (t2 join t3).

2531

--error ER_BAD_FIELD_ERROR

2527

-- error 1054

2532

2528

select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a);

2533

2529

select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c);

2534

2530

select * from t1 join t2 join t4 using (c);

2589

2585

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

2590

2586

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

2591

2587

# should return all records

2592

--error ER_INVALID_DATE_VALUE # Bad date

2588

--error 1686 # Bad date

2593

2589

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

2594

--error ER_INVALID_DATETIME_VALUE # Bad date

2590

--error 1686 # Bad date

2595

2591

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

2596

2592

# should return 4,5

2597

--error ER_INVALID_DATETIME_VALUE # Bad date

2593

--error 1686 # Bad date

2598

2594

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

2599

--error ER_INVALID_DATETIME_VALUE # Bad date

2595

--error 1686 # Bad date

2600

2596

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

2601

--error ER_INVALID_DATETIME_VALUE # Bad date

2597

--error 1686 # Bad date

2602

2598

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

2603

--error ER_INVALID_DATETIME_VALUE # Bad date

2599

--error 1686 # Bad date

2604

2600

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

2605

2601

drop table t1;

2606

2602

2689

2685

2690

2686

# Bug#15538 unchecked table absense caused server crash.

2691

2687

2692

--error ER_PARSE_ERROR

2688

--error 1064

2693

2689

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

2694

2690

2695

2691

2720

2716

create table t1 (a int);

2721

2717

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

2722

2718

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

2723

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

2719

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

2724

2720

analyze table t2;

2725

2721

select 'In next EXPLAIN, B.rows must be exactly 10:' Z;

2726

2722

3278

3274

KEY (c33, c34, c32));

3279

3275

3280

3276

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

3281

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

3277

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

3282

3278

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

3283

3279

(2, 2, 0, 0),

3284

3280

(3, 3, 1, 0),

3456

3452

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

3457

3453

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

3458

3454

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 »