~drizzle-trunk/drizzle/development : revision 1

mysql-test/t/distinct.test

# Bug with distinct and INSERT INTO

# Bug with group by and not used fields

--disable_warnings

drop table if exists t1,t2,t3;

--enable_warnings

CREATE TABLE t1 (id int,facility char(20));

CREATE TABLE t2 (facility char(20));

INSERT INTO t1 VALUES (NULL,NULL);

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

INSERT INTO t1 VALUES (0,'');

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

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

INSERT INTO t1 VALUES (3,'ANC');

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

INSERT INTO t1 VALUES (5,'FBX');

INSERT INTO t1 VALUES (6,'MT');

INSERT INTO t1 VALUES (7,'P');

INSERT INTO t1 VALUES (8,'RV');

INSERT INTO t1 VALUES (9,'SRV');

INSERT INTO t1 VALUES (10,'VMT');

INSERT INTO t2 SELECT DISTINCT FACILITY FROM t1;

select id from t1 group by id;

select * from t1 order by id;

select id-5,facility from t1 order by "id-5";

select id,concat(facility) from t1 group by id ;

select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a;

select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp;

SELECT DISTINCT FACILITY FROM t1;

SELECT FACILITY FROM t2;

SELECT count(*) from t1,t2 where t1.facility=t2.facility;

select count(facility) from t1;

select count(*) from t1;

select count(*) from t1 where facility IS NULL;

select count(*) from t1 where facility = NULL;

select count(*) from t1 where facility IS NOT NULL;

select count(*) from t1 where id IS NULL;

select count(*) from t1 where id IS NOT NULL;

drop table t1,t2;

# Problem with distinct without results

CREATE TABLE t1 (UserId int(11) DEFAULT '0' NOT NULL);

INSERT INTO t1 VALUES (20);

INSERT INTO t1 VALUES (27);

SELECT UserId FROM t1 WHERE Userid=22;

SELECT UserId FROM t1 WHERE UserId=22 group by Userid;

SELECT DISTINCT UserId FROM t1 WHERE UserId=22 group by Userid;

SELECT DISTINCT UserId FROM t1 WHERE UserId=22;

drop table t1;

# Test of distinct

CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);

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

CREATE TABLE t2 (a int(10) unsigned not null, key (A));

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

CREATE TABLE t3 (a int(10) unsigned, key(A), b text);

INSERT INTO t3 VALUES (1,'1'),(2,'2');

SELECT DISTINCT t3.b FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;

INSERT INTO t2 values (1),(2),(3);

INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');

explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;

SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;

# Create a lot of data into t3;

create temporary table t4 select * from t3;

insert into t3 select * from t4;

insert into t4 select * from t3;

insert into t3 select * from t4;

insert into t4 select * from t3;

insert into t3 select * from t4;

insert into t4 select * from t3;

insert into t3 select * from t4;

explain select distinct t1.a from t1,t3 where t1.a=t3.a;

#flush status;

select distinct t1.a from t1,t3 where t1.a=t3.a;

#show status like 'Handler%';

#flush status;

select distinct 1 from t1,t3 where t1.a=t3.a;

#show status like 'Handler%';

explain SELECT distinct t1.a from t1;

explain SELECT distinct t1.a from t1 order by a desc;

explain SELECT t1.a from t1 group by a order by a desc;

explain SELECT distinct t1.a from t1 order by a desc limit 1;

explain SELECT distinct a from t3 order by a desc limit 2;

explain SELECT distinct a,b from t3 order by a+1;

100

explain SELECT distinct a,b from t3 order by a limit 2;

101

explain SELECT a,b from t3 group by a,b order by a+1;

102

103

drop table t1,t2,t3,t4;

104

105

CREATE TABLE t1 (name varchar(255));

106

INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');

107

SELECT DISTINCT * FROM t1 LIMIT 2;

108

SELECT DISTINCT name FROM t1 LIMIT 2;

109

SELECT DISTINCT 1 FROM t1 LIMIT 2;

110

drop table t1;

111

112

CREATE TABLE t1 (

113

ID int(11) NOT NULL auto_increment,

114

NAME varchar(75) DEFAULT '' NOT NULL,

115

LINK_ID int(11) DEFAULT '0' NOT NULL,

116

PRIMARY KEY (ID),

117

KEY NAME (NAME),

118

KEY LINK_ID (LINK_ID)

119

);

