Incorrect conversion of date type in Postgres adapter with ActiveRecord

[leboshi]

Date values passed from ActiveRecord to Postgres are losing a day when passed to the JDBC layer using prepared statements. This is seen most succinctly in the dates in this EXPLAIN:

Report Load (11.7ms)  SELECT "reports".* FROM "reports" WHERE "reports"."report_date" = ?  [["report_date", Tue, 19 Nov 2019]]
=> EXPLAIN for: SELECT "reports".* FROM "reports" WHERE "reports"."report_date" = ? [["report_date", "2019-11-19"]]
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on reports  (cost=0.00..4.54 rows=1 width=41)
   Filter: (report_date = '2019-11-18'::date)
(2 rows)

A few observations:

1. This only happens when using prepared statements, as the following standalone test suite demonstrates.
2. The driver and AR connection are correctly setting Postgres's timezone to UTC, so it doesn't seem to be a midnight crossing issue. In any event, I'm locally in -0500, and that generally doesn't throw things an entire day backward when a midnight crossing is the issue.
3. Basic ActiveRecord intelligence around type detection/casting doesn't affect the issue. You can replace any of the Date objects in the test suite with ISO strings that would normally be valid for date fields in ActiveRecord (e.g., '2020-02-12'), and the bug still occurs.
4. Dates fetched from Postgres are correctly deserialized as Date objects; this only affects dates going from ActiveRecord to Postgres.
5. The included standalone test suite only exercises UPDATE statements, but this also affects INSERTs and WHERE clauses (and likely others) in SELECTs.
6. It seems to happen at the point where data is passed down to Java, as you can see in the EXPLAIN above. I've debugged down the stack to where @connection.execute_prepared_query is called on line 47 of lib/arjdbc/abstract/database_statements.rb, and the date is still correct until it's passed to execute_prepared_query. Not sure if this has anything to do with the timezone casting insanity in setDate in PgPreparedStatement. (I've dealt with Postgres's timezone madness before... Not diving into that at 3 a.m.)

EXPECTED OUTCOME:
All three tests below should pass.

ACTUAL OUTCOME:
test_prepared_statement_for_date_column_sends_correct_date_to_postgres fails.

TEST NOTE:
The following is entirely standalone except that it calls out to createdb. Postgres should be installed, running, and accessible by JRuby.

ENVIRONMENT:

• Using latest JRuby on Mac OSX: jruby 9.2.9.0 (2.5.7) 2019-10-30 458ad3e Java HotSpot(TM) 64-Bit Server VM 25.211-b12 on 1.8.0_211-b12 +jit [darwin-x86_64]
• Tested against both release 60.1 and master (test suite below uses master)
• Tested against Rails 6.0.0 in test suite below, but bug was originally discovered in Rails 6.0.2.1
• Using Postgres 11.5

# 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", "6.0.0"
  gem 'activerecord-jdbcpostgresql-adapter', github: 'jruby/activerecord-jdbc-adapter'
end

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

`createdb -E unicode -T template0 activerecord_unittest`

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "activerecord_unittest", encoding: "unicode")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.date :published_on
  end
end

class Post < ActiveRecord::Base; end

class BugTest < Minitest::Test
  def test_prepared_statement_for_date_column_sends_correct_date_to_postgres
    Post.create!
    date = Date.new(2020, 2, 12)
    Post.first.update_columns published_on: date

    assert_equal date, Post.first.published_on
  ensure
    Post.connection.execute 'TRUNCATE posts;'
  end

  def test_unprepared_statement_for_date_column_sends_correct_date_to_postgres
    Post.create!
    date = Date.new(2020, 2, 12)

    ActiveRecord::Base.connection.unprepared_statement do
      Post.first.update_columns published_on: date
    end

    assert_equal date, Post.first.published_on
  ensure
    Post.connection.execute 'TRUNCATE posts;'
  end

  def test_raw_arel_for_date_column_sends_correct_date_to_postgres
    Post.create!
    date = Date.new(2020, 2, 12)

    update_arel = Arel::UpdateManager.new
    update_arel.table Post.arel_table
    update_arel.set [
                        [Post.arel_table[:published_on], date]
                    ]
    Post.connection.update update_arel

    assert_equal date, Post.first.published_on
  ensure
    Post.connection.execute 'TRUNCATE posts;'
  end
end

[dr-itz]

Thanks for the excellent report!

First I couldn't reproduce, but since I'm in +0100, I tried running with JRUBY_OPTS=-J-Duser.timezone=America/Detroit and sure enough it breaks.

I'll look into it over the next few days.

[dr-itz]

See #1058

[headius]

@dr-itz So this should be fixed now with that PR merged, yes?

[dr-itz]

@dr-itz So this should be fixed now with that PR merged, yes?

It does for me... maybe @leboshi could give this a try?

[enebo]

@leboshi when you do try this and if it is still broken then re-open this issue. Optimistically resolving this.