searching-bug-reduction.rb

#!/usr/bin/env ruby

# A couple of Searchlogic searches. Both are identical from a code
# perspective. With one, the user supplies a '.' (period) as part of the
# search query. This search blows up because it's generating an invalid SQL
# statement. I say it shouldn't. The error in question is:
#     
# Mysql::Error: Not unique table/alias: 'users':
#     
#   SELECT `companies`.`id` AS t0_r0,
#          `companies`.`name` AS t0_r1,
#          `users`.`id` AS t1_r0,
#          `users`.`name` AS t1_r1,
#          `users`.`company_id` AS t1_r2
#   FROM `companies`
#   LEFT OUTER JOIN `users` ON users.company_id = companies.id
#   INNER JOIN      `users` ON users.company_id = companies.id
#   WHERE ((companies.name LIKE '%a.c%') OR (users.name LIKE '%a.c%'))
#     
# As you can see, it's generating a LEFT OUTER JOIN and an INNER JOIN with the
# same name, which is what's causing MySQL to blow up.  The interesting thing,
# however, is that the other test without the dot (the one that works!)
# produces a completely different set of queries:
#    
#   SELECT `companies`.* FROM `companies`
#   INNER JOIN `users` ON users.company_id = companies.id
#   WHERE ((companies.name LIKE '%abc%') OR (users.name LIKE '%abc%'))
#    
# (then separately loading any associated user ids with a single query to pull
# back all the matching ids).
#
# Just to be clear, there are no regular expressions involved here. The '.' is
# not a special character in MySQL. In MySQL, '%' is the Kleene-star and '_'
# matches exactly one character.
#
# So. WTF?

require 'rubygems'

require 'active_record'

require 'test/unit'
require 'active_support/test_case'

# Set up ActiveRecord
ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(
  :adapter  => "mysql",
  :host     => "localhost",
  :username => "root",
  :database => "reduction_script"
)

# Searchlogic wants to be brought in after AR has been configured.
require 'searchlogic'

# Create the database schema
ActiveRecord::Schema.define(:version => 1) do
  create_table "companies", :force => true do |t|
    t.string   "name",       :null => false
  end

  create_table "users", :force => true do |t|
    t.string   "name",          :null => false
    t.integer  "company_id",    :null => false
  end
end

class Company < ActiveRecord::Base
  has_one :user
  accepts_nested_attributes_for :user
end

class User < ActiveRecord::Base
  belongs_to :company
end

class SearchWithDotTest < ActiveSupport::TestCase
  test "searching without a dot in the search string" do
    assert_nothing_raised do
      search = Company.search(:name_or_user_name_like => "abc")
      companies = search.find(:all, :include => :user)
    end
  end

  test "searching with a dot in the search string (not eager loading user)" do
    assert_nothing_raised do
      search = Company.search(:name_or_user_name_like => "a.c")
      companies = search.find(:all)
    end
  end

  test "searching with a dot in the search string and eager loading user" do
    assert_nothing_raised do
      search = Company.search(:name_or_user_name_like => "a.c")
      companies = search.find(:all, :include => :user)
    end
  end
end