ActiveRecord normalization interferes with Wild-Card matching

[matthee]

Steps to reproduce

When doing wild-card searches with ActiveRecord, the normalization gets applied to underlying Arel matchers. While this behavior is expected in some cases, it might interfere when searching for records using LIKE and ILIKE queries.

# frozen_string_literal: true

require "bundler/inline"

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

  gem "rails"
  # If you want to test against edge Rails replace the previous line with this:
  # gem "rails", github: "rails/rails", branch: "main"

  gem "sqlite3"
end

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

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.string :title
  end
end

class Post < ActiveRecord::Base
  normalizes :title, with: -> { _1.gsub(/[^a-zA-Z\s]/, "")}
end

class PostWithoutNormalization < ActiveRecord::Base
  self.table_name = :posts
end

class BugTest < ActiveSupport::TestCase
  # Works
  def test_querying_without_normalization
    PostWithoutNormalization.transaction do
      post = PostWithoutNormalization.create!(title: "Hello world")
      assert_equal post, PostWithoutNormalization.where(PostWithoutNormalization.arel_table[:title].matches("Hello%")).first
    end
  end

  # Fails, as the % gets stripped from the matcher by Rails' normalization
  def test_querying_with_normalization
    Post.transaction do
      post = Post.create!(title: "Hello world")
      assert_equal post, Post.where(Post.arel_table[:title].matches("Hello%")).first
    end
  end
end

Expected behavior

I expect the Post to be found in both test cases.

Actual behavior

In the test_with_normalization test, the normalization kicks in and ActiveRecord performs a query, which basically boils down to ... title LIKE 'Hello'... (note the missing %). Therefore, the post titled "Hello world" cannot be found.

System configuration

Rails version: 7.2.2 (also reproduced on 8.0.0.rc2)

Ruby version: 3.2.5

[dari-us]

Similar behavior could happen before with serialize. To mitigate this, I used a hackish approach like the following:

class Stuff < ApplicationRecord
  serialize :stuff_array, type: Array, coder: JSON
end

Stuff.where(Arel::Table.new(:stuffs)[:stuff_array].matches("%something%"))
# produces desired
# SELECT * FROM `stuffs` WHERE `stuffs`.stuff_array` LIKE '%something%'

The normalization that ActiveRecord applies is super useful if dealing with MAC Adresses for example to allow searching with different formats from user input. It's a double-edged blade.

[dmitry]

Found that it's already working as expected with Arel::Nodes::Quoted. Here's a test case that demonstrates this:

assert_equal post, Post.where(Post.arel_table[:title].matches(Arel::Nodes::Quoted.new("Hello%"))).first

The normalization is already skipped for Arel::Nodes::Quoted values, so SQL wildcards are preserved. Just make sure to use Arel::Nodes::Quoted.new when working with SQL patterns in matches.

It would be good if https://github.com/rails/rails/pull/53545 is merged so it will keep working.

[rafaelfranca]

Public API for matching works fine Post.where("title LIKE ?", "Hello%"). Use it instead of private API

[matthee]

Thanks for all your feedback!

@rafaelfranca this is actually a very reduced example of what we are using in our application, so sadly this is not possible with our current setup.

@dmitry & @dari-us I'll try to work out a solution based on your suggestions, thank you both!