MySQL - Group By - How does it determine which row to return from joined table

Question

I am getting desired, but inexplicable behavior on a particular query, and wanted to get more information before pushing up.

I have 3 tables:

foo

bar

bar_image

And paring back all distractors, the fundamental query looks like this:

SELECT
    f.id AS foo_id,
    b.id AS bar_id,
    COALESCE(bi.image_path, b.legacy_image_path) AS image_path
FROM
    foo AS f
INNER JOIN bar AS b ON b.foo_id = f.id
LEFT OUTER JOIN bar_image AS bi ON bi.bar_id = b.id AND bi.deleted_at > f.created_at
GROUP BY
    f.id

And the sample sets:

; The following query will result in image_path = a.png due to no matching rows in bar_image

foo
    id: 1
    created_at: 1000

bar
    id: 1
    legacy_image_path: a.png

bar_image
    {empty}

; New foo row created, after image replacement
; Above query will return
; foo: 1, image_path: a.png
; foo: 2, image_path: b.png

foo
    id: 2
    created_at: 2000

bar
    id: 1
    legacy_image_path: b.png

bar_image
    id: 1
    image_path: a.png
    deleted_at: 1500

; And another iteration for sake of thoroughness
; Another foo row created, after another image replacement
; Above query will return
; foo: 1, image_path: a.png
; foo: 2, image_path: b.png
; foo: 3, image_path: c.png

foo
    id: 3
    created_at: 3000

bar
    id: 1
    legacy_image_path: c.png

bar_image
    id: 2
    image_path: b.png
    deleted_at 2500

As you can see, with the group by on the foo table, and the given join to the bar_image table, each foo is correctly displaying the proper image_path, which is desired, but I cannot explain why this is occurring.

When running the query after all the new rows were inserted, foo:1 would return two image rows from bar_image table, but the group by is truncating them to a single row, how does MySQL know to select the closest value, vs some other (seemingly random) row?

Answer 1

MySQL will pick a row, depending on the index used in the execution of the query. If indexes are added or dropped, if tables grow, if version of optimizer changes, if the moon is full, there is a chance that a different index is used and different results you get. This type of query is inconsistent, despite appearing consistent (usually with small tables). See https://stackoverflow.com/questions/1645921/mysql-group-by-behavior

Also read: Why does MySQL add a feature that conflicts with SQL standards? and MySQL docs (5.5): handling of GROUP BY And the changes to be done in the (still in development 5.7): MySQL docs (5.7): handling of GROUP BY

And lastly, a post in Roland Bouman's blog (about these last changes: MySQL 5.7.5: GROUP BY respects functional dependencies!, related to his (7 year old one) post: Debunking GROUP BY myths, where he discussed this mysql culprit/bug/feature (however you want to call it). - https://dba.stackexchange.com/users/993