Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 1.2.0
-
Fix Version/s: 1.2.2
-
Component/s: Geographical
-
Labels:None
-
Environment:PostgreSQL 8.3 on Linux
Description
500 | Internal Server Error | Doctrine_Connection_Pgsql_Exception
SQLSTATE[22003]: Numeric value out of range: 7 ERROR: input is out of range
DQL:
at Doctrine_Connection->execute('SELECT h.id AS h_id, h.name AS hname, h.address AS haddress, h.zip AS hzip, h.url AS hurl, h.photo_url AS hphoto_url, h.checkin_from AS hcheckin_from, h.checkin_to AS hcheckin_to, h.checkout_from AS hcheckout_from, h.checkout_to AS hcheckout_to, h.is_published AS his_published, h.class AS hclass, h.currencycode AS hcurrencycode, h.minrate AS hminrate, h.maxrate AS hmaxrate, h.preferred AS hpreferred, h.nr_rooms AS hnr_rooms, h.bookable_direct AS hbookable_direct, h.ranking AS hranking, h.city_id AS hcity_id, h.policygroup_id AS hpolicygroup_id, h.is_closed AS his_closed, h.hoteltype_id AS hhoteltype_id, h.class_is_estimated AS hclass_is_estimated, h.review_nr AS hreview_nr, h.review_score AS hreview_score, h.created_at AS hcreated_at, h.updated_at AS hupdated_at, h.latitude AS hlatitude, h.longitude AS hlongitude, ((ACOS(SIN(45.41244780000000000000 * PI() / 180) * SIN(h.latitude * PI() / 180) + COS(45.41244780000000000000 * PI() / 180) * COS(h.latitude * PI() / 180) * COS((6.63685100000000000000 - h.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS h0, ((ACOS(SIN(45.41244780000000000000 * PI() / 180) * SIN(h.latitude * PI() / 180) + COS(45.41244780000000000000 * PI() / 180) * COS(h.latitude * PI() / 180) * COS((6.63685100000000000000 - h.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) AS h1 FROM hotel h WHERE (((ACOS(SIN(45.41244780000000000000 * PI() / 180) * SIN(h.latitude * PI() / 180) + COS(45.41244780000000000000 * PI() / 180) * COS(h.latitude * PI() / 180) * COS((6.63685100000000000000 - h.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) < 3 AND h.is_published = ? AND h.id != ?) ORDER BY h_1 asc LIMIT 5', array('true', 189646))
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php line 976
I'm using NUMERIC on column type with precision 10, but the problem is not in the column latitude and longitude, it´s on calculation.
To resolve this bug I made a casting inside of ACOS function calculation, but I don't know how it work on mysql.
-
-
- I've posted a patch file attached here with the change that I made.
-
Examples of result of h_0, h_1 on sql fixed.
0.132901717706014221300; 0.213884392376139928479590400
0.15938002241913817800; 0.256497124695862251207283200
0.205676327970298764600; 0.331003964361032495016422400
0.205676327970298764600; 0.331003964361032495016422400