Sometimes it’s the little things

If you write enough reports in SQL, eventually you need to dynamically generate dates and such and either populate a table with a large number of sequential integers, or do something insane with UNIONs to produce the numbers. At least, I eventually always need to do this. One of the new features in our upcoming MariaDB 10 release simplifies that for me: the SEQUENCE storage engine. You can read about the SEQUENCE storage engine in the MariaDB Knowledge Base. Installing it is simple enough:

INSTALL PLUGIN sequence SONAME 'ha_sequence.so';

Usage is also simple enough:

USE test;
SELECT seq FROM seq_1_to_5;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+

SELECT seq FROM seq_1_to_10_step_3;
+-----+
| seq |
+-----+
|   1 |
|   4 |
|   7 |
|  10 |
+-----+

SELECT seq FROM seq_1_to_10_step_3 WHERE seq % 2;
+-----+
| seq |
+-----+
|   1 |
|   7 |
+-----+

This has a number of uses, but a couple of examples loosely based on things I do:

SELECT NOW() - INTERVAL (seq) DAY x FROM seq_1_to_5;
+---------------------+
| x                   |
+---------------------+
| 2014-01-27 18:23:23 |
| 2014-01-26 18:23:23 |
| 2014-01-25 18:23:23 |
| 2014-01-24 18:23:23 |
| 2014-01-23 18:23:23 |
+---------------------+

SELECT LAST_DAY(NOW() - INTERVAL (seq) MONTH) x FROM seq_1_to_15;
+------------+
| x          |
+------------+
| 2013-12-31 |
| 2013-11-30 |
| 2013-10-31 |
| 2013-09-30 |
| 2013-08-31 |
| 2013-07-31 |
| 2013-06-30 |
| 2013-05-31 |
| 2013-04-30 |
| 2013-03-31 |
| 2013-02-28 |
| 2013-01-31 |
| 2012-12-31 |
| 2012-11-30 |
| 2012-10-31 |
+------------+

It behaves like any other table, so you can JOIN against it, use ORDER BY operations and so on, opening up a number of other possibilities. It simplifies a lot of things for me, and hopefully I’ll never do this again:

SELECT (a+b) x FROM ( SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) zz1 CROSS JOIN ( SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) zz2;

🙂 Give it a try! Or, if you want to read even more about it, check out Federico Razzoli’s blog post.