120

121

INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0),(2,'Jack',0),(3,'Bill',0);

122

123

CREATE TABLE t2 (

124

ID int(11) NOT NULL auto_increment,

125

NAME varchar(150) DEFAULT '' NOT NULL,

126

PRIMARY KEY (ID),

127

KEY NAME (NAME)

128

);

129

130

SELECT DISTINCT

131

t2.id AS key_link_id,

132

t2.name AS link

133

FROM t1

134

LEFT JOIN t2 ON t1.link_id=t2.id

135

GROUP BY t1.id

136

ORDER BY link;

137

drop table t1,t2;

138

139

140

# Problem with table dependencies

141

142

143

create table t1 (

144

id int not null,

145

name tinytext not null,

146

unique (id)

147

);

148

create table t2 (

149

id int not null,

150

idx int not null,

151

unique (id, idx)

152

);

153

create table t3 (

154

id int not null,

155

idx int not null,

156

unique (id, idx)

157

);

158

insert into t1 values (1,'yes'), (2,'no');

159

insert into t2 values (1,1);

160

insert into t3 values (1,1);

161

EXPLAIN

162

SELECT DISTINCT

163

t1.id

164

from

165

166

straight_join

167

168

straight_join

169

170

straight_join

171

t1 as j_lj_t2 left join t2 as t2_lj

172

on j_lj_t2.id=t2_lj.id

173

straight_join

174

t1 as j_lj_t3 left join t3 as t3_lj

175

on j_lj_t3.id=t3_lj.id

176

WHERE

177

((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))

178

AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));

179

SELECT DISTINCT

180

t1.id

181

from

182

183

straight_join

184

185

straight_join

186

187

straight_join

188

t1 as j_lj_t2 left join t2 as t2_lj

189

on j_lj_t2.id=t2_lj.id

190

straight_join

191

t1 as j_lj_t3 left join t3 as t3_lj

192

on j_lj_t3.id=t3_lj.id

193

WHERE

194

((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))

195

AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));

196

drop table t1,t2,t3;

197

198

199

# Test using DISTINCT on a function that contains a group function

200

# This also test the case when one doesn't use all fields in GROUP BY.

201

202

203

create table t1 (a int not null, b int not null, t time);

204

insert into t1 values (1,1,"00:06:15"),(1,2,"00:06:15"),(1,2,"00:30:15"),(1,3,"00:06:15"),(1,3,"00:30:15");

205

select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;

206

select distinct a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;

207

create table t2 (a int not null primary key, b int);

208

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

209

select t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;

210

select distinct t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;

211

drop table t1,t2;

212

213

214

# Test problem with DISTINCT and HAVING

215

216

create table t1 (a int not null,b char(5), c text);

217

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

218

select distinct a from t1 group by b,a having a > 2 order by a desc;

219

select distinct a,c from t1 group by b,c,a having a > 2 order by a desc;

220

drop table t1;

221

222

223

# Test problem with DISTINCT and ORDER BY DESC

224

225

226

create table t1 (a char(1), key(a)) engine=myisam;

227

insert into t1 values('1'),('1');

228

select * from t1 where a >= '1';

229

select distinct a from t1 order by a desc;

230

select distinct a from t1 where a >= '1' order by a desc;

231

drop table t1;

232

233

234

# Test when using a not previously used column in ORDER BY

235

236

237

CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME);

238

CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10));

239

240

INSERT INTO t1 (email, infoID, dateentered) VALUES

241

('test1@testdomain.com', 1, '2002-07-30 22:56:38'),

242

('test1@testdomain.com', 1, '2002-07-27 22:58:16'),

243

('test2@testdomain.com', 1, '2002-06-19 15:22:19'),

244

('test2@testdomain.com', 2, '2002-06-18 14:23:47'),

245

('test3@testdomain.com', 1, '2002-05-19 22:17:32');

246

247

INSERT INTO t2(infoID, shipcode) VALUES

248

(1, 'Z001'),

249

(2, 'R002');

250

251

SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID;

252

SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC;

253

SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC;

254

drop table t1,t2;

255

256

257

# test with table.* in DISTINCT

258

259

260

