Geocoder produces PG::SyntaxError on rails 4.1 for count queries

[mswart]

After upgrade to rails 4.1 Geocoder fails to count the number of results when searching by distance. I have reproduced it with a very simple example:

> Place.all.near([0.05, 0.1], 10, units: :km).count
SELECT COUNT(places.*, 6371.0 * 2 * ASIN(SQRT(POWER(SIN((0.05 - places.latitude) * PI() / 180 / 2), 2) + COS(0.05 * PI() / 180) * COS(places.latitude * PI() / 180) * POWER(SIN((0.1 - places.longitude) * PI() / 180 / 2), 2))) AS distance, CAST(DEGREES(ATAN2( RADIANS(places.longitude - 0.1), RADIANS(places.latitude - 0.05))) + 360 AS decimal) % 360 AS bearing) FROM "places"  WHERE (places.latitude BETWEEN -0.039932160591873045 AND 0.13993216059187305 AND places.longitude BETWEEN 0.010067805164487686 AND 0.18993219483551232 AND 6371.0 * 2 * ASIN(SQRT(POWER(SIN((0.05 - places.latitude) * PI() / 180 / 2), 2) + COS(0.05 * PI() / 180) * COS(places.latitude * PI() / 180) * POWER(SIN((0.1 - places.longitude) * PI() / 180 / 2), 2))) <= 10)
PG::SyntaxError: ERROR:  syntax error at or near "AS"
LINE 1: ...((0.1 - places.longitude) * PI() / 180 / 2), 2))) AS distanc...

In Rails 4.0.4 was

SELECT COUNT(*) FROM "places" WHERE (places.latitude BETWEEN -0.039932160591873045 AND 0.13993216059187305 AND places.longitude BETWEEN 0.010067805164487686 AND 0.18993219483551232 AND 6371.0 * 2 * ASIN(SQRT(POWER(SIN((0.05 - places.latitude) * PI() / 180 / 2), 2) + COS(0.05 * PI() / 180) * COS(places.latitude * PI() / 180) * POWER(SIN((0.1 - places.longitude) * PI() / 180 / 2), 2))) <= 10)
=> 0

Normal query work still in 4.1.0:

> Place.all.near([0.05, 0.1], 10, units: :km)
SELECT places.*, 6371.0 * 2 * ASIN(SQRT(POWER(SIN((0.05 - places.latitude) * PI() / 180 / 2), 2) + COS(0.05 * PI() / 180) * COS(places.latitude * PI() / 180) * POWER(SIN((0.1 - places.longitude) * PI() / 180 / 2), 2))) AS distance, CAST(DEGREES(ATAN2( RADIANS(places.longitude - 0.1), RADIANS(places.latitude - 0.05))) + 360 AS decimal) % 360 AS bearing FROM "places"  WHERE (places.latitude BETWEEN -0.039932160591873045 AND 0.13993216059187305 AND places.longitude BETWEEN 0.010067805164487686 AND 0.18993219483551232 AND 6371.0 * 2 * ASIN(SQRT(POWER(SIN((0.05 - places.latitude) * PI() / 180 / 2), 2) + COS(0.05 * PI() / 180) * COS(places.latitude * PI() / 180) * POWER(SIN((0.1 - places.longitude) * PI() / 180 / 2), 2))) <= 10)  ORDER BY distance ASC
=> #<ActiveRecord::Relation []>

Model:

class Place < ActiveRecord::Base
  reverse_geocoded_by :latitude, :longitude
end

Schema:

ActiveRecord::Schema.define(version: 20140409132943) do
  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  create_table "places", force: true do |t|
    t.datetime "created_at"
    t.datetime "updated_at"
    t.float    "latitude"
    t.float    "longitude"
  end
end

Tested with
ruby 2.1
rails 4.1.0 (worked with rails 4.0.4)
geocoder 1.1.9 and master @2a29613acda2468e23056921cc257252d2128ea8
postgresql 9.1

[cyrusstoller]

+1

Oddly this issue only seems to arise for me when adding distance parameters greater > 10.

[heidar]

+1 happens for me with postgres and sqlite, same as @mswart described.

[jesus-sayar]

+1 I have the same problem. I am also using Rails 4.1.0 and Ruby 2.0

[alexreisner]

To everyone experiencing this bug: I believe this is a result of this change in Rails 4.1. Can you please confirm that using count(:all) solves the problem?

[mswart]

@alexreisner Yes works (with example app + real app):

> Place.near([0.05, 0.1], 10, units: :km).count(:all)
SELECT COUNT(*) FROM "canteens"  WHERE (canteens.latitude BETWEEN -0.039932160591873045 AND 0.13993216059187305 AND canteens.longitude BETWEEN 0.010067805164487686 AND 0.18993219483551232 AND 6371.0 * 2 * ASIN(SQRT(POWER(SIN((0.05 - canteens.latitude) * PI() / 180 / 2), 2) + COS(0.05 * PI() / 180) * COS(canteens.latitude * PI() / 180) * POWER(SIN((0.1 - canteens.longitude) * PI() / 180 / 2), 2))) <= 10)
=> 0

