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?