Doctrine 1
  1. Doctrine 1
  2. DC-524

Out Of Range in Template Geographical Calculation using Postgresql

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical 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

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Nei Rauni Santos
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: