Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.39, 11.3.2
-
None
-
Linux and Windows
Description
Let's create samle data:
CREATE TABLE foo(id int, name varchar(200)); |
CREATE TABLE bar(our_id int, foo_id int, tag varchar(200)); |
INSERT INTO foo VALUES(1,'test'); |
INSERT INTO foo VALUES(2,'tost'); |
INSERT INTO bar VALUES(1,1,'tag1'); |
INSERT INTO bar VALUES(1,2,'tag2'); |
INSERT INTO bar VALUES(2,1,'tag3'); |
INSERT INTO bar VALUES(2,2,'tag4'); |
So the tables look like this:
select * from foo; |
+------+------+ |
| id | name | |
+------+------+ |
| 1 | test |
|
| 2 | tost |
|
+------+------+ |
2 rows in set (0.000 sec) |
|
|
select * from bar; |
+--------+--------+------+ |
| our_id | foo_id | tag |
|
+--------+--------+------+ |
| 1 | 1 | tag1 |
|
| 1 | 2 | tag2 |
|
| 2 | 1 | tag3 |
|
| 2 | 2 | tag4 |
|
+--------+--------+------+ |
Now when we query the data for id 1 and 2 the results are expected:
Regular join with group_concat
select f.id, b.foo_id as fid, group_concat('foo') as tags from foo f join bar b on b.foo_id = f.id where f.id = 1; |
+------+------+---------+ |
| id | fid | tags |
|
+------+------+---------+ |
| 1 | 1 | foo,foo |
|
+------+------+---------+ |
|
|
select f.id, b.foo_id as fid, group_concat('foo') as tags from foo f join bar b on b.foo_id = f.id where f.id = 2; |
+------+------+---------+ |
| id | fid | tags |
|
+------+------+---------+ |
| 2 | 2 | foo,foo |
|
+------+------+---------+ |
Join with SELECT without group_concat:
select f.id, sub.fid from foo f join (select b.foo_id as fid from bar b) sub on sub.fid = f.id where f.id = 1; |
+------+------+ |
| id | fid |
|
+------+------+ |
| 1 | 1 |
|
| 1 | 1 |
|
+------+------+ |
2 rows in set (0.000 sec) |
|
|
select f.id, sub.fid from foo f join (select b.foo_id as fid from bar b) sub on sub.fid = f.id where f.id = 2; |
+------+------+ |
| id | fid |
|
+------+------+ |
| 2 | 2 |
|
| 2 | 2 |
|
+------+------+ |
and simple query with group_concat:
select b.foo_id as fid, group_concat('foo') as tags from bar b where b.foo_id = 1; |
+------+---------+ |
| fid | tags |
|
+------+---------+ |
| 1 | foo,foo |
|
+------+---------+ |
1 row in set (0.000 sec) |
|
|
select b.foo_id as fid, group_concat('foo') as tags from bar b where b.foo_id = 2; |
+------+---------+ |
| fid | tags |
|
+------+---------+ |
| 2 | foo,foo |
|
+------+---------+ |
So far so good. But now, when we want to use simple query from the above as subquery to join with, the results are:
select f.id, sub.fid from foo f join (select b.foo_id as fid, group_concat('foo') as tags from bar b) sub on sub.fid = f.id where f.id = 1; |
+------+------+ |
| id | fid |
|
+------+------+ |
| 1 | 1 |
|
+------+------+ |
1 row in set (0.001 sec) |
|
|
select f.id, sub.fid from foo f join (select b.foo_id as fid, group_concat('foo') as tags from bar b) sub on sub.fid = f.id where f.id = 2; |
Empty set (0.001 sec) |
while expected behaviour is that second query also works fine and returns data (same as the first one)