~drizzle-trunk/drizzle/development : revision 511.1.3

4

show variables like 'div_precision_increment';

5

Variable_name Value

6

div_precision_increment 5

7

create table t1 (grp int, a bigint unsigned, c char(10) not null);

7

create table t1 (grp int, a bigint, c char(10) not null);

8

insert into t1 values (1,1,"a");

9

insert into t1 values (2,2,"b");

10

insert into t1 values (2,3,"c");

47

select count(distinct a),count(distinct grp) from t1;

48

count(distinct a) count(distinct grp)

49

6 3

50

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;

51

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)

52

21 6 3.50000 1.70783 2.91667 7 0 1 6 E

53

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;

54

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)

55

NULL NULL 0 NULL NULL NULL 0 18446744073709551615 NULL NULL

56

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

57

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

58

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

59

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;

60

grp sum

61

NULL NULL

62

1 7

63

2 20.25

64

3 45.48316324759439

65

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

50

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

66

51

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

67

52

replace into t2 select grp, a, c from t1 limit 2,1;

68

53

select * from t2;

73

58

3 9 E

74

59

2 3 c

75

60

drop table t1,t2;

76

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

61

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

77

62

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

78

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

63

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

79

64

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

80

65

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

81

66

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

116

101

2 NULL

117

102

3 2

118

103

drop table t1,t2;

119

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

104

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

120

105

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

121

106

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

122

107

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

246

231

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

247

232

zzz BBB

248

233

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;

306

234

create table t1 (a int);

307

235

select avg(2) from t1;

308

236

avg(2)

310

238

drop table t1;

311

239

