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

1794

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

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;

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;

1796

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

1797

--error ER_INVALID_DATETIME_VALUE # bad datetime

1795

# Disable PS becasue we get more warnings from PS than from normal execution

1796

--disable_ps_protocol

1798

1797

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;

1798

--enable_ps_protocol

1799

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

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;

1801

DROP TABLE t1,t2;

1828

insert into t1 values ();

1829

insert into t1 values ();

1830

# ,

1831

--sorted_result

1832

1831

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

1833

1832

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

1834

1833

# stright_join

1864

1863

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

1865

1864

drop table t1;

1866

1865

1867

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;

1868

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

1869

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;

1870

1869

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

1871

1870

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

1872

1871

drop table t1,t2;

1924

1923

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

1925

1924

1926

1925

1927

CREATE TEMPORARY TABLE t1 (

1926

CREATE TABLE t1 (

1928

1927

i int NOT NULL default '0',

1929

1928

c char(10) NOT NULL default '',

1930

1929

PRIMARY KEY (i),

1990

1989

create table t1 (a int);

1991

1990

select all all * from t1;

1992

1991

select distinct distinct * from t1;

1993

--error ER_WRONG_USAGE

1992

--error 1221

1994

1993

select all distinct * from t1;

1995

--error ER_WRONG_USAGE

1994

--error 1221

1996

1995

select distinct all * from t1;

1997

1996

drop table t1;

1998

1997

2174

2173

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

2175

2174

create table t11 like t1;

2176

2175

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

2177

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

2178

2177

show table status like 't1%';

2179

2178

select 123 as a from t1 where f1 is null;

2180

2179

drop table t1,t11;

2238

2237

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

2239

2238

2240

2239

EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);

2241

--error ER_KEY_DOES_NOT_EXITS

2240

--error 1176

2242

2241

EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);

2243

--error ER_KEY_DOES_NOT_EXITS

2242

--error 1176

2244

2243

EXPLAIN SELECT * FROM t1 FORCE INDEX (a);

2245

2244

2246

2245

DROP TABLE t1;

2321

2320

# 1. has integer type,

2322

2321

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

2323

2322

# 3. available in every server build

2324

INSERT INTO t1 VALUES(@@server_id);

2325

INSERT INTO t2 VALUES(@@server_id);

2323

INSERT INTO t1 VALUES(@@connect_timeout);

2324

INSERT INTO t2 VALUES(@@connect_timeout);

2326

2325

2327

2326

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

2328

2327

--replace_column 1 X 2 X

2329

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

2330

2329

2331

2330

DROP TABLE t1, t2;

2332

2331

2336

2335

# Test for bug #6474

2337

2336

2338

2337

2339

