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

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)

21 6 3.50000 1.70783 2.91667 7 0 1 6 E

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;

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)

NULL NULL 0 NULL NULL NULL 0 18446744073709551615 NULL NULL

1 1 1 1.00000 0.00000 0.00000 1 1 1 1 a a

2 5 2 2.50000 0.50000 0.25000 3 2 2 3 b c

3 15 3 5.00000 0.81650 0.66667 7 4 4 6 C E

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;

grp sum

NULL NULL

1 7

2 20.25

3 45.48316324759439

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 into t2 select grp, a, c from t1 limit 2,1;

select * from t2;

3 9 E

2 3 c

drop table t1,t2;

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;

id avg(value1) std(value1) variance(value1)

101

116

2 NULL

102

117

3 2

103

118

drop table t1,t2;

104

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

119

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

105

120

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

106

121

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

107

122

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

231

246

max(t1.a2) max(t2.a1)

232

247

zzz BBB

233

248

drop table t1,t2;

249

CREATE TABLE t1 (a int, b int);

250

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

251

count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)

252

0 NULL NULL NULL NULL NULL 18446744073709551615 0

253

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;

254

a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)

255

insert into t1 values (1,null);

256

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;

257

a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)

258

1 0 NULL NULL NULL NULL NULL 18446744073709551615 0

259

insert into t1 values (1,null);

260

insert into t1 values (2,null);

261

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;

262

a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)

263

1 0 NULL NULL NULL NULL NULL 18446744073709551615 0

264

2 0 NULL NULL NULL NULL NULL 18446744073709551615 0

265

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;

266

a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)

267

1 0 NULL NULL NULL NULL NULL 18446744073709551615 0

268

2 0 NULL NULL NULL NULL NULL 18446744073709551615 0

269

insert into t1 values (2,1);

270

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;

271

a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)

272

1 0 NULL NULL NULL NULL NULL 18446744073709551615 0

273

2 1 1 1.00000 0.00000 1 1 1 1

274

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;

275

a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)

276

1 0 NULL NULL NULL NULL NULL 18446744073709551615 0

277

2 1 1 1.00000 0.00000 1 1 1 1

278

insert into t1 values (3,1);

279

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;

280

a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)

281

1 0 NULL NULL NULL NULL NULL 18446744073709551615 0

282

2 1 1 1.00000 0.00000 1 1 1 1

283

3 1 1 1.00000 0.00000 1 1 1 1

284

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;

285

a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) bit_xor(b)

286

1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 0

287

2 1 1 1.00000 0.00000 1 1 1 1 1

288

3 1 1 1.00000 0.00000 1 1 1 1 1

289

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;

290

id select_type table type possible_keys key key_len ref rows filtered Extra

291

1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using filesort

292

Warnings:

293

Note 1003 select sql_big_result "test"."t1"."a" AS "a",count("test"."t1"."b") AS "count(b)",sum("test"."t1"."b") AS "sum(b)",avg("test"."t1"."b") AS "avg(b)",std("test"."t1"."b") AS "std(b)",min("test"."t1"."b") AS "min(b)",max("test"."t1"."b") AS "max(b)",bit_and("test"."t1"."b") AS "bit_and(b)",bit_or("test"."t1"."b") AS "bit_or(b)",bit_xor("test"."t1"."b") AS "bit_xor(b)" from "test"."t1" group by "test"."t1"."a"

294

drop table t1;

295

create table t1 (col int);

296

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

297

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

298

bit_and(col) bit_or(col)

299

18446744073709551612 18446744073709551615

300

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

301

bit_and(col) bit_or(col)

302

18446744073709551613 18446744073709551613

303

18446744073709551614 18446744073709551614

304

18446744073709551615 18446744073709551615

305

drop table t1;

234

306

create table t1 (a int);

235

307

select avg(2) from t1;

236

308

avg(2)

238

310

drop table t1;

239

311

