~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(11),value1 float(10,2));

CREATE TABLE t1 (id int,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(11),name char(20));

CREATE TABLE t2 (id int,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(6) primary key, c char(10), b text);

create table t1 (a int 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');

147

141

drop table t1,t2;

148

142

149

143

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

181

144

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

182

145

183

146

236

199

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

237

200

238

201

# Show the table contents

202

--sorted_result

239

203

select * from t1;

204

--sorted_result

240

205

select * from t2;

241

206

242

207

# Queries with min/max functions

398

363

399

364

# Bug #6658 MAX(column) returns incorrect coercibility

400

365

401

create table t1 (a char character set latin2);

366

create table t1 (a char);

402

367

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

403

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

404

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

368

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

405

369

show create table t1;

406

370

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

371

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

407

372

show create table t2;

408

373

drop table t2;

409

374

create table t2 select concat(a) from t1;

375

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

410

376

show create table t2;

411

377

drop table t2,t1;

412

378

462

428

# Bug #5555 GROUP BY enum_field" returns incorrect results

463

429

464

430

465

CREATE TABLE t1 (

466

id int(10) NOT NULL auto_increment,

431

CREATE TEMPORARY TABLE t1 (

432

id int NOT NULL auto_increment,

467

433

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

468

434

PRIMARY KEY (id)

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

) ENGINE=MyISAM;

436

437

INSERT INTO t1 VALUES

438

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

439

486

440

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

487

441

drop table t1;

488

442

493

447

create table t1(a int, b datetime);

494

448

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

495

449

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

450

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

496

451

show create table t2;

497

452

drop table t1, t2;

498

453

563

518

EXPLAIN SELECT MAX(b) FROM t1;

564

519

DROP TABLE t1;

565

520

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;

574

521

575

522

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

576

523

580

527

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

581

528

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

582

529

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

583

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

530

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

584

531

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

585

532

GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);

586

533

DROP TABLE t1;

593

540

create table t2 (ff double);

594

541

insert into t2 values (2.2);

595

542

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

596

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

543

select sum(distinct ff) from t2;

597

544

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

598

545

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

599

546

600

547

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

601

548

insert into t1 values(1.1);

602

549

insert into t1 values(2.2);

603

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

604

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

550

select sum(distinct df) from t1;

551

select min(df) from t1;

605

552

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

606

553

select 1e8 * min(df) from t1;

607

554

616

563

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

617

564

618

565

619

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

566

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

620

567

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

621

568

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

622

569

DROP TABLE t1;

671

618

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

672

619

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

673

620

621

--sorted_result

674

622

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

675

623

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

676

624

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

687

635

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

688

636

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

689

637

690

SELECT SQL_NO_CACHE

638

SELECT

691

639

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

692

640

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

693

641

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

699

647

700

648

701

649

create table t1 select variance(0);

650

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

702

651

show create table t1;

703

652

drop table t1;

704

653

create table t1 select stddev(0);

654

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

705

655

show create table t1;

706

656

drop table t1;

707

657

Older »