~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

Note 1003 /* select#1 */ select `test`.`t1`.`product` AS `product`,`test`.`t1`.`country_id` AS `country_id`,`test`.`t1`.`year` AS `year`,sum(`test`.`t1`.`profit`) AS `sum(profit)` from `test`.`t1` group by `test`.`t1`.`product`,`test`.`t1`.`country_id`,`test`.`t1`.`year` with rollup

select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;

product country_id sum(profit)

TV 1 400

TV 2 200

TV NULL 600

Phone 3 10

100

Phone NULL 10

101

Computer 1 4200

102

Computer 2 2700

103

Computer NULL 6900

104

Calculator 1 125

105

Calculator 2 150

106

Calculator NULL 275

107

NULL NULL 7785

108

select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 5;

109

product country_id year sum(profit)

110

Calculator 1 1999 50

111

Calculator 1 2000 75

112

Calculator 1 NULL 125

113

Calculator 2 2000 150

114

Calculator 2 NULL 150

115

select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 3,3;

116

product country_id year sum(profit)

117

Calculator 2 2000 150

118

Calculator 2 NULL 150

119

Calculator NULL NULL 275

120

select product, country_id, count(*), count(distinct year) from t1 group by product, country_id;

121

product country_id count(*) count(distinct year)

122

Calculator 1 2 2

123

Calculator 2 2 1

124

Computer 1 3 2

125

Computer 2 2 1

126

Phone 3 1 1

127

TV 1 3 2

128

TV 2 2 1

129

select product, country_id, count(*), count(distinct year) from t1 group by product, country_id with rollup;

130

product country_id count(*) count(distinct year)

131

Calculator 1 2 2

132

Calculator 2 2 1

133

Calculator NULL 4 2

134

Computer 1 3 2

135

Computer 2 2 1

136

Computer NULL 5 2

137

Phone 3 1 1

138

Phone NULL 1 1

139

TV 1 3 2

140

TV 2 2 1

141

TV NULL 5 2

142

NULL NULL 15 3

143

select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having country_id = 1;

144

product country_id year sum(profit)

145

Calculator 1 1999 50

146

Calculator 1 2000 75

147

Calculator 1 NULL 125

148

Computer 1 1999 2700

149

Computer 1 2000 1500

150

Computer 1 NULL 4200

151

TV 1 1999 250

152

TV 1 2000 150

153

TV 1 NULL 400

154

select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 200;

155

product country_id year sum(profit)

156

Calculator NULL NULL 275

157

Computer 1 1999 2700

158

Computer 1 2000 1500

159

Computer 1 NULL 4200

160

Computer 2 2000 2700

161

Computer 2 NULL 2700

162

Computer NULL NULL 6900

163

TV 1 1999 250

164

TV 1 NULL 400

165

TV NULL NULL 600

166

NULL NULL NULL 7785

167

select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 7000;

168

product country_id year sum(profit)

169

NULL NULL NULL 7785

170

select concat(product,':',country_id) as 'prod', concat(":",year,":") as 'year',1+1, sum(profit)/count(*) from t1 group by 1,2 with rollup;

171

prod year 1+1 sum(profit)/count(*)

172

Calculator:1 :1999: 2 50.00000

173

Calculator:1 :2000: 2 75.00000

174

Calculator:1 NULL 2 62.50000

175

Calculator:2 :2000: 2 75.00000

176

Calculator:2 NULL 2 75.00000

177

Computer:1 :1999: 2 1350.00000

178

Computer:1 :2000: 2 1500.00000

179

Computer:1 NULL 2 1400.00000

180

Computer:2 :2000: 2 1350.00000

181

Computer:2 NULL 2 1350.00000

182

Phone:3 :2003: 2 10.00000

183

Phone:3 NULL 2 10.00000

184

TV:1 :1999: 2 125.00000

185

TV:1 :2000: 2 150.00000

186

TV:1 NULL 2 133.33333

187

TV:2 :2000: 2 100.00000

188

TV:2 NULL 2 100.00000

189

NULL NULL 2 519.00000

190

select product, sum(profit)/count(*) from t1 group by product with rollup;

191

product sum(profit)/count(*)

192

Calculator 68.75000

193

Computer 1380.00000

194

Phone 10.00000

195

TV 120.00000

