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 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 not null primary key,b int);
65
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
66
CREATE TABLE t2 (a int not null, key (A));
67
INSERT INTO t2 VALUES (1),(2);
68
CREATE TABLE t3 (a int, 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;
90
show status like 'Handler%';
92
select distinct 1 from t1,t3 where t1.a=t3.a;
94
show status like 'Handler%';
96
explain SELECT distinct t1.a from t1;
97
explain SELECT distinct t1.a from t1 order by a desc;
98
explain SELECT t1.a from t1 group by a order by a desc;
99
explain SELECT distinct t1.a from t1 order by a desc limit 1;
100
explain SELECT distinct a from t3 order by a desc limit 2;
101
explain SELECT distinct a,b from t3 order by a+1;
102
explain SELECT distinct a,b from t3 order by a limit 2;
103
explain SELECT a,b from t3 group by a,b order by a+1;
105
drop table t1,t2,t3,t4;
107
CREATE TABLE t1 (name varchar(255));
108
INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');
109
SELECT DISTINCT * FROM t1 LIMIT 2;
110
SELECT DISTINCT name FROM t1 LIMIT 2;
111
SELECT DISTINCT 1 FROM t1 LIMIT 2;
115
ID int NOT NULL auto_increment,
116
NAME varchar(75) DEFAULT '' NOT NULL,
117
LINK_ID int DEFAULT '0' NOT NULL,
120
KEY LINK_ID (LINK_ID)
123
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0),(2,'Jack',0),(3,'Bill',0);
126
ID int NOT NULL auto_increment,
127
NAME varchar(150) DEFAULT '' NOT NULL,
133
t2.id AS key_link_id,
136
LEFT JOIN t2 ON t1.link_id=t2.id
142
# Problem with table dependencies
147
name tinytext not null,
160
insert into t1 values (1,'yes'), (2,'no');
161
insert into t2 values (1,1);
162
insert into t3 values (1,1);
173
t1 as j_lj_t2 left join t2 as t2_lj
174
on j_lj_t2.id=t2_lj.id
176
t1 as j_lj_t3 left join t3 as t3_lj
177
on j_lj_t3.id=t3_lj.id
179
((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
180
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
190
t1 as j_lj_t2 left join t2 as t2_lj
191
on j_lj_t2.id=t2_lj.id
193
t1 as j_lj_t3 left join t3 as t3_lj
194
on j_lj_t3.id=t3_lj.id
196
((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
197
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
201
# Test problem with DISTINCT and HAVING
203
create table t1 (a int not null,b char(5), c text);
204
insert into t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4);
205
select distinct a from t1 group by b,a having a > 2 order by a desc;
206
select distinct a,c from t1 group by b,c,a having a > 2 order by a desc;
210
# Test problem with DISTINCT and ORDER BY DESC
213
create table t1 (a char(1), key(a));
214
insert into t1 values('1'),('1');
215
select * from t1 where a >= '1';
216
select distinct a from t1 order by a desc;
217
select distinct a from t1 where a >= '1' order by a desc;
221
# Test when using a not previously used column in ORDER BY
224
CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME);
225
CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10));
227
INSERT INTO t1 (email, infoID, dateentered) VALUES
228
('test1@testdomain.com', 1, '2002-07-30 22:56:38'),
229
('test1@testdomain.com', 1, '2002-07-27 22:58:16'),
230
('test2@testdomain.com', 1, '2002-06-19 15:22:19'),
231
('test2@testdomain.com', 2, '2002-06-18 14:23:47'),
232
('test3@testdomain.com', 1, '2002-05-19 22:17:32');
234
INSERT INTO t2(infoID, shipcode) VALUES
238
SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID;
239
SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC;
240
SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC;
244
# test with table.* in DISTINCT
247
CREATE TABLE t1 (privatemessageid int NOT NULL auto_increment, folderid int NOT NULL default '0', userid int NOT NULL default '0', touserid int NOT NULL default '0', fromuserid int NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int NOT NULL default '0', showsignature int NOT NULL default '0', iconid int NOT NULL default '0', messageread int NOT NULL default '0', readtime int NOT NULL default '0', receipt int NOT NULL default '0', deleteprompt int NOT NULL default '0', multiplerecipients int NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid));
248
INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0);
249
CREATE TABLE t2 (userid int NOT NULL auto_increment, usergroupid int NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid int NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser int 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 int NOT NULL default '0', showemail int NOT NULL default '0', invisible int NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle int NOT NULL default '0', joindate int NOT NULL default '0', cookieuser int NOT NULL default '0', daysprune int NOT NULL default '0', lastvisit int NOT NULL default '0', lastactivity int NOT NULL default '0', lastpost int NOT NULL default '0', posts int NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification int NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm int NOT NULL default '0', emailonpm int NOT NULL default '0', pmpopup int NOT NULL default '0', avatarid int NOT NULL default '0', avatarrevision int NOT NULL default '0', options int NOT NULL default '15', birthday date NULL, maxposts int NOT NULL default '-1', startofweek int NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int NOT NULL default '0', nosessionhash int NOT NULL default '0', autorefresh int NOT NULL default '-1', messagepopup int NOT NULL default '0', inforum int NOT NULL default '0', ratenum int NOT NULL default '0', ratetotal int NOT NULL default '0', allowrate int NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum));
250
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,NULL,-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1);
251
SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid);
255
# test with const_item in ORDER BY
258
CREATE TABLE t1 (a int primary key, b int, c int);
259
INSERT t1 VALUES (1,2,3);
260
CREATE TABLE t2 (a int primary key, b int, c int);
261
INSERT t2 VALUES (3,4,5);
262
SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c;
266
# Test of LEFT() with distinct
269
CREATE table t1 ( `id` int NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) AUTO_INCREMENT=3 ;
270
INSERT INTO t1 VALUES (1, 'aaaaa');
271
INSERT INTO t1 VALUES (3, 'aaaaa');
272
INSERT INTO t1 VALUES (2, 'eeeeeee');
273
select distinct left(name,1) as name from t1;
277
# Test case from sel000100
281
ID int NOT NULL auto_increment,
282
NAME varchar(75) DEFAULT '' NOT NULL,
283
LINK_ID int DEFAULT '0' NOT NULL,
286
KEY LINK_ID (LINK_ID)
289
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0);
290
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (2,'Jack',0);
291
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (3,'Bill',0);
294
ID int NOT NULL auto_increment,
295
NAME varchar(150) DEFAULT '' NOT NULL,
301
t2.id AS key_link_id,
304
LEFT JOIN t2 ON t1.link_id=t2.id
314
html varchar(5) default NULL,
319
INSERT INTO t1 VALUES ('1',1,0);
320
SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin;
324
# Test cases for #12625: DISTINCT for a list with constants
327
CREATE TABLE t1 (a int);
328
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
329
SELECT DISTINCT a, 1 FROM t1;
330
SELECT DISTINCT 1, a FROM t1;
332
CREATE TABLE t2 (a int, b int);
333
INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5);
334
SELECT DISTINCT a, b, 2 FROM t2;
335
SELECT DISTINCT 2, a, b FROM t2;
336
SELECT DISTINCT a, 2, b FROM t2;
340
# Bug#16458: Simple SELECT FOR UPDATE causes "Result Set not updatable"
343
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
344
INSERT INTO t1 VALUES (1,1), (2,1), (3,1);
345
EXPLAIN SELECT DISTINCT a FROM t1;
346
EXPLAIN SELECT DISTINCT a,b FROM t1;
347
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
348
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
349
WHERE t1_1.a = t1_2.a;
350
EXPLAIN SELECT a FROM t1 GROUP BY a;
351
EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
352
EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
354
CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT,
356
INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
357
EXPLAIN SELECT DISTINCT a FROM t2;
358
EXPLAIN SELECT DISTINCT a,a FROM t2;
359
EXPLAIN SELECT DISTINCT b,a FROM t2;
360
EXPLAIN SELECT DISTINCT a,c FROM t2;
361
EXPLAIN SELECT DISTINCT c,a,b FROM t2;
363
EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
364
CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);
365
EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
369
# Bug 9784 DISTINCT IFNULL truncates data
371
create table t1 (id int, dsc varchar(50));
372
insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three");
373
select distinct id, IFNULL(dsc, '-') from t1;
377
# Bug 21456: SELECT DISTINCT(x) produces incorrect results when using order by
379
CREATE TABLE t1 (a int primary key, b int);
381
INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2);
383
explain SELECT DISTINCT a, b FROM t1 ORDER BY b;
384
SELECT DISTINCT a, b FROM t1 ORDER BY b;
391
# Bug #15745 ( COUNT(DISTINCT CONCAT(x,y)) returns wrong result)
394
ID int NOT NULL auto_increment,
395
x varchar(20) default NULL,
396
y decimal(10,0) default NULL,
401
INSERT INTO t1 VALUES
409
select count(distinct x,y) from t1;
410
select count(distinct concat(x,y)) from t1;
414
# Bug #18068: SELECT DISTINCT
416
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
418
INSERT INTO t1 VALUES (1, 101);
419
INSERT INTO t1 SELECT a + 1, a + 101 FROM t1;
420
INSERT INTO t1 SELECT a + 2, a + 102 FROM t1;
421
INSERT INTO t1 SELECT a + 4, a + 104 FROM t1;
422
INSERT INTO t1 SELECT a + 8, a + 108 FROM t1;
424
EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
425
SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
430
# Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
432
CREATE TABLE t1 (a INT, UNIQUE (a));
433
INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3);
434
EXPLAIN SELECT DISTINCT a FROM t1;
435
#result must have one row with NULL
436
SELECT DISTINCT a FROM t1;
437
EXPLAIN SELECT a FROM t1 GROUP BY a;
438
#result must have one row with NULL
439
SELECT a FROM t1 GROUP BY a;
444
#Bug #27659: SELECT DISTINCT returns incorrect result set when field is
448
CREATE TABLE t1 (a INT, b INT);
449
INSERT INTO t1 VALUES(1,1),(1,2),(1,3);
450
SELECT DISTINCT a, b FROM t1;
451
SELECT DISTINCT a, a, b FROM t1;
454
--echo End of 5.0 tests
457
# Bug #34928: Confusion by having Primary Key and Index
459
CREATE TABLE t1(a INT, b INT, c INT, d INT DEFAULT 0, e INT DEFAULT 0,
460
PRIMARY KEY(a,b,c,d,e),
464
INSERT INTO t1(a, b, c) VALUES (1, 1, 1),
471
EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
473
SELECT DISTINCT a, b, d, c FROM t1;
477
--echo End of 5.1 tests