Forum: Ruby on Rails rails find :conditions seems to remove extra blanks? (e.g. where description = 'x x x x x') is this

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
D5df9fcd7ef4c3c937435d7d6adeab2a?d=identicon&s=25 Greg Hauptmann (Guest)
on 2009-02-27 04:17
(Received via mailing list)
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
http://blog.gregnet.org/
6883e5ef03484d4fcef507d7b4f1d243?d=identicon&s=25 Matt Jones (Guest)
on 2009-02-27 21:53
(Received via mailing list)
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...@gmail.com>
D5df9fcd7ef4c3c937435d7d6adeab2a?d=identicon&s=25 Greg Hauptmann (Guest)
on 2009-02-28 00:51
(Received via mailing list)
2009/2/28 Matt Jones <al2o3cr@gmail.com>

> 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)
6883e5ef03484d4fcef507d7b4f1d243?d=identicon&s=25 Matt Jones (Guest)
on 2009-03-01 01:13
(Received via mailing list)
On Feb 27, 6:50 pm, Greg Hauptmann <greg.hauptmann.r...@gmail.com>
wrote:
> 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
This topic is locked and can not be replied to.