Greg Hauptmann
2009-Feb-27 03:16 UTC
rails find :conditions seems to remove extra blanks? (e.g. where description = ''x x x x x'') is this a rails bug?
Hi, Is this a Rails bug? NORMAL SQL ?> AccountItem.find_by_sql( "select * from account_items where description ''FISHERS IGA HAMILTON HAMILTON AU 000318''") => [#<AccountItem id: 1100, account_id: 5, recurring_id: nil, date: "2008-12-30", amount: #<BigDecimal:24fc840,''-0.4E3'',4(12)>, balance: #<BigDecimal:24fc69c,''0.745538E4'',8(12)>, description: "FISHERS IGA HAMILTON HAMILTON AU 000318", notes: nil, projection: false, created_at: "2009-02-08 22:29:25", updated_at: "2009-02-08 22:29:25">]>>WITH FINDER - DOES FIND THE RECORD ?> AccountItem.find(:all, :conditions => ["description = ?", ''FISHERS IGA HAMILTON HAMILTON AU 000318''] ) => [] EXTRACT FROM SERVER LOG - THE CREATED SQL SEEMS TO BE MISSING THE EXTRA SPACES?? [4;36;1mAccountItem Load (3.0ms) [0m [0;1mSELECT * FROM `account_items` WHERE (description = ''FISHERS IGA HAMILTON HAMILTON AU 000318'') [0m That is find method seems to be mis-translating the SQL and removing spaces. -- Greg blog.gregnet.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Matt Jones
2009-Feb-27 20:52 UTC
Re: rails find :conditions seems to remove extra blanks? (e.g. where description = ''x x x x x'') is this a rails bug?
The log entries get passed through .squeeze('' ''), I''m guessing to make them look nicer on the log. (activerecord/lib/active_record/connection_adapters/ abstract_adapter.rb, line 189) Otherwise, I can''t see anywhere in the code that''s dropping the spaces. Google''s formatting engine chews up spaces pretty bad, but are you *sure* you''ve got the same number in that second query? On an unrelated note, are you really sure you want a fixed-width space- delimited field in the DB? Wouldn''t it be easier to use multiple fields? --Matt Jones On Feb 26, 10:16 pm, Greg Hauptmann <greg.hauptmann.r...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi, > Is this a Rails bug? > > NORMAL SQL > ?> AccountItem.find_by_sql( "select * from account_items where description > ''FISHERS IGA HAMILTON HAMILTON AU 000318''") > => [#<AccountItem id: 1100, account_id: 5, recurring_id: nil, date: > "2008-12-30", amount: #<BigDecimal:24fc840,''-0.4E3'',4(12)>, balance: > #<BigDecimal:24fc69c,''0.745538E4'',8(12)>, description: "FISHERS IGA HAMILTON > HAMILTON AU 000318", notes: nil, projection: false, created_at: > "2009-02-08 22:29:25", updated_at: "2009-02-08 22:29:25">] > > > > WITH FINDER - DOES FIND THE RECORD > ?> AccountItem.find(:all, :conditions => ["description = ?", ''FISHERS IGA > HAMILTON HAMILTON AU 000318''] ) > => [] > EXTRACT FROM SERVER LOG - THE CREATED SQL SEEMS TO BE MISSING THE EXTRA > SPACES?? > [4;36;1mAccountItem Load (3.0ms) [0m [0;1mSELECT * FROM > `account_items` WHERE (description = ''FISHERS IGA HAMILTON HAMILTON AU > 000318'') [0m > > That is find method seems to be mis-translating the SQL and removing spaces. > > -- > Greghttp://blog.gregnet.org--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Greg Hauptmann
2009-Feb-27 23:50 UTC
Re: rails find :conditions seems to remove extra blanks? (e.g. where description = ''x x x x x'') is this a rails bug?
2009/2/28 Matt Jones <al2o3cr-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>> Google''s formatting > engine chews up spaces pretty bad, but are you *sure* you''ve got the > same number in > that second query? >It should be as I used the save copy/paste of the string. I take your point re the standard output and what could happen there, however the bottom line seems to be when I use exactly the same string in the "find" method it doesn''t find it, whereas if I use it in the "find_by_sql" method it does find the row. I wonder how I could confirm the exact SQL that is getting issued to the DB without having to worry about any changes to it whilst it is being displayed. Perhaps I''ll try to switch on logging in mysql and monitor at that point?> On an unrelated note, are you really sure you want a fixed-width space- > delimited field in the DB? > Wouldn''t it be easier to use multiple fields? >Not exactly sure what you meant here. The description field is just from CSV file for bank transaction downloads. I thought I would have a table that just exactly maps the CSV files into it (then uniqueness is based on Date-Amount-Description) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Matt Jones
2009-Mar-01 00:12 UTC
Re: rails find :conditions seems to remove extra blanks? (e.g. where description = ''x x x x x'') is this a rails bug?
On Feb 27, 6:50 pm, Greg Hauptmann <greg.hauptmann.r...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 2009/2/28 Matt Jones <al2o...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > Google''s formatting > > engine chews up spaces pretty bad, but are you *sure* you''ve got the > > same number in > > that second query? > > It should be as I used the save copy/paste of the string. I take your point > re the standard output and what could happen there, however the bottom line > seems to be when I use exactly the same string in the "find" method it > doesn''t find it, whereas if I use it in the "find_by_sql" method it does > find the row. > > I wonder how I could confirm the exact SQL that is getting issued to the DB > without having to worry about any changes to it whilst it is being > displayed. Perhaps I''ll try to switch on logging in mysql and monitor at > that point? >That would be a good idea. You might also want to try some queries like: AccountItem.find(:all, :conditions => ["description LIKE ?", ''FISHERS IGA%''] ) with progressively longer substrings to see if any of those work.> > On an unrelated note, are you really sure you want a fixed-width space- > > delimited field in the DB? > > Wouldn''t it be easier to use multiple fields? > > Not exactly sure what you meant here. The description field is just from > CSV file for bank transaction downloads. I thought I would have a table > that just exactly maps the CSV files into it (then uniqueness is based on > Date-Amount-Description)My thought was that this "description" field looks like more than one field glued together, probably screen-scraped from a bank terminal interface somewhere. The multiple spaces imply that it''s probably fixed-width, so why not break the field apart according to those fixed- width fields? (Without any knowledge of the data source, it looks like [Name, City, (Country?), (Account number?)]) --Matt Jones --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---