Range of dates with infinite end are stored incorrectly in Postgres database

[pean]

In Rails 7.1.3 date ranges with unbounded end is stored incorrectly when the end is included and it is i stored with excluded end.

Steps to reproduce

1. Create a table with a Postgres date range column
2. Create and entry in the table with a range with included unbounded end (Date.today..) and save it
3. Reload that entry and the range comes back with excluded end (Date.today...)

require "bundler/inline"

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

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

  gem "rails", '~> 7.1.3'
  gem "pg"
end

require "active_record"
require "minitest/autorun"
require "logger"

# You may need to change your pg settings accordingly
db_config = {
  adapter: 'postgresql',
  database: 'infinityrange_test',
  host: 'localhost', # Change this to your PostgreSQL host
  port: 5432,
  username: 'postgres', # Change this to your PostgreSQL username
  password: 'postgres'  # Change this to your PostgreSQL password
}

begin
  ActiveRecord::Base.establish_connection(db_config.except(:database))
  ActiveRecord::Base.connection.drop_database(db_config[:database]) rescue nil
  ActiveRecord::Base.connection.create_database(db_config[:database])
end
ActiveRecord::Base.establish_connection(db_config)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :infinity_ranges do |t|
    t.tsrange :date_range
  end
end

class InfinityRange < ActiveRecord::Base; end

class InfinityRangeTest < Minitest::Test
  def setup
    @time = Time.utc(2024)
  end

  def test_date_range_infinity
    # Create a range with infinity end including end 2024-01-01 00:00:00 UTC..
    # There are multiple ways to create a range with infinity end but all of 
    # these give the same result
    # range = (@time..Float::INFINITY)
    # range = (@time..DateTime::Infinity.new)
    # range = (@time..Date::Infinity.new)
    range = (@time..)

    record = InfinityRange.new(date_range: range)

    # Range is including end
    refute_predicate record.date_range, :exclude_end?

    # Save an reload the record to read back the value from db
    record.save!
    record.reload

    # Range is no longer including range and returns 2024-01-01 00:00:00 UTC...
    # but it should be 2024-01-01 00:00:00 UTC..
    assert_equal (@time..), record.date_range
    refute_predicate record.date_range, :exclude_end?

    # To read back excluding end it needs to be stored like this
    assert_equal(
      "[\"#{@time.to_fs(:db)}\",infinity]",
      record.read_attribute_before_type_cast(:date_range),
    )
  end

  # This test is to shows that the range can be inserted directly into the
  # database and then read back as expected
  def test_direct_insert
    # Insert the equivalent of (@time..) directly into the database
    sql = "INSERT INTO infinity_ranges (date_range) VALUES ('[#{@time},infinity]')"
    ActiveRecord::Base.connection.execute(sql)

    record = InfinityRange.last

    assert_equal (@time..), record.date_range
    refute_predicate record.date_range, :exclude_end?

    assert_equal(
      "[\"#{@time.to_fs(:db)}\",infinity]",
      record.read_attribute_before_type_cast(:date_range),
    )
  end

  def test_direct_insert_nil
    # Insert the equivalent of (@time..) directly into the database
    sql = "INSERT INTO infinity_ranges (date_range) VALUES ('[#{@time},]')"
    ActiveRecord::Base.connection.execute(sql)

    record = InfinityRange.last

    assert_equal (@time..), record.date_range
    refute_predicate record.date_range, :exclude_end?

    assert_equal(
      "[\"#{@time.to_fs(:db)}\",infinity]",
      record.read_attribute_before_type_cast(:date_range),
    )
  end
end

☝️ These assertions fails btw, except intest_direct_insert that all pass.

Expected behavior

To save the range including unbounded end as it was specified.

Actual behavior

Range is saved in database excluding end and when it's read back into rails it is not the same value.

System configuration

Rails version: 7.1.3

Ruby version: ruby 3.2.3 (2024-01-18 revision 52bb2ac0a6) [arm64-darwin23]

[Earlopain]

Duplicate/Related to #51111?

[pean]

Duplicate/Related to #51111?

Not really, that is about how data is read back from the database and the change that unbounded values now comes back as nil where the before Rails 7.1 came back as Float::INFINITY, which I think is a change to the better.

This is about what how the data is going into the database. When a boundless end is sent to PostgreSQL it is sent as nil which in PostgreSQL will be stored as an excluded end, but if it is sent as infinity it will be stored as inclusive, and be returned as such.

It is possible that this could be considered a PostgreSQL problem too, because range = ["2024-01-01 00:00",infinity] could be the same as range = ["2024-01-01 00:00",], but the latter is saved as ["2024-01-01 00:00",).

From what I can tell, the value that goes into the database has to be modified from nil, to string infinity. But I could be wrong.