Wednesday 12 June 2013

MySQL: "Truncated incorrect DOUBLE value" issue

Man oh man this had me going for quite a while.
Guess I must be getting old.
Anyway, I have a rails site that uses ActiveRecord to a MySQL db.
One table has two columns that are integers, but have to be unsigned as they hold values > 2147483647.
(See http://dev.mysql.com/doc/refman/5.5/en/integer-types.html for more detail)
Anyway, the insertions went just fine.
An example output of the table structure and last few rows is as follows:

mysql> show columns from ranges;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| starts_at | int(10) unsigned | NO   | PRI | NULL    |                |
| ends_at   | int(10) unsigned | NO   | PRI | NULL    |                |
| iso3_code | varchar(50)      | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from ranges order by id desc limit 5;
+--------+------------+------------+-----------+
| id     | starts_at  | ends_at    | iso3_code |
+--------+------------+------------+-----------+
| 126602 | 4278190080 | 4294967295 | ZZZ       |
| 126601 | 4261412864 | 4278190079 | ZZZ       |
| 126600 | 4244635648 | 4261412863 | ZZZ       |
| 126599 | 4227858432 | 4244635647 | ZZZ       |
| 126598 | 4211081216 | 4227858431 | ZZZ       |
+--------+------------+------------+-----------+
5 rows in set (0.00 sec)

My code gets a value and does a search for the row that includes that number.
So if for instance I want the iso3_code for value 4261412864:

mysql> select * from ranges where starts_at >= 4261412864 
order by starts_at limit 1;
+--------+------------+------------+-----------+
| id     | starts_at  | ends_at    | iso3_code |
+--------+------------+------------+-----------+
| 126601 | 4261412864 | 4278190079 | ZZZ       |
+--------+------------+------------+-----------+
1 row in set (0.00 sec)

The problem was the generated SQL from ActiveRecord shown below:

SELECT `ranges`.* FROM `ranges` WHERE ("starts_at" >= 4261412864) 
ORDER BY `id` LIMIT 1

In the code I got a nil row set.
My first thought was: WTF?
So I cut and pasted it into the mysql command line and saw this:

mysql> SELECT `ranges`.* FROM `ranges` WHERE ("starts_at" >= 4261412864) 
ORDER BY `id` LIMIT 1;
Empty set, 1 warning (0.00 sec)

Wait. Warnings?

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'starts_at' |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

What? DOUBLE? But the column starts_at is an integer...
So I started changing column types, and generally wasting my time for a couple of hours.

To cut a long story short it's programmer blindness.
It's obvious in retrospect.
The select statement is incorrect and I was interpreting the warning incorrectly.
I assumed the message referred to the column starts_at.
Nope.
What the select where clause is actually saying:
"Oh. You want rows from ranges where the string 'starts_at' has a value that is greater or equal to 4261412864. Er. Ok. I'll try to cast the STRING 'starts_at' to a DOUBLE and then do a comparison. Er... Ok. That didn't produce any rows and by the way I tried to cast that STRING to a double and failed."
Dammit.
So I looked at my code (simplified of course):

range = Range.where('"starts_at" >= ?', val).order(:starts_at).first

Oh how could I have been so dumb?
Quick change to test:

range = Range.where('`starts_at` >= ?', val).order(:starts_at).first

And all is fine.

1 comment:

  1. Oracle will implicitly convert 4261412864 to a string, and you will get always zero rows or all of them.

    ReplyDelete