Issue with DECIMAL columns and MySQL #12

Closed
SteveFenn opened this Issue Sep 5, 2011 · 7 comments

Projects

None yet

2 participants

@SteveFenn

Hey

I have just started using the gem and it's great!

Whilst using it I have encountered a problem with scoped_search on decimals. I've isolated my problem down to the simple test case below:

class Order < ActiveRecord::Base

    scoped_search :on => :total

end

"total" is a DECIMAL column within the database, then running this line within console:

> Order.search_for("123.12")

Gives these results in the development log:

Order Load (65.0ms)  SELECT * FROM `orders` WHERE (`orders`.`total` = 123)

It seems that at some point my search number is being converted into an integer. I believe this should be querying for "orders.total = 123.12". As the moment, the query is, of course, effectively looking for values of 123.00, and hence is not finding the order I'm looking for.

I am using rails version 2.3.10, scoped_search version 2.3.1 and I am using MySQL.

I apologise if this post doesn't make sense.

Thanks for the plugin,
Steve

@abenari
Collaborator
abenari commented Sep 6, 2011

----- Original Message -----

From: "SteveFenn" reply@reply.github.com
To: abenari@redhat.com
Sent: Monday, September 5, 2011 6:27:07 PM
Subject: [scoped_search] Issue with DECIMAL columns and MySQL (#12)
Hey

I have just started using the gem and it's great!

Whilst using it I have encountered a problem with scoped_search on
decimals. I've isolated my problem down to the simple test case below:

class Order < ActiveRecord::Base

scoped_search :on => :total

end
"total" is a DECIMAL column within the database, then running this
line within console:

Order.search_for("123.12")

Gives these results in the development log:

Order Load (65.0ms) SELECT * FROM orders WHERE (orders.total =
123)

It seems that at some point my search number is being converted into
an integer. I believe this should be querying for "orders.total =
123.12". As the moment, the query is, of course, effectively looking
for values of 123.00, and hence is not finding the order I'm looking
for.

I am using rails version 2.3.10, scoped_search version 2.3.1 and I am
using MySQL.

I apologise if this post doesn't make sense.

Thanks for the plugin,
Steve

Reply to this email directly or view it on GitHub:
#12

Thanks for reporting the problem.
I'll try to reproduce it and will get back to you as soon as I have results.
Amos.

@abenari
Collaborator
abenari commented Sep 6, 2011

----- Original Message -----

From: "SteveFenn" reply@reply.github.com
To: abenari@redhat.com
Sent: Monday, September 5, 2011 6:27:07 PM
Subject: [scoped_search] Issue with DECIMAL columns and MySQL (#12)
Hey

I have just started using the gem and it's great!

Whilst using it I have encountered a problem with scoped_search on
decimals. I've isolated my problem down to the simple test case below:

class Order < ActiveRecord::Base

scoped_search :on => :total

end
"total" is a DECIMAL column within the database, then running this
line within console:

Order.search_for("123.12")

Gives these results in the development log:

Order Load (65.0ms) SELECT * FROM orders WHERE (orders.total =
123)

It seems that at some point my search number is being converted into
an integer. I believe this should be querying for "orders.total =
123.12". As the moment, the query is, of course, effectively looking
for values of 123.00, and hence is not finding the order I'm looking
for.

I am using rails version 2.3.10, scoped_search version 2.3.1 and I am
using MySQL.

I apologise if this post doesn't make sense.

Thanks for the plugin,
Steve

Reply to this email directly or view it on GitHub:
#12

Hi Steve,
I have found the following suggestion in a blog:

...
Check the definition of the table in Mysql, chances are you have a decimal column with no decimal "scale" set.
By default, if you don't tell Mysql ho many decimal places to use it will have none, and ActiveRecord will then simply interpret that as an Integer.
Update you column with a migration like this:
change_column('table_name', 'colun_name', :decimal, { :scale => 2, :precision => 10 } )

See original post here:
http://n4k3d.com/blog/post/18/activerecord_magically_changing_decimal_columns_to_integers/

Please update me if this solves your issue.
Thanks,
Amos.

@SteveFenn

Hi Amos,

Thanks for getting back so quickly

I've taken a look, and the column is a decimal - it's got :precision => 8, :scale => 3 I should have put this in the original post.

The regular Rails finders seem to work fine:

$ Order.find_by_total(123.12)

This gives this result in the development log:

=> SELECT * FROM `orders` WHERE (`total` = 123.12) LIMIT 1

Checked order.total as well:

$ Order.first.total.class
=> BigDecimal

Thank you again for looking into this
Steve

@abenari
Collaborator
abenari commented Sep 6, 2011

----- Original Message -----

From: "SteveFenn" reply@reply.github.com
To: abenari@redhat.com
Sent: Tuesday, September 6, 2011 11:25:56 AM
Subject: Re: [scoped_search] Issue with DECIMAL columns and MySQL (#12)
Hi Amos,

Thanks for getting back so quickly

I've taken a look, and the column is a decimal - it's got :precision
=> 8, :scale => 3 I should have put this in the original post.

The regular Rails finders seem to work fine:

$ Order.find_by_total(123.12)

This gives this result in the development log:

=> SELECT * FROM orders WHERE (total = 123.12) LIMIT 1

Checked order.total as well:

$ Order.first.total.class
=> BigDecimal

Thank you again for looking into this
Steve

Reply to this email directly or view it on GitHub:
#12 (comment)

Hi Steve,
I have managed to reproduce the issue.
It is now fixed and I have added a relevant test.
Thanks again for reporting the issue.
The fix will be available shortly in the gem release.
Thanks,
Amos.

@SteveFenn

Hi Amos,

Thanks for looking into this and fixing it, Its a great plugin

When the gem release comes out I will test and confirm it fixes problem

Thanks,
Steve

@SteveFenn SteveFenn closed this Sep 6, 2011
@SteveFenn SteveFenn reopened this Sep 6, 2011
@abenari abenari closed this Sep 6, 2011
@abenari
Collaborator
abenari commented Sep 6, 2011

fixed in release 2.3.3

@SteveFenn

Hey

This has fixed the original problem we were having,

Thanks again

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment