Implicit casting you don't want to see around

July 7, 2010

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.

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

3 Comments to "Implicit casting you don't want to see around"

  1. Mrten wrote:

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

  2. Giuseppe Maxia wrote:

    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. Monty Taylor wrote:

    ++

    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 Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org