Postgresql date type is not properly typecasted when using direct SQL queries like select_all

[bogdan]

When using direct SQL query, the date is not properly typecasted on Postgresql:

> ActiveRecord::Base.connection.select_all("  SELECT '2024-01-01'::date ")
   (0.5ms)    SELECT '2024-01-01'::date
  ↳ config/initializers/local.rb:52:in `sa'
=> #<ActiveRecord::Result:0x0000000115516010
 @column_types=
  {0=>#<ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Date:0x0000000116a5acf0 @limit=nil, @precision=nil, @scale=nil, @timezone=nil>,
   "date"=>#<ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Date:0x0000000116a5acf0 @limit=nil, @precision=nil, @scale=nil, @timezone=nil>},
 @columns=["date"],
 @hash_rows=nil,
 @rows=[["2024-01-01"]]>

Notes

• The column type seems to be recognized correctly, but typecast is not applied
• SQLite seems to generate even worth result like 2024 as integer.
• Timestamp doesn't have this problem

Reproduce

# frozen_string_literal: true

require 'bundler/inline'

gemfile(true) do
  source 'https://rubygems.org'

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem 'rails'

  gem 'pg'
  # gem 'sqlite3'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

# `CREATE DATABASE rails_51428_test;`
ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_test')
# ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')
ActiveRecord::Base.logger = Logger.new($stdout)


class DateTest < Minitest::Test
  def test_stuff
    value = ActiveRecord::Base.connection.select_value(
      "select cast('2024-01-01' as date)"
    )
    assert_equal Date.new(2024, 01, 01), value
  end
end

Expected behavior

Expected: Mon, 01 Jan 2024
  Actual: "2024-01-01"

System configuration

Rails version: 7.1.3

Ruby version: 3.2.2

[rebase-master]

@bogdan Correct me if you think I am wrong but I don't think this is an issue with Rails/Activerecord.
If I understood you correctly, what you are trying to do from this statement:

value = ActiveRecord::Base.connection.select_value(
      "select cast('2024-01-01' as date)"
    )
assert_equal Date.new(2024, 01, 01), value

is to extract the date from the database by casting it as date and comparing it with a Date object. I don't think that's the right way to go about it.

Date.new(2024, 01, 01) gives you a date object and your ActiveRecord query is giving you a string. So the comparison will fail. What you can do is to parse the date result from ActiveRecord and then compare it against the date object:

assert_equal Date.new(2024, 01, 01), Date.parse(value).

Additionally, since Date.new(2024, 01, 01) object will give you Mon, 01 Jan 2024 so you need to have the same format of date returned from ActiveRecord. You can do this in the following way:

value = ActiveRecord::Base.connection.select_value(
   "SELECT TO_CHAR(DATE '2024-01-01', 'Dy, DD Mon YYYY');"
  )

Now if you compare the two dates:

assert_equal Date.new(2024, 01, 01), Date.parse(value)

, it should be equal.

Let me know if that works for you.

[JoeDupuis]

@bogdan This is not a bug in Rails. select_value is a pretty low level API.

It unpacks the result object from the raw underlying connection adapter, but it doesn't convert or interpret types of the results (It does type casting for params/bindings).

When trying with the Mysql2 adapter, dates are parsed to a Ruby Date object, because the Mysql2 gem itself is doing it inside the C extension.

The PG gems does support mechanism for type casting, but those are never called by select_value.

You can cast the result by passing it to: ActiveModel::Type::Date.new.cast.

[bogdan]

Ok, so I tried a few tests using pg directly:

require 'pg'

# Replace these with your actual database credentials
dbname = 'rails_test'
user = 'bogdan'
password = nil
host = 'localhost'  # or the appropriate host

# Establish a connection to the database
conn = PG.connect(dbname: dbname, user: user, password: password, host: host)

# Execute a query
query = "select cast('2024-01-01' as date)"
puts conn.exec(query).to_a.inspect
query = "select cast('2024-01-01' as timestamp)"
puts conn.exec(query).to_a.inspect

# Close the database connection
conn.close

Outputs:

[{"date"=>"2024-01-01"}]
[{"timestamp"=>"2024-01-01 00:00:00"}]

Which means that it doens't perform typecast neither for timestamp nor for date.
But select_value does perform typecast only for timestamp:

ActiveRecord::Base.connection.select_value("  SELECT '2024-01-01'::timestamp ")
# => 2024-01-01 00:00:00 UTC
 ActiveRecord::Base.connection.select_value("  SELECT '2024-01-01'::date ")
# => "2024-01-01"

If so, who is performing typecast for timestamp within ActiveRecord and why it should do it only for timestamp but not for date?

[JoeDupuis]

Interesting. I missed this because the typecasting doesn't happen inside of Rails, but Rails does add some decoders to the connection when initializing it.

The impact radius is pretty big, just adding date here works, but could break a bunch of stuff downstream.
Perhaps it can be added, we already have timestamp after all, but we wouldn't want an expectation of type casting at this point since all adapters would have to support it.

I'll check if it blows up the test suite later.

[rebase-master]

@bogdan This is what chatGPT is telling me:

The difference in return types from these ActiveRecord queries when casting to Date and Timestamp in PostgreSQL comes down to how ActiveRecord interprets and converts the SQL result sets into Ruby objects, based on the data types returned by PostgreSQL.

1. Casting to Date:

   • When you cast a value to Date in PostgreSQL and fetch it through ActiveRecord with select_value, PostgreSQL returns just a date without time information (YYYY-MM-DD).
   • ActiveRecord receives this value and, recognizing it as a simple date string without time information, returns it directly as a string in Ruby. There's no time zone information or time component to consider, which makes it straightforward for ActiveRecord to handle it as a string.

2. Casting to Timestamp:

   • Casting to Timestamp in PostgreSQL includes both date and time information (YYYY-MM-DD HH:MM:SS). When time information is present, PostgreSQL also associates time zone information with the timestamp (even if it's not explicitly set, it uses the default time zone of the server or the session).
   • ActiveRecord sees this as a more complex data type that includes both date and time components. It uses Ruby's Time class to represent this type of data because Time can accurately represent both date and time, including time zone information.
   • Thus, when you fetch a Timestamp value from PostgreSQL through ActiveRecord, ActiveRecord converts it into a Ruby Time object, which represents the date, time, and time zone (UTC in this case, if not specified otherwise).

This behavior underscores ActiveRecord's design to map SQL data types to Ruby's data types intuitively. For simple dates, a string is often sufficient and follows Ruby's principle of least surprise. For timestamps, which inherently carry more complexity due to the inclusion of time (and potentially time zone), a more capable object like Ruby's Time is returned.

This automatic conversion facilitates easier manipulation and comparison of date and time values within Ruby, adhering to the conventions of the Ruby language and the expectations of developers working within its ecosystem.

[bogdan]

@rebase-master well, @JoeDupuis's answer looks better to me, really happy that human defeated the machine on this tiny case.

[JoeDupuis]

really happy that human defeated the machine

Take that robot!

You probably saw that I submitted a PR #51483 for it already, but it could be rejected.
Casting with ActiveModel::Type::Date.new.cast would futureproof your code as it doesn't choke on a date input.

Unrelated, but I am a big fan of your datagrid gem. Thank you for writing it 😁