196

NULL 519.00000

197

select left(product,4) as prod, sum(profit)/count(*) from t1 group by prod with rollup;

198

prod sum(profit)/count(*)

199

Calc 68.75000

200

Comp 1380.00000

201

Phon 10.00000

202

TV 120.00000

203

NULL 519.00000

204

select concat(product,':',country_id), 1+1, sum(profit)/count(*) from t1 group by concat(product,':',country_id) with rollup;

205

concat(product,':',country_id) 1+1 sum(profit)/count(*)

206

Calculator:1 2 62.50000

207

Calculator:2 2 75.00000

208

Computer:1 2 1400.00000

209

Computer:2 2 1350.00000

210

Phone:3 2 10.00000

211

TV:1 2 133.33333

212

TV:2 2 100.00000

213

NULL 2 519.00000

214

select product, country , year, sum(profit) from t1,t2 where t1.country_id=t2.country_id group by product, country, year with rollup;

215

product country year sum(profit)

216

Calculator India 2000 150

217

Calculator India NULL 150

218

Calculator USA 1999 50

219

Calculator USA 2000 75

220

Calculator USA NULL 125

221

Calculator NULL NULL 275

222

Computer India 2000 2700

223

Computer India NULL 2700

224

Computer USA 1999 2700

225

Computer USA 2000 1500

226

Computer USA NULL 4200

227

Computer NULL NULL 6900

228

Phone Finland 2003 10

229

Phone Finland NULL 10

230

Phone NULL NULL 10

231

TV India 2000 200

232

TV India NULL 200

233

TV USA 1999 250

234

TV USA 2000 150

235

TV USA NULL 400

236

TV NULL NULL 600

237

NULL NULL NULL 7785

238

select product, `sum` from (select product, sum(profit) as 'sum' from t1 group by product with rollup) as tmp where product is null;

239

product sum

240

NULL 7785

241

select product from t1 where exists (select product, country_id , sum(profit) from t1 as t2 where t1.product=t2.product group by product, country_id with rollup having sum(profit) > 6000);

242

product

243

Computer

244

Computer

245

Computer

246

Computer

247

Computer

248

select product, country_id , year, sum(profit) from t1 group by product, country_id, year having country_id is NULL;

249

product country_id year sum(profit)

250

select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup;

251

concat(':',product,':') sum(profit) avg(profit)

252

:Calculator: 275 68.75000

253

:Computer: 6900 1380.00000

254

:Phone: 10 10.00000

255

:TV: 600 120.00000

256

NULL 7785 519.00000

257

select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;

258

ERROR 42000: This version of MySQL doesn't yet support 'CUBE'

259

explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;

260

ERROR 42000: This version of MySQL doesn't yet support 'CUBE'

261

select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube union all select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;

262

ERROR 42000: This version of MySQL doesn't yet support 'CUBE'

263

drop table t1,t2;

264

CREATE TABLE t1 (i int);

265

INSERT INTO t1 VALUES(100);

266

CREATE TABLE t2 (i int);

267

INSERT INTO t2 VALUES (100),(200);

268

SELECT i, COUNT(*) FROM t1 GROUP BY i WITH ROLLUP;

269

i COUNT(*)

270

100 1

271

NULL 1

272

SELECT t1.i, t2.i, COUNT(*) FROM t1,t2 GROUP BY t1.i,t2.i WITH ROLLUP;

273

i i COUNT(*)

274

100 100 1

275

100 200 1

276

100 NULL 2

277

NULL NULL 2

278

drop table t1,t2;

279

CREATE TABLE user_day(

280

user_id INT NOT NULL,

281

date DATE NOT NULL,

282

UNIQUE INDEX user_date (user_id, date)

283

);

284

INSERT INTO user_day VALUES

285

(1, '2004-06-06' ),

286

(1, '2004-06-07' ),

287

(2, '2004-06-06' );

288

SELECT

289

d.date AS day,

290

COUNT(d.user_id) as sample,

291

COUNT(next_day.user_id) AS not_cancelled

292

FROM user_day d

293

LEFT JOIN user_day next_day

294

ON next_day.user_id=d.user_id AND

295

next_day.date= DATE_ADD( d.date, interval 1 day )

296

GROUP BY day;

297

day sample not_cancelled