CREATE TABLE t1 (privatemessageid int(10) unsigned NOT NULL auto_increment, folderid smallint(6) NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', touserid int(10) unsigned NOT NULL default '0', fromuserid int(10) unsigned NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int(10) unsigned NOT NULL default '0', showsignature smallint(6) NOT NULL default '0', iconid smallint(5) unsigned NOT NULL default '0', messageread smallint(6) NOT NULL default '0', readtime int(10) unsigned NOT NULL default '0', receipt smallint(6) unsigned NOT NULL default '0', deleteprompt smallint(6) unsigned NOT NULL default '0', multiplerecipients smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid)) ENGINE=MyISAM;

261

INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0);

262

CREATE TABLE t2 (userid int(10) unsigned NOT NULL auto_increment, usergroupid smallint(5) unsigned NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid smallint(5) unsigned NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser smallint(6) NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail smallint(6) NOT NULL default '0', showemail smallint(6) NOT NULL default '0', invisible smallint(6) NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle smallint(6) NOT NULL default '0', joindate int(10) unsigned NOT NULL default '0', cookieuser smallint(6) NOT NULL default '0', daysprune smallint(6) NOT NULL default '0', lastvisit int(10) unsigned NOT NULL default '0', lastactivity int(10) unsigned NOT NULL default '0', lastpost int(10) unsigned NOT NULL default '0', posts smallint(5) unsigned NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification smallint(6) NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm smallint(6) NOT NULL default '0', emailonpm smallint(6) NOT NULL default '0', pmpopup smallint(6) NOT NULL default '0', avatarid smallint(6) NOT NULL default '0', avatarrevision int(6) unsigned NOT NULL default '0', options smallint(6) NOT NULL default '15', birthday date NOT NULL default '0000-00-00', maxposts smallint(6) NOT NULL default '-1', startofweek smallint(6) NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int(10) unsigned NOT NULL default '0', nosessionhash smallint(6) NOT NULL default '0', autorefresh smallint(6) NOT NULL default '-1', messagepopup tinyint(2) NOT NULL default '0', inforum smallint(5) unsigned NOT NULL default '0', ratenum smallint(5) unsigned NOT NULL default '0', ratetotal smallint(5) unsigned NOT NULL default '0', allowrate smallint(5) unsigned NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum)) ENGINE=MyISAM;

263

INSERT INTO t2 VALUES (33,6,'Kevin','0','kevin@stileproject.com',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,'0000-00-00',-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1);

264

SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid);

265

DROP TABLE t1,t2;

266

267

268

# test with const_item in ORDER BY

269

270

271

CREATE TABLE t1 (a int primary key, b int, c int);

272

INSERT t1 VALUES (1,2,3);

273

CREATE TABLE t2 (a int primary key, b int, c int);

274

INSERT t2 VALUES (3,4,5);

275

SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c;

276

DROP TABLE t1,t2;

277

278

279

# Test of LEFT() with distinct

280

281

282

CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;

283

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

284

INSERT INTO t1 VALUES (3, 'aaaaa');

285

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

286

select distinct left(name,1) as name from t1;

287

drop table t1;

288

289

290

# Test case from sel000100

291

292

293

CREATE TABLE t1 (

294

ID int(11) NOT NULL auto_increment,

295

NAME varchar(75) DEFAULT '' NOT NULL,

296

LINK_ID int(11) DEFAULT '0' NOT NULL,

297

PRIMARY KEY (ID),

298

KEY NAME (NAME),

299

KEY LINK_ID (LINK_ID)

300

);

301

302

INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0);

303

INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (2,'Jack',0);

304

INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (3,'Bill',0);

305

306

CREATE TABLE t2 (

307

ID int(11) NOT NULL auto_increment,

308

NAME varchar(150) DEFAULT '' NOT NULL,

309

PRIMARY KEY (ID),

310

KEY NAME (NAME)

311

);

312

313

SELECT DISTINCT

314

t2.id AS key_link_id,

315

t2.name AS link

316

FROM t1

317

LEFT JOIN t2 ON t1.link_id=t2.id

318

GROUP BY t1.id

319

ORDER BY link;

320

drop table t1,t2;

321

322

323

# test case for #674

324

325

326

