- Create two simple tables and fill them with rows:
- (optional) Enable outer_join_with_cache optimization, if it's disabled by default:
- Select all rows, joining by `link`, ordering by `field1`, using group_concat function in select query:
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:
- 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.
- 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`:
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