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

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;

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

1797

--error ER_INVALID_DATETIME_VALUE # bad datetime

1797

--error 1686 # bad datetime

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;

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;

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

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

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;

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

2590

--error 1686 # Bad date

2592

2591

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

2593

--error ER_INVALID_DATETIME_VALUE # Bad date

2592

--error 1686 # Bad date

2594

2593

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

2595

2594

# should return 4,5

2596

--error ER_INVALID_DATETIME_VALUE # Bad date

2595

--error 1686 # Bad date

2597

2596

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

2598

--error ER_INVALID_DATETIME_VALUE # Bad date

2597

--error 1686 # Bad date

2599

2598

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

2600

--error ER_INVALID_DATETIME_VALUE # Bad date

2599

--error 1686 # Bad date

2601

2600

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

2602

--error ER_INVALID_DATETIME_VALUE # Bad date

2601

--error 1686 # Bad date

2603

2602

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

2604

2603

drop table t1;

2605

2604

2688

2687

2689

2688

# Bug#15538 unchecked table absense caused server crash.

2690

2689

2691

--error ER_PARSE_ERROR

2690

--error 1064

2692

2691

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

2693

2692

2694

2693

2719

2718

create table t1 (a int);

2720

2719

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

2721

2720

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;

2721

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

2723

2722

analyze table t2;

2724

2723

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

2725

2724

3277

3276

KEY (c33, c34, c32));

3278

3277

3279

3278

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

3280

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

3279

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

3281

3280

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

3282

3281

(2, 2, 0, 0),

3283

3282

(3, 3, 1, 0),

Older »