Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

This query performs a FizzBuzz in SQLite 2.8.17 without creating any tables or sprocs.

First attempt:

select case when fb <> '' then fb else n end from (
    select g + f * 2 + e * 4 + d * 8 
        + c * 16 + b * 32 + a * 64 as n,
    coalesce(fizz, '') || coalesce(buzz, '') as fb 
    from
    (select 0 union select 1 as a),
    (select 0 union select 1 as b),
    (select 0 union select 1 as c),
    (select 0 union select 1 as d),
    (select 0 union select 1 as e),
    (select 0 union select 1 as f),
    (select 0 union select 1 as g)
    left join (select 'Fizz' as fizz, 3 as fizzstep)
        on n % fizzstep = 0 
    left join (select 'Buzz' as buzz, 5 as buzzstep)
        on n % buzzstep = 0
    where n between 1 and 100
);

Second attempt:

select case
    when n % 3 = 0 and n % 5 = 0 then 'FizzBuzz'
    when n % 3 = 0 then 'Fizz'
    when n % 5 = 0 then 'Buzz'
    else n
end from (
    select g + f * 2 + e * 4 + d * 8 + 
        c * 16 + b * 32 + a * 64 as n from
    (select 0 union select 1 as a),
    (select 0 union select 1 as b),
    (select 0 union select 1 as c),
    (select 0 union select 1 as d),
    (select 0 union select 1 as e),
    (select 0 union select 1 as f),
    (select 0 union select 1 as g)
    where n between 1 and 100
);

Can it be improved, either by:

  • making it shorter / less redundant, or
  • making it significantly faster, or
  • making it more portable so it also works in another RDBMS (including SQLite 3)?

Note that CTEs are not available until later versions of SQLite. Also note that this won't work as written on sqlfiddle.com; they're using SQLite 3.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Non-standard SQL

It's difficult to write SQL that is completely portable. Nevertheless, you should fix two practices in your query that blatantly disregard SQL standards.

You have no ORDER BY clause on your query, so strictly speaking, the database is free to return the resulting rows in any order. If the results happen to be sequential, consider yourself lucky.

The way you defined n in the second-level SELECT's column list, then referenced n within the its FROM clause, is non-standard SQL. I'm surprised that SQLite lets you do that. Generally, columns need to be introduced within the FROM-clause, and referenced in the SELECT's column list, not the other way around as you have done.

Critique

I like your use of binary counting to generate numbers. I'd tweak it in three ways, though:

  • Rename the columns. The least significant digit should be named a, so that the number generator could be more easily extended to generate larger numbers. Even better, name them n0, n2, n4, n8, etc.
  • Use UNION ALL instead of UNION.
  • Use ternary instead of binary. Since 3 is closer to e, ternary has slightly better radix economy than binary. (My guess is that UNION ALL would be less expensive than a JOIN, so creating 125 rows by joining three tables of five rows each would work well too.)

I like your first implementation better as well — the use of LEFT OUTER JOIN is quite clever. Instead of CASE, I would use the COALESCE() function, eliminating the fb table alias altogether.

The indentation is misleading. It's not obvious that there are multiple levels of nested queries. It's also unclear which clauses go with which SELECT.

Proposed solution for SQLite 2

SELECT COALESCE(fizz || buzz, fizz, buzz, n) AS fizzbuzz
    FROM (
            SELECT n0 + 3 * n3 + 9 * n9 + 27 * n27 + 81 * n81 AS n
                FROM
                    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 AS  n0),
                    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 AS  n3),
                    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 AS  n9),
                    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 AS n27),
                    (SELECT 0 UNION ALL SELECT 1                    AS n81)
        )
        LEFT OUTER JOIN
            (SELECT 3 AS fizzstep, 'Fizz' AS fizz)
                ON n % fizzstep = 0
        LEFT OUTER JOIN
            (SELECT 5 AS buzzstep, 'Buzz' AS buzz)
                ON n % buzzstep = 0
    WHERE n BETWEEN 1 AND 100
    ORDER BY n;