create table t1(

240

312

a1 char(3) primary key,

241

a2 int,

313

a2 smallint,

242

314

a3 char(3),

243

315

a4 real,

244

316

a5 date,

279

351

select * from t1;

280

352

a1 a2 a3 a4 a5

281

353

AME 0 SEA 0.1 1942-02-19

354

HBR 1 SEA 0.085 1948-03-05

355

BOT 2 SEA 0.085 1951-11-29

356

BMC 3 SEA 0.085 1958-09-08

357

TWU 0 LAX 0.08 1969-10-05

282

358

BDL 0 DEN 0.08 1960-11-27

283

BMC 3 SEA 0.085 1958-09-08

284

BOT 2 SEA 0.085 1951-11-29

285

359

DTX 1 NYC 0.08 1961-05-04

360

PLS 1 WDC 0.075 1949-01-02

361

ZAJ 2 CHI 0.075 1960-06-15

362

VVV 2 MIN 0.075 1959-06-28

286

363

GTM 3 DAL 0.07 1977-09-23

287

HBR 1 SEA 0.085 1948-03-05

364

SSJ NULL CHI NULL 1974-03-19

288

365

KKK 3 ATL NULL NULL

289

PLS 1 WDC 0.075 1949-01-02

290

SSJ NULL CHI NULL 1974-03-19

291

TWU 0 LAX 0.08 1969-10-05

292

VVV 2 MIN 0.075 1959-06-28

366

XXX NULL MIN NULL NULL

293

367

WWW 1 LED NULL NULL

294

XXX NULL MIN NULL NULL

295

ZAJ 2 CHI 0.075 1960-06-15

296

368

select * from t2;

297

369

a1 a2 a3 a4

298

AAA AAA AA AME

370

TKF Seattle WA AME

371

LCC Los Angeles CA TWU

299

372

DEN Denver CO BDL

373

SDC San Diego CA TWU

374

NOL New Orleans LA GTM

300

375

LAK Los Angeles CA TWU

301

LCC Los Angeles CA TWU

302

NOL New Orleans LA GTM

303

SDC San Diego CA TWU

304

TKF Seattle WA AME

376

AAA AAA AA AME

305

377

explain

306

378

select min(a1) from t1;

307

379

id select_type table type possible_keys key key_len ref rows Extra

529

601

explain

530

602

select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX';

531

603

id select_type table type possible_keys key key_len ref rows Extra

532

1 SIMPLE t1 range PRIMARY PRIMARY 0 NULL 7 Using where; Using index

604

1 SIMPLE t1 index PRIMARY PRIMARY 3 NULL 15 Using where; Using index

533

605

explain

534

606

select min(a1) from t1 where a1 != 'KKK';

535

607

id select_type table type possible_keys key key_len ref rows Extra

536

1 SIMPLE t1 index PRIMARY PRIMARY 14 NULL 15 Using where; Using index

608

1 SIMPLE t1 index PRIMARY PRIMARY 3 NULL 15 Using where; Using index

537

609

explain

538

610

select max(a3) from t1 where a2 < 2 and a3 < 'SEA';

539

611

id select_type table type possible_keys key key_len ref rows Extra

540

1 SIMPLE t1 range k1 k1 5 NULL 6 Using where; Using index

612

1 SIMPLE t1 range k1 k1 3 NULL 6 Using where; Using index

541

613

explain

542

614

select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA';

543

615

id select_type table type possible_keys key key_len ref rows Extra

544

1 SIMPLE t1 range k1 k1 20 NULL 1 Using where; Using index

545

1 SIMPLE t2 range k1 k1 11 NULL 3 Using where; Using index; Using join buffer

616

1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index

617

1 SIMPLE t2 range k1 k1 3 NULL 4 Using where; Using index; Using join buffer

546

618

explain

547

619

select min(a4 - 0.01) from t1;

548

620

id select_type table type possible_keys key key_len ref rows Extra

549

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15

621

1 SIMPLE t1 index NULL k2 12 NULL 15 Using index

550

622

explain

551

623

select max(a4 + 0.01) from t1;

552

624

id select_type table type possible_keys key key_len ref rows Extra

553

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15

625

1 SIMPLE t1 index NULL k2 12 NULL 15 Using index

554

626

explain

555

627

select min(a3) from t1 where (a2 +1 ) is null;

556

628

id select_type table type possible_keys key key_len ref rows Extra

557

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

629

1 SIMPLE t1 index NULL k1 7 NULL 15 Using where; Using index

558

630

explain

559

631

select min(a3) from t1 where (a2 + 1) = 2;

560

632

id select_type table type possible_keys key key_len ref rows Extra

561

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

633

1 SIMPLE t1 index NULL k1 7 NULL 15 Using where; Using index

562

634

explain

563

635

select min(a3) from t1 where 2 = (a2 + 1);

564

636

id select_type table type possible_keys key key_len ref rows Extra

565

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

637

1 SIMPLE t1 index NULL k1 7 NULL 15 Using where; Using index

566

638

explain

567

639

select min(a2) from t1 where a2 < 2 * a2 - 8;

568

640

id select_type table type possible_keys key key_len ref rows Extra

569

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

641

1 SIMPLE t1 index NULL k1 7 NULL 15 Using where; Using index

570

642

explain

571

643

select min(a1) from t1 where a1 between a3 and 'KKK';

572

644

id select_type table type possible_keys key key_len ref rows Extra

573

1 SIMPLE t1 index PRIMARY PRIMARY 14 NULL 15 Using where

645

1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 15 Using where

574

646

explain

575

647

select min(a4) from t1 where (a4 + 0.01) between 0.07 and 0.08;

576

648

id select_type table type possible_keys key key_len ref rows Extra

577

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

649

1 SIMPLE t1 index NULL k2 12 NULL 15 Using where; Using index

578

650

explain

579

651

select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME';

580

652

id select_type table type possible_keys key key_len ref rows Extra

581

1 SIMPLE t2 index k2 PRIMARY 14 NULL 7 Using where

582

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using index; Using join buffer

653

1 SIMPLE t2 range k2 k2 4 NULL 6 Using where; Using index

654

1 SIMPLE t1 index NULL PRIMARY 3 NULL 15 Using index; Using join buffer

583

655

drop table t1, t2;

584

656

create table t1 (a char(10));

585

657

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

587

659

coercibility(max(a))

588

660

589

661

drop table t1;

590

create table t1 (a char);

662

create table t1 (a char character set latin2);

591

663

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

664

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

665

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

666

charset(max(a)) coercibility(max(a)) charset(min(a)) coercibility(min(a))

667

latin2 2 latin2 2

592

668

show create table t1;

593

669

Table Create Table

594

t1 CREATE TABLE `t1` (

595

`a` varchar(1)

596

) ENGINE=InnoDB

670

t1 CREATE TABLE "t1" (

671

"a" char(1) CHARACTER SET latin2

672

) ENGINE=MyISAM DEFAULT CHARSET=latin1

597

673

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

598

674

show create table t2;

599

675

Table Create Table

600

t2 CREATE TABLE `t2` (

601

`max(a)` varchar(1),

602

`min(a)` varchar(1)

603

) ENGINE=InnoDB

676

t2 CREATE TABLE "t2" (

677

"max(a)" char(1) CHARACTER SET latin2,

678

"min(a)" char(1) CHARACTER SET latin2

679

) ENGINE=MyISAM DEFAULT CHARSET=latin1

604

680

drop table t2;

605

681

create table t2 select concat(a) from t1;

606

682

show create table t2;

607

683

Table Create Table

608

t2 CREATE TABLE `t2` (

609

`concat(a)` varchar(1)

610

) ENGINE=InnoDB

684

t2 CREATE TABLE "t2" (

685

"concat(a)" varchar(1) CHARACTER SET latin2

686

) ENGINE=MyISAM DEFAULT CHARSET=latin1

611

687

drop table t2,t1;

612

688

create table t1 (a int);

613

689

insert into t1 values (1);

660

736

NULL

661

737

DROP TABLE t1;

662

738

CREATE TABLE t1 (

663

id int NOT NULL auto_increment,

739

id int(10) unsigned NOT NULL auto_increment,

664

740

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

665

741

PRIMARY KEY (id)

666

) ENGINE=MyISAM;

742

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

743

INSERT INTO t1 VALUES

744

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

745

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

746

val count(*)

747

one 2

748

two 2

749

three 1

750

drop table t1;

751

CREATE TABLE t1 (

752

id int(10) unsigned NOT NULL auto_increment,

753

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

754

PRIMARY KEY (id)

755

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

667

756

INSERT INTO t1 VALUES

668

757

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

669

758

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

677

766

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

678

767

show create table t2;

679

768

Table Create Table

680

t2 CREATE TABLE `t2` (

681

`MAX(b)` datetime

682

) ENGINE=InnoDB

769

t2 CREATE TABLE "t2" (

770

"MAX(b)" datetime

771

) ENGINE=MyISAM DEFAULT CHARSET=latin1

683

772

drop table t1, t2;

684

773

create table t1(f1 datetime);

685

774

insert into t1 values (now());

691

780

create table t2 select f2 from (select now() f2 from t1) a;

692

781

show columns from t2;

693

782

Field Type Null Key Default Extra

694

f2 datetime YES NULL

783

f2 datetime NO NULL

695

784

drop table t2, t1;

696

785

CREATE TABLE t1(

697

786

id int PRIMARY KEY,

743

832

id select_type table type possible_keys key key_len ref rows Extra

744

833

1 SIMPLE t1 ALL NULL NULL NULL NULL 2

745

834

DROP TABLE t1;

835

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

836

INSERT INTO t1 VALUES

837

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

838

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

839

SELECT MAX(b) FROM t1;

840

MAX(b)

841

__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________zz

842

EXPLAIN SELECT MAX(b) FROM t1;

843

id select_type table type possible_keys key key_len ref rows Extra

844

1 SIMPLE t1 ALL NULL NULL NULL NULL 4

845

DROP TABLE t1;

746

846

CREATE TABLE t1 (a INT, b INT);

747

847

INSERT INTO t1 VALUES (1,1),(1,2),(2,3);

748

848

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

753

853

(SELECT COUNT(DISTINCT 12))

754

854

755

855

756

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

856

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

757

857

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

758

858

GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);

759

AVG(2) COUNT(*) COUNT(12) COUNT(DISTINCT 12) MIN(2) MAX(2) STD(2) VARIANCE(2) SUM(2) GROUP_CONCAT(2) GROUP_CONCAT(DISTINCT 2)

760

2.00000 1 1 1 2 2 0.00000 0.00000 2 2 2

859

AVG(2) BIT_AND(2) BIT_OR(2) BIT_XOR(2) COUNT(*) COUNT(12) COUNT(DISTINCT 12) MIN(2) MAX(2) STD(2) VARIANCE(2) SUM(2) GROUP_CONCAT(2) GROUP_CONCAT(DISTINCT 2)

860

2.00000 2 2 2 1 1 1 2 2 0.00000 0.00000 2 2 2

761

861

DROP TABLE t1;

762

862

create table t2 (ff double);

763

863

insert into t2 values (2.2);

764

864

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

765

865

cast(sum(distinct ff) as decimal(5,2))

766

866

2.20

767

select sum(distinct ff) from t2;

768

sum(distinct ff)

769

2.2

867

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

868

cast(sum(distinct ff) as signed)

869

770

870

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

771

871

cast(variance(ff) as decimal(10,3))

772

872

0.000

776

876

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

777

877

insert into t1 values(1.1);

778

878

insert into t1 values(2.2);

779

select sum(distinct df) from t1;

780

sum(distinct df)

781

3.3

782

select min(df) from t1;

783

min(df)

784

1.1

879

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

880

cast(sum(distinct df) as signed)

881

882

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

883

cast(min(df) as signed)

884

785

885

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

786

886

1e8 * sum(distinct df)

787

887

330000000

794

894

cast(min(ifl) as decimal(5,2))

795

895

1.00

796

896

drop table t1, t2, t3;

797

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

897

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

798

898

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

799

899

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

800

900

id stddev_pop(value1) var_pop(value1) stddev_samp(value1) var_samp(value1)

895

995

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

896

996

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

897

997

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

898

SELECT

998

SELECT SQL_NO_CACHE

899

999

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

900

1000

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

901

1001

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

902

1002

minid

903

NULL

1003

904

1004

DROP TABLE t1,t2;

905

1005

create table t1 select variance(0);

906

1006

show create table t1;

907

1007

Table Create Table

908

t1 CREATE TABLE `t1` (

909

`variance(0)` double(8,4)

910

) ENGINE=InnoDB

1008

t1 CREATE TABLE "t1" (

1009

"variance(0)" double(8,4)

1010

) ENGINE=MyISAM DEFAULT CHARSET=latin1

911

1011

drop table t1;

912

1012

create table t1 select stddev(0);

913

1013

show create table t1;

914

1014

Table Create Table

915

t1 CREATE TABLE `t1` (

916

`stddev(0)` double(8,4)

917

) ENGINE=InnoDB

1015

t1 CREATE TABLE "t1" (

1016

"stddev(0)" double(8,4)

1017

) ENGINE=MyISAM DEFAULT CHARSET=latin1

918

1018

drop table t1;

919

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

1019

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

920

1020

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

921

1021

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

922

1022

std(s1/s2)

955

1055

956

1056

957

1057

drop table bug22555;

958

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

1058

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

959

1059

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

960

1060

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

961

1061

i count(*)

1139

1239

0.21325763586649340

1140

1240

set @@div_precision_increment=@saved_div_precision_increment;

1141

1241

drop table bug22555;

1142

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

1242

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

1143

1243

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

1144

1244

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

1145

1245

var_samp(s) var_pop(s)

1151

1251

var_samp(e) var_pop(e)

1152

1252

6.7000 5.3600

1153

1253

drop table bug22555;

1154

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

1254

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

1155

1255

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

1156

1256

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

1157

1257

null null

1229

1329

INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );

1230

1330

EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;

1231

1331

id select_type table type possible_keys key key_len ref rows Extra

1232

1 SIMPLE t2 ref a a 5 const 1

1332

1 SIMPLE t2 ref a a 5 const 2

1233

1333

SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;

1234

1334

MIN(b) MIN(c)

1235

1335

3 2

Older »