~drizzle-trunk/drizzle/development

select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;

select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp;

--disable_warnings

select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp;

--enable_warnings

create table t2 (grp int, a bigint, c char(10));

insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;

# Problem with std()

CREATE TABLE t1 (id int,value1 float(10,2));

CREATE TABLE t1 (id int(11),value1 float(10,2));

INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00);

CREATE TABLE t2 (id int,name char(20));

CREATE TABLE t2 (id int(11),name char(20));

INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two');

select id, avg(value1), std(value1), variance(value1) from t1 group by id;

select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id;

# test of count

create table t1 (a int primary key, c char(10), b text);

create table t1 (a int(6) primary key, c char(10), b text);

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

INSERT INTO t1 VALUES (2,'2','2');

INSERT INTO t1 VALUES (4,'4','4');

128

134

129

135

insert into t2 values('AAA', 10, 0.5);

130

136

insert into t2 values('BBB', 20, 1.0);

131

--sorted_result

132

137

select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;

133

138

134

139

select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;

142

147

drop table t1,t2;

143

148

144

149

150

# Test of group function and NULL values

151

152

153

CREATE TABLE t1 (a int, b int);

154

select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;

155

select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;

156

insert into t1 values (1,null);

157

select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;

158

insert into t1 values (1,null);

159

insert into t1 values (2,null);

160

select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;

161

select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;

162

insert into t1 values (2,1);

163

select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;

164

select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;

165

insert into t1 values (3,1);

166

select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;

167

select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a;

168

explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a;

169

drop table t1;

170

171

172

# Bug #1972: test for bit_and(), bit_or() and negative values

173

174

create table t1 (col int);

175

insert into t1 values (-1), (-2), (-3);

176

select bit_and(col), bit_or(col) from t1;

177

select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col;

178

drop table t1;

179

180

145

181

# Bug #3376: avg() and an empty table

146

182

147

183

200

236

insert into t2 values('AAA','AAA','AA','AME');

201

237

202

238

# Show the table contents

203

--sorted_result

204

239

select * from t1;

205

--sorted_result

206

240

select * from t2;

207

241

208

242

# Queries with min/max functions

355

389

#select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;

356

390

#drop table t1;

357

391

392

393

create table t1 (a char(10));

394

insert into t1 values ('a'),('b'),('c');

395

select coercibility(max(a)) from t1;

396

drop table t1;

397

358

398

359

399

# Bug #6658 MAX(column) returns incorrect coercibility

360

400

361

create table t1 (a char);

401

create table t1 (a char character set latin2);

362

402

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

363

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

403

select charset(max(a)), coercibility(max(a)),

404

charset(min(a)), coercibility(min(a)) from t1;

364

405

show create table t1;

365

406

create table t2 select max(a),min(a) from t1;

366

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

367

407

show create table t2;

368

408

drop table t2;

369

409

create table t2 select concat(a) from t1;

370

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

371

410

show create table t2;

372

411

drop table t2,t1;

373

412

423

462

# Bug #5555 GROUP BY enum_field" returns incorrect results

424

463

425

464

426

CREATE TEMPORARY TABLE t1 (

427

id int NOT NULL auto_increment,

465

CREATE TABLE t1 (

466

id int(10) NOT NULL auto_increment,

428

467

val enum('one','two','three') NOT NULL default 'one',

429

468

PRIMARY KEY (id)

430

) ENGINE=MyISAM;

431

432

INSERT INTO t1 VALUES

433

(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');

434

469

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

470

471

INSERT INTO t1 VALUES

472

(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');

473

474

select val, count(*) from t1 group by val;

475

drop table t1;

476

477

CREATE TABLE t1 (

478

id int(10) NOT NULL auto_increment,

479

val set('one','two','three') NOT NULL default 'one',

480

PRIMARY KEY (id)

481

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

482

483

INSERT INTO t1 VALUES

484

(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');

485

435

486

select val, count(*) from t1 group by val;

436

487

drop table t1;

437

488

442

493

create table t1(a int, b datetime);

443

494

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

444

495

create table t2 select MAX(b) from t1 group by a;

445

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

446

496

show create table t2;

447

497

drop table t1, t2;

448

498

513

563

EXPLAIN SELECT MAX(b) FROM t1;

514

564

DROP TABLE t1;

515

565

566

CREATE TABLE t1 (id int , b varchar(512), INDEX(b(250))) COLLATE latin1_bin;

567

INSERT INTO t1 VALUES

568

(1,CONCAT(REPEAT('_', 250), "qq")), (1,CONCAT(REPEAT('_', 250), "zz")),

569

(1,CONCAT(REPEAT('_', 250), "aa")), (1,CONCAT(REPEAT('_', 250), "ff"));

570

571

SELECT MAX(b) FROM t1;

572

EXPLAIN SELECT MAX(b) FROM t1;

573

DROP TABLE t1;

516

574

517

575

# Bug #16792 query with subselect, join, and group not returning proper values

518

576

522

580

SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;

523

581

SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;

524

582

# an attempt to test all aggregate function with no table.

525

SELECT AVG(2), COUNT(*), COUNT(12),

583

SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12),

526

584

COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),

527

585

GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);

528

586

DROP TABLE t1;

535

593

create table t2 (ff double);

536

594

insert into t2 values (2.2);

537

595

select cast(sum(distinct ff) as decimal(5,2)) from t2;

538

select sum(distinct ff) from t2;

596

select cast(sum(distinct ff) as signed) from t2;

539

597

select cast(variance(ff) as decimal(10,3)) from t2;

540

598

select cast(min(ff) as decimal(5,2)) from t2;

541

599

542

600

create table t1 (df decimal(5,1));

543

601

insert into t1 values(1.1);

544

602

insert into t1 values(2.2);

545

select sum(distinct df) from t1;

546

select min(df) from t1;

603

select cast(sum(distinct df) as signed) from t1;

604

select cast(min(df) as signed) from t1;

547

605

select 1e8 * sum(distinct df) from t1;

548

606

select 1e8 * min(df) from t1;

549

607

558

616

# BUG#3190, WL#1639: Standard Deviation STDDEV - 2 different calculations

559

617

560

618

561

CREATE TABLE t1 (id int,value1 float(10,2));

619

CREATE TABLE t1 (id int(11),value1 float(10,2));

562

620

INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00), (2,13.00);

563

621

select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;

564

622

DROP TABLE t1;

613

671

(3,2,1), (3,2,2), (3,2,3),

614

672

(3,3,1), (3,3,2), (3,3,3);

615

673

616

--sorted_result

617

674

SELECT b/c as v, a FROM t1 ORDER BY v;

618

675

SELECT b/c as v, SUM(a) FROM t1 GROUP BY v;

619

676

SELECT SUM(a) FROM t1 GROUP BY b/c;

630

687

CREATE TABLE t2 (a INT PRIMARY KEY, b INT);

631

688

INSERT INTO t2 VALUES (1,1), (3,3);

632

689

633

--sorted_result

634

SELECT

690

SELECT SQL_NO_CACHE

635

691

(SELECT SUM(c.a) FROM t1 ttt, t2 ccc

636

692

WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid

637

693

FROM t1 t, t2 c WHERE t.a = c.b;

643

699

644

700

645

701

create table t1 select variance(0);

646

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

647

702

show create table t1;

648

703

drop table t1;

649

704

create table t1 select stddev(0);

650

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

651

705

show create table t1;

652

706

drop table t1;

653

707

Older »