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.
Oracle will implicitly convert 4261412864 to a string, and you will get always zero rows or all of them.
ReplyDelete