Implicit casting you don’t want to see around

In Beware of implicit casting, I have outlined the dangers of implicit casting. Here’s a few more real-world examples I have tackled:

Number-String comparisons

Much like in programming languages, implicit casting is made to numbers when at least one of the arguments is a number. Thus:

mysql> SELECT 3 = '3.0';
+-----------+
| 3 = '3.0' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT '3' = '3.0';
+-------------+
| '3' = '3.0' |
+-------------+
|           0 |
+-------------+

The second query consists of pure strings comparison. It has no way to determine that number comparison should be made.

Direct DATE arithmetics

The first query seems to work, but is completely incorrect. The second explains why. The third is a total mess.

mysql> SELECT DATE('2010-01-01')+3;
+----------------------+
| DATE('2010-01-01')+3 |
+----------------------+
|             20100104 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE('2010-01-01')-3;
+----------------------+
| DATE('2010-01-01')-3 |
+----------------------+
|             20100098 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT '2010-01-01' - 3;
+------------------+
| '2010-01-01' - 3 |
+------------------+
|             2007 |
+------------------+
1 row in set, 1 warning (0.00 sec)

Number-String comparisons, big integers

Look at the following crazy comparisons:

mysql> SELECT 1234 = '1234';
+---------------+
| 1234 = '1234' |
+---------------+
|             1 |
+---------------+

mysql> SELECT 123456789012345678 = '123456789012345678';
+-------------------------------------------+
| 123456789012345678 = '123456789012345678' |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+

mysql> SELECT 123456789012345678 = '123456789012345677';
+-------------------------------------------+
| 123456789012345678 = '123456789012345677' |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+

The amazing result of the last two comparisons may strike as odd. Actually, it may strike as a bug, and indeed when a customer approached me with this behavior I was at loss for words. But this is documented. The manual describes the cases for casting, then states: “… In all other cases, the arguments are compared as floating-point (real) numbers. …”

Lessons learned:

  • Be careful when comparing strings with floating point values. Matching depends on how both are represented.
  • Avoid converting temporal types to strings when doing date manipulation.
  • Avoid direct math on temporal types.
  • Avoid casting BIGINTs represented by strings. Casting will turn out to use FLOATs and may be incorrect.

Last but not least:

  • Use the proper data types for your data’s representation. When dealing with numbers, use numbers. When dealing with temporal values, use temporal types.

3 thoughts on “Implicit casting you don’t want to see around

  1. fun if you combine the knowledge from this post with languages that have no strong type (say, php).

  2. Excellent advice, Shlomi.
    For completeness, I should mention that, when using proper date arithmetic syntax, the implicit conversion works as expected.

    mysql> SELECT '2010-01-01' + interval 3 day;
    +-------------------------------+
    | '2010-01-01' + interval 3 day |
    +-------------------------------+
    | 2010-01-04                    |
    +-------------------------------+
    
    mysql> SELECT '2010-01-01' - interval 3 day;
    +-------------------------------+
    | '2010-01-01' - interval 3 day |
    +-------------------------------+
    | 2009-12-29                    |
    +-------------------------------+
    
  3. ++

    I’d also like to point out that passing in your numbers as strings causes the database server to create a Object representing the string value for them and then to convert that into a number which involves a bunch of conversion code. If you just pass it in as a number, then it gets directly shunted into a number in the parser from the protocol.

    NEVER a good idea to use “3” if you want it to be a number.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.