298

2004-06-06 2 1

299

2004-06-07 1 0

300

SELECT

301

d.date AS day,

302

COUNT(d.user_id) as sample,

303

COUNT(next_day.user_id) AS not_cancelled

304

FROM user_day d

305

LEFT JOIN user_day next_day

306

ON next_day.user_id=d.user_id AND

307

next_day.date= DATE_ADD( d.date, interval 1 day )

308

GROUP BY day

309

WITH ROLLUP;

310

day sample not_cancelled

311

2004-06-06 2 1

312

2004-06-07 1 0

313

NULL 3 1

314

DROP TABLE user_day;

315

CREATE TABLE t1 (a int, b int);

316

INSERT INTO t1 VALUES

317

(1,4),

318

(2,2), (2,2),

319

(4,1), (4,1), (4,1), (4,1),

320

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

321

SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;

322

SUM(b)

323

324

325

326

327

SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;

328

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

329

SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;

330

SUM(b) COUNT(DISTINCT b)

331

4 1

332

6 2

333

4 1

334

14 3

335

SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;

336

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

337

SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;

338

SUM(b) COUNT(*)

339

4 1

340

6 4

341

4 4

342

14 9

343

SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;

344

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

345

SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;

346

SUM(b) COUNT(DISTINCT b) COUNT(*)

347

4 1 1

348

6 2 4

349

4 1 4

350

14 3 9

351

SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1

352

GROUP BY a WITH ROLLUP;

353

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

354

SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;

355

a sum(b)

356

1 4

357

1 4

358

2 2

359

2 4

360

2 6

361

4 4

362

4 4

363

NULL 14

364

SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;

365

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

366

SELECT b, a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;

367

b a sum(b)

368

4 1 4

369

NULL 1 4

370

1 2 2

371

2 2 4

372

NULL 2 6

373

1 4 4

374

NULL 4 4

375

NULL NULL 14

376

SELECT DISTINCT b,a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;

377

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

378

ALTER TABLE t1 ADD COLUMN c INT;

379

SELECT a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;

380

a b sum(c)

381

1 4 NULL

382

1 4 NULL

383

1 NULL NULL

384

2 1 NULL

385

2 1 NULL

386

2 2 NULL

387

2 2 NULL

388

2 NULL NULL

389

4 1 NULL

390

4 1 NULL

391

4 NULL NULL

392

NULL NULL NULL

393

SELECT distinct a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;

394

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

395

DROP TABLE t1;

396

CREATE TABLE t1 (a int, b int);

397

INSERT INTO t1 VALUES

398

(1,4),

399

(2,2), (2,2),

400

(4,1), (4,1), (4,1), (4,1),

401

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

402

SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;

403

a SUM(b)

404

1 4

405

SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;

406

a SUM(b)

407

1 4

408

DROP TABLE t1;

409

CREATE TABLE t1 (a int(11) NOT NULL);

410

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

411

SELECT a, SUM(a) m FROM t1 GROUP BY a WITH ROLLUP;

412

a m

413

1 1

414

2 2

415

NULL 3

416

SELECT * FROM ( SELECT a, SUM(a) m FROM t1 GROUP BY a WITH ROLLUP ) t2;

417

a m

418

1 1

419

2 2

420

NULL 3

421

DROP TABLE t1;

422

set div_precision_increment= @sav_dpi;

423

CREATE TABLE t1 (a int(11));

424

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

425

SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d

426

GROUP BY a;

427

a SUM(a) SUM(a)+1

428

1 1 2

429

2 2 3

430

SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d

431

GROUP BY a WITH ROLLUP;

432

a SUM(a) SUM(a)+1

433

1 1 2

434

2 2 3

435

NULL 3 4

436

SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d

437

GROUP BY a;

438

a SUM(a) SUM(a)+1

439

1 1 2

440

2 2 3

441

SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d

442

GROUP BY a WITH ROLLUP;

443

a SUM(a) SUM(a)+1

444

1 1 2

445

2 2 3

446

NULL 3 4

447

SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a)

448

FROM (SELECT 1 a, 2 b UNION SELECT 2,3 UNION SELECT 5,6 ) d

449

GROUP BY a WITH ROLLUP;

450

