... now with even more SQL

A couple of days ago I wrote a post about creating a Mandelbrot set in SQL in which I mentioned that MySQL doesn't have any means of creating an image. The idea of this would be as outlandish as the flight simulator file browser they put in Jurassic Park to make computers look interesting - there is no way that such a thing would ever exist in real life. I was surprised, therefore, when a colleague suggested using Netpbm, a 'lowest common denominator' image format that can be created as a text file. This, together with MySQL's ability to write the result of a query to a file, means that the whole thing can be done via SQL without resorting to any perl. The only downside is that the file is created with the permissions of the system user running the database server and, as a security measure, it will not write to a file that already exists. This means that the whole file must be created by a single query, superuser privilege is required if you want to delete the file and the file must be deleted before attempting to create it again. The solution looks like:

SELECT @xmax:=COUNT(c_re) INTO @xmax FROM points GROUP BY c_im LIMIT 1;
SELECT @ymax:=COUNT(c_im) INTO @ymax FROM points GROUP BY c_re LIMIT 1;
SET group_concat_max_len=11*@xmax*@ymax;
SELECT
  'P3', @xmax, @ymax, 200,
  GROUP_CONCAT(
    CONCAT(
      IF( active=1, 0, 55+MOD(steps, 200) ), ' ',
      IF( active=1, 0, 55+MOD(POWER(steps,3), 200) ), ' ',
      IF( active=1, 0, 55+MOD(POWER(steps,2), 200) ) )
    ORDER BY c_im ASC, c_re ASC SEPARATOR ' ' )
    INTO OUTFILE '/tmp/image.ppm'
  FROM points;

The output filename may need to be changed depending on operating system. The output image looks like the one to the right although this image has been converted into a png file as Picasa doesn't like Netpbm files and, furthermore, the png file is about 1% of the size. Theoretically it should be possible to create the ppm file in the slightly smaller binary format with the code below but for some reason it doesn't work for me:

SELECT @xmax:=COUNT(c_re) INTO @xmax FROM points GROUP BY c_im LIMIT 1;
SELECT @ymax:=COUNT(c_im) INTO @ymax FROM points GROUP BY c_re LIMIT 1;
SET group_concat_max_len=3*@xmax*@ymax;
SELECT
  'P6', @xmax, @ymax, 200,
  GROUP_CONCAT(
    CONCAT(
      CHAR(IF( active=1, 0, 55+MOD(steps, 200) ) ),
      CHAR(IF( active=1, 0, 55+MOD(POWER(steps,3), 200) ) ),
      CHAR(IF( active=1, 0, 55+MOD(POWER(steps,2), 200) ) ) )
    ORDER BY c_im ASC, c_re ASC SEPARATOR '' )
    INTO OUTFILE '/tmp/image.ppm'
  FROM points;

I would be interested to know if anyone can see the problem.