Programming

MySQL 5.6.10 handles INNER JOIN / subquery -pair differently than 5.5.29. I found out this by accident when working code ceased to return proper results.

Example setup, a very simple table and couple of rows:

CREATE TABLE `memberlevelhistory` (
  `member` tinyint(3) unsigned NOT NULL,
  `changedate` date NOT NULL,
  `level` int(10) unsigned NOT NULL,
  `approved` char(1) NOT NULL,
  PRIMARY KEY (`changedate`,`member`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `memberlevelhistory`
(`member`, `changedate`, `level`, `approved`)
VALUES
(5, '2009-08-01', 1, 'Y'),
(5, '2009-08-27', 2, 'Y'),
(5, '2009-10-01', 4, 'Y'),
(5, '2010-01-01', 5, 'Y'),
(5, '2010-02-01', 8, 'Y'),
(5, '2010-03-15', 9, 'Y'),
(5, '2011-02-01', 11, 'Y'),
(5, '2011-05-01', 12, 'Y'),
(5, '2012-02-01', 13, 'Y'),
(5, '2012-03-01', 14, 'Y'),
(5, '2012-04-01', 15, 'Y');

Description of columns:

• member: user ID
• changedate: when member lever was changed
• level: user level
• approved: level change approved by administration
  

The idea of the table is that most recent approved level is user's current level.

Example query to get user's current approved level with total number of approved user levels:

SELECT mlh.changedate, count(*), mlh.level
FROM `memberlevelhistory` mlh
INNER JOIN (
    SELECT member, changedate, level
    FROM `memberlevelhistory`
    WHERE member = 5
    AND approved <> 'N'
    ORDER BY `changedate` DESC
) AS `mlh2` ON mlh.member = mlh2.member AND mlh.changedate = mlh2.changedate
WHERE mlh.member = 5
AND mlh.approved <> 'N'

MySQL 5.5 result, current level as expected:

+------------+----------+-------+
| changedate | count(*) | level |
+------------+----------+-------+
| 2012-04-01 |       11 |    15 |
+------------+----------+-------+
1 row in set (0.00 sec)

MySQL 5.6 result, a surprise here:

+------------+----------+-------+
| changedate | count(*) | level |
+------------+----------+-------+
| 2009-08-01 |       11 |     1 |
+------------+----------+-------+
1 row in set (0.00 sec)

The query behaviour has changed. The subquery ORDER BY -clause has no effect. I did solve the problem of latest level with LIMIT 1 in the subquery, but it ruins the COUNT(*). I'm still working to replicate the 5.5 result in a single query, if a solution can be found, I'll blog about it.