a SUM(a) SUM(a)+1 CONCAT(SUM(a),'x') SUM(a)+SUM(a) SUM(a)

451

1 1 2 1x 2 1

452

2 2 3 2x 4 2

453

5 5 6 5x 10 5

454

NULL 8 9 8x 16 8

455

DROP TABLE t1;

456

CREATE TABLE t1 (a int(11));

457

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

458

SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;

459

a a+1 SUM(a)

460

1 2 1

461

2 3 2

462

NULL NULL 3

463

SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;

464

a+1

465

466

467

NULL

468

SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;

469

a+SUM(a)

470

471

472

NULL

473

SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;

474

a b

475

2 3

476

SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;

477

a b

478

NULL NULL

479

SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;

480

a b

481

NULL NULL

482

SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;

483

IFNULL(a, 'TEST')

484

485

486

TEST

487

CREATE TABLE t2 (a int, b int);

488

INSERT INTO t2 VALUES

489

(1,4),

490

(2,2), (2,2),

491

(4,1), (4,1), (4,1), (4,1),

492

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

493

SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP;

494

a b SUM(b)

495

1 4 4

496

1 NULL 4

497

2 1 2

498

2 2 4

499

2 NULL 6

500

4 1 4

501

4 NULL 4

502

NULL NULL 14

503

SELECT a,b,SUM(b), a+b as c FROM t2

504

GROUP BY a,b WITH ROLLUP HAVING c IS NULL;

505

a b SUM(b) c

506

1 NULL 4 NULL

507

2 NULL 6 NULL

508

4 NULL 4 NULL

509

NULL NULL 14 NULL

510

SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2

511

GROUP BY a, b WITH ROLLUP;

512

IFNULL(a, 'TEST') COALESCE(b, 'TEST')

513

1 4

514

1 TEST

515

2 1

516

2 2

517

2 TEST

518

4 1

519

4 TEST

520

TEST TEST

521

DROP TABLE t1,t2;

522

CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL);

523

INSERT INTO t1 VALUES (1, 1);

524

INSERT INTO t1 VALUES (1, 2);

525

SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;

526

a b c count

527

1 1 1 1

528

1 1 NULL 1

529

1 2 1 1

530

1 2 NULL 1

531

1 NULL NULL 2

532

NULL NULL NULL 2

533

DROP TABLE t1;

534

CREATE TABLE t1 (a int(11) NOT NULL);

535

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

536

SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;

537

a a + 1 COUNT(*)

538

1 2 1

539

2 3 1

540

NULL NULL 2

541

SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;

542

a LENGTH(a) COUNT(*)

543

1 1 1

544

2 1 1

545

NULL NULL 2

546

DROP TABLE t1;

547

create table t1 ( a varchar(9), b int );

548

insert into t1 values('a',1),(null,2);

549

select a, max(b) from t1 group by a with rollup;

550

a max(b)

551

NULL 2

552

a 1

553

NULL 2

554

select distinct a, max(b) from t1 group by a with rollup;

555

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

556

drop table t1;

557

create table t1 (a varchar(22) not null , b int);

558

insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);

559

select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;

560

left(a,10) a sum(b)

561

2006-07-01 2006-07-01 21:30 1

562

2006-07-01 2006-07-01 23:30 10

563

2006-07-01 NULL 11

564

NULL NULL 11

565

select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;

566

x a sum(b)

567

2006-07-01 2006-07-01 21:30 1

568

2006-07-01 2006-07-01 23:30 10

569

2006-07-01 NULL 11

570

NULL NULL 11

571

drop table t1;

572

CREATE TABLE t1 (a int, b int);

573

INSERT INTO t1

574

VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);

575

SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;

576

a SUM(b)

577

1 30

578

2 90

579

3 30

580

NULL 150

581

SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;

582

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

583

SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;

584

a b COUNT(*)

585

1 10 1

586

1 20 1

587

1 NULL 2

588

2 10 2

589

2 30 1

590

2 40 1

591

2 NULL 4

592

3 30 1

593

3 NULL 1

594

NULL NULL 7

595

SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;

596

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

597

SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;

598

x a SUM(b)

599

x 1 30

600

x 2 90

601

x 3 30

602

x NULL 150

603

NULL NULL 150

604

SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;

605

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

606

SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;

607

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

608

DROP TABLE t1;

