Search code examples
phpmysqljoininnodbmysql-5.7

Upgrade to mysql 5.7 from 5.5, MyISAM to InnoDB, LEFT JOIN and NULL issue

I've recently updated a database from MySQL 5.5 with MyISAM tables to MySQL 5.7 InnoDB tables. Most things are working absolutely fine, but there's a few particular queries that are giving strange results.

Background: A suggestion is first approved and can then be voted on. A suggestion is visible to only the suggester before it is approved.

Query:

SELECT description, suggested_by, voted, votes
FROM shop_suggestions s
LEFT JOIN (SELECT suggestion, 1 AS voted FROM shop_suggestion_votes WHERE student = 60910) AS voted ON (voted.suggestion = s.id)
LEFT JOIN (SELECT suggestion, COUNT(student) AS votes FROM shop_suggestion_votes GROUP BY suggestion) AS votes ON (votes.suggestion = s.id)
WHERE  approved > 0 OR suggested_by = 60910 ORDER BY votes
  • The first join (LEFT) is to see if the voter has already voted on this
  • The second join (LEFT) is to see how many votes there are in total

What's really strange, is that when the ORDER BY votes part is there, the voted value is always 1, whereas if it's left off, it's NULL as expected when somebody hasn't voted.

This behaviour is different to what it was before my upgrade, where the query worked perfectly. I'm assuming there's some kind of logic error somewhere, but I can't figure it out. Any help would be greatly appreciated!!

Solution

  • Your query looks all right to me. But, you can simplify it:

    SELECT description, suggested_by, voted, votes
FROM shop_suggestions s LEFT JOIN
     (SELECT suggestion, COUNT(student) as votes,
             MAX(student = 60910) as voted
      FROM shop_suggestion_votes
      GROUP BY suggestion
     ) votes
     ON (votes.suggestion = s.id)
WHERE approved > 0 OR suggested_by = 60910
ORDER BY votes;

    If I had to speculate on the actual problem, I would guess that you over-simplified the query and have removed the offending problem. Some commonly (mis)used features of MySQL are documented not to always work, but they are used anyway. One that comes to mind is the use of columns in a select that are not in a group by. Another is the order of evaluation of expressions that use variables. Your code has neither of these, but perhaps your original code has something suspicious.