0

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?

  • 4
    MySQL has an interesting GROUP BY behaviour, in that it will return any old crap where a proper RDBMS would instead throw an error. stackoverflow.com/questions/1645921/mysql-group-by-behavior – Philᵀᴹ Mar 11 '15 at 22:32
  • 1
    I suppose that the INNER JOIN bar AS b ON b.foo_id = b.id have a typo and should be INNER JOIN bar AS b ON b.foo_id = f.id – Giovanni Mar 12 '15 at 14:45
  • I have difficulty reading the sample data because columns bar.foo_id and bar_image.bar_id are missing. – Giovanni Mar 12 '15 at 14:48
  • 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). – ypercubeᵀᴹ Mar 12 '15 at 18:22
  • 1
    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). – ypercubeᵀᴹ Mar 12 '15 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.