2007-12-19

MySQL T-SQL CASE Statement: Bad Language Design

Imagine you want to do something like this in a stored procedure:

SET @var = SELECT var FROM t; --some generic value
CASE
WHEN @var = 1 THEN CALL do_something();
WHEN @var = 2 THEN CALL do_something_else();
END CASE;

And it works fine for a while until you get and error:

ERROR 1339 (20000): Case not found for CASE statement

This happens due to the fact there is no WHEN clause to handle this specific value. It's unlike the regular switch clause logic in C, but it seems reasonable. So I've tried adding an empty ELSE clause that would do nothing:

SET @var = SELECT var FROM t; --some generic value
CASE
WHEN @var = 1 THEN CALL do_something();
WHEN @var = 2 THEN CALL do_something_else();
ELSE --or ELSE; or ELSE NULL; or any combination you might think of
END CASE;

Nope, we get the usual "You have an error in your SQL syntax; check the manual....". Naturally, the manual didn't say anything about it.
First I did some dummy SET @tmp = NULL; assignment to fill the ELSE clause, but then I did some research, and got to Bug #14042.

Short version:

The "correct" way to do this, is like so:

SET @var = SELECT var FROM t; --some generic value
CASE
WHEN @var = 1 THEN CALL do_something();
WHEN @var = 2 THEN CALL do_something_else();
ELSE BEGIN END;
END CASE;

Longer version:

When found this bug, it was a low severity feature request that asked for the reasonable thing - just remove this error check and exit the CASE. Unfortunately, the MySQL decision was to mark it as "Won't fix" and close the bug.

It seemed to me a really twisted logic, but I wanted others to know about it, so I've added a comment on the MySQL Manual page with a short explanation and example.
After about 30 minutes (which is a really fast response time from the moderator) I got an email stating that my comment was removed, and that now the bug is reopened as a "Documentation Bug". The manual is still not updated with this issue, but I guess it will be added sometime soon.

Apparently if something is a really well documented bug with a well known workaround, it becomes a feature.

Update: The bug was closed and the manual page now includes an explanation. Also, it seems that this is not the fault of MySQL at all- this is actually a standards-compliant feature (take a look at the comments).

5 comments:

  1. Can't it swallow

    SET @var = SELECT var FROM t;
    CASE
    WHEN @var=1 THEN CALL do_something();
    WHEN @var=2 THEN CALL do_something_else();
    WHEN 1=1 THEN CALL default();
    END CASE;

    or maybe just do ELSE 1=1 or some kind of another empty statement
    ?

    ReplyDelete
  2. WHEN 1=1 THEN CALL default();

    Will catch all the queries that don't fit any conditions, just what ELSE should do - so it is not the main point here.

    The empty statement you are looking for is this "BEGIN END;" that I mentioned. 1=1 is not a statement by itself.

    ReplyDelete
  3. Hi!

    welcome to the planet!!!

    I filed the bug you are referring to.

    Personally, I don't find it "bad" language design - it is actually pretty good to be informed that a condition was not anticipated (another thing I like about CASE is that it does'nt "fall through like the switch statement in C, java, PHP etc).

    On another level, MySQL's behavior here is exactly as defined in the standard (ISO/IEC 9075-4:2003 (E), 13.6 case statement, page 103):

    "c) Otherwise, an exception condition is raised: case not found for case statement, and the execution of
    the <case statement> is terminated immediately."

    So IMO, it can't do better here. What remains is the clunky syntax to suppress the error. It would probably be ugly no matter what noop construct would be available.

    ;)

    Roland Bouman

    ReplyDelete
  4. Roland:
    Thanks :)

    Very interesting! I didn't know it was defined in this way in the SQL Standard... Good to know.

    Probably the standard designers believe you shouldn't be able to "fall through" the case statement, since T-SQL is more sensitive than C when it comes to small programming mistakes - and things should be stricter.

    ReplyDelete