~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

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

1796

--disable_ps_protocol

1799

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

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;

1802

1801

DROP TABLE t1,t2;

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

2593

2590

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

2594

--error ER_INVALID_DATETIME_VALUE # 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

2598

2593

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

2599

--error ER_INVALID_DATETIME_VALUE # Bad date

2600

2594

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

2601

--error ER_INVALID_DATETIME_VALUE # Bad date

2595

# should return 1,2,3

2602

2596

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

2603

--error ER_INVALID_DATETIME_VALUE # Bad date

2604

2597

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

2605

2598

drop table t1;

2606

2599

2689

2682

2690

2683

# Bug#15538 unchecked table absense caused server crash.

2691

2684

2692

--error ER_PARSE_ERROR

2685

--error 1064

2693

2686

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

2694

2687

2695

2688

3303

3296

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

3304

3297

# than comparison

3305

3298

3306

3307

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

3308

3299

3309

3300

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

3310

3301

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

3311

3302

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

3312

3303

3313

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

3314

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

3315

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

3316

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

3317

3308

3318

3309

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

3319

3310

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

3320

3311

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

3321

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

3322

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

3323

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

3324

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

3325

3316

# no time at all:

3326

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

3327

3318

# partial time:

3328

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

3329

3320

# fail, different second part:

3330

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

3331

3322

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

3332

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

3333

3324

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

3334

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

3335

3326

# succeed:

3336

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

3337

3328

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

3338

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

3339

3330

# invalid date (Feb 30) succeeds

3340

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

3341

3332

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

3342

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

3343

3334

# 0-day, succeed

3344

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

3345

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

3346

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

3347

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

3348

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

3349

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

3350

#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') = '';

3351

3342

3352

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

3353

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

3354

#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') = '';

3355

3346

3356

3347

# these three should work!

3357

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

3358

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

3359

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

3360

3351

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

3361

3352

3362

3353

--echo

3456

3447

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

3457

3448

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

3458

3449

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 »