Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.10, 10.0.11, 10.0.13, 10.1.0
-
OS: doesn't matter (works on both Linux or Windows)
Hardware: doesn't matter
Description
Reproducing
- Create two simple tables and fill them with rows:
CREATE TABLE `table1` (
`id` INT AUTO_INCREMENT,
`link` INT,
`field1` INT,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB;
CREATE TABLE `table2` (
`id` INT AUTO_INCREMENT,
`link` INT,
`field2` INT,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB;
INSERT INTO `table1` (`link`, `field1`) VALUES (3, 1);
INSERT INTO `table1` (`link`, `field1`) VALUES (1, 2);
INSERT INTO `table1` (`link`, `field1`) VALUES (2, 3);
INSERT INTO `table2` (`link`, `field2`) VALUES (2, 1);
INSERT INTO `table2` (`link`, `field2`) VALUES (3, 1);
- (optional) Enable outer_join_with_cache optimization, if it's disabled by default:
SET SESSION optimizer_switch='outer_join_with_cache=on';
- Select all rows, joining by `link`, ordering by `field1`, using group_concat function in select query:
select
field1, t1.link, field2, group_concat('any string or field')
from table1 t1
left join table2 t2 on t1.link = t2.link
group by field1
Notice everything works fine now:
field1 link field2 group_concat 1 3 1 any string of field 2 1 NULL any string of field 3 2 1 any string of field - Disable outer_join_with_cache optimization:
SET SESSION optimizer_switch='outer_join_with_cache=off';
- Repeat step 3.
Notice that the result of the query has been changed: there is NULL value instead of 1 - it's wrong:field1 link field2 group_concat 1 3 NULL any string of field 2 1 NULL any string of field 3 2 1 any string of field - If your database is small you can control this behavior by switching outer_join_with_cache on and off, otherwise it seems like outer_join_with_cache is always turned off, so it always reproduces on big tables, whether outer_join_with_cache is on or off.
Other points
- Using group_concat seems to be a necessary condition for reproducing this bug.
- If you will order the query in step 3 by `t1.link`:
select
field1, t1.link, field2, group_concat('any string or field')
from table1 t1
left join table2 t2 on t1.link = t2.link
group by field1
order by t1.link
everything will work fine:
field1 link field2 group_concat 2 1 NULL any string of field 3 2 1 any string of field 1 3 1 any string of field
Attachments
Issue Links
- relates to
-
MDEV-5719 Wrong result with GROUP BY and LEFT OUTER JOIN
- Closed
Thanks for the report and for the complete test case.
The problem is probably the same as, or related to,
MDEV-5719. I'm assigning it to psergey to check the provided test case after the fix forMDEV-5719.