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
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.