Crappy Code Hopscotch

I'm officially coining the term "crappy code hopscotch" to refer to the stupid games you have to play to workaround crappy code. I guess it could equally well refer to that feeling of being surprised by the effects of crappy code in any otherwise simple task, which might not be altogether unlike the feeling of unpleasant surprise you would get if someone were to throw a pile of dog poo in your hopscotch square.

The term popped into my head today while doing some MySQL wrangling; I was testing a stored function that called LOWER() on the results of CONCAT_WS(). Sounds simple enough: lower-case the result of concatenating strings with a separator. Check this output from MySQL 5.1:

mysql> SELECT LOWER(CONCAT_WS(' ', 'MySQL', 'scores', 'a', 0));
+--------------------------------------------------+
| LOWER(CONCAT_WS(' ', 'MySQL', 'scores', 'a', 0)) |
+--------------------------------------------------+
| MySQL scores a 0                                 |
+--------------------------------------------------+

Silent lower-case fail.

The problem, it seems, is that CONCAT_WS() doesn't convert the numeric argument to a string, but rather decides to convert *all* of the parameters to BINARY types and, as a result, returns a BINARY value. To it's credit, at least LOWER() is documented as being a no-op on BINARY values, hence the useless output shown above. What amazed me is that not just the undocumented, unintuitive behavior of CONCAT_WS() but that MySQL did not emit a single warning when LOWER() returned a value without, you know, lower-casing it.

So out of nowhere, I find myself playing crappy code hopscotch. I can explicitly either cast the numeric argument to CONCAT_WS() to a string or else let CONCAT_WS() return a BINARY value and explicitly convert that back to a string before passing it to LOWER().

Two crappy boxes to pick from and I got to put my foot in one of them.