Hacker's ramblings

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.

I got bunch of automated comments to this blog. The comments were very generic about "how great this blog is" and "how fast the site loads", blah. blah. I typically check the moderation box for my blog entries, so they were just hoping to get automated publicity. In my case I just deleted the crap.

The idea of this spam-campaign was to distribute links to freemasonrysecrets.com

WTF?! Who cares about that?