CREATE TABLE t1 (

327

html varchar(5) default NULL,

328

rin int(11) default '0',

329

rout int(11) default '0'

330

) ENGINE=MyISAM;

331

332

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

333

SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin;

334

drop table t1;

335

336

337

# Test cases for #12625: DISTINCT for a list with constants

338

339

340

CREATE TABLE t1 (a int);

341

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

342

SELECT DISTINCT a, 1 FROM t1;

343

SELECT DISTINCT 1, a FROM t1;

344

345

CREATE TABLE t2 (a int, b int);

346

INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5);

347

SELECT DISTINCT a, b, 2 FROM t2;

348

SELECT DISTINCT 2, a, b FROM t2;

349

SELECT DISTINCT a, 2, b FROM t2;

350

351

DROP TABLE t1,t2;

352

353

# Bug#16458: Simple SELECT FOR UPDATE causes "Result Set not updatable"

354

# error.

355

356

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

357

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

358

EXPLAIN SELECT DISTINCT a FROM t1;

359

EXPLAIN SELECT DISTINCT a,b FROM t1;

360

EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;

361

EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2

362

WHERE t1_1.a = t1_2.a;

363

EXPLAIN SELECT a FROM t1 GROUP BY a;

364

EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;

365

EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;

366

367

CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT,

368

PRIMARY KEY (a,b));

369

INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);

370

EXPLAIN SELECT DISTINCT a FROM t2;

371

EXPLAIN SELECT DISTINCT a,a FROM t2;

372

EXPLAIN SELECT DISTINCT b,a FROM t2;

373

EXPLAIN SELECT DISTINCT a,c FROM t2;

374

EXPLAIN SELECT DISTINCT c,a,b FROM t2;

375

376

EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;

377

CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);

378

EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;

379

380

DROP TABLE t1,t2;

381

382

# Bug 9784 DISTINCT IFNULL truncates data

383

384

create table t1 (id int, dsc varchar(50));

385

insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three");

386

select distinct id, IFNULL(dsc, '-') from t1;

387

drop table t1;

388

389

390

# Bug 21456: SELECT DISTINCT(x) produces incorrect results when using order by

391

392

CREATE TABLE t1 (a int primary key, b int);

393

394

INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2);

395

396

explain SELECT DISTINCT a, b FROM t1 ORDER BY b;

397

SELECT DISTINCT a, b FROM t1 ORDER BY b;

398

DROP TABLE t1;

399

400

# End of 4.1 tests

401

402

403

404

# Bug #15745 ( COUNT(DISTINCT CONCAT(x,y)) returns wrong result)

405

406

