~drizzle-trunk/drizzle/development

INSERT INTO t1 VALUES (469713,1,164123,1,164123,1),(317330,164124,317193,101,153170,1),(469434,317194,375620,101,58527,1),(591816,375621,484273,1,108653,1),(591807,484274,534671,91,50488,1),(318885,534672,649362,101,114791,1),(318728,649363,775520,102,126259,1),(336829,775521,813997,101,38577,1),(317740,813998,953227,101,139330,1),(1,813998,953227,101,139330,1);

CREATE TABLE t2 (

id int NOT NULL default '0',

contig_id int NOT NULL default '0',

seq_start int NOT NULL default '0',

seq_end int NOT NULL default '0',

strand int NOT NULL default '0',

id int(10) NOT NULL default '0',

contig_id int(10) NOT NULL default '0',

seq_start int(10) NOT NULL default '0',

seq_end int(10) NOT NULL default '0',

strand int(2) NOT NULL default '0',

KEY id (id)

);

INSERT INTO t2 VALUES (133195,469713,61327,61384,1),(133196,469713,64113,64387,1),(133197,1,1,1,0),(133197,1,1,1,-2);

# Test problem with having and MAX() IS NOT NULL

CREATE TABLE t1 (Fld1 int default NULL,Fld2 int default NULL);

CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL);

INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50);

select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null;

select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null;

# select list and TEXT field

CREATE TEMPORARY TABLE t1 (

`id` bigint NOT NULL default '0',

CREATE TABLE t1 (

`id` bigint(20) NOT NULL default '0',

`description` text

) ENGINE=MyISAM;

CREATE TEMPORARY TABLE t2 (

`id` bigint NOT NULL default '0',

CREATE TABLE t2 (

`id` bigint(20) NOT NULL default '0',

`description` varchar(20)

) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1, 'test');

INSERT INTO t2 VALUES (1, 'test');

CREATE TEMPORARY TABLE t3 (

`id` bigint NOT NULL default '0',

`order_id` bigint NOT NULL default '0'

CREATE TABLE t3 (

`id` bigint(20) NOT NULL default '0',

`order_id` bigint(20) NOT NULL default '0'

100

) ENGINE=MyISAM;

101

102

select

207

select count(*) as count_col1 from t1 group by col1 having col1 = 10;

208

select count(*) as count_col1 from t1 as tmp1 group by col1 having col1 = 10;

209

select count(*) from t1 group by col2 having col2 = 'hello';

210

--error ER_BAD_FIELD_ERROR

210

--error 1054

211

select count(*) from t1 group by col2 having col1 = 10;

212

select col1 as count_col1 from t1 as tmp1 group by col1 having col1 = 10;

213

select col1 as count_col1 from t1 as tmp1 group by col1 having count_col1 = 10;

221

select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having count_col1 = 10;

222

select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col2 = 'hello';

223

select col1 as count_col1,col2 as group_col2 from t1 as tmp1 group by col1,col2 having group_col2 = 'hello';

224

--error ER_PARSE_ERROR

224

--error 1064

225

select sum(col1) as co12 from t1 group by col2 having col2 10;

226

select sum(col1) as co2, count(col2) as cc from t1 group by col1 having col1 =10;

227

--error ER_BAD_FIELD_ERROR

227

--error 1054

228

select t2.col2 from t2 group by t2.col1, t2.col2 having t1.col1 <= 10;

229

230

273

274

# nested queries with HAVING, inner having column resolved in outer FROM clause

275

# the outer having column is not referenced in GROUP BY which results in an error

276

--error ER_BAD_FIELD_ERROR

276

--error 1054

277

select t1.col1 from t1

278

where t1.col2 in

279

(select t2.col2 from t2

302

# correlated subqueries - inner having column 't1.col2' resolves to

303

# the outer FROM clause, which cannot be used because the outer query

304

# is grouped

305

--error ER_BAD_FIELD_ERROR

305

--error 1054

306

select sum(col1) from t1

307

group by col_t1

308

having col_t1 in (select sum(t2.col1) from t2

318

319

# queries with joins and ambiguous column names

320

321

--error ER_NON_UNIQ_ERROR

321

--error 1052

322

select t1.col1, t2.col1 from t1, t2 where t1.col1 = t2.col1

323

group by t1.col1, t2.col1 having col1 = 2;

324

325

--error ER_NON_UNIQ_ERROR

325

--error 1052

326

select t1.col1*10+t2.col1 from t1,t2 where t1.col1=t2.col1

327

group by t1.col1, t2.col1 having col1 = 2;

328

352

353

select count(s1) from t1 group by s1 having s1*0=0;

354

355

--error ER_NON_UNIQ_ERROR

355

-- error 1052

356

select * from t1 a, t1 b group by a.s1 having s1 is null;

357

# ANSI requires: 0 rows

358

# MySQL returns:

364

365

drop table t1;

366

367

create table t1 (s1 char);

367

create table t1 (s1 char character set latin1 collate latin1_german1_ci);

368

insert into t1 values ('b'),('y');

369

370

select s1,count(s1) from t1 group by s1 having s1 = 'y';

370

select s1,count(s1) from t1

371

group by s1 collate latin1_swedish_ci having s1 = 'y';

371

372

# ANSI requires: 1 row, with count(s1) = 2

372

373

# MySQL returns: 1 row, with count(s1) = 1

373

374

Older »