~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 unsigned, c char(10));

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

# REPLACE ... SELECT doesn't yet work with PS

# 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 smallint(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');

141

147

drop table t1,t2;

142

148

143

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

144

181

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

145

182

146

183

155

192

# Create database schema

156

193

create table t1(

157

194

a1 char(3) primary key,

158

a2 int,

195

a2 smallint,

159

196

a3 char(3),

160

197

a4 real,

161

198

a5 date,

199

236

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

200

237

201

238

# Show the table contents

202

--sorted_result

203

239

select * from t1;

204

--sorted_result

205

240

select * from t2;

206

241

207

242

# Queries with min/max functions

363

398

364

399

# Bug #6658 MAX(column) returns incorrect coercibility

365

400

366

create table t1 (a char);

401

create table t1 (a char character set latin2);

367

402

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

368

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

369

405

show create table t1;

370

406

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

371

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

372

407

show create table t2;

373

408

drop table t2;

374

409

create table t2 select concat(a) from t1;

375

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

376

410

show create table t2;

377

411

drop table t2,t1;

378

412

428

462

# Bug #5555 GROUP BY enum_field" returns incorrect results

429

463

430

464

431

CREATE TEMPORARY TABLE t1 (

432

id int NOT NULL auto_increment,

465

CREATE TABLE t1 (

466

id int(10) unsigned NOT NULL auto_increment,

433

467

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

434

468

PRIMARY KEY (id)

435

) ENGINE=MyISAM;

436

437

INSERT INTO t1 VALUES

438

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

439

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

440

486

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

441

487

drop table t1;

442

488

447

493

create table t1(a int, b datetime);

448

494

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

449

495

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

450

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

451

496

show create table t2;

452

497

drop table t1, t2;

453

498

518

563

EXPLAIN SELECT MAX(b) FROM t1;

519

564

DROP TABLE t1;

520

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;

521

574

522

575

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

523

576

527

580

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

528

581

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

529

582

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

530

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

583

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

531

584

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

532

585

GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);

533

586

DROP TABLE t1;

540

593

create table t2 (ff double);

541

594

insert into t2 values (2.2);

542

595

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

543

select sum(distinct ff) from t2;

596

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

544

597

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

545

598

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

546

599

547

600

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

548

601

insert into t1 values(1.1);

549

602

insert into t1 values(2.2);

550

select sum(distinct df) from t1;

551

select min(df) from t1;

603

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

604

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

552

605

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

553

606

select 1e8 * min(df) from t1;

554

607

563

616

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

564

617

565

618

566

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

619

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

567

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

568

621

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

569

622

DROP TABLE t1;

618

671

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

619

672

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

620

673

621

--sorted_result

622

674

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

623

675

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

624

676

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

635

687

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

636

688

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

637

689

638

SELECT

690

SELECT SQL_NO_CACHE

639

691

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

640

692

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

641

693

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

647

699

648

700

649

701

create table t1 select variance(0);

650

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

651

702

show create table t1;

652

703

drop table t1;

653

704

create table t1 select stddev(0);

654

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

655

705

show create table t1;

656

706

drop table t1;

657

707

660

710

# Bug#22555: STDDEV yields positive result for groups with only one row

661

711

662

712

663

create table bug22555 (i int primary key auto_increment, s1 int, s2 int, e decimal(30,10), o double);

713

create table bug22555 (i smallint primary key auto_increment, s1 smallint, s2 smallint, e decimal(30,10), o double);

664

714

insert into bug22555 (s1, s2, e, o) values (53, 78, 11.4276528, 6.828112), (17, 78, 5.916793, 1.8502951), (18, 76, 2.679231, 9.17975591), (31, 62, 6.07831, 0.1), (19, 41, 5.37463, 15.1), (83, 73, 14.567426, 7.959222), (92, 53, 6.10151, 13.1856852), (7, 12, 13.92272, 3.442007), (92, 35, 11.95358909, 6.01376678), (38, 84, 2.572, 7.904571);

665

715

select std(s1/s2) from bug22555 group by i;

666

716

select std(e) from bug22555 group by i;

667

717

select std(o) from bug22555 group by i;

668

718

drop table bug22555;

669

719

670

create table bug22555 (i int, s1 int, s2 int, o1 double, o2 double, e1 decimal, e2 decimal);

720

create table bug22555 (i smallint, s1 smallint, s2 smallint, o1 double, o2 double, e1 decimal, e2 decimal);

671

721

insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);

672

722

select i, count(*) from bug22555 group by i;

673

723

select std(s1/s2) from bug22555 where i=1;

723

773

set @@div_precision_increment=@saved_div_precision_increment;

724

774

drop table bug22555;

725

775

726

create table bug22555 (s int, o double, e decimal);

776

create table bug22555 (s smallint, o double, e decimal);

727

777

insert into bug22555 values (1,1,1),(2,2,2),(3,3,3),(6,6,6),(7,7,7);

728

778

select var_samp(s), var_pop(s) from bug22555;

729

779

select var_samp(o), var_pop(o) from bug22555;

730

780

select var_samp(e), var_pop(e) from bug22555;

731

781

drop table bug22555;

732

782

733

create table bug22555 (s int, o double, e decimal);

783

create table bug22555 (s smallint, o double, e decimal);

734

784

insert into bug22555 values (null,null,null),(null,null,null);

735

785

select var_samp(s) as 'null', var_pop(s) as 'null' from bug22555;

736

786

select var_samp(o) as 'null', var_pop(o) as 'null' from bug22555;

Older »