CREATE TABLE t1 (

407

ID int(11) NOT NULL auto_increment,

408

x varchar(20) default NULL,

409

y decimal(10,0) default NULL,

410

PRIMARY KEY (ID),

411

KEY (y)

412

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

413

414

INSERT INTO t1 VALUES

415

(1,'ba','-1'),

416

(2,'ba','1150'),

417

(306,'ba','-1'),

418

(307,'ba','1150'),

419

(611,'ba','-1'),

420

(612,'ba','1150');

421

422

select count(distinct x,y) from t1;

423

select count(distinct concat(x,y)) from t1;

424

drop table t1;

425

426

427

# Bug #18068: SELECT DISTINCT

428

429

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

430

431

INSERT INTO t1 VALUES (1, 101);

432

INSERT INTO t1 SELECT a + 1, a + 101 FROM t1;

433

INSERT INTO t1 SELECT a + 2, a + 102 FROM t1;

434

INSERT INTO t1 SELECT a + 4, a + 104 FROM t1;

435

INSERT INTO t1 SELECT a + 8, a + 108 FROM t1;

436

437

EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;

438

SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;

439

440

DROP TABLE t1;

441

# The test case for bug#20836 should be re-enabled when bug#16861 is resolved

442

# The results for the test should be the same as in 4.1.

443

444

#Bug #20836: Selecting into variables results in wrong results being returned

445

446

#--disable_warnings

447

#DROP TABLE IF EXISTS t1;

448

#--enable_warnings

449

450

#CREATE TABLE t1 (id INT NOT NULL, fruit_id INT NOT NULL, fruit_name varchar(20)

451

#default NULL);

452

453

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

454

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

455

#INSERT INTO t1 VALUES (3,2,'APPLE');

456

#INSERT INTO t1 VALUES (4,3,'PEAR');

457

458

#SELECT DISTINCT fruit_id, fruit_name INTO @v1, @v2 FROM t1 WHERE fruit_name =

459

#'APPLE';

460

#SELECT @v1, @v2;

461

462

#SELECT DISTINCT fruit_id, fruit_name INTO @v3, @v4 FROM t1 GROUP BY fruit_id,

463

#fruit_name HAVING fruit_name = 'APPLE';

464

#SELECT @v3, @v4;

465

466

#SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE

467

#fruit_name = 'APPLE';

468

#SELECT @v5, @v6, @v7, @v8;

469

470

#SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1

471

#WHERE fruit_name = 'APPLE';

472

#SELECT @v5, @v6, @v7, @v8, @v9, @v10;

473

474

#SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO

475

#@v13, @v14 FROM t1 WHERE fruit_name = 'APPLE';

476

#SELECT @v11, @v12, @v13, @v14;

477

478

#SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE';

479

#SELECT @v15, @v16;

480

481

#SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name =

482

#'APPLE';

483

#SELECT @v17, @v18;

484

485

#--disable_warnings

486

#DROP TABLE IF EXISTS t2;

487

#--enable_warnings

488

489

#CREATE TABLE t2 (fruit_id INT NOT NULL, fruit_name varchar(20)

490

#default NULL);

491

492

#SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE

493

#'../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE';

494

#LOAD DATA INFILE '../tmp/data1.tmp' INTO TABLE t2;

495

#--exec rm $MYSQL_TEST_DIR/var/tmp/data1.tmp

496

497

#SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE

498

#'../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE';

499

#LOAD DATA INFILE '../tmp/data2.tmp' INTO TABLE t2;

500

#--exec rm $MYSQL_TEST_DIR/var/tmp/data2.tmp

501

502

#SELECT @v19, @v20;

503

#SELECT * FROM t2;

504

505

#DROP TABLE t1;

506

#DROP TABLE t2;

507

508

509

# Bug #15881: cast problems

510

511

CREATE TABLE t1 (a CHAR(1)); INSERT INTO t1 VALUES('A'), (0);

512

SELECT a FROM t1 WHERE a=0;

513

SELECT DISTINCT a FROM t1 WHERE a=0;

514

DROP TABLE t1;

515

CREATE TABLE t1 (a DATE);

516

INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');

517

EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');

518

EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1

519

WHERE ADDDATE(a,1) = '2002-08-03');

520

CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);

521

INSERT INTO t2 VALUES (0xf6);

522

INSERT INTO t2 VALUES ('oe');

523

524

SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt;

525

SELECT COUNT(*) FROM

526

(SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;

527

528

DROP TABLE t1, t2;

529

530

531

# Bug #25551: inconsistent behaviour in grouping NULL, depending on index type

532

533

CREATE TABLE t1 (a INT, UNIQUE (a));

534

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

535

EXPLAIN SELECT DISTINCT a FROM t1;

536

#result must have one row with NULL

537

SELECT DISTINCT a FROM t1;

538

EXPLAIN SELECT a FROM t1 GROUP BY a;

539

#result must have one row with NULL

540

SELECT a FROM t1 GROUP BY a;

541

542

DROP TABLE t1;

543

544

545

#Bug #27659: SELECT DISTINCT returns incorrect result set when field is

546

#repeated

547

548

549

CREATE TABLE t1 (a INT, b INT);

550

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

551

SELECT DISTINCT a, b FROM t1;

552

SELECT DISTINCT a, a, b FROM t1;

553

DROP TABLE t1;

554

555

--echo End of 5.0 tests

556

557

558

# Bug #34928: Confusion by having Primary Key and Index

559

560

CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT,

561

PRIMARY KEY(a,b,c,d,e),

562

KEY(a,b,d,c)

563

);

564

565

INSERT INTO t1(a, b, c) VALUES (1, 1, 1),

566

(1, 1, 2),

567

(1, 1, 3),

568

(1, 2, 1),

569

(1, 2, 2),

570

(1, 2, 3);

571

572

EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;

573

574

SELECT DISTINCT a, b, d, c FROM t1;

575

576

DROP TABLE t1;

577

578

--echo End of 5.1 tests

Older »