Skip to content

# Implicit casting you don’t want to see around

MySQL Data Types SQL July 7, 2010July 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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
3
Leave a Reply

3 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
Mrten

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

Guest
Giuseppe Maxia

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                    |
    +-------------------------------+

Guest
Monty Taylor

++

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.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.