Rails: ActiveRecord, as of 4.2, inconsistently raises RangeError on some read-only queries, but not others

[vandrijevik]

Steps to reproduce

1. Pick any database table that has a 32-bit integer column. Any table with a default id column will do (provided that it is the usual int(11) on MySQL or integer on PostgreSQL). Suppose you pick people, corresponding to the ActiveRecord model person.
2. Execute Person.where(id: 2147483648).exists?
   

   Expected behavior

The query should return false. I base this on the behavior of PostgreSQL and MySQL (in strict mode), which are both happy to perform the SELECT, but refuse the out-of-range inserts:

PostgreSQL

# insert into people (id) values (2147483648);
ERROR:  integer out of range
Time: 0.281 ms

# select * from people where id = 2147483648;
+----+-----+
| id | age |
+----+-----+
+----+-----+
(0 rows)

MySQL

mysql> SET SESSION sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into people (id) values (2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> select * from people where id = 2147483648;
Empty set (0.00 sec)

I think this behavior is reasonable, because given an integer that is too large to fit in the column (2147483648), the answer to the question “find me all rows where this column has a value that cannot possibly fit in the column” is an obvious empty set.

Actual behavior

The query raises a RangeError:

irb(main):001:0> Person.where(id: 2147483648).exists?
RangeError: 2147483648 is out of range for ActiveRecord::Type::Integer with limit 4

System configuration

Rails version:
4.2.5.2
4.2.6

Ruby version:
ruby 2.2.4p230 (2015-12-16 revision 53155) [x86_64-darwin15]
ruby 2.3.0p0 (2015-12-25 revision 53290) [x86_64-darwin15]

Additional Notes

It is perhaps noteworthy that executing Person.find(2147483648).present? instead results in different behavior:

irb(main):004:0> Person.where(id: 2147483648).exists?
RangeError: 2147483648 is out of range for ActiveRecord::Type::Integer with limit 4
irb(main):005:0> Person.find_by(id: 2147483648).present?
=> false

[vandrijevik]

For easier replication, ar42_range_error.rb.txt is a self-contained script which shows the problem – GitHub doesn’t allow .rb as an attachment extension, so just rename the file.

[matthewd]

This is expected behaviour. It's a consequence of using prepared statements: we're not running the query you think we are. We're actually doing:

prepare q as select * from people where id = $1;
execute q(2147483648);

(Similarly, note the different query plans for the "direct" query, comparing the 2147483648 version with one that's in-range.)

We're able to special-case Model.find and Model.find_by, because we know out-of-range means no record. In the general case of working with an arbitrarily-filtered relation, that's not true (the most trivial example being Person.where.not(id: 2147483648).exists?).

[vandrijevik]

Thanks for the reply, @matthewd! I must say, this is very surprising behavior to me, especially since AR now behaves differently in the three cases (where it did not use to), but even more so because a jump from 4.1 to 4.2 results in such a big and breaking API difference.