CREATE TEMPORARY TABLE t1 (

2338

CREATE TABLE t1 (

2340

2339

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

2341

2340

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

2342

2341

F2I4 int NOT NULL default '0'

2389

2388

# Bug#9799

2390

2389

2391

2390

2392

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

2391

create table t1 (s1 int) engine=myisam;

2393

2392

insert into t1 values (0);

2394

2393

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

2395

2394

drop table t1;

2486

2485

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

2487

2486

INSERT INTO t2 VALUES (2);

2488

2487

INSERT INTO t3 VALUES (3);

2489

--error ER_NON_UNIQ_ERROR

2488

-- error 1052

2490

2489

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

2491

--error ER_NON_UNIQ_ERROR

2490

-- error 1052

2492

2491

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

2493

--error ER_NON_UNIQ_ERROR

2492

-- error 1052

2494

2493

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

2495

--error ER_NON_UNIQ_ERROR

2494

-- error 1052

2496

2495

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

2497

2496

2498

2497

drop table t1, t2, t3;

2527

2526

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

2528

2527

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

2529

2528

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

2530

--error ER_BAD_FIELD_ERROR

2529

-- error 1054

2531

2530

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

2532

2531

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

2533

2532

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

2588

2587

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

2589

2588

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

2590

2589

# should return all records

2591

--error ER_INVALID_DATE_VALUE # Bad date

2592

2590

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

2593

--error ER_INVALID_DATETIME_VALUE # Bad date

2594

2591

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

2595

2592

# should return 4,5

2596

--error ER_INVALID_DATETIME_VALUE # Bad date

2597

2593

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

2598

--error ER_INVALID_DATETIME_VALUE # Bad date

2599

2594

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

2600

--error ER_INVALID_DATETIME_VALUE # Bad date

2595

# should return 1,2,3

2601

2596

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

2602

--error ER_INVALID_DATETIME_VALUE # Bad date

2603

2597

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

2604

2598

drop table t1;

2605

2599

2688

2682

2689

2683

# Bug#15538 unchecked table absense caused server crash.

2690

2684

2691

--error ER_PARSE_ERROR

2685

--error 1064

2692

2686

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

2693

2687

2694

2688

2719

2713

create table t1 (a int);

2720

2714

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

2721

2715

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

2722

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

2716

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

2723

2717

analyze table t2;

2724

2718

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

2725

2719

3277

3271

KEY (c33, c34, c32));

3278

3272

3279

3273

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

3280

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

3274

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

3281

3275

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

3282

3276

(2, 2, 0, 0),

3283

3277

(3, 3, 1, 0),

3302

3296

# Bug#31800: Date comparison fails with timezone and slashes for greater

3303

3297

# than comparison

3304

3298

3305

3306

# @TODO Commenting all str_to_date out for now...should move to a plugin.

3307

3299

3308

3300

# On DATETIME-like literals with trailing garbage, BETWEEN fudged in a

3309

3301

# DATETIME comparator, while greater/less-than used bin-string comparisons.

3310

3302

# Should correctly be compared as DATE or DATETIME, but throw a warning:

3311

3303

3312

#select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'

3313

# and '2007/10/20 00:00:00 GMT';

3314

#select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';

3315

#select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';

3304

select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'

3305

and '2007/10/20 00:00:00 GMT';

3306

select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';

3307

select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';

3316

3308

3317

3309

# We have all we need -- and trailing garbage:

3318

3310

# (leaving out a leading zero in first example to prove it's a

3319

3311

# value-comparison, not a string-comparison!)

3320

#select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';

3321

#select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';

3322

#select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';

3323

#select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';

3312

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';

3313

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';

3314

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';

3315

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';

3324

3316

# no time at all:

3325

#select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';

3317

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';

3326

3318

# partial time:

3327

#select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3319

select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3328

3320

# fail, different second part:

3329

#select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3321

select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3330

3322

# correct syntax, no trailing nonsense -- this one must throw no warning:

3331

#select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';

3323

select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';

3332

3324

# no warning, but failure (different hour parts):

3333

#select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';

3325

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';

3334

3326

# succeed:

3335

#select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';

3327

select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';

3336

3328

# succeed, but warn for "trailing garbage" (":34"):

3337

#select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';

3329

select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';

3338

3330

# invalid date (Feb 30) succeeds

3339

#select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';

3331

select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';

3340

3332

# 0-day for both, just works in default SQL mode.

3341

#select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';

3333

select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';

3342

3334

# 0-day, succeed

3343

#select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'

3344

# and '2007/10/20 00:00:00';

3345

#select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';

3346

#select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';

3347

#select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';

3348

#select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';

3349

#select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';

3335

select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'

3336

and '2007/10/20 00:00:00';

3337

select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';

3338

select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';

3339

select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';

3340

select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';

3341

select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';

3350

3342

3351

#select str_to_date('1','%Y-%m-%d') = '1';

3352

#select str_to_date('1','%Y-%m-%d') = '1';

3353

#select str_to_date('','%Y-%m-%d') = '';

3343

select str_to_date('1','%Y-%m-%d') = '1';

3344

select str_to_date('1','%Y-%m-%d') = '1';

3345

select str_to_date('','%Y-%m-%d') = '';

3354

3346

3355

3347

# these three should work!

3356

#select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;

3357

#select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';

3358

#select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;

3348

select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;

3349

select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';

3350

select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;

3359

3351

###########################################################################

3360

3352

3361

3353

--echo

3455

3447

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

3456

3448

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

3457

3449

DROP TABLE t1;

3458

3459

3460

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

3461

3462

CREATE TABLE t1 (a INT);

3463

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

3464

CREATE TABLE t2 (b INT);

3465

INSERT INTO t2 VALUES (2);

3466

SELECT * FROM t1 WHERE a = 1 + 1;

3467

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

3468

SELECT * FROM t1 HAVING a = 1 + 1;

3469

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

3470

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

3471

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

3472

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

3473

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

3474

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

3475

3476

DROP TABLE t1, t2;

Older »