create table t1(

312

240

a1 char(3) primary key,

313

a2 smallint,

241

a2 int,

314

242

a3 char(3),

315

243

a4 real,

316

244

a5 date,

351

279

select * from t1;

352

280

a1 a2 a3 a4 a5

353

281

AME 0 SEA 0.1 1942-02-19

354

HBR 1 SEA 0.085 1948-03-05

282

BDL 0 DEN 0.08 1960-11-27

283

BMC 3 SEA 0.085 1958-09-08

355

284

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

358

BDL 0 DEN 0.08 1960-11-27

359

285

DTX 1 NYC 0.08 1961-05-04

286

GTM 3 DAL 0.07 1977-09-23

287

HBR 1 SEA 0.085 1948-03-05

288

KKK 3 ATL NULL NULL

360

289

PLS 1 WDC 0.075 1949-01-02

361

ZAJ 2 CHI 0.075 1960-06-15

290

SSJ NULL CHI NULL 1974-03-19

291

TWU 0 LAX 0.08 1969-10-05

362

292

VVV 2 MIN 0.075 1959-06-28

363

GTM 3 DAL 0.07 1977-09-23

364

SSJ NULL CHI NULL 1974-03-19

365

KKK 3 ATL NULL NULL

293

WWW 1 LED NULL NULL

366

294

XXX NULL MIN NULL NULL

367

WWW 1 LED NULL NULL

295

ZAJ 2 CHI 0.075 1960-06-15

368

296

select * from t2;

369

297

a1 a2 a3 a4

370

TKF Seattle WA AME

298

AAA AAA AA AME

299

DEN Denver CO BDL

300

LAK Los Angeles CA TWU

371

301

LCC Los Angeles CA TWU

372

DEN Denver CO BDL

302

NOL New Orleans LA GTM

373

303

SDC San Diego CA TWU

374

NOL New Orleans LA GTM

375

LAK Los Angeles CA TWU

376

AAA AAA AA AME

304

TKF Seattle WA AME

377

305

explain

378

306

select min(a1) from t1;

379

307

id select_type table type possible_keys key key_len ref rows Extra

601

529

explain

602

530

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

603

531

id select_type table type possible_keys key key_len ref rows Extra

604

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

532

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

605

533

explain

606

534

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

607

535

id select_type table type possible_keys key key_len ref rows Extra

608

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

536

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

609

537

explain

610

538

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

611

539

id select_type table type possible_keys key key_len ref rows Extra

612

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

540

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

613

541

explain

614

542

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

615

543

id select_type table type possible_keys key key_len ref rows Extra

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

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

618

546

explain

619

547

select min(a4 - 0.01) from t1;

620

548

id select_type table type possible_keys key key_len ref rows Extra

621

1 SIMPLE t1 index NULL k2 12 NULL 15 Using index

549

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15

622

550

explain

623

551

select max(a4 + 0.01) from t1;

624

552

id select_type table type possible_keys key key_len ref rows Extra

625

1 SIMPLE t1 index NULL k2 12 NULL 15 Using index

553

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15

626

554

explain

627

555

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

628

556

id select_type table type possible_keys key key_len ref rows Extra

629

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

557

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

630

558

explain

631

559

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

632

560

id select_type table type possible_keys key key_len ref rows Extra

633

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

561

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

634

562

explain

635

563

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

636

564

id select_type table type possible_keys key key_len ref rows Extra

637

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

565

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

638

566

explain

639

567

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

640

568

id select_type table type possible_keys key key_len ref rows Extra

641

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

569

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

642

570

explain

643

571

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

644

572

id select_type table type possible_keys key key_len ref rows Extra

645

1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 15 Using where

573

1 SIMPLE t1 index PRIMARY PRIMARY 14 NULL 15 Using where

646

574

explain

647

575

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

648

576

id select_type table type possible_keys key key_len ref rows Extra

649

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

577

1 SIMPLE t1 index NULL PRIMARY 14 NULL 15 Using where

650

578

explain

651

579

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

652

580

id select_type table type possible_keys key key_len ref rows Extra

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

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

655

583

drop table t1, t2;

656

584

create table t1 (a char(10));

657

585

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

659

587

coercibility(max(a))

660

588

2

661

589

drop table t1;

662

create table t1 (a char character set latin2);

590

create table t1 (a char);

663

591

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

668

592

show create table t1;

669

593

Table Create Table

670

t1 CREATE TABLE "t1" (

671

"a" char(1) CHARACTER SET latin2

672

) ENGINE=MyISAM DEFAULT CHARSET=latin1

594

t1 CREATE TABLE `t1` (

595

`a` varchar(1)

596

) ENGINE=InnoDB

673

597

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

674

598

show create table t2;

675

599

Table Create Table

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

600

t2 CREATE TABLE `t2` (

601

`max(a)` varchar(1),

602

`min(a)` varchar(1)

603

) ENGINE=InnoDB

680

604

drop table t2;

681

605

create table t2 select concat(a) from t1;

682

606

show create table t2;

683

607

Table Create Table

684

t2 CREATE TABLE "t2" (

685

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

686

) ENGINE=MyISAM DEFAULT CHARSET=latin1

608

t2 CREATE TABLE `t2` (

609

`concat(a)` varchar(1)

610

) ENGINE=InnoDB

687

611

drop table t2,t1;

688

612

create table t1 (a int);

689

613

insert into t1 values (1);

736

660

NULL

737

661

DROP TABLE t1;

738

662

CREATE TABLE t1 (

739

id int(10) unsigned NOT NULL auto_increment,

663

id int NOT NULL auto_increment,

740

664

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

741

665

PRIMARY KEY (id)

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;

666

) ENGINE=MyISAM;

756

667

INSERT INTO t1 VALUES

757

668

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

758

669

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

766

677

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

767

678

show create table t2;

768

679

Table Create Table

769

t2 CREATE TABLE "t2" (

770

"MAX(b)" datetime

771

) ENGINE=MyISAM DEFAULT CHARSET=latin1

680

t2 CREATE TABLE `t2` (

681

`MAX(b)` datetime

682

) ENGINE=InnoDB

772

683

drop table t1, t2;

773

684

create table t1(f1 datetime);

774

685

insert into t1 values (now());

780

691

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

781

692

show columns from t2;

782

693

Field Type Null Key Default Extra

783

f2 datetime NO NULL

694

f2 datetime YES NULL

784

695

drop table t2, t1;

785

696

CREATE TABLE t1(

786

697

id int PRIMARY KEY,

832

743

id select_type table type possible_keys key key_len ref rows Extra

833

744

1 SIMPLE t1 ALL NULL NULL NULL NULL 2

834

745

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;

846

746

CREATE TABLE t1 (a INT, b INT);

847

747

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

848

748

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

853

753

(SELECT COUNT(DISTINCT 12))

854

754

1

855

755

1

856

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

756

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

857

757

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

858

758

GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 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

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

861

761

DROP TABLE t1;

862

762

create table t2 (ff double);

863

763

insert into t2 values (2.2);

864

764

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

865

765

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

866

766

2.20

867

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

868

cast(sum(distinct ff) as signed)

869

2

767

select sum(distinct ff) from t2;

768

sum(distinct ff)

769

2.2

870

770

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

871

771

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

872

772

0.000

876

776

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

877

777

insert into t1 values(1.1);

878

778

insert into t1 values(2.2);

879

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

880

cast(sum(distinct df) as signed)

881

3

882

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

883

cast(min(df) as signed)

884

0

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

885

785

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

886

786

1e8 * sum(distinct df)

887

787

330000000

894

794

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

895

795

1.00

896

796

drop table t1, t2, t3;

897

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

797

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

898

798

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

899

799

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

900

800

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

995

895

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

996

896

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

997

897

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

998

SELECT SQL_NO_CACHE

898

SELECT

999

899

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

1000

900

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

1001

901

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

1002

902

minid

1003

1

903

NULL

1004

904

DROP TABLE t1,t2;

1005

905

create table t1 select variance(0);

1006

906

show create table t1;

1007

907

Table Create Table

1008

t1 CREATE TABLE "t1" (

1009

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

1010

) ENGINE=MyISAM DEFAULT CHARSET=latin1

908

t1 CREATE TABLE `t1` (

909

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

910

) ENGINE=InnoDB

1011

911

drop table t1;

1012

912

create table t1 select stddev(0);

1013

913

show create table t1;

1014

914

Table Create Table

1015

t1 CREATE TABLE "t1" (

1016

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

1017

) ENGINE=MyISAM DEFAULT CHARSET=latin1

915

t1 CREATE TABLE `t1` (

916

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

917

) ENGINE=InnoDB

1018

918

drop table t1;

1019

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

919

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

1020

920

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

1021

921

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

1022

922

std(s1/s2)

1055

955

0

1056

956

0

1057

957

drop table bug22555;

1058

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

958

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

1059

959

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

1060

960

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

1061

961

i count(*)

1239

1139

0.21325763586649340

1240

1140

set @@div_precision_increment=@saved_div_precision_increment;

1241

1141

drop table bug22555;

1242

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

1142

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

1243

1143

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

1244

1144

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

1245

1145

var_samp(s) var_pop(s)

1251

1151

var_samp(e) var_pop(e)

1252

1152

6.7000 5.3600

1253

1153

drop table bug22555;

1254

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

1154

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

1255

1155

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

1256

1156

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

1257

1157

null null

1329

1229

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

1330

1230

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

1331

1231

id select_type table type possible_keys key key_len ref rows Extra

1332

1 SIMPLE t2 ref a a 5 const 2

1232

1 SIMPLE t2 ref a a 5 const 1

1333

1233

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

1334

1234

MIN(b) MIN(c)

1335

1235

3 2