1# The include statement below is a temp one for tests that are yet to 2#be ported to run with InnoDB, 3#but needs to be kept for tests that would need MyISAM in future. 4--source include/force_myisam_default.inc 5 6# 7# Bug with distinct and INSERT INTO 8# Bug with group by and not used fields 9# 10 11--disable_warnings 12drop table if exists t1,t2,t3; 13--enable_warnings 14 15CREATE TABLE t1 (id int,facility char(20)); 16CREATE TABLE t2 (facility char(20)); 17INSERT INTO t1 VALUES (NULL,NULL); 18INSERT INTO t1 VALUES (-1,''); 19INSERT INTO t1 VALUES (0,''); 20INSERT INTO t1 VALUES (1,'/L'); 21INSERT INTO t1 VALUES (2,'A01'); 22INSERT INTO t1 VALUES (3,'ANC'); 23INSERT INTO t1 VALUES (4,'F01'); 24INSERT INTO t1 VALUES (5,'FBX'); 25INSERT INTO t1 VALUES (6,'MT'); 26INSERT INTO t1 VALUES (7,'P'); 27INSERT INTO t1 VALUES (8,'RV'); 28INSERT INTO t1 VALUES (9,'SRV'); 29INSERT INTO t1 VALUES (10,'VMT'); 30INSERT INTO t2 SELECT DISTINCT FACILITY FROM t1; 31 32select id from t1 group by id; 33select * from t1 order by id; 34select id-5,facility from t1 order by "id-5"; 35--source include/turn_off_only_full_group_by.inc 36select id,concat(facility) from t1 group by id ; 37select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a; 38--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc 39select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp; 40 41SELECT DISTINCT FACILITY FROM t1; 42SELECT FACILITY FROM t2; 43SELECT count(*) from t1,t2 where t1.facility=t2.facility; 44select count(facility) from t1; 45select count(*) from t1; 46select count(*) from t1 where facility IS NULL; 47select count(*) from t1 where facility = NULL; 48select count(*) from t1 where facility IS NOT NULL; 49select count(*) from t1 where id IS NULL; 50select count(*) from t1 where id IS NOT NULL; 51 52drop table t1,t2; 53 54# 55# Problem with distinct without results 56# 57CREATE TABLE t1 (UserId int(11) DEFAULT '0' NOT NULL); 58INSERT INTO t1 VALUES (20); 59INSERT INTO t1 VALUES (27); 60 61SELECT UserId FROM t1 WHERE Userid=22; 62SELECT UserId FROM t1 WHERE UserId=22 group by Userid; 63SELECT DISTINCT UserId FROM t1 WHERE UserId=22 group by Userid; 64SELECT DISTINCT UserId FROM t1 WHERE UserId=22; 65drop table t1; 66 67# 68# Test of distinct 69# 70 71CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned); 72INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1); 73CREATE TABLE t2 (a int(10) unsigned not null, key (A)); 74INSERT INTO t2 VALUES (1),(2); 75CREATE TABLE t3 (a int(10) unsigned, key(A), b text); 76INSERT INTO t3 VALUES (1,'1'),(2,'2'); 77SELECT DISTINCT t3.b FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; 78INSERT INTO t2 values (1),(2),(3); 79INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2'); 80explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; 81SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; 82 83# Create a lot of data into t3; 84create temporary table t4 select * from t3; 85insert into t3 select * from t4; 86insert into t4 select * from t3; 87insert into t3 select * from t4; 88insert into t4 select * from t3; 89insert into t3 select * from t4; 90insert into t4 select * from t3; 91insert into t3 select * from t4; 92 93ANALYZE TABLE t1,t2,t3; 94 95explain select distinct t1.a from t1,t3 where t1.a=t3.a; 96 97# This query uses the "not used in distinct" optimization (search for 98# not_used_in_distinct in code); it means that when we have a partial 99# record for t1, and we find a match in t3, we have a combination 100# rec_t1|rec_t3_a, and thus (DISTINCT is used and the SELECT list does 101# not contain columns of t2) we have rec_t1 in the result set; we don't 102# need to explore more records of t3, as all it would do is producing 103# more rec_t1|rec_t3_x, which would all be eliminated (due to 104# DISTINCT). 105# Without this optimization, the first query does ~200 read_next instead 106# of 4. 107flush status; 108select distinct t1.a from t1,t3 where t1.a=t3.a; 109show status like 'Handler_read%'; 110flush status; 111select distinct 1 from t1,t3 where t1.a=t3.a; 112show status like 'Handler_read%'; 113 114let $iteration=2; 115let $q_type= explain; 116# First EXPLAIN query, then execute query 117while($iteration) 118{ 119 eval $q_type SELECT distinct t1.a from t1; 120 eval $q_type SELECT distinct t1.a from t1 order by a desc; 121 eval $q_type SELECT t1.a from t1 group by a order by a desc; 122 eval $q_type SELECT distinct t1.a from t1 order by a desc limit 1; 123 eval $q_type SELECT distinct a from t3 order by a desc limit 2; 124 eval $q_type SELECT distinct a,b from t3 order by a+1; 125 eval $q_type SELECT distinct a,b from t3 order by a limit 2; 126 eval $q_type SELECT a,b from t3 group by a,b order by a+1; 127 128 let $q_type=; 129 dec $iteration; 130} 131 132drop table t1,t2,t3,t4; 133 134CREATE TABLE t1 (name varchar(255)); 135INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae'); 136SELECT DISTINCT * FROM t1 LIMIT 2; 137SELECT DISTINCT name FROM t1 LIMIT 2; 138SELECT DISTINCT 1 FROM t1 LIMIT 2; 139drop table t1; 140 141CREATE TABLE t1 ( 142 ID int(11) NOT NULL auto_increment, 143 NAME varchar(75) DEFAULT '' NOT NULL, 144 LINK_ID int(11) DEFAULT '0' NOT NULL, 145 PRIMARY KEY (ID), 146 KEY NAME (NAME), 147 KEY LINK_ID (LINK_ID) 148); 149 150INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0),(2,'Jack',0),(3,'Bill',0); 151 152CREATE TABLE t2 ( 153 ID int(11) NOT NULL auto_increment, 154 NAME varchar(150) DEFAULT '' NOT NULL, 155 PRIMARY KEY (ID), 156 KEY NAME (NAME) 157); 158 159# We see the functional dependency implied by ON condition 160 161SELECT DISTINCT 162 t2.id AS key_link_id, 163 t2.name AS link 164FROM t1 165LEFT JOIN t2 ON t1.link_id=t2.id 166GROUP BY t1.id 167ORDER BY link; 168 169drop table t1,t2; 170 171# 172# Problem with table dependencies 173# 174 175create table t1 ( 176 id int not null, 177 name tinytext not null, 178 unique (id) 179); 180create table t2 ( 181 id int not null, 182 idx int not null, 183 unique (id, idx) 184); 185create table t3 ( 186 id int not null, 187 idx int not null, 188 unique (id, idx) 189); 190insert into t1 values (1,'yes'), (2,'no'); 191insert into t2 values (1,1); 192insert into t3 values (1,1); 193EXPLAIN 194SELECT DISTINCT 195 t1.id 196from 197 t1 198 straight_join 199 t2 200 straight_join 201 t3 202 straight_join 203 t1 as j_lj_t2 left join t2 as t2_lj 204 on j_lj_t2.id=t2_lj.id 205 straight_join 206 t1 as j_lj_t3 left join t3 as t3_lj 207 on j_lj_t3.id=t3_lj.id 208WHERE 209 ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2)) 210 AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); 211SELECT DISTINCT 212 t1.id 213from 214 t1 215 straight_join 216 t2 217 straight_join 218 t3 219 straight_join 220 t1 as j_lj_t2 left join t2 as t2_lj 221 on j_lj_t2.id=t2_lj.id 222 straight_join 223 t1 as j_lj_t3 left join t3 as t3_lj 224 on j_lj_t3.id=t3_lj.id 225WHERE 226 ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2)) 227 AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); 228drop table t1,t2,t3; 229 230# 231# Test using DISTINCT on a function that contains a group function 232# This also test the case when one doesn't use all fields in GROUP BY. 233# 234 235create table t1 (a int not null, b int not null, t time); 236insert 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"); 237select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b; 238select distinct a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b; 239create table t2 (a int not null primary key, b int); 240insert into t2 values (1,1),(2,2),(3,3); 241select 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; 242select 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; 243drop table t1,t2; 244 245# 246# Test problem with DISTINCT and HAVING 247# 248create table t1 (a int not null,b char(5), c text); 249insert into t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4); 250select distinct a from t1 group by b,a having a > 2 order by a desc; 251select distinct a,c from t1 group by b,c,a having a > 2 order by a desc; 252drop table t1; 253 254# 255# Test problem with DISTINCT and ORDER BY DESC 256# 257 258create table t1 (a char(1), key(a)) engine=myisam; 259insert into t1 values('1'),('1'); 260select * from t1 where a >= '1'; 261select distinct a from t1 order by a desc; 262select distinct a from t1 where a >= '1' order by a desc; 263drop table t1; 264 265# 266# Test when using a not previously used column in ORDER BY 267# 268 269CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME); 270CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10)); 271 272INSERT INTO t1 (email, infoID, dateentered) VALUES 273 ('test1@testdomain.com', 1, '2002-07-30 22:56:38'), 274 ('test1@testdomain.com', 1, '2002-07-27 22:58:16'), 275 ('test2@testdomain.com', 1, '2002-06-19 15:22:19'), 276 ('test2@testdomain.com', 2, '2002-06-18 14:23:47'), 277 ('test3@testdomain.com', 1, '2002-05-19 22:17:32'); 278 279INSERT INTO t2(infoID, shipcode) VALUES 280 (1, 'Z001'), 281 (2, 'R002'); 282 283SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID; 284--source include/turn_off_only_full_group_by.inc 285SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC; 286SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC; 287--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc 288drop table t1,t2; 289 290# 291# test with table.* in DISTINCT 292# 293SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 294CREATE 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; 295INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0); 296CREATE 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; 297INSERT 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); 298SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid); 299DROP TABLE t1,t2; 300SET sql_mode = default; 301# 302# test with const_item in ORDER BY 303# 304 305CREATE TABLE t1 (a int primary key, b int, c int); 306INSERT t1 VALUES (1,2,3); 307CREATE TABLE t2 (a int primary key, b int, c int); 308INSERT t2 VALUES (3,4,5); 309--source include/turn_off_only_full_group_by.inc 310SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c; 311--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc 312DROP TABLE t1,t2; 313 314# 315# Test of LEFT() with distinct 316# 317 318CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ; 319INSERT INTO t1 VALUES (1, 'aaaaa'); 320INSERT INTO t1 VALUES (3, 'aaaaa'); 321INSERT INTO t1 VALUES (2, 'eeeeeee'); 322select distinct left(name,1) as name from t1; 323drop table t1; 324 325# 326# Test case from sel000100 327# 328 329CREATE TABLE t1 ( 330 ID int(11) NOT NULL auto_increment, 331 NAME varchar(75) DEFAULT '' NOT NULL, 332 LINK_ID int(11) DEFAULT '0' NOT NULL, 333 PRIMARY KEY (ID), 334 KEY NAME (NAME), 335 KEY LINK_ID (LINK_ID) 336); 337 338INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0); 339INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (2,'Jack',0); 340INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (3,'Bill',0); 341 342CREATE TABLE t2 ( 343 ID int(11) NOT NULL auto_increment, 344 NAME varchar(150) DEFAULT '' NOT NULL, 345 PRIMARY KEY (ID), 346 KEY NAME (NAME) 347); 348 349SELECT DISTINCT 350 t2.id AS key_link_id, 351 t2.name AS link 352FROM t1 353LEFT JOIN t2 ON t1.link_id=t2.id 354GROUP BY t1.id 355ORDER BY link; 356 357drop table t1,t2; 358 359# 360# test case for #674 361# 362 363CREATE TABLE t1 ( 364 html varchar(5) default NULL, 365 rin int(11) default '0', 366 rout int(11) default '0' 367) ENGINE=MyISAM; 368 369INSERT INTO t1 VALUES ('1',1,0); 370--source include/turn_off_only_full_group_by.inc 371SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; 372--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc 373drop table t1; 374 375# 376# Test cases for #12625: DISTINCT for a list with constants 377# 378 379CREATE TABLE t1 (a int); 380INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 381SELECT DISTINCT a, 1 FROM t1; 382SELECT DISTINCT 1, a FROM t1; 383 384CREATE TABLE t2 (a int, b int); 385INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5); 386SELECT DISTINCT a, b, 2 FROM t2; 387SELECT DISTINCT 2, a, b FROM t2; 388SELECT DISTINCT a, 2, b FROM t2; 389 390DROP TABLE t1,t2; 391# 392# Bug#16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" 393# error. 394# 395CREATE TABLE t1(a INT PRIMARY KEY, b INT); 396INSERT INTO t1 VALUES (1,1), (2,1), (3,1); 397EXPLAIN SELECT DISTINCT a FROM t1; 398EXPLAIN SELECT DISTINCT a,b FROM t1; 399EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2; 400EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2 401 WHERE t1_1.a = t1_2.a; 402EXPLAIN SELECT a FROM t1 GROUP BY a; 403EXPLAIN SELECT a,b FROM t1 GROUP BY a,b; 404EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b; 405 406CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, 407 PRIMARY KEY (a,b)); 408INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); 409EXPLAIN SELECT DISTINCT a FROM t2; 410EXPLAIN SELECT DISTINCT a,a FROM t2; 411EXPLAIN SELECT DISTINCT b,a FROM t2; 412EXPLAIN SELECT DISTINCT a,c FROM t2; 413EXPLAIN SELECT DISTINCT c,a,b FROM t2; 414 415--source include/turn_off_only_full_group_by.inc 416EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d; 417# After adding unique constraint, GROUP BY is ok. 418--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc 419CREATE UNIQUE INDEX c_b_unq ON t2 (c,b); 420EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d; 421 422DROP TABLE t1,t2; 423 424# Bug 9784 DISTINCT IFNULL truncates data 425# 426create table t1 (id int, dsc varchar(50)); 427insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three"); 428select distinct id, IFNULL(dsc, '-') from t1; 429drop table t1; 430 431# 432# Bug 21456: SELECT DISTINCT(x) produces incorrect results when using order by 433# 434CREATE TABLE t1 (a int primary key, b int); 435 436INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2); 437 438explain SELECT DISTINCT a, b FROM t1 ORDER BY b; 439SELECT DISTINCT a, b FROM t1 ORDER BY b; 440DROP TABLE t1; 441 442# End of 4.1 tests 443 444 445# 446# Bug #15745 ( COUNT(DISTINCT CONCAT(x,y)) returns wrong result) 447# 448CREATE TABLE t1 ( 449 ID int(11) NOT NULL auto_increment, 450 x varchar(20) default NULL, 451 y decimal(10,0) default NULL, 452 PRIMARY KEY (ID), 453 KEY (y) 454) ENGINE=MyISAM DEFAULT CHARSET=latin1; 455 456INSERT INTO t1 VALUES 457(1,'ba','-1'), 458(2,'ba','1150'), 459(306,'ba','-1'), 460(307,'ba','1150'), 461(611,'ba','-1'), 462(612,'ba','1150'); 463 464select count(distinct x,y) from t1; 465select count(distinct concat(x,y)) from t1; 466drop table t1; 467 468# 469# Bug #18068: SELECT DISTINCT 470# 471CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b)); 472 473INSERT INTO t1 VALUES (1, 101); 474INSERT INTO t1 SELECT a + 1, a + 101 FROM t1; 475INSERT INTO t1 SELECT a + 2, a + 102 FROM t1; 476INSERT INTO t1 SELECT a + 4, a + 104 FROM t1; 477INSERT INTO t1 SELECT a + 8, a + 108 FROM t1; 478 479EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; 480SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; 481 482DROP TABLE t1; 483 484#Bug #20836: Selecting into variables results in wrong results being returned 485--disable_warnings 486DROP TABLE IF EXISTS t1; 487--enable_warnings 488 489CREATE TABLE t1 (id INT NOT NULL, fruit_id INT NOT NULL, fruit_name varchar(20) 490default NULL); 491 492INSERT INTO t1 VALUES (1,1,'ORANGE'); 493INSERT INTO t1 VALUES (2,2,'APPLE'); 494INSERT INTO t1 VALUES (3,2,'APPLE'); 495INSERT INTO t1 VALUES (4,3,'PEAR'); 496 497SELECT DISTINCT fruit_id, fruit_name INTO @v1, @v2 FROM t1 WHERE fruit_name = 498'APPLE'; 499SELECT @v1, @v2; 500 501SELECT DISTINCT fruit_id, fruit_name INTO @v3, @v4 FROM t1 GROUP BY fruit_id, 502fruit_name HAVING fruit_name = 'APPLE'; 503SELECT @v3, @v4; 504 505SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE 506fruit_name = 'APPLE'; 507SELECT @v5, @v6, @v7, @v8; 508 509SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1 510WHERE fruit_name = 'APPLE'; 511SELECT @v5, @v6, @v7, @v8, @v9, @v10; 512 513SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO 514@v13, @v14 FROM t1 WHERE fruit_name = 'APPLE'; 515SELECT @v11, @v12, @v13, @v14; 516 517SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE'; 518SELECT @v15, @v16; 519 520SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name = 521'APPLE'; 522SELECT @v17, @v18; 523 524--disable_warnings 525DROP TABLE IF EXISTS t2; 526--enable_warnings 527 528CREATE TABLE t2 (fruit_id INT NOT NULL, fruit_name varchar(20) 529default NULL); 530 531SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE 532'../../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE'; 533LOAD DATA INFILE '../../tmp/data1.tmp' INTO TABLE t2; 534--error 0,1 535--remove_file $MYSQLTEST_VARDIR/tmp/data1.tmp 536 537SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE 538'../../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE'; 539LOAD DATA INFILE '../../tmp/data2.tmp' INTO TABLE t2; 540--remove_file $MYSQLTEST_VARDIR/tmp/data2.tmp 541 542SELECT @v19, @v20; 543SELECT * FROM t2; 544 545DROP TABLE t1; 546DROP TABLE t2; 547 548# 549# Bug #15881: cast problems 550# 551CREATE TABLE t1 (a CHAR(1)); INSERT INTO t1 VALUES('A'), (0); 552SELECT a FROM t1 WHERE a=0; 553SELECT DISTINCT a FROM t1 WHERE a=0; 554DROP TABLE t1; 555CREATE TABLE t1 (a DATE); 556INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06'); 557EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03'); 558EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1 559 WHERE ADDDATE(a,1) = '2002-08-03'); 560CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci); 561INSERT INTO t2 VALUES (0xf6); 562INSERT INTO t2 VALUES ('oe'); 563 564SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt; 565SELECT COUNT(*) FROM 566 (SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt; 567 568DROP TABLE t1, t2; 569 570# 571# Bug #25551: inconsistent behaviour in grouping NULL, depending on index type 572# 573CREATE TABLE t1 (a INT, UNIQUE (a)); 574INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3); 575EXPLAIN SELECT DISTINCT a FROM t1; 576#result must have one row with NULL 577SELECT DISTINCT a FROM t1; 578EXPLAIN SELECT a FROM t1 GROUP BY a; 579#result must have one row with NULL 580SELECT a FROM t1 GROUP BY a; 581 582DROP TABLE t1; 583 584# 585#Bug #27659: SELECT DISTINCT returns incorrect result set when field is 586#repeated 587# 588# 589CREATE TABLE t1 (a INT, b INT); 590INSERT INTO t1 VALUES(1,1),(1,2),(1,3); 591SELECT DISTINCT a, b FROM t1; 592SELECT DISTINCT a, a, b FROM t1; 593DROP TABLE t1; 594 595--echo End of 5.0 tests 596 597# 598# Bug #34928: Confusion by having Primary Key and Index 599# 600CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, 601 PRIMARY KEY(a,b,c,d,e), 602 KEY(a,b,d,c) 603); 604 605INSERT IGNORE INTO t1(a, b, c) VALUES (1, 1, 1), 606 (1, 1, 2), 607 (1, 1, 3), 608 (1, 2, 1), 609 (1, 2, 2), 610 (1, 2, 3); 611 612EXPLAIN SELECT DISTINCT a, b, d, c FROM t1; 613 614SELECT DISTINCT a, b, d, c FROM t1; 615 616DROP TABLE t1; 617 618--echo # 619--echo # Bug #46159: simple query that never returns 620--echo # 621 622# Set max_heap_table_size to the minimum value so that GROUP BY table in the 623# SELECT query below gets converted to MyISAM 624SET @old_max_heap_table_size = @@max_heap_table_size; 625SET @@max_heap_table_size = 16384; 626 627# Set sort_buffer_size to the mininum value so that remove_duplicates() calls 628# remove_dup_with_compare() 629SET @old_sort_buffer_size = @@sort_buffer_size; 630SET @@sort_buffer_size = 32804; 631 632CREATE TABLE t1(c1 int, c2 VARCHAR(20)); 633INSERT INTO t1 VALUES (1, '1'), (1, '1'), (2, '2'), (3, '1'), (3, '1'), (4, '4'); 634# Now we just need to pad the table with random data so we have enough unique 635# values to force conversion of the GROUP BY table to MyISAM 636INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 637INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 638INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 639INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 640INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 641INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 642INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 643INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; 644--source include/turn_off_only_full_group_by.inc 645 646# First rows of the GROUP BY table that will be processed by 647# remove_dup_with_compare() 648SELECT c1, c2, COUNT(*) FROM t1 GROUP BY c1 LIMIT 4; 649 650# The actual test case 651SELECT DISTINCT c2 FROM t1 GROUP BY c1 HAVING COUNT(*) > 1; 652 653# Cleanup 654 655--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc 656DROP TABLE t1; 657SET @@sort_buffer_size = @old_sort_buffer_size; 658SET @@max_heap_table_size = @old_max_heap_table_size; 659 660--echo End of 5.1 tests 661 662 663--echo # 664--echo # Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb 665--echo # 666 667CREATE TABLE t1 (a INT(1), b INT(1)); 668INSERT INTO t1 VALUES (1111, 2222), (3333, 4444); 669SELECT DISTINCT CONCAT(a,b) AS c FROM t1 ORDER BY 1; 670DROP TABLE t1; 671 672--echo # 673--echo # Bug#16539979 BASIC SELECT COUNT(DISTINCT ID) IS BROKEN. 674--echo # Bug#17867117 ERROR RESULT WHEN "COUNT + DISTINCT + CASE WHEN" NEED MERGE_WALK 675--echo # 676 677SET @tmp_table_size_save= @@tmp_table_size; 678SET @@tmp_table_size= 1024; 679 680CREATE TABLE t1 (a INT); 681INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 682INSERT INTO t1 SELECT a+8 FROM t1; 683INSERT INTO t1 SELECT a+16 FROM t1; 684INSERT INTO t1 SELECT a+32 FROM t1; 685INSERT INTO t1 SELECT a+64 FROM t1; 686INSERT INTO t1 VALUE(NULL); 687SELECT COUNT(DISTINCT a) FROM t1; 688SELECT COUNT(DISTINCT (a+0)) FROM t1; 689DROP TABLE t1; 690 691create table tb( 692id int auto_increment primary key, 693v varchar(32)) 694engine=myisam charset=gbk; 695insert into tb(v) values("aaa"); 696insert into tb(v) (select v from tb); 697insert into tb(v) (select v from tb); 698insert into tb(v) (select v from tb); 699insert into tb(v) (select v from tb); 700insert into tb(v) (select v from tb); 701insert into tb(v) (select v from tb); 702 703update tb set v=concat(v, id); 704select count(distinct case when id<=64 then id end) from tb; 705select count(distinct case when id<=63 then id end) from tb; 706drop table tb; 707 708SET @@tmp_table_size= @tmp_table_size_save; 709 710--echo End of 5.5 tests 711 712--echo # 713--echo # Bug#13335170 - ASSERT IN 714--echo # PLAN_CHANGE_WATCHDOG::~PLAN_CHANGE_WATCHDOG() ON SELECT DISTINCT 715--echo # 716 717CREATE TABLE t1 ( 718 col_int_key int(11) NOT NULL, 719 col_time_key time NOT NULL, 720 col_datetime_key datetime NOT NULL, 721 KEY col_int_key (col_int_key), 722 KEY col_time_key (col_time_key), 723 KEY col_datetime_key (col_datetime_key) 724) ENGINE=InnoDB; 725 726INSERT INTO t1 VALUES (7,'06:17:39','2003-08-21 00:00:00'); 727--source include/turn_off_only_full_group_by.inc 728 729SELECT DISTINCT col_int_key 730FROM t1 731WHERE col_int_key IN ( 18, 6, 84, 4, 0, 2, 8, 3, 7, 9, 1 ) 732 AND col_datetime_key BETWEEN '2001-08-04' AND '2003-06-13' 733ORDER BY col_time_key 734LIMIT 3; 735 736--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc 737DROP TABLE t1; 738 739--echo # 740--echo # BUG#13540692: WRONG NULL HANDLING WITH RIGHT JOIN + 741--echo # DISTINCT OR ORDER BY 742--echo # 743 744CREATE TABLE t1 ( 745 a INT, 746 b INT NOT NULL 747); 748INSERT INTO t1 VALUES (1,2), (3,3); 749 750SET @save_optimizer_switch= @@optimizer_switch; 751SET @@SESSION.optimizer_switch="derived_merge=off"; 752 753let $query= 754SELECT DISTINCT subselect.b 755FROM t1 LEFT JOIN 756 (SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect 757 ON t1.a = subselect.b 758; 759 760--echo 761eval EXPLAIN $query; 762eval $query; 763 764SET @@SESSION.optimizer_switch= @save_optimizer_switch; 765 766DROP TABLE t1; 767 768--echo # 769--echo # BUG#13538387: WRONG RESULT ON SELECT DISTINCT + LEFT JOIN + 770--echo # LIMIT + MIX OF MYISAM AND INNODB 771--echo # 772 773CREATE TABLE t1 (a INT); 774INSERT INTO t1 VALUES (2),(3); 775 776CREATE TABLE t2 (b INT); 777 778CREATE TABLE t3 ( 779 a INT, 780 b INT, 781 PRIMARY KEY (b) 782); 783INSERT INTO t3 VALUES (2001,1), (2007,2); 784 785let $query= 786SELECT DISTINCT t3.a AS t3_date 787FROM t1 788 LEFT JOIN t2 ON false 789 LEFT JOIN t3 ON t2.b = t3.b 790LIMIT 1; 791 792eval EXPLAIN $query; 793eval $query; 794 795DROP TABLE t1,t2,t3; 796 797--echo 798--echo # BUG#13581713 ONLY_FULL_GROUP_BY DOES NOT BLOCK "SELECT 799--echo # DISTINCT A ORDER BY B" 800--echo 801 802create table t1(a int, b int, c int) engine=InnoDB; 803create table t2(a int, b int, c int) engine=InnoDB; 804insert into t2 values(); 805analyze table t2; 806 807--echo # Test when selecting from base table 808let $source=t1; 809let $source_no_alias=t1; 810--source include/bug13581713.inc 811 812--echo # Test when selecting from view 813create view v1 as select t1.* from t1 left join t2 on 1; 814let $source=v1; 815let $source_no_alias=v1; 816--source include/bug13581713.inc 817drop view v1; 818 819--echo # Test when selecting from view, again 820create view v1 as select t1.a*2 as a, t1.b*2 as b, t1.c*2 as c from t1; 821let $source=v1; 822let $source_no_alias=v1; 823--source include/bug13581713.inc 824drop view v1; 825 826--echo # Test when selecting from derived table 827let $source=(SELECT t1.* FROM t1 left join t2 on 1) AS derived; 828let $source_no_alias=(SELECT t1.* FROM t1 left join t2 on 1); 829--source include/bug13581713.inc 830 831--error ER_FIELD_IN_ORDER_NOT_SELECT 832select distinct t1_outer.a from t1 t1_outer 833order by t1_outer.b; 834--error ER_FIELD_IN_ORDER_NOT_SELECT 835select distinct t1_outer.a from t1 t1_outer 836order by (select max(t1_outer.b+t1_inner.b) from t1 t1_inner); 837select 838 (select distinct 1 from t1 t1_inner 839 group by t1_inner.a order by max(t1_outer.b)) 840 from t1 t1_outer; 841 842drop table t1, t2; 843 844--echo # 845--echo # Bug#22686994 REGRESSION FOR A GROUPING QUERY WITH DISTINCT 846--echo # 847 848CREATE TABLE t1 (a INTEGER, b INTEGER); 849 850INSERT INTO t1 VALUES (1,3), (2,4), (1,5), 851(1,3), (2,1), (1,5), (1,7), (3,1), 852(3,2), (3,1), (2,4); 853 854SELECT DISTINCT (COUNT(DISTINCT b) + 1) AS c FROM t1 GROUP BY a; 855DROP TABLE t1; 856 857--echo # End of test for Bug#22686994 858 859