1drop table if exists t1,t2,t3; 2CREATE TABLE t1 (id int,facility char(20)); 3CREATE TABLE t2 (facility char(20)); 4INSERT INTO t1 VALUES (NULL,NULL); 5INSERT INTO t1 VALUES (-1,''); 6INSERT INTO t1 VALUES (0,''); 7INSERT INTO t1 VALUES (1,'/L'); 8INSERT INTO t1 VALUES (2,'A01'); 9INSERT INTO t1 VALUES (3,'ANC'); 10INSERT INTO t1 VALUES (4,'F01'); 11INSERT INTO t1 VALUES (5,'FBX'); 12INSERT INTO t1 VALUES (6,'MT'); 13INSERT INTO t1 VALUES (7,'P'); 14INSERT INTO t1 VALUES (8,'RV'); 15INSERT INTO t1 VALUES (9,'SRV'); 16INSERT INTO t1 VALUES (10,'VMT'); 17INSERT INTO t2 SELECT DISTINCT FACILITY FROM t1; 18select id from t1 group by id; 19id 20NULL 21-1 220 231 242 253 264 275 286 297 308 319 3210 33select * from t1 order by id; 34id facility 35NULL NULL 36-1 370 381 /L 392 A01 403 ANC 414 F01 425 FBX 436 MT 447 P 458 RV 469 SRV 4710 VMT 48select id-5,facility from t1 order by "id-5"; 49id-5 facility 50NULL NULL 51-6 52-5 53-4 /L 54-3 A01 55-2 ANC 56-1 F01 570 FBX 581 MT 592 P 603 RV 614 SRV 625 VMT 63select id,concat(facility) from t1 group by id ; 64id concat(facility) 65NULL NULL 66-1 670 681 /L 692 A01 703 ANC 714 F01 725 FBX 736 MT 747 P 758 RV 769 SRV 7710 VMT 78select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a; 79a max(id) b 8010 10 VMT 819 9 SRV 828 8 RV 837 7 P 846 6 MT 855 5 FBX 864 4 F01 873 3 ANC 882 2 A01 891 1 /L 90-1 -1 910 0 92NULL NULL NULL 93select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp; 94grp count(*) 95NULL 1 960 6 971 6 98SELECT DISTINCT FACILITY FROM t1; 99FACILITY 100NULL 101 102/L 103A01 104ANC 105F01 106FBX 107MT 108P 109RV 110SRV 111VMT 112SELECT FACILITY FROM t2; 113FACILITY 114NULL 115 116/L 117A01 118ANC 119F01 120FBX 121MT 122P 123RV 124SRV 125VMT 126SELECT count(*) from t1,t2 where t1.facility=t2.facility; 127count(*) 12812 129select count(facility) from t1; 130count(facility) 13112 132select count(*) from t1; 133count(*) 13413 135select count(*) from t1 where facility IS NULL; 136count(*) 1371 138select count(*) from t1 where facility = NULL; 139count(*) 1400 141select count(*) from t1 where facility IS NOT NULL; 142count(*) 14312 144select count(*) from t1 where id IS NULL; 145count(*) 1461 147select count(*) from t1 where id IS NOT NULL; 148count(*) 14912 150drop table t1,t2; 151CREATE TABLE t1 (UserId int(11) DEFAULT '0' NOT NULL); 152INSERT INTO t1 VALUES (20); 153INSERT INTO t1 VALUES (27); 154SELECT UserId FROM t1 WHERE Userid=22; 155UserId 156SELECT UserId FROM t1 WHERE UserId=22 group by Userid; 157UserId 158SELECT DISTINCT UserId FROM t1 WHERE UserId=22 group by Userid; 159UserId 160SELECT DISTINCT UserId FROM t1 WHERE UserId=22; 161UserId 162drop table t1; 163CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned); 164INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1); 165CREATE TABLE t2 (a int(10) unsigned not null, key (A)); 166INSERT INTO t2 VALUES (1),(2); 167CREATE TABLE t3 (a int(10) unsigned, key(A), b text); 168INSERT INTO t3 VALUES (1,'1'),(2,'2'); 169SELECT DISTINCT t3.b FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; 170b 1711 172INSERT INTO t2 values (1),(2),(3); 173INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2'); 174explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; 175id select_type table type possible_keys key key_len ref rows Extra 1761 SIMPLE t2 index a a 4 NULL 5 Using index; Using temporary 1771 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using where 1781 SIMPLE t3 ref a a 5 test.t1.b 2 Using index 179SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; 180a 1811 182create temporary table t4 select * from t3; 183insert into t3 select * from t4; 184insert into t4 select * from t3; 185insert into t3 select * from t4; 186insert into t4 select * from t3; 187insert into t3 select * from t4; 188insert into t4 select * from t3; 189insert into t3 select * from t4; 190explain select distinct t1.a from t1,t3 where t1.a=t3.a; 191id select_type table type possible_keys key key_len ref rows Extra 1921 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary 1931 SIMPLE t3 ref a a 5 test.t1.a 11 Using index; Distinct 194select distinct t1.a from t1,t3 where t1.a=t3.a; 195a 1961 1972 198select distinct 1 from t1,t3 where t1.a=t3.a; 1991 2001 201explain SELECT distinct t1.a from t1; 202id select_type table type possible_keys key key_len ref rows Extra 2031 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index 204explain SELECT distinct t1.a from t1 order by a desc; 205id select_type table type possible_keys key key_len ref rows Extra 2061 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index 207explain SELECT t1.a from t1 group by a order by a desc; 208id select_type table type possible_keys key key_len ref rows Extra 2091 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index 210explain SELECT distinct t1.a from t1 order by a desc limit 1; 211id select_type table type possible_keys key key_len ref rows Extra 2121 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index 213explain SELECT distinct a from t3 order by a desc limit 2; 214id select_type table type possible_keys key key_len ref rows Extra 2151 SIMPLE t3 index NULL a 5 NULL 40 Using index 216explain SELECT distinct a,b from t3 order by a+1; 217id select_type table type possible_keys key key_len ref rows Extra 2181 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort 219explain SELECT distinct a,b from t3 order by a limit 2; 220id select_type table type possible_keys key key_len ref rows Extra 2211 SIMPLE t3 index NULL a 5 NULL 2 Using temporary 222explain SELECT a,b from t3 group by a,b order by a+1; 223id select_type table type possible_keys key key_len ref rows Extra 2241 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort 225drop table t1,t2,t3,t4; 226CREATE TABLE t1 (name varchar(255)); 227INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae'); 228SELECT DISTINCT * FROM t1 LIMIT 2; 229name 230aa 231ab 232SELECT DISTINCT name FROM t1 LIMIT 2; 233name 234aa 235ab 236SELECT DISTINCT 1 FROM t1 LIMIT 2; 2371 2381 239drop table t1; 240CREATE TABLE t1 ( 241ID int(11) NOT NULL auto_increment, 242NAME varchar(75) DEFAULT '' NOT NULL, 243LINK_ID int(11) DEFAULT '0' NOT NULL, 244PRIMARY KEY (ID), 245KEY NAME (NAME), 246KEY LINK_ID (LINK_ID) 247); 248INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0),(2,'Jack',0),(3,'Bill',0); 249CREATE TABLE t2 ( 250ID int(11) NOT NULL auto_increment, 251NAME varchar(150) DEFAULT '' NOT NULL, 252PRIMARY KEY (ID), 253KEY NAME (NAME) 254); 255SELECT DISTINCT 256t2.id AS key_link_id, 257t2.name AS link 258FROM t1 259LEFT JOIN t2 ON t1.link_id=t2.id 260GROUP BY t1.id 261ORDER BY link; 262key_link_id link 263NULL NULL 264drop table t1,t2; 265create table t1 ( 266id int not null, 267name tinytext not null, 268unique (id) 269); 270create table t2 ( 271id int not null, 272idx int not null, 273unique (id, idx) 274); 275create table t3 ( 276id int not null, 277idx int not null, 278unique (id, idx) 279); 280insert into t1 values (1,'yes'), (2,'no'); 281insert into t2 values (1,1); 282insert into t3 values (1,1); 283set @save_join_cache_level=@@join_cache_level; 284set join_cache_level=1; 285EXPLAIN 286SELECT DISTINCT 287t1.id 288from 289t1 290straight_join 291t2 292straight_join 293t3 294straight_join 295t1 as j_lj_t2 left join t2 as t2_lj 296on j_lj_t2.id=t2_lj.id 297straight_join 298t1 as j_lj_t3 left join t3 as t3_lj 299on j_lj_t3.id=t3_lj.id 300WHERE 301((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2)) 302AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); 303id select_type table type possible_keys key key_len ref rows Extra 3041 SIMPLE t1 index id id 4 NULL 2 Using index; Using temporary 3051 SIMPLE t2 index id id 8 NULL 1 Using index; Using join buffer (flat, BNL join) 3061 SIMPLE t3 index id id 8 NULL 1 Using index; Using join buffer (flat, BNL join) 3071 SIMPLE j_lj_t2 index id id 4 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) 3081 SIMPLE t2_lj ref id id 4 test.j_lj_t2.id 1 Using where; Using index 3091 SIMPLE j_lj_t3 index id id 4 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) 3101 SIMPLE t3_lj ref id id 4 test.j_lj_t3.id 1 Using where; Using index; Distinct 311SELECT DISTINCT 312t1.id 313from 314t1 315straight_join 316t2 317straight_join 318t3 319straight_join 320t1 as j_lj_t2 left join t2 as t2_lj 321on j_lj_t2.id=t2_lj.id 322straight_join 323t1 as j_lj_t3 left join t3 as t3_lj 324on j_lj_t3.id=t3_lj.id 325WHERE 326((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2)) 327AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); 328id 3292 330drop table t1,t2,t3; 331set join_cache_level=@save_join_cache_level; 332create table t1 (a int not null, b int not null, t time); 333insert 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"); 334select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b; 335a sec_to_time(sum(time_to_sec(t))) 3361 00:06:15 3371 00:36:30 3381 00:36:30 339select distinct a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b; 340a sec_to_time(sum(time_to_sec(t))) 3411 00:06:15 3421 00:36:30 343create table t2 (a int not null primary key, b int); 344insert into t2 values (1,1),(2,2),(3,3); 345select 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; 346a sec_to_time(sum(time_to_sec(t))) 3471 00:06:15 3481 00:36:30 3491 00:36:30 350select 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; 351a sec_to_time(sum(time_to_sec(t))) 3521 00:06:15 3531 00:36:30 354drop table t1,t2; 355create table t1 (a int not null,b char(5), c text); 356insert into t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4); 357select distinct a from t1 group by b,a having a > 2 order by a desc; 358a 3594 3603 361select distinct a,c from t1 group by b,c,a having a > 2 order by a desc; 362a c 3634 NULL 3643 NULL 365drop table t1; 366create table t1 (a char(1), key(a)) engine=myisam; 367insert into t1 values('1'),('1'); 368select * from t1 where a >= '1'; 369a 3701 3711 372select distinct a from t1 order by a desc; 373a 3741 375select distinct a from t1 where a >= '1' order by a desc; 376a 3771 378drop table t1; 379CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME); 380CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10)); 381INSERT INTO t1 (email, infoID, dateentered) VALUES 382('test1@testdomain.com', 1, '2002-07-30 22:56:38'), 383('test1@testdomain.com', 1, '2002-07-27 22:58:16'), 384('test2@testdomain.com', 1, '2002-06-19 15:22:19'), 385('test2@testdomain.com', 2, '2002-06-18 14:23:47'), 386('test3@testdomain.com', 1, '2002-05-19 22:17:32'); 387INSERT INTO t2(infoID, shipcode) VALUES 388(1, 'Z001'), 389(2, 'R002'); 390SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID; 391email shipcode 392test1@testdomain.com Z001 393test2@testdomain.com Z001 394test2@testdomain.com R002 395test3@testdomain.com Z001 396SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC; 397email 398test1@testdomain.com 399test2@testdomain.com 400test3@testdomain.com 401SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC; 402email shipcode 403test1@testdomain.com Z001 404test2@testdomain.com Z001 405test2@testdomain.com R002 406test3@testdomain.com Z001 407drop table t1,t2; 408CREATE 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; 409INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0); 410CREATE 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; 411INSERT 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); 412SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid); 413privatemessageid 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 414128 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 415DROP TABLE t1,t2; 416CREATE TABLE t1 (a int primary key, b int, c int); 417INSERT t1 VALUES (1,2,3); 418CREATE TABLE t2 (a int primary key, b int, c int); 419INSERT t2 VALUES (3,4,5); 420SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c; 421a b 4221 4 423DROP TABLE t1,t2; 424CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ; 425INSERT INTO t1 VALUES (1, 'aaaaa'); 426INSERT INTO t1 VALUES (3, 'aaaaa'); 427INSERT INTO t1 VALUES (2, 'eeeeeee'); 428select distinct left(name,1) as name from t1; 429name 430a 431e 432drop table t1; 433CREATE TABLE t1 ( 434ID int(11) NOT NULL auto_increment, 435NAME varchar(75) DEFAULT '' NOT NULL, 436LINK_ID int(11) DEFAULT '0' NOT NULL, 437PRIMARY KEY (ID), 438KEY NAME (NAME), 439KEY LINK_ID (LINK_ID) 440); 441INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0); 442INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (2,'Jack',0); 443INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (3,'Bill',0); 444CREATE TABLE t2 ( 445ID int(11) NOT NULL auto_increment, 446NAME varchar(150) DEFAULT '' NOT NULL, 447PRIMARY KEY (ID), 448KEY NAME (NAME) 449); 450SELECT DISTINCT 451t2.id AS key_link_id, 452t2.name AS link 453FROM t1 454LEFT JOIN t2 ON t1.link_id=t2.id 455GROUP BY t1.id 456ORDER BY link; 457key_link_id link 458NULL NULL 459drop table t1,t2; 460CREATE TABLE t1 ( 461html varchar(5) default NULL, 462rin int(11) default '0', 463rout int(11) default '0' 464) ENGINE=MyISAM; 465INSERT INTO t1 VALUES ('1',1,0); 466SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; 467html prod 4681 0.0000 469drop table t1; 470CREATE TABLE t1 (a int); 471INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 472SELECT DISTINCT a, 1 FROM t1; 473a 1 4741 1 4752 1 4763 1 4774 1 4785 1 479SELECT DISTINCT 1, a FROM t1; 4801 a 4811 1 4821 2 4831 3 4841 4 4851 5 486CREATE TABLE t2 (a int, b int); 487INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5); 488SELECT DISTINCT a, b, 2 FROM t2; 489a b 2 4901 1 2 4912 2 2 4922 3 2 4932 4 2 4943 5 2 495SELECT DISTINCT 2, a, b FROM t2; 4962 a b 4972 1 1 4982 2 2 4992 2 3 5002 2 4 5012 3 5 502SELECT DISTINCT a, 2, b FROM t2; 503a 2 b 5041 2 1 5052 2 2 5062 2 3 5072 2 4 5083 2 5 509DROP TABLE t1,t2; 510CREATE TABLE t1(a INT PRIMARY KEY, b INT); 511INSERT INTO t1 VALUES (1,1), (2,1), (3,1); 512EXPLAIN SELECT DISTINCT a FROM t1; 513id select_type table type possible_keys key key_len ref rows Extra 5141 SIMPLE t1 index NULL PRIMARY 4 NULL 3 Using index 515EXPLAIN SELECT DISTINCT a,b FROM t1; 516id select_type table type possible_keys key key_len ref rows Extra 5171 SIMPLE t1 ALL NULL NULL NULL NULL 3 518EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2; 519id select_type table type possible_keys key key_len ref rows Extra 5201 SIMPLE t1_1 ALL NULL NULL NULL NULL 3 Using temporary 5211 SIMPLE t1_2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join) 522EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2 523WHERE t1_1.a = t1_2.a; 524id select_type table type possible_keys key key_len ref rows Extra 5251 SIMPLE t1_1 ALL PRIMARY NULL NULL NULL 3 Using temporary 5261 SIMPLE t1_2 eq_ref PRIMARY PRIMARY 4 test.t1_1.a 1 Using index; Distinct 527EXPLAIN SELECT a FROM t1 GROUP BY a; 528id select_type table type possible_keys key key_len ref rows Extra 5291 SIMPLE t1 index NULL PRIMARY 4 NULL 3 Using index 530EXPLAIN SELECT a,b FROM t1 GROUP BY a,b; 531id select_type table type possible_keys key key_len ref rows Extra 5321 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 533EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b; 534id select_type table type possible_keys key key_len ref rows Extra 5351 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 536CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, 537PRIMARY KEY (a,b)); 538INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); 539EXPLAIN SELECT DISTINCT a FROM t2; 540id select_type table type possible_keys key key_len ref rows Extra 5411 SIMPLE t2 index NULL PRIMARY 8 NULL 3 Using index 542EXPLAIN SELECT DISTINCT a,a FROM t2; 543id select_type table type possible_keys key key_len ref rows Extra 5441 SIMPLE t2 index NULL PRIMARY 8 NULL 3 Using index 545EXPLAIN SELECT DISTINCT b,a FROM t2; 546id select_type table type possible_keys key key_len ref rows Extra 5471 SIMPLE t2 index NULL PRIMARY 8 NULL 3 Using index 548EXPLAIN SELECT DISTINCT a,c FROM t2; 549id select_type table type possible_keys key key_len ref rows Extra 5501 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary 551EXPLAIN SELECT DISTINCT c,a,b FROM t2; 552id select_type table type possible_keys key key_len ref rows Extra 5531 SIMPLE t2 ALL NULL NULL NULL NULL 3 554EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d; 555id select_type table type possible_keys key key_len ref rows Extra 5561 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort 557CREATE UNIQUE INDEX c_b_unq ON t2 (c,b); 558EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d; 559id select_type table type possible_keys key key_len ref rows Extra 5601 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using filesort 561DROP TABLE t1,t2; 562create table t1 (id int, dsc varchar(50)); 563insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three"); 564select distinct id, IFNULL(dsc, '-') from t1; 565id IFNULL(dsc, '-') 5661 line number one 5672 line number two 5683 line number three 569drop table t1; 570CREATE TABLE t1 (a int primary key, b int); 571INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2); 572explain SELECT DISTINCT a, b FROM t1 ORDER BY b; 573id select_type table type possible_keys key key_len ref rows Extra 5741 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 575SELECT DISTINCT a, b FROM t1 ORDER BY b; 576a b 5771 1 5783 2 5792 3 580DROP TABLE t1; 581CREATE TABLE t1 ( 582ID int(11) NOT NULL auto_increment, 583x varchar(20) default NULL, 584y decimal(10,0) default NULL, 585PRIMARY KEY (ID), 586KEY (y) 587) ENGINE=MyISAM DEFAULT CHARSET=latin1; 588INSERT INTO t1 VALUES 589(1,'ba','-1'), 590(2,'ba','1150'), 591(306,'ba','-1'), 592(307,'ba','1150'), 593(611,'ba','-1'), 594(612,'ba','1150'); 595select count(distinct x,y) from t1; 596count(distinct x,y) 5972 598select count(distinct concat(x,y)) from t1; 599count(distinct concat(x,y)) 6002 601drop table t1; 602CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b)); 603INSERT INTO t1 VALUES (1, 101); 604INSERT INTO t1 SELECT a + 1, a + 101 FROM t1; 605INSERT INTO t1 SELECT a + 2, a + 102 FROM t1; 606INSERT INTO t1 SELECT a + 4, a + 104 FROM t1; 607INSERT INTO t1 SELECT a + 8, a + 108 FROM t1; 608EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; 609id select_type table type possible_keys key key_len ref rows Extra 6101 SIMPLE t1 index NULL PRIMARY 8 NULL 16 Using where; Using index 611SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; 612a a 613DROP TABLE t1; 614DROP TABLE IF EXISTS t1; 615CREATE TABLE t1 (id INT NOT NULL, fruit_id INT NOT NULL, fruit_name varchar(20) 616default NULL); 617INSERT INTO t1 VALUES (1,1,'ORANGE'); 618INSERT INTO t1 VALUES (2,2,'APPLE'); 619INSERT INTO t1 VALUES (3,2,'APPLE'); 620INSERT INTO t1 VALUES (4,3,'PEAR'); 621SELECT DISTINCT fruit_id, fruit_name INTO @v1, @v2 FROM t1 WHERE fruit_name = 622'APPLE'; 623SELECT @v1, @v2; 624@v1 @v2 6252 APPLE 626SELECT DISTINCT fruit_id, fruit_name INTO @v3, @v4 FROM t1 GROUP BY fruit_id, 627fruit_name HAVING fruit_name = 'APPLE'; 628SELECT @v3, @v4; 629@v3 @v4 6302 APPLE 631SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE 632fruit_name = 'APPLE'; 633SELECT @v5, @v6, @v7, @v8; 634@v5 @v6 @v7 @v8 6352 APPLE 2 APPLE 636SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1 637WHERE fruit_name = 'APPLE'; 638SELECT @v5, @v6, @v7, @v8, @v9, @v10; 639@v5 @v6 @v7 @v8 @v9 @v10 6402 APPLE 2 APPLE 4 APPLEAPPLE 641SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO 642@v13, @v14 FROM t1 WHERE fruit_name = 'APPLE'; 643SELECT @v11, @v12, @v13, @v14; 644@v11 @v12 @v13 @v14 6454 APPLEAPPLE 4 APPLEAPPLE 646SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE'; 647SELECT @v15, @v16; 648@v15 @v16 6494 APPLEAPPLE 650SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name = 651'APPLE'; 652SELECT @v17, @v18; 653@v17 @v18 6544 Bob 655DROP TABLE IF EXISTS t2; 656CREATE TABLE t2 (fruit_id INT NOT NULL, fruit_name varchar(20) 657default NULL); 658SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE 659'../../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE'; 660LOAD DATA INFILE '../../tmp/data1.tmp' INTO TABLE t2; 661SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE 662'../../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE'; 663LOAD DATA INFILE '../../tmp/data2.tmp' INTO TABLE t2; 664SELECT @v19, @v20; 665@v19 @v20 6662 APPLE 667SELECT * FROM t2; 668fruit_id fruit_name 6692 APPLE 6702 APPLE 671DROP TABLE t1; 672DROP TABLE t2; 673CREATE TABLE t1 (a CHAR(1)); 674INSERT INTO t1 VALUES('A'), (0); 675SELECT a FROM t1 WHERE a=0; 676a 677A 6780 679Warnings: 680Warning 1292 Truncated incorrect DOUBLE value: 'A' 681SELECT DISTINCT a FROM t1 WHERE a=0; 682a 683A 6840 685Warnings: 686Warning 1292 Truncated incorrect DOUBLE value: 'A' 687DROP TABLE t1; 688CREATE TABLE t1 (a DATE); 689INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06'); 690EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03'); 691id select_type table type possible_keys key key_len ref rows Extra 6921 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 6932 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 694EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1 695WHERE ADDDATE(a,1) = '2002-08-03'); 696id select_type table type possible_keys key key_len ref rows Extra 6971 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 6982 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 699CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci); 700INSERT INTO t2 VALUES (0xf6); 701INSERT INTO t2 VALUES ('oe'); 702SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt; 703COUNT(*) 7042 705SELECT COUNT(*) FROM 706(SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt; 707COUNT(*) 7082 709DROP TABLE t1, t2; 710CREATE TABLE t1 (a INT, UNIQUE (a)); 711INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3); 712EXPLAIN SELECT DISTINCT a FROM t1; 713id select_type table type possible_keys key key_len ref rows Extra 7141 SIMPLE t1 index NULL a 5 NULL 6 Using index 715SELECT DISTINCT a FROM t1; 716a 717NULL 7181 7192 7203 7214 722EXPLAIN SELECT a FROM t1 GROUP BY a; 723id select_type table type possible_keys key key_len ref rows Extra 7241 SIMPLE t1 index NULL a 5 NULL 6 Using index 725SELECT a FROM t1 GROUP BY a; 726a 727NULL 7281 7292 7303 7314 732DROP TABLE t1; 733CREATE TABLE t1 (a INT, b INT); 734INSERT INTO t1 VALUES(1,1),(1,2),(1,3); 735SELECT DISTINCT a, b FROM t1; 736a b 7371 1 7381 2 7391 3 740SELECT DISTINCT a, a, b FROM t1; 741a a b 7421 1 1 7431 1 2 7441 1 3 745DROP TABLE t1; 746End of 5.0 tests 747CREATE TABLE t1(a INT, b INT, c INT, d INT default 0, e INT default 0, 748PRIMARY KEY(a,b,c,d,e), 749KEY(a,b,d,c) 750); 751INSERT INTO t1(a, b, c) VALUES (1, 1, 1), 752(1, 1, 2), 753(1, 1, 3), 754(1, 2, 1), 755(1, 2, 2), 756(1, 2, 3); 757EXPLAIN SELECT DISTINCT a, b, d, c FROM t1; 758id select_type table type possible_keys key key_len ref rows Extra 7591 SIMPLE t1 index NULL a 16 NULL 6 Using index 760SELECT DISTINCT a, b, d, c FROM t1; 761a b d c 7621 1 0 1 7631 1 0 2 7641 1 0 3 7651 2 0 1 7661 2 0 2 7671 2 0 3 768DROP TABLE t1; 769# 770# Bug #46159: simple query that never returns 771# 772SET @old_max_heap_table_size = @@max_heap_table_size; 773SET @@max_heap_table_size = 16384; 774SET @old_sort_buffer_size = @@sort_buffer_size; 775SET @@sort_buffer_size = 32804; 776CREATE TABLE t1(c1 int, c2 VARCHAR(20)); 777INSERT INTO t1 VALUES (1, '1'), (1, '1'), (2, '2'), (3, '1'), (3, '1'), (4, '4'); 778INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 779INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 780INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 781INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 782INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 783INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 784INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 785INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 786SELECT c1, c2, COUNT(*) FROM t1 GROUP BY c1 LIMIT 4; 787c1 c2 COUNT(*) 7881 1 2 7892 2 1 7903 1 2 7914 4 1 792SELECT DISTINCT c2 FROM t1 GROUP BY c1 HAVING COUNT(*) > 1; 793c2 7941 7955 796DROP TABLE t1; 797SET @@sort_buffer_size = @old_sort_buffer_size; 798SET @@max_heap_table_size = @old_max_heap_table_size; 799End of 5.1 tests 800create table t1 (a varchar(100)); 801insert t1 values ('2010-10-10'), ('20101010'); 802select * from t1 where a = DATE('2010-10-10'); 803a 8042010-10-10 80520101010 806select distinct a from t1 where a = DATE('2010-10-10'); 807a 8082010-10-10 80920101010 810explain select distinct a from t1 where a = DATE('2010-10-10'); 811id select_type table type possible_keys key key_len ref rows Extra 8121 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary 813drop table t1; 814# date = string 815create table t1 (a date); 816insert t1 values ('2010-10-10'), ('20101010'); 817explain select distinct a from t1 where a = '2010-10-10'; 818id select_type table type possible_keys key key_len ref rows Extra 8191 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 820drop table t1; 821# double = string 822create table t1 (a double); 823insert t1 values (2), (2); 824explain select distinct a from t1 where a = '2'; 825id select_type table type possible_keys key key_len ref rows Extra 8261 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 827# double = int 828explain select distinct a from t1 where a = 2; 829id select_type table type possible_keys key key_len ref rows Extra 8301 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 831# string = double 832alter table t1 modify a varchar(100); 833explain select distinct a from t1 where a = 2e0; 834id select_type table type possible_keys key key_len ref rows Extra 8351 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary 836drop table t1; 837create table t1 (f1 varchar(40)); 838insert into t1 values ('2010-10-10 00:00:00.0001'),('2010-10-10 00:00:00.0002'),('2010-10-10 00:00:00.0003'); 839select time(f1) from t1 ; 840time(f1) 84100:00:00.000100 84200:00:00.000200 84300:00:00.000300 844select distinct time(f1) from t1 ; 845time(f1) 84600:00:00.000100 84700:00:00.000200 84800:00:00.000300 849drop table t1; 850create table t1(i int, g int); 851insert into t1 values (null, 1), (0, 2); 852select distinct i from t1 group by g; 853i 854NULL 8550 856drop table t1; 857create table t1(i int, g blob); 858insert into t1 values (null, 1), (0, 2); 859select distinct i from t1 group by g; 860i 861NULL 8620 863drop table t1; 864create table t1 (a int) engine=myisam; 865insert into t1 values (0),(7); 866create table t2 (b int) engine=myisam; 867insert into t2 values (7),(0),(3); 868create algorithm=temptable view v as 869select distinct (select max(a) from t1 where alias.b = a) as field1 from t2 as alias group by field1; 870select * from v; 871field1 872NULL 8730 8747 875select distinct (select max(a) from t1 where alias.b = a) as field1 from t2 as alias group by field1; 876field1 877NULL 8780 8797 880drop view v; 881drop table t1, t2; 882CREATE TABLE t1 ( 883id int, i1 int, i2 int DEFAULT 0, 884d1 date DEFAULT '2000-01-01', d2 date DEFAULT '2000-01-01', 885t1 time DEFAULT '00:00:00', t2 time DEFAULT '00:00:00', 886dt1 datetime NOT NULL DEFAULT '2000-01-01 00:00:00', 887dt2 datetime NOT NULL DEFAULT '2000-01-01 00:00:00', 888c1 varchar(1) NOT NULL, c2 varchar(1) NOT NULL 889) ENGINE=MyISAM; 890CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 891INSERT INTO t1 (id,i1,c1,c2) VALUES 892(1,7,'t','t'),(2,4,'k','k'),(3,2,'e','e'),(4,0,'i','i'),(5,1,'t','t'),(6,91,'m','m'), 893(7,6,'z','z'),(8,3,'c','c'),(9,6,'i','i'),(10,8,'v','v'), (11,1,'l','l'),(12,4,'j','j'), 894(13,5,'w','w'),(14,0,'r','r'),(15,7,'o','o'),(16,9,'o','o'),(17,7,'u','u'),(18,6,'f','f'), 895(19,0,'l','l'),(20,6,'g','g'),(21,1,'e','e'),(22,7,'y','y'),(23,0,'p','p'),(24,6,'v','v'), 896(25,5,'d','d'),(26,9,'i','i'),(27,5,'z','z'),(28,2,'q','q'),(29,4,'j','j'),(30,9,'m','m'), 897(31,8,'d','d'),(32,5,'r','r'),(33,1,'r','r'),(34,1,'k','k'),(35,4,'p','p'),(36,2,'x','x'), 898(37,5,'w','w'),(38,0,'k','k'),(39,7,'y','y'),(40,4,'p','p'),(41,9,'l','l'),(42,2,'u','u'), 899(43,3,'r','r'),(44,5,'y','y'),(45,3,'u','u'),(46,9,'t','t'),(47,8,'f','f'),(48,2,'f','f'), 900(49,2,'q','q'),(50,6,'v','v'),(51,6,'u','u'),(52,0,'b','b'),(53,1,'n','n'),(54,2,'p','p'), 901(55,0,'y','y'),(56,1,'l','l'),(57,1,'c','c'),(58,0,'d','d'),(59,2,'y','y'),(60,7,'l','l'), 902(61,6,'m','m'),(62,9,'q','q'),(63,0,'j','j'),(64,3,'u','u'),(65,4,'w','w'),(66,5,'p','p'), 903(67,8,'z','z'),(68,5,'u','u'),(69,7,'b','b'),(70,0,'f','f'),(71,6,'u','u'),(72,1,'i','i'), 904(73,9,'s','s'),(74,3,'y','y'),(75,5,'s','s'),(76,8,'x','x'),(77,3,'s','s'),(78,3,'l','l'), 905(79,8,'b','b'),(80,0,'p','p'),(81,9,'m','m'),(82,5,'k','k'),(83,7,'u','u'),(84,0,'y','y'), 906(85,2,'x','x'),(86,5,'h','h'),(87,5,'j','j'),(88,5,'o','o'),(89,9,'o','o'),(90,1,'c','c'), 907(91,7,'k','k'),(92,9,'t','t'),(93,3,'h','h'),(94,6,'g','g'),(95,9,'r','r'),(96,2,'i','i'); 908CREATE TABLE t2 (i INT) ENGINE=MyISAM; 909INSERT INTO t2 VALUES (7),(8); 910SELECT STRAIGHT_JOIN COUNT(DISTINCT t1.id) FROM 911t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; 912COUNT(DISTINCT t1.id) 91318 914EXPLAIN EXTENDED 915SELECT STRAIGHT_JOIN DISTINCT t1.id FROM 916t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; 917id select_type table type possible_keys key key_len ref rows filtered Extra 9181 PRIMARY t1 ALL NULL NULL NULL NULL 96 100.00 Using where; Using temporary 9191 PRIMARY <derived2> ref key0 key0 5 test.t1.i1 9 100.00 Using where 9201 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 9212 DERIVED t1 ALL NULL NULL NULL NULL 96 100.00 Using where 922Warnings: 923Note 1003 /* select#1 */ select straight_join distinct `test`.`t1`.`id` AS `id` from `test`.`t1` join `test`.`v1` join `test`.`t2` where `test`.`t2`.`i` = `v1`.`id` and `v1`.`i1` = `test`.`t1`.`i1` and `v1`.`id` <> 3 924set join_buffer_size=1024; 925SELECT STRAIGHT_JOIN DISTINCT t1.id FROM 926t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; 927id 92818 92920 93024 93143 93245 93350 93451 93561 93664 9377 93871 93974 94077 94178 9428 9439 94493 94594 946set join_buffer_size=1024*16; 947SELECT STRAIGHT_JOIN DISTINCT t1.id FROM 948t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; 949id 95018 95120 95224 95343 95445 95550 95651 95761 95864 9597 96071 96174 96277 96378 9648 9659 96693 96794 968set join_buffer_size=default; 969SELECT STRAIGHT_JOIN DISTINCT t1.id FROM 970t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; 971id 9727 9739 97418 97520 97624 97750 97851 97961 98071 98194 9828 98343 98445 98564 98674 98777 98878 98993 990DROP VIEW v1; 991DROP TABLE t1,t2; 992# 993# Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb 994# 995CREATE TABLE t1 (a INT(1), b INT(1)); 996INSERT INTO t1 VALUES (1111, 2222), (3333, 4444); 997SELECT DISTINCT CONCAT(a,b) AS c FROM t1 ORDER BY 1; 998c 99911112222 100033334444 1001DROP TABLE t1; 1002# 1003# Bug#16539979 BASIC SELECT COUNT(DISTINCT ID) IS BROKEN. 1004# Bug#17867117 ERROR RESULT WHEN "COUNT + DISTINCT + CASE WHEN" NEED MERGE_WALK 1005# 1006SET @tmp_table_size_save= @@tmp_table_size; 1007SET @@tmp_table_size= 1024; 1008CREATE TABLE t1 (a INT); 1009INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 1010INSERT INTO t1 SELECT a+8 FROM t1; 1011INSERT INTO t1 SELECT a+16 FROM t1; 1012INSERT INTO t1 SELECT a+32 FROM t1; 1013INSERT INTO t1 SELECT a+64 FROM t1; 1014INSERT INTO t1 VALUE(NULL); 1015SELECT COUNT(DISTINCT a) FROM t1; 1016COUNT(DISTINCT a) 1017128 1018SELECT COUNT(DISTINCT (a+0)) FROM t1; 1019COUNT(DISTINCT (a+0)) 1020128 1021DROP TABLE t1; 1022create table tb( 1023id int auto_increment primary key, 1024v varchar(32)) 1025engine=myisam charset=gbk; 1026insert into tb(v) values("aaa"); 1027insert into tb(v) (select v from tb); 1028insert into tb(v) (select v from tb); 1029insert into tb(v) (select v from tb); 1030insert into tb(v) (select v from tb); 1031insert into tb(v) (select v from tb); 1032insert into tb(v) (select v from tb); 1033update tb set v=concat(v, id); 1034select count(distinct case when id<=64 then id end) from tb; 1035count(distinct case when id<=64 then id end) 103664 1037select count(distinct case when id<=63 then id end) from tb; 1038count(distinct case when id<=63 then id end) 103963 1040drop table tb; 1041SET @@tmp_table_size= @tmp_table_size_save; 1042# 1043# MDEV-14695: Assertion `n < m_size' failed in Bounds_checked_array<Element_type>::operator 1044# 1045CREATE TABLE t1 (b1 BIT, b2 BIT, b3 BIT, b4 BIT , b5 BIT, b6 BIT); 1046INSERT INTO t1 VALUES (1,0,0,1,0,1),(0,1,0,0,1,0); 1047SELECT DISTINCT b1+'0', b2+'0', b3+'0', b4+'0', b5+'0', b6 +'0' FROM t1; 1048b1+'0' b2+'0' b3+'0' b4+'0' b5+'0' b6 +'0' 10491 0 0 1 0 1 10500 1 0 0 1 0 1051DROP TABLE t1; 1052# 1053# MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free 1054# or Invalid write in JOIN::make_aggr_tables_info 1055# 1056CREATE TABLE t1 (pk INT PRIMARY KEY); 1057INSERT INTO t1 VALUES (1),(2); 1058explain 1059( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ) 1060UNION 1061( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ); 1062id select_type table type possible_keys key key_len ref rows Extra 10631 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index; Using temporary 10642 UNCACHEABLE UNION t1 index NULL PRIMARY 4 NULL 2 Using index; Using temporary 1065NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 1066( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ) 1067UNION 1068( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ); 10691 10701 1071drop table t1; 1072End of 5.5 tests 1073