MySQL does not support ILIKE queries (have_a_version_with_changes, where_object_changes, other areas)

[CloCkWeRX]

Thank you for your contribution!

Due to limited volunteers, issues that do not follow these instructions will be
closed without comment.

Check the following boxes:

• This is not a usage question, this is a bug report
• This bug can be reproduced with the script I provide below
• This bug can be reproduced in the latest release of the paper_trail gem

Due to limited volunteers, we cannot answer usage questions. Please ask such
questions on StackOverflow.

Bug reports must use the following template:

# STEP ONE: What versions are you using?

# NOTE: I had to stick this in a Gemfile
gemfile(true) do
   ruby "3.4.4"
   source "https://rubygems.org"
   gem "activerecord", "~> 8"
   gem "rspec" # Deliberately not minitest as the rspec test helper is how I found it.
   gem "paper_trail", "17.0.0", require: false
   gem "trilogy"
end

require "active_record"
# require "minitest/autorun"
require "rspec"
require "rspec/autorun"
require 'paper_trail/frameworks/rspec'
require "logger"

# Please use sqlite for your bug reports, if possible.
# ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")

# MySQL 8
# Use via DATABASE_URL=trilogy://user@127.0.0.1/example bundle exec ruby script.rb
ActiveRecord::Base.establish_connection(adapter: "trilogy", url: ENV['DATABASE_URL'])

ActiveRecord::Base.logger = nil
ActiveRecord::Schema.define do
  # STEP TWO: Define your tables here.
  create_table :users, force: true do |t|
    t.text :first_name, null: false
    t.timestamps null: false
  end

  create_table :versions, force: true do |t|
    t.string :item_type, null: false
    t.integer :item_id, null: false
    t.string :event, null: false
    t.string :whodunnit
    t.json :object
    t.json :object_changes
    t.datetime :created_at
  end
  add_index :versions, %i[item_type item_id]
end
ActiveRecord::Base.logger = Logger.new(STDOUT)
require "paper_trail"

# STEP FOUR: Define your AR models here.
class User < ActiveRecord::Base
  has_paper_trail
end

# STEP FIVE: Please write a test that demonstrates your issue.
describe User do
  with_versioning do
    describe '`have_a_version_with_changes` matcher' do
      let!(:user) { User.create(first_name: "Jane") }

      it 'is possible to do assertions on version changes' do
        user.update!(first_name: "Susy")
        expect(user).to have_a_version_with_changes name: "Susy"
      end
    end
  end
end

# STEP SIX: Run this script using `ruby my_bug_report.rb`

Output:

Failures:

  1) User with versioning `have_a_version_with_changes` matcher is possible to do assertions on version changes
     Failure/Error: expect(user).to have_a_version_with_changes name: "Susy"
     
     ActiveRecord::StatementInvalid:
       Trilogy::QueryError: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE '[\"Susy\",%') OR (object_changes->>'name' ILIKE '[%,\"Susy\"]%'))) LIMIT ' at line 1 (trilogy_query_recv)
     # script.rb:59:in 'block (4 levels) in <main>'
     # ------------------
     # --- Caused by: ---
     # Trilogy::QueryError:
     #   1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE '[\"Susy\",%') OR (object_changes->>'name' ILIKE '[%,\"Susy\"]%'))) LIMIT ' at line 1 (trilogy_query_recv)
     #   script.rb:59:in 'block (4 levels) in <main>'

What:

• ILIKE is not part of the SQL standard, even if it is widely supported in postgres and similar.
• Oracle, SQL Server would also be equally affected

Previously reported:
#1496

Affected areas:
https://github.com/search?q=repo%3Apaper-trail-gem%2Fpaper_trail%20ILIKE&type=code

Suggested fix:

• Compose with arel and use ILIKE on supported systems, LIKE + LOWER() on unsupported systems?

[github-actions]

This issue has been automatically marked as stale due to inactivity.
The resources of our volunteers are limited.
Bug reports must provide a script that reproduces the bug, using our template. Feature suggestions must include a promise to build the feature yourself.
Thank you for all your contributions.

[CloCkWeRX]

Not stale.