Bug 486142

+++ This bug was initially created as a clone of Bug #486036 +++

Description of problem:
The custom field called 'cf_pm_score' is created in the database as a freetext field type (varchar) and even though it only contains integer values it is not sorted correctly by numerical order. Therefore you get results such as:

http://hurl.test.redhat.com/G48Zdo

Changing to the column type in the database for bugs.cf_pm_score to INTEGER does fix the sorting issue but is not optimal as anything other than an integer entered there will cause a database error to occur.

Another fix is to hack Bugzilla/Search to alter the ORDER BY clause to do a type conversion on the fly of the cf_pm_score data to integer allowing the order by to work numerically.

I have a patch that does the latter and works well.

Dave

--- Additional comment from dkl on 2009-02-17 18:13:26 EDT ---

Created an attachment (id=332312)
Patch to hack the order by for cf_pm_score to sort numerically (v1)

Patch to hack the order by for cf_pm_score to sort numerically.

Please review
Dave

--- Additional comment from nelhawar on 2009-02-18 00:00:17 EDT ---

(From update of attachment 332312 [details])
Hey Dave, I tested the patch on bz-web2 and works good.

Noura

--- Additional comment from kbaker on 2009-02-18 11:09:41 EDT ---

definitely a hack. 

Riek or Subhendu can confirm but AIUI this field will always be integer. Can we take a copy of the data, drop the custom field and re-add it as an int cf?

Bill Peck is hitting a simliar issue in beaker.

--- Additional comment from dkl on 2009-02-18 11:26:11 EDT ---

(In reply to comment #3)
> definitely a hack. 
> 
> Riek or Subhendu can confirm but AIUI this field will always be integer. Can we
> take a copy of the data, drop the custom field and re-add it as an int cf?
> 
> Bill Peck is hitting a simliar issue in beaker.

I agree but if I change the column to INT and someone per chance enters something with alpha character in it, the code currently thinks the columns is a varchar type which will produce a nasty DB error.

I would rather take the time do this right in another bug that creates a new FIELD_TYPE_INTEGER that has the proper sanity checks before inserting/updating the data in the db. Currently the code will still think that the column is a FIELD_TYPE_FREETEXT.

This order by hack will get his reports working until we can add the new functionality and properly convert the cf_pm_score over to FIELD_TYPE_INTEGER.

Dave