[cyrusstoller]

Just tried it. count(:all) works for me.

Any tips on how to make this work with https://github.com/mislav/will_paginate

[alexreisner]

Thanks guys, I'm closing this.

@cyrusstoller I don't use will_paginate so I'm not sure what the problem is, but it's possible that gem is similarly calling count with no argument. Might require submitting a patch for that.

[mswart]

@alexreisner It is not possible to adjust the implementation of the near scope accordingly? Because other scopes that apply only ordering and filtering still works in rails 4.1. Of cause sometimes you want the distance for every objects in the collection, but there are also many use cases where the reason the apply the near scope is only to filter and/or order the collection. Maybe an optional parameter that specify whether the distance from every record is needed?

> Place.order(:latitude).count
SELECT COUNT(*) FROM "places"
=> 0
> Place.where(latitude: 0.3).count
SELECT COUNT(*) FROM "places"  WHERE "places"."latitude" = 0.3
=> 0

[alexreisner]

@mswart true, although to order the collection you need the SELECT clause. If we're talking about only selecting objects within a given radius and we don't care about how far away they are from the center then removing the SELECT clause fixes the count issue. However, that seems like a pretty specific use case so obviously we don't want that to be default behavior. I don't see any way to fix both problems without app changes.

[cyrusstoller]

@alexreisner fair enough. Thanks for tracking this down!

[mswart]

@alexreisner Would it be ok to add an order_by_distance option to the near scope?
With this I could write:

Place.near([0.05, 0.1], 20, select_bearing: false, select_distance: false, order_by_distance: true).count
SELECT COUNT(places.*) FROM "places"  WHERE (places.latitude BETWEEN -0.23946356622169596 AND 0.33946356622169593 AND places.longitude BETWEEN -0.18946367644128673 AND 0.38946367644128677 AND 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((0.05 - places.latitude) * PI() / 180 / 2), 2) + COS(0.05 * PI() / 180) * COS(places.latitude * PI() / 180) * POWER(SIN((0.1 - places.longitude) * PI() / 180 / 2), 2))) <= 20) ORDER BY 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((0.05 - places.latitude) * PI() / 180 / 2), 2) + COS(0.05 * PI() / 180) * COS(places.latitude * PI() / 180) * POWER(SIN((0.1 - places.longitude) * PI() / 180 / 2), 2))) ASC
=> 0

(Distance is not calculated as one additional column, but instead directly in the order clause)

With this approach only the place where the scope is applied needs to be changed, and not every place, where a given scope is neither counted or normal used. Additional to will_paging and other pagination gems / responder could be used without adjustment. And because a simple count is enough in most cases, most gems / authors used and probably will still use count without :all.

Alternative select_distance: false could automatically move the distance column into the order clause, so that no additional select column are needed.

I could try to provide a PR.

[alexreisner]

@mswart an :order_by_distance option sounds good. Would definitely be interested in a pull request.

[maxcal]

I´m getting this issue in a plain RSpec controller test

class StationsController < ApplicationController
  # GET /stations/search?lat=x&lng=x&radius
  def search
    radius = params[:radius] || 20
    @stations = Station.all.near([params[:lat], params[:lng]], radius, units: :km)
  end
end

describe "GET search" do
    get :search, :lat => 53.884916, :lng => 27.53088, :radius => 1000
    # ActiveRecord::StatementInvalid
    # PG::SyntaxError: ERROR:  syntax error at or near "AS"
    expect(assigns(:stations)).to_not be_empty
    # Works
    expect(assigns(:stations).count(:all)).to eq 0
end

[vicovictor]

@cyrusstoller wondering if you got it working with will_paginate?

[cyrusstoller]

@victorngkp unfortunately I haven't. I'm still running rails 4.0.5 because of this.

[cobyism]

I’m running into this exact same issue with a @mymodel.any? call, not .count. Any suggestions?

[alexreisner]

@cobyism I think the problem you are having is more likely the one described in #652. Correct?

[cobyism]

@alexreisner Oh, yes. That’s exactly what I’m running into—sorry, I had a look for existing issues for that, but I obviously missed it. Thanks!

[hmaack]

Here you can find the ”count” part in the readme - https://github.com/alexreisner/geocoder#rails-41-note

[sunny]

Link moved to: https://github.com/alexreisner/geocoder#using-count-with-rails-41

[mikeheft]

I'm getting this same error in rails 4.2 even with the argument as suggested.

[kpzart]

I have this (or related) issue when i upgrade from rails 5.1 to 5.2 or higher, and the count(:all) solution does not work, because a call limit after the near statement:

locations.near(current_location, radius).limit(100).count(:all)
*** ArgumentError Exception: wrong number of arguments (given 0, expected 1)

nil