609

CREATE TABLE t1(id int, type char(1));

610

INSERT INTO t1 VALUES

611

(1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),

612

(6,"B"),(7,"A"),(8,"C"),(9,"A"),(10,"C");

613

CREATE VIEW v1 AS SELECT * FROM t1;

614

SELECT type FROM t1 GROUP BY type WITH ROLLUP;

615

type

616

617

618

619

NULL

620

SELECT type FROM v1 GROUP BY type WITH ROLLUP;

621

type

622

623

624

625

NULL

626

EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;

627

id select_type table type possible_keys key key_len ref rows Extra

628

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort

629

DROP VIEW v1;

630

DROP TABLE t1;

631

CREATE TABLE t1 (a int(11) NOT NULL);

632

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

633

CREATE VIEW v1 AS

634

SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;

635

DESC v1;

636

Field Type Null Key Default Extra

637

a bigint(11) YES NULL

638

LENGTH(a) bigint(10) YES NULL

639

COUNT(*) bigint(21) NO 0

640

SELECT * FROM v1;

641

a LENGTH(a) COUNT(*)

642

1 1 1

643

2 1 1

644

NULL NULL 2

645

DROP VIEW v1;

646

DROP TABLE t1;

647

CREATE TABLE t1 (a int, KEY (a));

648

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

649

SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;

650

a SUM(a)

651

1 4

652

3 6

653

4 4

654

NULL 14

655

DROP TABLE t1;

656

657

# Bug#31095: Unexpected NULL constant caused server crash.

658

659

create table t1(a int);

660

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

661

select count(a) from t1 group by null with rollup;

662

count(a)

663

664

665

drop table t1;

666

##############################################################

667

CREATE TABLE t1(a INT);

668

INSERT INTO t1 VALUES(0);

669

SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;

670

671

672

673

DROP TABLE t1;

674

675

# Bug #48131: crash group by with rollup, distinct,

676

# filesort, with temporary tables

677

678

CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY);

679

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

680

CREATE TABLE t2 (b INT);

681

INSERT INTO t2 VALUES (100);

682

SELECT a, b FROM t1, t2 GROUP BY a, b WITH ROLLUP;

683

a b

684

1 100

685

1 NULL

686

2 100

687

2 NULL

688

NULL NULL

689

SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;

690

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

691

DROP TABLE t1, t2;

692

693

# Bug #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP

694

# and only const tables

695

CREATE TABLE t1 (a INT);

696

CREATE TABLE t2 (b INT);

697

INSERT INTO t1 VALUES (1);

698

INSERT INTO t2 VALUES (1);

699

SELECT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;

700

701

702

NULL

703

NULL

704

SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;

705

ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT

706

DROP TABLE t1, t2;

707

End of 5.0 tests

708

709

# Bug#13011553 CRASH IN SORTCMP OR CACHED_ITEM_STR::CMP IF GROUP BY SUBQUERY WITH ROLLUP

710

711

CREATE TABLE t1 (f1 DATETIME) ENGINE = MyISAM;

712

INSERT INTO t1 VALUES ('2012-12-20 00:00:00'), (NULL);

713

SELECT f1 FROM t1 GROUP BY

714

(SELECT f1 FROM t1 HAVING f1 < '2012-12-21 00:00:00') WITH ROLLUP;

715

716

2012-12-20 00:00:00

717

2012-12-20 00:00:00

718

DROP TABLE t1;

719

CREATE TABLE t1 (f1 DATE) ENGINE = MyISAM;

720

INSERT INTO t1 VALUES ('2012-12-20'), (NULL);

721

SELECT f1 FROM t1 GROUP BY

722

(SELECT f1 FROM t1 HAVING f1 < '2012-12-21') WITH ROLLUP;

723

724

2012-12-20

725

2012-12-20

726

DROP TABLE t1;

727

CREATE TABLE t1 (f1 TIME) ENGINE = MyISAM;

728

INSERT INTO t1 VALUES ('11:11:11'), (NULL);

729

SELECT f1 FROM t1 GROUP BY

730

(SELECT f1 FROM t1 HAVING f1 < '12:12:12') WITH ROLLUP;

731

732

11:11:11

733

11:11:11

734

DROP TABLE t1;

735

End of 5.5 tests

Older »