ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: syntax error at or near "AS"

[homanchou]

Upgraded from pg_search 0.7 and rails 4.0.5 to pg_search 0.7.8 and rails 4.2.beta4

Now getting this error:

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: syntax error at or near "AS"

The old query from 0.7 looked like:

SELECT COUNT(*) FROM "merchants" WHERE (((setweight(to_tsvector('simple', coalesce("merchants"."email"::text, '')), 'A') || setweight(to_tsvector('simple', coalesce("merchants"."company_name"::text, '')), 'B') || setweight(to_tsvector('simple', coalesce("merchants"."first_name"::text, '')), 'C') || setweight(to_tsvector('simple', coalesce("merchants"."last_name"::text, '')), 'D')) @@ (to_tsquery('simple', ''' ' || 'test@sellbrite.com' || ' ''' || ':*'))) OR ((coalesce("merchants"."email"::text, '') || ' ' || coalesce("merchants"."company_name"::text, '') || ' ' || coalesce("merchants"."first_name"::text, '') || ' ' || coalesce("merchants"."last_name"::text, '')) % 'test@sellbrite.com')) AND "merchants"."state" IN ('subscribed', 'in_trial', 'tester', 'trial_ended', 'canceled', 'suspended') AND (sign_in_count > 0)

new query from 0.7.8 looks like

SELECT COUNT("merchants".*, ((ts_rank((setweight(to_tsvector('simple', coalesce("merchants"."email"::text, '')), 'A') || setweight(to_tsvector('simple', coalesce("merchants"."company_name"::text, '')), 'B') || setweight(to_tsvector('simple', coalesce("merchants"."first_name"::text, '')), 'C') || setweight(to_tsvector('simple', coalesce("merchants"."last_name"::text, '')), 'D')), (to_tsquery('simple', ''' ' || 'test@sellbrite.com' || ' ''')), 0))) AS pg_search_rank) FROM "merchants" WHERE (((setweight(to_tsvector('simple', coalesce("merchants"."email"::text, '')), 'A') || setweight(to_tsvector('simple', coalesce("merchants"."company_name"::text, '')), 'B') || setweight(to_tsvector('simple', coalesce("merchants"."first_name"::text, '')), 'C') || setweight(to_tsvector('simple', coalesce("merchants"."last_name"::text, '')), 'D')) @@ (to_tsquery('simple', ''' ' || 'test@sellbrite.com' || ' ''')))) AND "merchants"."state" IN ('subscribed', 'in_trial', 'tester', 'trial_ended', 'canceled', 'suspended') AND (sign_in_count > 0))

My model looks like:

class Merchant < ActiveRecord::Base

    include PgSearch

   #creates a search method on the product model
  pg_search_scope :search, against: [
     [:email, 'A'],
     [:company_name, 'B'],
     [:first_name, 'C'],
     [:last_name, 'D'],
  ], :using => {
                  :tsearch => {:prefix => true},
                  :trigram => {}
                }

psql --version
psql (PostgreSQL) 9.3.2

Is this a pg_search bug or an activerecord 4.2 bug?

[DylanGriffith]

I'm also seeing this issue with:

     Failure/Error: match = Message.search_content("hello").count
     ActiveRecord::StatementInvalid:
       PG::SyntaxError: ERROR:  syntax error at or near "AS"
       LINE 1: ...query('simple', ''' ' || 'hello' || ' ''')), 0))) AS pg_sear...
                                                                    ^
       : SELECT COUNT("messages".*, ((ts_rank((to_tsvector('simple', coalesce("messages"."content"::text, ''))), (to_tsquery('simple', ''' ' || 'hello' || ' ''')), 0))) AS pg_search_rank) FROM "messages"  WHERE (((to_tsvector('simple', coalesce("messages"."content"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'hello' || ' '''))))

Model:

class Message < ActiveRecord::Base
  include PgSearch
  belongs_to :channel
  pg_search_scope :search, :against => :content
end 

$ psql --version                                                                                                                                                                                                    
psql (PostgreSQL) 9.3.4
$ rails --version
Rails 4.1.7

[DylanGriffith]

Also for me the problem only occured when using count. Otherwise enumerating the results actually worked fine. It looks like you're using count too. In your 2 examples it looks like there is a difference between the way it is constructing a COUNT query.

[nertzy]

Looks like you're having the same problem as #127, #153, and #180.

Due to a change in Active Record (See rails/rails#10710) you need to pass :all to count.

MyModel.my_scope.count(:all)

Please let me know whether this works for you.