2
# Bug with distinct and INSERT INTO
3
# Bug with group by and not used fields
7
drop table if exists t1,t2,t3;
10
CREATE TABLE t1 (id int,facility char(20));
11
CREATE TABLE t2 (facility char(20));
12
INSERT INTO t1 VALUES (NULL,NULL);
13
INSERT INTO t1 VALUES (-1,'');
14
INSERT INTO t1 VALUES (0,'');
15
INSERT INTO t1 VALUES (1,'/L');
16
INSERT INTO t1 VALUES (2,'A01');
17
INSERT INTO t1 VALUES (3,'ANC');
18
INSERT INTO t1 VALUES (4,'F01');
19
INSERT INTO t1 VALUES (5,'FBX');
20
INSERT INTO t1 VALUES (6,'MT');
21
INSERT INTO t1 VALUES (7,'P');
22
INSERT INTO t1 VALUES (8,'RV');
23
INSERT INTO t1 VALUES (9,'SRV');
24
INSERT INTO t1 VALUES (10,'VMT');
25
INSERT INTO t2 SELECT DISTINCT FACILITY FROM t1;
27
select id from t1 group by id;
28
select * from t1 order by id;
29
select id-5,facility from t1 order by "id-5";
30
select id,concat(facility) from t1 group by id ;
31
select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a;
32
select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp;
34
SELECT DISTINCT FACILITY FROM t1;
35
SELECT FACILITY FROM t2;
36
SELECT count(*) from t1,t2 where t1.facility=t2.facility;
37
select count(facility) from t1;
38
select count(*) from t1;
39
select count(*) from t1 where facility IS NULL;
40
select count(*) from t1 where facility = NULL;
41
select count(*) from t1 where facility IS NOT NULL;
42
select count(*) from t1 where id IS NULL;
43
select count(*) from t1 where id IS NOT NULL;
48
# Problem with distinct without results
50
CREATE TABLE t1 (UserId int(11) DEFAULT '0' NOT NULL);
51
INSERT INTO t1 VALUES (20);
52
INSERT INTO t1 VALUES (27);
54
SELECT UserId FROM t1 WHERE Userid=22;
55
SELECT UserId FROM t1 WHERE UserId=22 group by Userid;
56
SELECT DISTINCT UserId FROM t1 WHERE UserId=22 group by Userid;
57
SELECT DISTINCT UserId FROM t1 WHERE UserId=22;
64
CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
65
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
66
CREATE TABLE t2 (a int(10) unsigned not null, key (A));
67
INSERT INTO t2 VALUES (1),(2);
68
CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
69
INSERT INTO t3 VALUES (1,'1'),(2,'2');
70
SELECT DISTINCT t3.b FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
71
INSERT INTO t2 values (1),(2),(3);
72
INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
73
explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
74
SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
76
# Create a lot of data into t3;
77
create temporary table t4 select * from t3;
78
insert into t3 select * from t4;
79
insert into t4 select * from t3;
80
insert into t3 select * from t4;
81
insert into t4 select * from t3;
82
insert into t3 select * from t4;
83
insert into t4 select * from t3;
84
insert into t3 select * from t4;
86
explain select distinct t1.a from t1,t3 where t1.a=t3.a;
88
select distinct t1.a from t1,t3 where t1.a=t3.a;
89
#show status like 'Handler%';
91
select distinct 1 from t1,t3 where t1.a=t3.a;
92
#show status like 'Handler%';
94
explain SELECT distinct t1.a from t1;
95
explain SELECT distinct t1.a from t1 order by a desc;
96
explain SELECT t1.a from t1 group by a order by a desc;
97
explain SELECT distinct t1.a from t1 order by a desc limit 1;
98
explain SELECT distinct a from t3 order by a desc limit 2;
99
explain SELECT distinct a,b from t3 order by a+1;
100
explain SELECT distinct a,b from t3 order by a limit 10;
101
explain SELECT a,b from t3 group by a,b order by a+1;
103
drop table t1,t2,t3,t4;
105
CREATE TABLE t1 (name varchar(255));
106
INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');
107
SELECT DISTINCT * FROM t1 LIMIT 2;
108
SELECT DISTINCT name FROM t1 LIMIT 2;
109
SELECT DISTINCT 1 FROM t1 LIMIT 2;
113
ID int(11) NOT NULL auto_increment,
114
NAME varchar(75) DEFAULT '' NOT NULL,
115
LINK_ID int(11) DEFAULT '0' NOT NULL,
118
KEY LINK_ID (LINK_ID)
121
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0),(2,'Jack',0),(3,'Bill',0);
124
ID int(11) NOT NULL auto_increment,
125
NAME varchar(150) DEFAULT '' NOT NULL,
131
t2.id AS key_link_id,
134
LEFT JOIN t2 ON t1.link_id=t2.id
140
# Problem with table dependencies
145
name tinytext not null,
158
insert into t1 values (1,'yes'), (2,'no');
159
insert into t2 values (1,1);
160
insert into t3 values (1,1);
171
t1 as j_lj_t2 left join t2 as t2_lj
172
on j_lj_t2.id=t2_lj.id
174
t1 as j_lj_t3 left join t3 as t3_lj
175
on j_lj_t3.id=t3_lj.id
177
((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
178
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
188
t1 as j_lj_t2 left join t2 as t2_lj
189
on j_lj_t2.id=t2_lj.id
191
t1 as j_lj_t3 left join t3 as t3_lj
192
on j_lj_t3.id=t3_lj.id
194
((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
195
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
199
# Test using DISTINCT on a function that contains a group function
200
# This also test the case when one doesn't use all fields in GROUP BY.
203
create table t1 (a int not null, b int not null, t time);
204
insert into t1 values (1,1,"00:06:15"),(1,2,"00:06:15"),(1,2,"00:30:15"),(1,3,"00:06:15"),(1,3,"00:30:15");
205
select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
206
select distinct a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
207
create table t2 (a int not null primary key, b int);
208
insert into t2 values (1,1),(2,2),(3,3);
209
select t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;
210
select distinct t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;
214
# Test problem with DISTINCT and HAVING
216
create table t1 (a int not null,b char(5), c text);
217
insert into t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4);
218
select distinct a from t1 group by b,a having a > 2 order by a desc;
219
select distinct a,c from t1 group by b,c,a having a > 2 order by a desc;
223
# Test problem with DISTINCT and ORDER BY DESC
226
create table t1 (a char(1), key(a)) engine=myisam;
227
insert into t1 values('1'),('1');
228
select * from t1 where a >= '1';
229
select distinct a from t1 order by a desc;
230
select distinct a from t1 where a >= '1' order by a desc;
234
# Test when using a not previously used column in ORDER BY
237
CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME);
238
CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10));
240
INSERT INTO t1 (email, infoID, dateentered) VALUES
241
('test1@testdomain.com', 1, '2002-07-30 22:56:38'),
242
('test1@testdomain.com', 1, '2002-07-27 22:58:16'),
243
('test2@testdomain.com', 1, '2002-06-19 15:22:19'),
244
('test2@testdomain.com', 2, '2002-06-18 14:23:47'),
245
('test3@testdomain.com', 1, '2002-05-19 22:17:32');
247
INSERT INTO t2(infoID, shipcode) VALUES
251
SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID;
252
SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC;
253
SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC;
257
# test with table.* in DISTINCT
260
CREATE TABLE t1 (privatemessageid int(10) unsigned NOT NULL auto_increment, folderid smallint(6) NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', touserid int(10) unsigned NOT NULL default '0', fromuserid int(10) unsigned NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int(10) unsigned NOT NULL default '0', showsignature smallint(6) NOT NULL default '0', iconid smallint(5) unsigned NOT NULL default '0', messageread smallint(6) NOT NULL default '0', readtime int(10) unsigned NOT NULL default '0', receipt smallint(6) unsigned NOT NULL default '0', deleteprompt smallint(6) unsigned NOT NULL default '0', multiplerecipients smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid)) ENGINE=MyISAM;
261
INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0);
262
CREATE TABLE t2 (userid int(10) unsigned NOT NULL auto_increment, usergroupid smallint(5) unsigned NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid smallint(5) unsigned NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser smallint(6) NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail smallint(6) NOT NULL default '0', showemail smallint(6) NOT NULL default '0', invisible smallint(6) NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle smallint(6) NOT NULL default '0', joindate int(10) unsigned NOT NULL default '0', cookieuser smallint(6) NOT NULL default '0', daysprune smallint(6) NOT NULL default '0', lastvisit int(10) unsigned NOT NULL default '0', lastactivity int(10) unsigned NOT NULL default '0', lastpost int(10) unsigned NOT NULL default '0', posts smallint(5) unsigned NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification smallint(6) NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm smallint(6) NOT NULL default '0', emailonpm smallint(6) NOT NULL default '0', pmpopup smallint(6) NOT NULL default '0', avatarid smallint(6) NOT NULL default '0', avatarrevision int(6) unsigned NOT NULL default '0', options smallint(6) NOT NULL default '15', birthday date NOT NULL default '0000-00-00', maxposts smallint(6) NOT NULL default '-1', startofweek smallint(6) NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int(10) unsigned NOT NULL default '0', nosessionhash smallint(6) NOT NULL default '0', autorefresh smallint(6) NOT NULL default '-1', messagepopup tinyint(2) NOT NULL default '0', inforum smallint(5) unsigned NOT NULL default '0', ratenum smallint(5) unsigned NOT NULL default '0', ratetotal smallint(5) unsigned NOT NULL default '0', allowrate smallint(5) unsigned NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum)) ENGINE=MyISAM;
263
INSERT INTO t2 VALUES (33,6,'Kevin','0','kevin@stileproject.com',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,'0000-00-00',-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1);
264
SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid);
268
# test with const_item in ORDER BY
271
CREATE TABLE t1 (a int primary key, b int, c int);
272
INSERT t1 VALUES (1,2,3);
273
CREATE TABLE t2 (a int primary key, b int, c int);
274
INSERT t2 VALUES (3,4,5);
275
SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c;
279
# Test of LEFT() with distinct
282
CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
283
INSERT INTO t1 VALUES (1, 'aaaaa');
284
INSERT INTO t1 VALUES (3, 'aaaaa');
285
INSERT INTO t1 VALUES (2, 'eeeeeee');
286
select distinct left(name,1) as name from t1;
290
# Test case from sel000100
294
ID int(11) NOT NULL auto_increment,
295
NAME varchar(75) DEFAULT '' NOT NULL,
296
LINK_ID int(11) DEFAULT '0' NOT NULL,
299
KEY LINK_ID (LINK_ID)
302
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0);
303
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (2,'Jack',0);
304
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (3,'Bill',0);
307
ID int(11) NOT NULL auto_increment,
308
NAME varchar(150) DEFAULT '' NOT NULL,
314
t2.id AS key_link_id,
317
LEFT JOIN t2 ON t1.link_id=t2.id
327
html varchar(5) default NULL,
328
rin int(11) default '0',
329
rout int(11) default '0'
332
INSERT INTO t1 VALUES ('1',1,0);
333
SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin;
337
# Test cases for #12625: DISTINCT for a list with constants
340
CREATE TABLE t1 (a int);
341
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
342
SELECT DISTINCT a, 1 FROM t1;
343
SELECT DISTINCT 1, a FROM t1;
345
CREATE TABLE t2 (a int, b int);
346
INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5);
347
SELECT DISTINCT a, b, 2 FROM t2;
348
SELECT DISTINCT 2, a, b FROM t2;
349
SELECT DISTINCT a, 2, b FROM t2;
353
# Bug#16458: Simple SELECT FOR UPDATE causes "Result Set not updatable"
356
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
357
INSERT INTO t1 VALUES (1,1), (2,1), (3,1);
358
EXPLAIN SELECT DISTINCT a FROM t1;
359
EXPLAIN SELECT DISTINCT a,b FROM t1;
360
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
361
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
362
WHERE t1_1.a = t1_2.a;
363
EXPLAIN SELECT a FROM t1 GROUP BY a;
364
EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
365
EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
367
CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b));
368
INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
369
EXPLAIN SELECT DISTINCT a FROM t2;
370
EXPLAIN SELECT DISTINCT a,a FROM t2;
371
EXPLAIN SELECT DISTINCT b,a FROM t2;
372
EXPLAIN SELECT DISTINCT a,c FROM t2;
373
EXPLAIN SELECT DISTINCT c,a,b FROM t2;
375
EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
376
CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);
377
EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
381
# Bug 9784 DISTINCT IFNULL truncates data
383
create table t1 (id int, dsc varchar(50));
384
insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three");
385
select distinct id, IFNULL(dsc, '-') from t1;
389
# Bug 21456: SELECT DISTINCT(x) produces incorrect results when using order by
391
CREATE TABLE t1 (a int primary key, b int);
393
INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2);
395
explain SELECT DISTINCT a, b FROM t1 ORDER BY b;
396
SELECT DISTINCT a, b FROM t1 ORDER BY b;
403
# Bug #15745 ( COUNT(DISTINCT CONCAT(x,y)) returns wrong result)
406
ID int(11) NOT NULL auto_increment,
407
x varchar(20) default NULL,
408
y decimal(10,0) default NULL,
411
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
413
INSERT INTO t1 VALUES
421
select count(distinct x,y) from t1;
422
select count(distinct concat(x,y)) from t1;
426
# Bug #18068: SELECT DISTINCT
428
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
430
INSERT INTO t1 VALUES (1, 101);
431
INSERT INTO t1 SELECT a + 1, a + 101 FROM t1;
432
INSERT INTO t1 SELECT a + 2, a + 102 FROM t1;
433
INSERT INTO t1 SELECT a + 4, a + 104 FROM t1;
434
INSERT INTO t1 SELECT a + 8, a + 108 FROM t1;
436
EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
437
SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
440
# The test case for bug#20836 should be re-enabled when bug#16861 is resolved
441
# The results for the test should be the same as in 4.1.
443
#Bug #20836: Selecting into variables results in wrong results being returned
446
#DROP TABLE IF EXISTS t1;
449
#CREATE TABLE t1 (id INT NOT NULL, fruit_id INT NOT NULL, fruit_name varchar(20)
452
#INSERT INTO t1 VALUES (1,1,'ORANGE');
453
#INSERT INTO t1 VALUES (2,2,'APPLE');
454
#INSERT INTO t1 VALUES (3,2,'APPLE');
455
#INSERT INTO t1 VALUES (4,3,'PEAR');
457
#SELECT DISTINCT fruit_id, fruit_name INTO @v1, @v2 FROM t1 WHERE fruit_name =
461
#SELECT DISTINCT fruit_id, fruit_name INTO @v3, @v4 FROM t1 GROUP BY fruit_id,
462
#fruit_name HAVING fruit_name = 'APPLE';
465
#SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE
466
#fruit_name = 'APPLE';
467
#SELECT @v5, @v6, @v7, @v8;
469
#SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1
470
#WHERE fruit_name = 'APPLE';
471
#SELECT @v5, @v6, @v7, @v8, @v9, @v10;
473
#SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO
474
#@v13, @v14 FROM t1 WHERE fruit_name = 'APPLE';
475
#SELECT @v11, @v12, @v13, @v14;
477
#SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE';
480
#SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name =
485
#DROP TABLE IF EXISTS t2;
488
#CREATE TABLE t2 (fruit_id INT NOT NULL, fruit_name varchar(20)
491
#SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE
492
#'../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE';
493
#LOAD DATA INFILE '../tmp/data1.tmp' INTO TABLE t2;
494
#--exec rm $MYSQL_TEST_DIR/var/tmp/data1.tmp
496
#SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE
497
#'../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE';
498
#LOAD DATA INFILE '../tmp/data2.tmp' INTO TABLE t2;
499
#--exec rm $MYSQL_TEST_DIR/var/tmp/data2.tmp
added
removed
mysql-test/t/distinct.test
