1
drop table if exists t1,t2,t3;
2
CREATE TABLE t1 (id int,facility char(20));
3
CREATE TABLE t2 (facility char(20));
4
INSERT INTO t1 VALUES (NULL,NULL);
5
INSERT INTO t1 VALUES (-1,'');
6
INSERT INTO t1 VALUES (0,'');
7
INSERT INTO t1 VALUES (1,'/L');
8
INSERT INTO t1 VALUES (2,'A01');
9
INSERT INTO t1 VALUES (3,'ANC');
10
INSERT INTO t1 VALUES (4,'F01');
11
INSERT INTO t1 VALUES (5,'FBX');
12
INSERT INTO t1 VALUES (6,'MT');
13
INSERT INTO t1 VALUES (7,'P');
14
INSERT INTO t1 VALUES (8,'RV');
15
INSERT INTO t1 VALUES (9,'SRV');
16
INSERT INTO t1 VALUES (10,'VMT');
17
INSERT INTO t2 SELECT DISTINCT FACILITY FROM t1;
18
select id from t1 group by id;
33
select * from t1 order by id;
48
select id-5,facility from t1 order by "id-5";
63
select id,concat(facility) from t1 group by id ;
78
select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a;
93
select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp;
98
SELECT DISTINCT FACILITY FROM t1;
112
SELECT FACILITY FROM t2;
126
SELECT count(*) from t1,t2 where t1.facility=t2.facility;
129
select count(facility) from t1;
132
select count(*) from t1;
135
select count(*) from t1 where facility IS NULL;
138
select count(*) from t1 where facility = NULL;
141
select count(*) from t1 where facility IS NOT NULL;
144
select count(*) from t1 where id IS NULL;
147
select count(*) from t1 where id IS NOT NULL;
151
CREATE TABLE t1 (UserId int(11) DEFAULT '0' NOT NULL);
152
INSERT INTO t1 VALUES (20);
153
INSERT INTO t1 VALUES (27);
154
SELECT UserId FROM t1 WHERE Userid=22;
156
SELECT UserId FROM t1 WHERE UserId=22 group by Userid;
158
SELECT DISTINCT UserId FROM t1 WHERE UserId=22 group by Userid;
160
SELECT DISTINCT UserId FROM t1 WHERE UserId=22;
163
CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
164
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
165
CREATE TABLE t2 (a int(10) unsigned not null, key (A));
166
INSERT INTO t2 VALUES (1),(2);
167
CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
168
INSERT INTO t3 VALUES (1,'1'),(2,'2');
169
SELECT DISTINCT t3.b FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
172
INSERT INTO t2 values (1),(2),(3);
173
INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
174
explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
175
id select_type table type possible_keys key key_len ref rows Extra
176
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using where; Using temporary
177
1 SIMPLE t3 ref a a 5 test.t1.b 2 Using index
178
1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct; Using join buffer (Block Nested Loop)
179
SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
182
create temporary table t4 select * from t3;
183
insert into t3 select * from t4;
184
insert into t4 select * from t3;
185
insert into t3 select * from t4;
186
insert into t4 select * from t3;
187
insert into t3 select * from t4;
188
insert into t4 select * from t3;
189
insert into t3 select * from t4;
190
explain select distinct t1.a from t1,t3 where t1.a=t3.a;
191
id select_type table type possible_keys key key_len ref rows Extra
192
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary
193
1 SIMPLE t3 ref a a 5 test.t1.a 11 Using index; Distinct
195
select distinct t1.a from t1,t3 where t1.a=t3.a;
199
show status like 'Handler_read%';
207
Handler_read_rnd_next 3
209
select distinct 1 from t1,t3 where t1.a=t3.a;
212
show status like 'Handler_read%';
220
Handler_read_rnd_next 2
221
explain SELECT distinct t1.a from t1;
222
id select_type table type possible_keys key key_len ref rows Extra
223
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index
224
explain SELECT distinct t1.a from t1 order by a desc;
225
id select_type table type possible_keys key key_len ref rows Extra
226
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index
227
explain SELECT t1.a from t1 group by a order by a desc;
228
id select_type table type possible_keys key key_len ref rows Extra
229
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index
230
explain SELECT distinct t1.a from t1 order by a desc limit 1;
231
id select_type table type possible_keys key key_len ref rows Extra
232
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 1 Using index
233
explain SELECT distinct a from t3 order by a desc limit 2;
234
id select_type table type possible_keys key key_len ref rows Extra
235
1 SIMPLE t3 index a a 5 NULL 40 Using index
236
explain SELECT distinct a,b from t3 order by a+1;
237
id select_type table type possible_keys key key_len ref rows Extra
238
1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort
239
explain SELECT distinct a,b from t3 order by a limit 2;
240
id select_type table type possible_keys key key_len ref rows Extra
241
1 SIMPLE t3 index NULL a 5 NULL 2 Using temporary
242
explain SELECT a,b from t3 group by a,b order by a+1;
243
id select_type table type possible_keys key key_len ref rows Extra
244
1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort
245
SELECT distinct t1.a from t1;
251
SELECT distinct t1.a from t1 order by a desc;
257
SELECT t1.a from t1 group by a order by a desc;
263
SELECT distinct t1.a from t1 order by a desc limit 1;
266
SELECT distinct a from t3 order by a desc limit 2;
270
SELECT distinct a,b from t3 order by a+1;
274
SELECT distinct a,b from t3 order by a limit 2;
278
SELECT a,b from t3 group by a,b order by a+1;
282
drop table t1,t2,t3,t4;
283
CREATE TABLE t1 (name varchar(255));
284
INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');
285
SELECT DISTINCT * FROM t1 LIMIT 2;
289
SELECT DISTINCT name FROM t1 LIMIT 2;
293
SELECT DISTINCT 1 FROM t1 LIMIT 2;
298
ID int(11) NOT NULL auto_increment,
299
NAME varchar(75) DEFAULT '' NOT NULL,
300
LINK_ID int(11) DEFAULT '0' NOT NULL,
303
KEY LINK_ID (LINK_ID)
305
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0),(2,'Jack',0),(3,'Bill',0);
307
ID int(11) NOT NULL auto_increment,
308
NAME varchar(150) DEFAULT '' NOT NULL,
313
t2.id AS key_link_id,
316
LEFT JOIN t2 ON t1.link_id=t2.id
324
name tinytext not null,
337
insert into t1 values (1,'yes'), (2,'no');
338
insert into t2 values (1,1);
339
insert into t3 values (1,1);
350
t1 as j_lj_t2 left join t2 as t2_lj
351
on j_lj_t2.id=t2_lj.id
353
t1 as j_lj_t3 left join t3 as t3_lj
354
on j_lj_t3.id=t3_lj.id
356
((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
357
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
358
id select_type table type possible_keys key key_len ref rows Extra
359
1 SIMPLE t1 index id id 4 NULL 2 Using index; Using temporary
360
1 SIMPLE t2 index id id 8 NULL 1 Using index; Distinct; Using join buffer (Block Nested Loop)
361
1 SIMPLE t3 index id id 8 NULL 1 Using index; Distinct; Using join buffer (Block Nested Loop)
362
1 SIMPLE j_lj_t2 index id id 4 NULL 2 Using where; Using index; Distinct; Using join buffer (Block Nested Loop)
363
1 SIMPLE t2_lj index id id 8 NULL 1 Using where; Using index; Distinct; Using join buffer (Block Nested Loop)
364
1 SIMPLE j_lj_t3 index id id 4 NULL 2 Using where; Using index; Distinct; Using join buffer (Block Nested Loop)
365
1 SIMPLE t3_lj index id id 8 NULL 1 Using where; Using index; Distinct; Using join buffer (Block Nested Loop)
375
t1 as j_lj_t2 left join t2 as t2_lj
376
on j_lj_t2.id=t2_lj.id
378
t1 as j_lj_t3 left join t3 as t3_lj
379
on j_lj_t3.id=t3_lj.id
381
((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
382
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
386
create table t1 (a int not null, b int not null, t time);
387
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");
388
select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
389
a sec_to_time(sum(time_to_sec(t)))
393
select distinct a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
394
a sec_to_time(sum(time_to_sec(t)))
397
create table t2 (a int not null primary key, b int);
398
insert into t2 values (1,1),(2,2),(3,3);
399
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;
400
a sec_to_time(sum(time_to_sec(t)))
404
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;
405
a sec_to_time(sum(time_to_sec(t)))
409
create table t1 (a int not null,b char(5), c text);
410
insert into t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4);
411
select distinct a from t1 group by b,a having a > 2 order by a desc;
415
select distinct a,c from t1 group by b,c,a having a > 2 order by a desc;
420
create table t1 (a char(1), key(a)) engine=myisam;
421
insert into t1 values('1'),('1');
422
select * from t1 where a >= '1';
426
select distinct a from t1 order by a desc;
429
select distinct a from t1 where a >= '1' order by a desc;
433
CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME);
434
CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10));
435
INSERT INTO t1 (email, infoID, dateentered) VALUES
436
('test1@testdomain.com', 1, '2002-07-30 22:56:38'),
437
('test1@testdomain.com', 1, '2002-07-27 22:58:16'),
438
('test2@testdomain.com', 1, '2002-06-19 15:22:19'),
439
('test2@testdomain.com', 2, '2002-06-18 14:23:47'),
440
('test3@testdomain.com', 1, '2002-05-19 22:17:32');
441
INSERT INTO t2(infoID, shipcode) VALUES
444
SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID;
446
test1@testdomain.com Z001
447
test2@testdomain.com Z001
448
test2@testdomain.com R002
449
test3@testdomain.com Z001
450
SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC;
455
SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC;
457
test1@testdomain.com Z001
458
test2@testdomain.com Z001
459
test2@testdomain.com R002
460
test3@testdomain.com Z001
462
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;
463
INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0);
464
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;
465
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);
466
SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid);
467
privatemessageid folderid userid touserid fromuserid title message dateline showsignature iconid messageread readtime receipt deleteprompt multiplerecipients userid usergroupid username password email styleid parentemail coppauser homepage icq aim yahoo signature adminemail showemail invisible usertitle customtitle joindate cookieuser daysprune lastvisit lastactivity lastpost posts timezoneoffset emailnotification buddylist ignorelist pmfolders receivepm emailonpm pmpopup avatarid avatarrevision options birthday maxposts startofweek ipaddress referrerid nosessionhash autorefresh messagepopup inforum ratenum ratetotal allowrate
468
128 0 33 33 8 :D 996121863 1 0 2 996122850 2 0 0 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
470
CREATE TABLE t1 (a int primary key, b int, c int);
471
INSERT t1 VALUES (1,2,3);
472
CREATE TABLE t2 (a int primary key, b int, c int);
473
INSERT t2 VALUES (3,4,5);
474
SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c;
478
CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
479
INSERT INTO t1 VALUES (1, 'aaaaa');
480
INSERT INTO t1 VALUES (3, 'aaaaa');
481
INSERT INTO t1 VALUES (2, 'eeeeeee');
482
select distinct left(name,1) as name from t1;
488
ID int(11) NOT NULL auto_increment,
489
NAME varchar(75) DEFAULT '' NOT NULL,
490
LINK_ID int(11) DEFAULT '0' NOT NULL,
493
KEY LINK_ID (LINK_ID)
495
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0);
496
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (2,'Jack',0);
497
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (3,'Bill',0);
499
ID int(11) NOT NULL auto_increment,
500
NAME varchar(150) DEFAULT '' NOT NULL,
505
t2.id AS key_link_id,
508
LEFT JOIN t2 ON t1.link_id=t2.id
515
html varchar(5) default NULL,
516
rin int(11) default '0',
517
rout int(11) default '0'
519
INSERT INTO t1 VALUES ('1',1,0);
520
SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin;
524
CREATE TABLE t1 (a int);
525
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
526
SELECT DISTINCT a, 1 FROM t1;
533
SELECT DISTINCT 1, a FROM t1;
540
CREATE TABLE t2 (a int, b int);
541
INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5);
542
SELECT DISTINCT a, b, 2 FROM t2;
549
SELECT DISTINCT 2, a, b FROM t2;
556
SELECT DISTINCT a, 2, b FROM t2;
564
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
565
INSERT INTO t1 VALUES (1,1), (2,1), (3,1);
566
EXPLAIN SELECT DISTINCT a FROM t1;
567
id select_type table type possible_keys key key_len ref rows Extra
568
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 Using index
569
EXPLAIN SELECT DISTINCT a,b FROM t1;
570
id select_type table type possible_keys key key_len ref rows Extra
571
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL
572
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
573
id select_type table type possible_keys key key_len ref rows Extra
574
1 SIMPLE t1_1 ALL NULL NULL NULL NULL 3 Using temporary
575
1 SIMPLE t1_2 index NULL PRIMARY 4 NULL 3 Using index; Distinct; Using join buffer (Block Nested Loop)
576
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
577
WHERE t1_1.a = t1_2.a;
578
id select_type table type possible_keys key key_len ref rows Extra
579
1 SIMPLE t1_1 ALL PRIMARY NULL NULL NULL 3 Using temporary
580
1 SIMPLE t1_2 eq_ref PRIMARY PRIMARY 4 test.t1_1.a 1 Using index; Distinct
581
EXPLAIN SELECT a FROM t1 GROUP BY a;
582
id select_type table type possible_keys key key_len ref rows Extra
583
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 Using index
584
EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
585
id select_type table type possible_keys key key_len ref rows Extra
586
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
587
EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
588
id select_type table type possible_keys key key_len ref rows Extra
589
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
590
CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT,
592
INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
593
EXPLAIN SELECT DISTINCT a FROM t2;
594
id select_type table type possible_keys key key_len ref rows Extra
595
1 SIMPLE t2 index PRIMARY PRIMARY 8 NULL 3 Using index
596
EXPLAIN SELECT DISTINCT a,a FROM t2;
597
id select_type table type possible_keys key key_len ref rows Extra
598
1 SIMPLE t2 index PRIMARY PRIMARY 8 NULL 3 Using index
599
EXPLAIN SELECT DISTINCT b,a FROM t2;
600
id select_type table type possible_keys key key_len ref rows Extra
601
1 SIMPLE t2 index PRIMARY PRIMARY 8 NULL 3 Using index
602
EXPLAIN SELECT DISTINCT a,c FROM t2;
603
id select_type table type possible_keys key key_len ref rows Extra
604
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary
605
EXPLAIN SELECT DISTINCT c,a,b FROM t2;
606
id select_type table type possible_keys key key_len ref rows Extra
607
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 NULL
608
EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
609
id select_type table type possible_keys key key_len ref rows Extra
610
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
611
CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);
612
EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
613
id select_type table type possible_keys key key_len ref rows Extra
614
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using filesort
616
create table t1 (id int, dsc varchar(50));
617
insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three");
618
select distinct id, IFNULL(dsc, '-') from t1;
624
CREATE TABLE t1 (a int primary key, b int);
625
INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2);
626
explain SELECT DISTINCT a, b FROM t1 ORDER BY b;
627
id select_type table type possible_keys key key_len ref rows Extra
628
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
629
SELECT DISTINCT a, b FROM t1 ORDER BY b;
636
ID int(11) NOT NULL auto_increment,
637
x varchar(20) default NULL,
638
y decimal(10,0) default NULL,
641
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
642
INSERT INTO t1 VALUES
649
select count(distinct x,y) from t1;
652
select count(distinct concat(x,y)) from t1;
653
count(distinct concat(x,y))
656
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
657
INSERT INTO t1 VALUES (1, 101);
658
INSERT INTO t1 SELECT a + 1, a + 101 FROM t1;
659
INSERT INTO t1 SELECT a + 2, a + 102 FROM t1;
660
INSERT INTO t1 SELECT a + 4, a + 104 FROM t1;
661
INSERT INTO t1 SELECT a + 8, a + 108 FROM t1;
662
EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
663
id select_type table type possible_keys key key_len ref rows Extra
664
1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 16 Using where; Using index
665
SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
668
DROP TABLE IF EXISTS t1;
669
CREATE TABLE t1 (id INT NOT NULL, fruit_id INT NOT NULL, fruit_name varchar(20)
671
INSERT INTO t1 VALUES (1,1,'ORANGE');
672
INSERT INTO t1 VALUES (2,2,'APPLE');
673
INSERT INTO t1 VALUES (3,2,'APPLE');
674
INSERT INTO t1 VALUES (4,3,'PEAR');
675
SELECT DISTINCT fruit_id, fruit_name INTO @v1, @v2 FROM t1 WHERE fruit_name =
680
SELECT DISTINCT fruit_id, fruit_name INTO @v3, @v4 FROM t1 GROUP BY fruit_id,
681
fruit_name HAVING fruit_name = 'APPLE';
685
SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE
686
fruit_name = 'APPLE';
687
SELECT @v5, @v6, @v7, @v8;
690
SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1
691
WHERE fruit_name = 'APPLE';
692
SELECT @v5, @v6, @v7, @v8, @v9, @v10;
693
@v5 @v6 @v7 @v8 @v9 @v10
694
2 APPLE 2 APPLE 4 APPLEAPPLE
695
SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO
696
@v13, @v14 FROM t1 WHERE fruit_name = 'APPLE';
697
SELECT @v11, @v12, @v13, @v14;
699
4 APPLEAPPLE 4 APPLEAPPLE
700
SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE';
704
SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name =
709
DROP TABLE IF EXISTS t2;
710
CREATE TABLE t2 (fruit_id INT NOT NULL, fruit_name varchar(20)
712
SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE
713
'../../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE';
714
LOAD DATA INFILE '../../tmp/data1.tmp' INTO TABLE t2;
715
SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE
716
'../../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE';
717
LOAD DATA INFILE '../../tmp/data2.tmp' INTO TABLE t2;
727
CREATE TABLE t1 (a CHAR(1));
728
INSERT INTO t1 VALUES('A'), (0);
729
SELECT a FROM t1 WHERE a=0;
734
Warning 1292 Truncated incorrect DOUBLE value: 'A'
735
SELECT DISTINCT a FROM t1 WHERE a=0;
740
Warning 1292 Truncated incorrect DOUBLE value: 'A'
742
CREATE TABLE t1 (a DATE);
743
INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');
744
EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');
745
id select_type table type possible_keys key key_len ref rows Extra
746
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
747
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
748
EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
749
WHERE ADDDATE(a,1) = '2002-08-03');
750
id select_type table type possible_keys key key_len ref rows Extra
751
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
752
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
753
CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
754
INSERT INTO t2 VALUES (0xf6);
755
INSERT INTO t2 VALUES ('oe');
756
SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt;
760
(SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;
764
CREATE TABLE t1 (a INT, UNIQUE (a));
765
INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3);
766
EXPLAIN SELECT DISTINCT a FROM t1;
767
id select_type table type possible_keys key key_len ref rows Extra
768
1 SIMPLE t1 index a a 5 NULL 6 Using index
769
SELECT DISTINCT a FROM t1;
776
EXPLAIN SELECT a FROM t1 GROUP BY a;
777
id select_type table type possible_keys key key_len ref rows Extra
778
1 SIMPLE t1 index a a 5 NULL 6 Using index
779
SELECT a FROM t1 GROUP BY a;
787
CREATE TABLE t1 (a INT, b INT);
788
INSERT INTO t1 VALUES(1,1),(1,2),(1,3);
789
SELECT DISTINCT a, b FROM t1;
794
SELECT DISTINCT a, a, b FROM t1;
801
CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT,
802
PRIMARY KEY(a,b,c,d,e),
805
INSERT INTO t1(a, b, c) VALUES (1, 1, 1),
811
EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
812
id select_type table type possible_keys key key_len ref rows Extra
813
1 SIMPLE t1 index PRIMARY,a a 16 NULL 6 Using index
814
SELECT DISTINCT a, b, d, c FROM t1;
824
# Bug #46159: simple query that never returns
826
SET @old_max_heap_table_size = @@max_heap_table_size;
827
SET @@max_heap_table_size = 16384;
828
SET @old_sort_buffer_size = @@sort_buffer_size;
829
SET @@sort_buffer_size = 32804;
830
CREATE TABLE t1(c1 int, c2 VARCHAR(20));
831
INSERT INTO t1 VALUES (1, '1'), (1, '1'), (2, '2'), (3, '1'), (3, '1'), (4, '4');
832
INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1;
833
INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1;
834
INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1;
835
INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1;
836
INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1;
837
INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1;
838
INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1;
839
INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1;
840
SELECT c1, c2, COUNT(*) FROM t1 GROUP BY c1 LIMIT 4;
846
SELECT DISTINCT c2 FROM t1 GROUP BY c1 HAVING COUNT(*) > 1;
851
SET @@sort_buffer_size = @old_sort_buffer_size;
852
SET @@max_heap_table_size = @old_max_heap_table_size;
855
# Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb
857
CREATE TABLE t1 (a INT(1), b INT(1));
858
INSERT INTO t1 VALUES (1111, 2222), (3333, 4444);
859
SELECT DISTINCT CONCAT(a,b) AS c FROM t1 ORDER BY 1;
866
# Bug#13335170 - ASSERT IN
867
# PLAN_CHANGE_WATCHDOG::~PLAN_CHANGE_WATCHDOG() ON SELECT DISTINCT
870
col_int_key int(11) NOT NULL,
871
col_time_key time NOT NULL,
872
col_datetime_key datetime NOT NULL,
873
KEY col_int_key (col_int_key),
874
KEY col_time_key (col_time_key),
875
KEY col_datetime_key (col_datetime_key)
877
INSERT INTO t1 VALUES (7,'06:17:39','2003-08-21 00:00:00');
878
SELECT DISTINCT col_int_key
880
WHERE col_int_key IN ( 18, 6, 84, 4, 0, 2, 8, 3, 7, 9, 1 )
881
AND col_datetime_key BETWEEN '2001-08-04' AND '2003-06-13'
882
ORDER BY col_time_key
887
# BUG#13540692: WRONG NULL HANDLING WITH RIGHT JOIN +
888
# DISTINCT OR ORDER BY
894
INSERT INTO t1 VALUES (1,2), (3,3);
896
EXPLAIN SELECT DISTINCT subselect.b
898
(SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect
899
ON t1.a = subselect.b
901
id select_type table type possible_keys key key_len ref rows Extra
902
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary
903
1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 5 test.t1.a 2 NULL
904
2 DERIVED it_a ALL NULL NULL NULL NULL 2 NULL
905
2 DERIVED it_b ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
906
SELECT DISTINCT subselect.b
908
(SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect
909
ON t1.a = subselect.b
916
# BUG#13538387: WRONG RESULT ON SELECT DISTINCT + LEFT JOIN +
917
# LIMIT + MIX OF MYISAM AND INNODB
919
CREATE TABLE t1 (a INT);
920
INSERT INTO t1 VALUES (2),(3);
921
CREATE TABLE t2 (b INT);
927
INSERT INTO t3 VALUES (2001,1), (2007,2);
928
EXPLAIN SELECT DISTINCT t3.a AS t3_date
930
LEFT JOIN t2 ON false
931
LEFT JOIN t3 ON t2.b = t3.b
933
id select_type table type possible_keys key key_len ref rows Extra
934
1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found
935
1 SIMPLE t3 const PRIMARY NULL NULL NULL 1 Using temporary
936
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Distinct
937
SELECT DISTINCT t3.a AS t3_date
939
LEFT JOIN t2 ON false
940
LEFT JOIN t3 ON t2.b = t3.b