Expecting integers, receiving floats in Rails

18 May, 2023

I ran into one of those times when you need to write some raw SQL for a query in Rails (using ActiveRecord) and got an strange or unexpected behavior when seeing that the code I was testing in psql wasn't the same I was ending up receiving in Rails.

To illustrate (and make it short) the case was just a simple floating division rounded up which I thought it'd return an integer in Rails, but ended up being a float;

ActiveRecord::Base.connection.execute('select ceil(3 / 2.0)').to_a
#   (0.2ms)  select ceil(3 / 2.0)
# => [{"ceil"=>0.2e1}]

(I really did instead Model.select('ceil(integer_value / float_value)') but it translated into the same.)

So, I couldn't get my head around why the result is 0.2e1 while the Postgres documentation clearly states;

ceil ( numeric ) → numeric

ceil ( double precision ) → double precision

Nearest integer greater than or equal to argument

ceil(42.2) → 43

ceil(-42.8) → -42

If I enter that example into psql I clearly receive -42. The same if I do ceil(3 / 2.0) or ceil(1.5) there's no difference and I get 2. So clearly the reason might not be Postgres but something between what executes the query and what sends the result back and that leads me to check how Rails handles this.

Knowing that at the end any query given to execute ends up being executed in DatabaseStatements#raw_execute through async_exec and that that method is an alias for exec we could try that flow by ourselves;

PG::Connection.open(dbname: 'template1').exec('select ceil(3 / 2.0)').to_a
# => [{"ceil"=>"2"}]

(template1 is just a Postgres default template database, should always be available for you to try things out)

That code seems to be working as expected, the result value for the ceil function is an integer which is wrapped into a string. No floats. We'll need to dig into ActiveRecord then.

At some point in DatabaseStatements the method query makes a conn.async_exec(sql).map_types!. If I know everything is okay until the async_exec perhaps the map_types! is changing the value I'm receiving.

Debugging the value of the argument for it being used (@type_map_for_results) I see it's a PG::TypeMapByOid object which implements the coders for mapping the result/output of the query. If I check its coders then I see only two;

[
  <PG::TextDecoder::Bytea:0x00007ffa6cf3c078 "bytea"  oid=17>,
  <ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::MoneyDecoder:0x00007ffa6cf43eb8 "money"  oid=790>
]

and trying the whole procedure with those decoders sadly doesn't yield the same result;

type_map_by_oid = PG::TypeMapByOid.new
type_map_by_oid.add_coder(PG::TextDecoder::Bytea.new(name: 'bytea', oid: 17))
type_map_by_oid.add_coder(ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::MoneyDecoder.new(name: 'money', oid: 790))
PG::Connection
  .open(dbname: 'template1')
  .exec('select ceil(3 / 2.0)')
  .map_types!(type_map_by_oid)
  .to_a
# => [{"ceil"=>"2"}]

so that's not where the problem is, or not the decoders that are changing "2" to 0.2e1 but because query is not the metho we're using, but execute.

The coders aren't being set anywhere nor map_types! is called anywhere there, but printing the coders at that point shows something insightful;

def execute(sql, name = nil)
  ...

  log(sql, name) do
    ActiveSupport::Dependencies.interlock.permit_concurrent_loads do
      pp [sql, @connection.type_map_for_results.try(:coders)]
      @connection.async_exec(sql)
    end
  end
end

prints the following;

[
  "select ceil(3 / 2.0)",
  [
    <PG::TextDecoder::Boolean:0x00007ffa6cf3d568 "bool"  oid=16>,
    <PG::TextDecoder::Integer:0x00007ffa6cf3d360 "int8"  oid=20>,
    <PG::TextDecoder::Integer:0x00007ffa6cf3d1f8 "int2"  oid=21>,
    <PG::TextDecoder::Integer:0x00007ffa6cf3d0b8 "int4"  oid=23>,
    <PG::TextDecoder::Integer:0x00007ffa6cf3cf28 "oid"  oid=26>,
    <PG::TextDecoder::Float:0x00007ffa6cf3cd70 "float4"  oid=700>,
    <PG::TextDecoder::Float:0x00007ffa6cf3cb90 "float8"  oid=701>,
    <PG::TextDecoder::TimestampUtc:0x00007ffa6cf43a30 "timestamp"  oid=1114>,
    <PG::TextDecoder::Timestamp:0x00007ffa6cf3c6b8 "timestamptz"  oid=1184>,
    <PG::TextDecoder::Numeric:0x00007ffa6cf3c438 "numeric"  oid=1700>
  ]
]

so that's a lot of examples to test, but tl;dr; PG::TextDecoder::Numeric is the one that's transforming the result to a float. Testing it alone returns the previous 0.2e1;

type_map_by_oid = PG::TypeMapByOid.new
type_map_by_oid.add_coder(PG::TextDecoder::Numeric.new(name: 'numeric', oid: 1700))
PG::Connection
  .open(dbname: 'template1')
  .exec('select ceil(3 / 2.0)')
  .map_types!(type_map_by_oid)
  .to_a
# => [{"ceil"=>0.2e1}]

while in absence it returns just the integer (as a string);

type_map_by_oid = PG::TypeMapByOid.new
PG::Connection
  .open(dbname: 'template1')
  .exec('select ceil(3 / 2.0)')
  .map_types!(type_map_by_oid)
  .to_a
# => [{"ceil"=>"2"}]

So, there's the why and how a raw SQL statement which should return an integer ends up transformed into a float. Maybe I am to blame for using ActiveRecord this way, although looking through the docs couldn't find anything related to this behavior. The PostgreSQL::DatabaseStatements#execute says;

Executes an SQL statement, returning a PG::Result object on success or raising a PG::Error exception otherwise.

Note: the PG::Result object is manually memory managed; if you don't need it specifically, you may want consider the exec_query wrapper.

No references about the type mapping (and select neither does).

If I needed to overcome this a simply ::int (or similar truncation for other RDBMS) might suffice (which is what I ended up doing :D) but I don't know if this result is something made on purpose or not.