posts about mistakes

Writing FizzBuzz in MySQL 5.7 for no good reason

Why are you in this doorless room? There's nothing but a keyboard -- black, unmarked keycaps, promising auditory violence -- and a monitor showing a terminal connection to a MySQL database. You type show engine innodb status; (each keystroke sharp & piercing) and a challenge scrolls into view: "Write FizzBuzz from 1 to 1000." You smile.

Writing FizzBuzz in MySQL 5.7 is totally useless, but it was a fun excuse to explore some MySQL features I haven't ever needed to use. Translating a number into the appropriate value is relatively simple, but because MySQL 5.7 lacks features like range() or rank(), generating the list of numbers from 1 to 1000 ends up being trickier than I expected. If you want to give this a go before reading the post, docker run --platform linux/amd64 --env MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:5.7; docker exec -it CONTAINER_ID mysql should set up and connect to a MySQL docker container (the --platform is necessary if you're on a machine with an M1).

SELECT CASE
    WHEN n % 15 = 0 THEN 'fizzbuzz'
    WHEN n % 5  = 0 THEN 'buzz'
    WHEN n % 3  = 0 THEN 'fizz'
    ELSE n
END fizz_buzz
FROM how_do_we_possibly_get_numbers;

Using a session variable to emulate rank

Documentation dances before your mind as your fingers fly on the keyboard. You just need a table to abuse and then you'll be free. You hunt through the information_schema to find your prey.

MySQL supports both global and per-session variables. I've never needed to use a per-session variable, but it does let you create a rank-like effect and generate a list of numbers.

... (
  select (@rank := ifnull(@rank, 0) + 1) n
  from information_schema.character_sets set1,
    information_schema.character_sets set2
  WHERE @rank < 1000;) as numbers 
);
set @rank = 0;

Creating a stored routine to set up a fizzbuzz table

You hear a door open behind you, but you don't turn around. It surely wouldn't hurt to try solving this in a more reasonable way. Relying on an existing table is inelegant. FizzBuzz deserves better.

Stored routines in MySQL can complicate understanding what a code-base is doing, but they are incredibly powerful. Using them, it's possible to iterate through a range of numbers, which is exactly what we need for our fizz-buzz problem.

drop table if exists fizz_buzz;
create table fizz_buzz (fizz_buzz varchar(10));

delimiter #

create procedure load_fizzbuzz(IN in_max int)

begin

declare v_max int unsigned default in_max;
declare v_i int unsigned default 0;

  truncate table fizz_buzz;
  start transaction;
  while v_i <= v_max do
    insert into fizz_buzz values (
      CASE
          WHEN v_i % 15 = 0 THEN 'fizzbuzz'
          WHEN v_i % 5  = 0 THEN 'buzz'
          WHEN v_i % 3  = 0 THEN 'fizz'
          ELSE v_i
      END
    );
    set v_i=v_i + 1;
  end while;
  commit;

end #

delimiter ;

call load_fizzbuzz(1000);
select * from fizz_buzz;

(another alternative here is creating a "loadable function". At some point, I'm tempted to give that a go and see how hard it'd be)

Using a view of digits with a cross-join

Workable, but inelegant. It surely wouldn't hurt to spend a little bit more time with the problem. Fizz, 4, Buzz, 5...

If we set up a digits view that has 0-9, we can do a cross join and then do a little bit of addition to generate 1-1000. We could inline the digits queries as well.

Interestingly, the same technique doesn't work with a temporary table: it turns out you can only reference a temporary table once in a query.

create or replace view digits as
  select 0 as d
  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;
select 1 + h * 100 + t * 10 + o n
from (
  select h.d as h, t.d as t, o.d as o
  FROM digits as h, digits as t, digits as o
) nums ORDER BY 1 ASC;

Failing to load a function

Better, but too slow. Your heart cries out for more FizzBuzz-ing speed. What if you want to fizz-buzz up to a million? Or a billion? There must be a better way.

It's possible to extend MySQL with loadable functions that run C or C++ code. Doing it this way lets us use a language that's slightly better suited to the problem. The sequence example from udf_example.cc is already set up to generate a list of numbers, but my complete lack of cmake knowledge meant that I wasn't able to get this working. I'm excited to come back to it when I've got a bit more time to play around!

Storing values in a table and taking advantage of an auto-incrementing primary key

"fizz, 1468, 1469, fizzbuzz, 1471..." The realization that you've been saying FizzBuzz to yourself for the past few minutes should bother you, but the chant feel right in your mouth. The percussive clicks from the keyboard keep time to the ever-increasing count.

I thought this would work, but it doesn't! I think there's a race condition in the primary key insertion code because despite inserting > 1000 rows, we end up consistently missing the same few digits between 41 and 60. I was surprised to see that this one didn't work! I'd love to have a better idea of what's going wrong.

drop table if exists nums;
create table nums (n int auto_increment, primary key(n));
insert into nums values (), ();
insert into nums () select null from nums n1, nums n2, nums n3, nums n4, nums n5;
insert into nums () select null from nums n1, nums n2;

select all_nums.n
from (
  select 1 + h * 100 + t * 10 + o n
  from (
    select h.d as h, t.d as t, o.d as o
    FROM digits as h, digits as t, digits as o
  ) nums
) all_nums
LEFT JOIN nums ON all_nums.n = nums.n
where nums.n is null;

You rip your hands from the keyboard. There's only one thing that could tear you away from the problem: the breeze from the open door is redolent with the sharp scent of coffee. You touch the monitor gently with a softly spoken promise to return.