Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
5.3.12, 5.5.33a
-
None
-
None
Description
drop table if exists t1; |
create table t1 ( |
id text not null |
,qty int not null |
) engine=innodb charset=utf8;
|
insert into t1(id,qty) values ('a',2),('a',2),('a',3),('a',4),('b',2),('c',1),('c',2); |
expected output
select a.*, b.* |
from t1 a |
left outer join t1 b |
on a.id = b.id |
and a.qty = b.qty; |
+----+-----+------+------+
|
| id | qty | id | qty |
|
+----+-----+------+------+
|
| a | 2 | a | 2 |
|
| a | 2 | a | 2 |
|
| a | 2 | a | 2 |
|
| a | 2 | a | 2 |
|
| a | 3 | a | 3 |
|
| a | 4 | a | 4 |
|
| b | 2 | b | 2 |
|
| c | 1 | c | 1 |
|
| c | 2 | c | 2 |
|
+----+-----+------+------+
|
9 rows in set (0.01 sec)
|
buggy query
select a.*, b.* from ( |
select |
id, qty
|
from t1 |
group by id,qty |
) a
|
left outer join ( |
select |
id, qty
|
from t1 |
group by id,qty |
) b
|
on a.id = b.id |
and a.qty = b.qty; |
+----+-----+------+------+
|
| id | qty | id | qty |
|
+----+-----+------+------+
|
| a | 2 | NULL | NULL |
|
| a | 3 | NULL | NULL |
|
| a | 4 | NULL | NULL |
|
| b | 2 | NULL | NULL |
|
| c | 1 | NULL | NULL |
|
| c | 2 | NULL | NULL |
|
+----+-----+------+------+
|
6 rows in set (0.01 sec)
|
inner join not affected
select a.*, b.* from ( |
select |
id, qty
|
from t1 |
group by id,qty |
) a
|
inner join ( |
select |
id, qty
|
from t1 |
group by id,qty |
) b
|
on a.id = b.id |
and a.qty = b.qty; |
+----+-----+----+-----+
|
| id | qty | id | qty |
|
+----+-----+----+-----+
|
| a | 2 | a | 2 |
|
| a | 3 | a | 3 |
|
| a | 4 | a | 4 |
|
| b | 2 | b | 2 |
|
| c | 1 | c | 1 |
|
| c | 2 | c | 2 |
|
+----+-----+----+-----+
|
6 rows in set (0.01 sec)
|
strcmp resolves
select a.*, b.* from ( |
select |
id, qty
|
from t1 |
group by id,qty |
) a
|
left outer join ( |
select |
id, qty
|
from t1 |
group by id,qty |
) b
|
on strcmp(a.id,b.id) = 0 |
and a.qty = b.qty; |
+----+-----+------+------+
|
| id | qty | id | qty |
|
+----+-----+------+------+
|
| a | 2 | a | 2 |
|
| a | 3 | a | 3 |
|
| a | 4 | a | 4 |
|
| b | 2 | b | 2 |
|
| c | 1 | c | 1 |
|
| c | 2 | c | 2 |
|
+----+-----+------+------+
|
6 rows in set (0.01 sec)
|
create identicial table with varchar id field
drop table if exists t2; |
create table t2 ( |
id varchar(255) not null |
,qty int not null |
) engine=innodb charset=utf8;
|
insert into t2(id,qty) values ('a',2),('a',2),('a',3),('a',4),('b',2),('c',1),('c',2); |
buggy query is resolved with varchar
select a.*, b.* from ( |
select |
id, qty
|
from t2 |
group by id,qty |
) a
|
left outer join ( |
select |
id, qty
|
from t2 |
group by id,qty |
) b
|
on a.id = b.id |
and a.qty = b.qty; |
+----+-----+------+------+
|
| id | qty | id | qty |
|
+----+-----+------+------+
|
| a | 2 | a | 2 |
|
| a | 3 | a | 3 |
|
| a | 4 | a | 4 |
|
| b | 2 | b | 2 |
|
| c | 1 | c | 1 |
|
| c | 2 | c | 2 |
|
+----+-----+------+------+
|
6 rows in set (0.01 sec)
|