Postgres prepared statements and array queries with certain syntax result in PG::InvalidTextRepresentation

[Earlopain]

Expected behavior

I have the following testcase which passes on Rails 7.1 but errors on main:

# frozen_string_literal: true

require "bundler/inline"

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

  gem "rails", github: "rails/rails"
  gem "pg"
end

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

ActiveRecord::Base.establish_connection(adapter: "postgresql", host: "localhost", database: "rails")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.integer :ratings, array: true
  end
end

class Post < ActiveRecord::Base
end

class BugTest < Minitest::Test
  def test_association_stuff
    Post.create(ratings: [1, 2, 3])
    p = Post.create(ratings: [4])
    Post.create(ratings: [5, 6, 7])

    Post.connection.unprepared_statement do
      # Passes
      assert_equal([p], Post.where("ratings @> '{?}'", 4))
    end
    # Also passes
    assert_equal([p], Post.where("ratings @> ARRAY[?]::integer[]", 4))

    # PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type integer: "$1"
    # LINE 1: SELECT "posts".* FROM "posts" WHERE (ratings @> '{$1}')
    assert_equal([p], Post.where("ratings @> '{?}'", 4))
  end
end

System configuration

Rails version: master

Ruby version: 3.2.2

[fatkodima]

This was changed in #51139 (cc @byroot).

In the meantime, this works:

    assert_equal([p], Post.where("ratings @> ?", '{4}'))

[matthewd]

Frankly, I feel like this is a [largely accidental] feature -- interpolating ? values into a string literal seems scary and dangerous and deeply inadvisable.

I also don't think there's much we can do about it: it's not reasonable for us to guarantee the exact characters that will be interpolated... at that point you might as well use #{}.

[Earlopain]

I won't be heartbroken if this doesn't work anymore. Rails only documents the ARRAY[?]::type[] notation at https://guides.rubyonrails.org/active_record_postgresql.html#array and its trivial to change the few occurrences to that.

A basic GitHub code search yields some results though, see here, so deprecation/better error message? I don't know if this warants that.

[byroot]

Yeah, I think we can close this. I don't think a specific deprecation would be helpful or needed, this seems extremely niche.

[Earlopain]

Just for posterity, a better replacement for my case above would be where("? = ANY(ratings)", 4) since only one element is being checked for. The array notation makes sense when checking again multiple elements.