Hi all,
I got a strange notification last night using hoptoad. For a rails app running rails 2.3.5 on a mysql database.
I have written a search engine for a site that combines a few named scopes. One of those named scopes is this;
named_scope :matching, lambda { |word| { :joins => :consumer, :conditions => ['(jobs.name like :word) OR (jobs.description like :word) OR (users.zip_code like :word) OR (users.city like :word)', { :word => "%#{word}%"}]} }
All works fine and the statement above (in combination with another default named scope) generates a nice sql statement;
SELECT `jobs`.* FROM `jobs` INNER JOIN `users` ON `users`.id = `jobs`.consumer_id AND (`users`.`type` = 'Consumer' ) WHERE (((`users`.locale = 'nl') AND ((jobs.name like '%test%') OR (jobs.description like '%test%') OR (users.zip_code like '%test%') OR (users.city like '%test%'))) AND (active = 1)) ORDER BY auction_ends_at <= '2010-02-26 09:25', `jobs`.updated_for_search_at DESC LIMIT 0, 8
However; when a user would enter a dot in the string to search it all goes wrong. The dot inside the search string, seems to blow up the query like this;
SELECT `jobs`.`id` AS t0_r0, `jobs`.`consumer_id` AS t0_r1, `jobs`.`name` AS t0_r2, `jobs`.`description` AS t0_r3, `jobs`.`field_id` AS t0_r4, `jobs`.`auction_ends_at` AS t0_r5, `jobs`.`materials_provided` AS t0_r6, `jobs`.`desired_first_day` AS t0_r7, `jobs`.`price_type_id` AS t0_r8, `jobs`.`created_at` AS t0_r9, `jobs`.`updated_at` AS t0_r10, `jobs`.`active` AS t0_r11, `jobs`.`best_bid` AS t0_r12, `jobs`.`bid_count` AS t0_r13, `jobs`.`updated_for_search_at` AS t0_r14, `jobs`.`highlight` AS t0_r15, `jobs`.`featured_till` AS t0_r16, `jobs`.`thumbnail` AS t0_r17, `jobs`.`expire_notification_sent` AS t0_r18, `jobs`.`worker_id` AS t0_r19, `jobs`.`asap` AS t0_r20, `jobs`.`worker_reminder_count` AS t0_r21, `jobs`.`next_worker_reminder_at` AS t0_r22, `jobs`.`view_count` AS t0_r23, `users`.`id` AS t1_r0, `users`.`username` AS t1_r1, `users`.`email` AS t1_r2, `users`.`crypted_password` AS t1_r3, `users`.`password_salt` AS t1_r4, `users`.`persistence_token` AS t1_r5, `users`.`created_at` AS t1_r6, `users`.`updated_at` AS t1_r7, `users`.`type` AS t1_r8, `users`.`first_name` AS t1_r9, `users`.`last_name` AS t1_r10, `users`.`address` AS t1_r11, `users`.`zip_code` AS t1_r12, `users`.`city` AS t1_r13, `users`.`country` AS t1_r14, `users`.`mobile` AS t1_r15, `users`.`company_name` AS t1_r16, `users`.`vat_number` AS t1_r17, `users`.`terms_and_conditions` AS t1_r18, `users`.`show_in_dictionary` AS t1_r19, `users`.`lat` AS t1_r20, `users`.`lng` AS t1_r21, `users`.`show_mobile` AS t1_r22, `users`.`single_access_token` AS t1_r23, `users`.`newsletter` AS t1_r24, `users`.`real_range` AS t1_r25, `users`.`extend_range_till` AS t1_r26, `users`.`score` AS t1_r27, `users`.`description` AS t1_r28, `users`.`url` AS t1_r29, `users`.`avatar_file_name` AS t1_r30, `users`.`avatar_content_type` AS t1_r31, `users`.`avatar_file_size` AS t1_r32, `users`.`avatar_updated_at` AS t1_r33, `users`.`current_login_at` AS t1_r34, `users`.`last_login_at` AS t1_r35, `users`.`locale` AS t1_r36, `users`.`vat_requested` AS t1_r37, `users`.`is_legal` AS t1_r38, `users`.`state_id` AS t1_r39, `users`.`fod_registered` AS t1_r40, `users`.`fod_registration_number` AS t1_r41, `users`.`fod_last_updated_at` AS t1_r42, `users`.`fod_last_checked_at` AS t1_r43, `fields`.`id` AS t2_r0, `fields`.`name_nl` AS t2_r1, `fields`.`name_fr` AS t2_r2, `fields`.`name_en` AS t2_r3, `fields`.`created_at` AS t2_r4, `fields`.`updated_at` AS t2_r5, `fields`.`jobs_count` AS t2_r6, `fields`.`aliases_nl` AS t2_r7, `fields`.`aliases_fr` AS t2_r8, `fields`.`aliases_en` AS t2_r9, `price_types`.`id` AS t3_r0, `price_types`.`name_nl` AS t3_r1, `price_types`.`position` AS t3_r2, `price_types`.`created_at` AS t3_r3, `price_types`.`updated_at` AS t3_r4, `price_types`.`name_fr` AS t3_r5, `price_types`.`name_en` AS t3_r6, `price_types`.`short_code` AS t3_r7 FROM `jobs` LEFT OUTER JOIN `users` ON `users`.id = `jobs`.consumer_id AND (`users`.`type` = 'Consumer' ) LEFT OUTER JOIN `fields` ON `fields`.id = `jobs`.field_id LEFT OUTER JOIN `price_types` ON `price_types`.id = `jobs`.price_type_id INNER JOIN `users` ON `users`.id = `jobs`.consumer_id AND (`users`.`type` = 'Consumer' ) WHERE (((`users`.locale = 'nl') AND ((jobs.name like '%v.c.s.%') OR (jobs.description like '%v.c.s.%') OR (users.zip_code like '%v.c.s.%') OR (users.city like '%v.c.s.%'))) AND (active = 1)) ORDER BY auction_ends_at <= '2010-02-26 09:26', `jobs`.updated_for_search_at DESC LIMIT 0, 8
Currently I impletmented a small "hack" in the named scope, which would replace a . with a \.;
{ :word => "%#{word.gsub('.', '\.')}%"}
Has anyone got any ideas on what may cause this behaviour?
Is it worth making a ticket in rails core lighthouse?
Stijn
