Mysql – MariaDB, SEQUENCE engine, dynamic upper bound

mariadbMySQLsequencesubquery

I'd like to generate a sequence in MariaDB starting at 1 and ending with whatever is the largest id in some other table. That other table has roughly this schema:

create table main (
  id integer primary key,
  -- ... other columns --
);

I've learned about MariaDB STORAGE engine for generating sequences but on the surface that's good only for generating static sequences, where you know the range at the time of writing the query, e.g.

MariaDB [skynetdb]> select * from seq_1_to_3;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
3 rows in set (0.001 sec)

But how do I generate sequence whose upper bound is the result of select max(id) from main? It seems that MariaDB doesn't accept meta-queries where I would construct a name of a table to query and then query it in a single query, e.g. something like this:

-- INVALID, FOR ILLUSTRATION ONLY
select * from concat('seq_1_to_', select max(id) from main)

Or is it possible? How? (Note I'm aware that I can do one additional roundtrip and construct the sequence table name on the client, but I want to avoid it because I don't have a feature rich client, only mysql command)

The following recursive common table expression (CTE) works

MariaDB [skynetdb]> with recursive nums(n) as (select 1 union all select n+1 from nums where n <= (select max(id) from main)) select * from nums;

but it's too slow. It seems it constructs whole nums table in memory first.

Best Answer

SELECT seq
    FROM seq_0_to_99999999
    WHERE seq BETWEEN 12345678 and 12345688;

works fine and fast. It returned 11 rows.

A more complex query:

SELECT MIN(FROM_DAYS(seq)) AS first_day,
       MAX(FROM_DAYS(seq)) AS last_day,
       COUNT(*) AS number_of_days
    FROM seq_0_to_99999999
    WHERE seq BETWEEN TO_DAYS('2020-01-01')
                  AND TO_DAYS(CURDATE());

Returned:

+------------+------------+----------------+
| first_day  | last_day   | number_of_days |
+------------+------------+----------------+
| 2020-01-01 | 2020-04-02 |             93 |
+------------+------------+----------------+
1 row in set (0.00 sec)

It, too, was fast, and touched only 94 rows according to the 'Handler_read%' values. Cf: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

For your case, you may need something like

WHERE seq BETWEEN ...
              AND ( SELECT MAX(...) FROM ... )

I find that the general use of seq is to generate a generous number of values, then let WHERE go for the range I need. The Optimizer avoids really generating the zillion values I seem to be asking for. (At least in examples like these.)

Related Question