Bonus: An attempt at portability

The following query works on SQLite 3, PostgreSQL, and MySQL. It would work in SQLite 2 too, if not for the casts.

SELECT COALESCE(fizz || buzz, fizz, buzz, CAST(n AS CHAR(8))) AS fizzbuzz
    FROM (
            SELECT n0 + 3 * n3 + 9 * n9 + 27 * n27 + 81 * n81 AS n
                FROM
                    (SELECT 0 AS n0  UNION ALL SELECT 1 UNION ALL SELECT 2 AS n0)  AS N0,
                    (SELECT 0 AS n3  UNION ALL SELECT 1 UNION ALL SELECT 2 AS n3)  AS N3,
                    (SELECT 0 AS n9  UNION ALL SELECT 1 UNION ALL SELECT 2 AS n9)  AS N9,
                    (SELECT 0 AS n27 UNION ALL SELECT 1 UNION ALL SELECT 2 AS n27) AS N27,
                    (SELECT 0 AS n81 UNION ALL SELECT 1                    AS n81) AS N81
        ) AS N
        LEFT OUTER JOIN
            (SELECT 3 AS fizzstep, CAST('Fizz' AS CHAR(4)) AS fizz) AS Fizz
                ON n % fizzstep = 0
        LEFT OUTER JOIN
            (SELECT 5 AS buzzstep, CAST('Buzz' AS CHAR(4)) AS buzz) AS Buzz
                ON n % buzzstep = 0
    WHERE n BETWEEN 1 AND 100
    ORDER BY n;

Portability notes:

  • PostgreSQL will complain that COALESCE() mixes text and integers unless you add explicit casting. MySQL requires the cast to be to a fixed-width CHAR, not VARCHAR. Unfortunately, SQLite only added support for the CAST() operator in version 3.2.3.
    • Although it is possible to rewrite CAST(n AS CHAR(8)) to '' || n, I haven't found any way to get rid of the other two casts in a way that satisfies PostgreSQL.
  • In MySQL, the || ANSI string concatenation operator is disabled by default. You need to SET sql_mode='PIPES_AS_CONCAT' to enable it.
    • SQLite does not support the CONCAT() function.
  • PostgreSQL and MySQL both require all subqueries / derived tables to have aliases.
  • In (select 0 union select 1 as a), you named the column in the last table of the UNION, rather than the first. Apparently, SQLite 2 insists on that. On the other hand, in most other databases, you should specify the column name on the first table of the UNION instead: (SELECT 0 AS a UNION SELECT 1). SQLite 3, MySQL and PostgreSQL, for example, will all insist on having the alias on the first table. The solution: specify the alias on both the first and the last tables of the UNION!
share|improve this answer
1  
Never thought I would learn so much from a FizzBuzz. –  Dagg Jul 16 at 12:32

I will have to admit I personally prefer the First attempt, but both seem like they would run fast and deliver as expected.

The only piece I don't like so much, which is found in both attempts, is this:

    select g + f * 2 + e * 4 + d * 8 
    + c * 16 + b * 32 + a * 64 as n

I think this could really use some parentheses for clarity, if nothing else.

    select (
    g + (f * 2) + (e * 4) + (d * 8) 
    + (c * 16) + (b * 32) + (a * 64) 
    ) as n
share|improve this answer
    
Agreed, that piece is horrible. I just don't see any way around it outside of a single subquery with select 1 union select 2 ... all the way to 100. I feel like parentheses just add noise here; I think it's fair to assume that the reader will understand the order of operations for basic arithmetic. –  Dagg Jul 16 at 4:58
    
What I'm really interested in is getting this to run in MySQL or PostgreSQL (and still run in SQLite 2). Try getting this to run in MySQL if you're up for a challenge... I was completely stumped. –  Dagg Jul 16 at 5:00
    
I'll try it tonight, don't let me forget if I don't get back to you. –  Phrancis Jul 16 at 15:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.