~drizzle-trunk/drizzle/development

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

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;

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

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;

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

3473

3471

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

3474

3472

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

3475

3473

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

3476

3477

DROP TABLE t1, t2;

Older »