SQL Antipatterns, Volume 1: join error (page 176)

Community PragProg Customers
,
1

@billkarwin

On page 176 , the derived-table-no-duplicates.sql code would not get the correct result if multiple product_id with the same latest date_reported value exist.

For example, product_id 2 has a bug_id 3456 and the latest reported_date is ‘2010-02-16’. But if product_id 3 has a bug_id 5999 with the same latest reported_date ‘2010-02-16’, then the query will get a wrong bug_id for product_id 2.

2

Thanks for the feedback blabla_bingo, I see the error. Yes, this is a mistake.

Here’s a working query for that example:


SELECT m.product_id, m.latest, MAX(b1.bug_id) AS latest_bug_id
FROM Bugs b1
JOIN BugsProducts p USING (bug_id)
JOIN (
  SELECT product_id, MAX(date_reported) AS latest
   FROM Bugs b2 JOIN BugsProducts USING (bug_id)
   GROUP BY product_id
) m
  ON (b1.date_reported = m.latest AND p.product_id = m.product_id)
GROUP BY m.product_id, m.latest;

I’ll try to get this correction into the next printing of the book.