A better approach to MySQL row generation

3:01 pm SQL, SQL

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

2 Responses

  1. Markus Winand Says:

    Hi John!

    But you cannot use a procedure in an SQL query? That’s why I went for the view approach.

    If there is a way to use the output of your nseq procedure for the join in the introductory example from my article, I would love to learn about that.

  2. John Haugeland Says:

    I … had no idea you couldn’t call a procedure in a query. The only times I use stored procedures are when I’m going serious hardass, and when I’m going serious hardass, *everything* is a procedure or a function.

    How’s that for egg on one’s face? :D

    Thanks for clarifying. Editing the article to make clear my error.

Leave a Comment

Your comment

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.