I love Markus Winand.  Use the Index, Luke taught me a lot about SQL.

Markus has produced a coping strategy for some of MySQL’s limitations in generating sequences.  I present an alternative form which produces arbitrary sized ranges, which he seems to believe is not possible.  Ah, Markus: it is.

So, this article is actually wrong.  There’s a call-time limitation I didn’t know about: apparently in MySQL you can’t call a stored procedure from a query.  That renders this seriously useless.  I’m leaving the article up just for the sake of permanence.

This advice is wrong.  Do not believe it.  I was in error.  It is kept only for posterity.

-- -------
--
--  in re: http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator
--
--  No, this way is not our only hope: there is another.

drop procedure if exists nseq;

delimiter //

-- -------
--
--  Use union all to generate a 2-row table.  
--  Use log 2 to find power expansion count to cap the desired size.
--  Use cross joins to expand our 2-row to the smallest superior table.
--  Select the diminished part to get our sequence.

create procedure nseq(in upto integer)
begin

    declare itLeft integer default -1;

    set itLeft = ceil(log2(upto));

    set @front := 'select rownum from (select @rownum := @rownum+1 as rownum
                   from (SELECT @rownum := 0) r, (select 1 as hidden union
                   all select 2) a0';
    set @upTo  := upto;

    set @interior := '';
    while (itLeft > 1) do
        set @interior := concat(@interior, ' cross join (select 1 as hidden
                         union all select 2) a', cast(itLeft as char));
        set itLeft    := itLeft - 1;
    end while;

    set @back  := ') sl where rownum <= ?;';
    set @query := concat(@front, @interior, @back);

    prepare stmt from @query;
    execute stmt using @upTo;  
    deallocate prepare stmt;  

end //

delimiter ;

Hopefully he will agree, or explain what I didn’t understand in his needs.