Programming

Gillian from Oracle informed me that my query is not valid SQL and the 5.5 version worked just because I was lucky.

The correct way of using aggregate function count() is something like this:

SELECT mlh.changedate, mlh_latest.counts, mlh.level
FROM memberlevelhistory mlh
INNER JOIN (
    SELECT member, MAX(changedate) as maxdate, COUNT(changedate) as counts
    FROM memberlevelhistory
    WHERE member = 5
    AND approved <> 'N'
) AS mlh_latest ON mlh.member = mlh_latest.member AND mlh.changedate = mlh_latest.maxdate
WHERE mlh.member = 5
AND mlh.approved <> 'N';

Now the result is equally correct on both tested versions.

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.

SQLite has very little support for typical arithmetic functions. In the SQLite contrib-section there is an extension for that by Liam Healy. The description goes:

Provide mathematical and string extension functions for SQL queries using the loadable extensions mechanism.

• Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi
• String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter
• Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile

To ease the installation, I packaged into into a RPM for CentOS 6:

• sqlite-extension-functions-2010.01.06-1.x86_64.rpm
• sqlite-extension-functions-2010.01.06-1.src.rpm

The source-RPM will build quite easily on any RPM-disto. There are no weird dependecies or anything.

Example usage:

# sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load libsqlitefunctions.so
sqlite> select floor(1.9);
1
sqlite> select ceil(2.1);
3
sqlite> select reverse("reverse");
esrever
sqlite> .quit

Now developing apps with SQLite back-end is much easier.

The puzzle at Mystery Hunt site. A PDF-version of it, to solve it with paper and pencil.

I wrote the regexp-puzzle into a HTML / JavaScript -page, go see it at http://opensource.hqcodeshop.com/grid/. It will display the regexp with red colour if it does not match.

This one is really tough to solve. Good luck!