pg_search: `SELECT DISTINCT` triggers ActiveRecord::StatementInvalid in v1.0.0

I just upgraded to 1.0.0 from 0.7.9 and I’m now getting a bunch of failures for tests that perform a pg_search query. I looked at the Changelog and the Readme, but I don’t see any instructions for upgrading from 0.7.9 to 1.0.0 or any mention of breaking changes, so I assumed everything should still work.

Here is the specific error I am getting:

ActiveRecord::StatementInvalid:
       PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
       LINE 1: ...ocations"."id" = pg_search.pg_search_id  ORDER BY pg_search....
                                                                    ^
       : SELECT  DISTINCT "locations".* FROM "locations" INNER JOIN (SELECT "locations"."id" AS pg_search_id, (ts_rank(("locations"."tsv_body"), (to_tsquery('english', ''' ' || 'changeme' || ' ''' || ':*')), 0)) AS rank FROM "locations" WHERE ((("locations"."tsv_body") @@ (to_tsquery('english', ''' ' || 'changeme' || ' ''' || ':*'))))) pg_search ON "locations"."id" = pg_search.pg_search_id  ORDER BY pg_search.rank DESC, "locations"."id" ASC LIMIT 30 OFFSET 0

And here is the query: https://github.com/codeforamerica/ohana-api/blob/master/app/models/concerns/search.rb#L26-35

Thoughts?

About this issue

Original URL
State: open
Created 9 years ago
Comments: 20

Commits related to this issue

Restrict pg_search to 0.x due to 1.0 breaking bug See https://github.com/Casecommons/pg_search/issues/238 — committed to codeforamerica/ohana-api by monfresh 9 years ago
Include scope from pg_search in searches for cookbooks. This solves an error following the upgrade of pg_search. Controller tests complained about SELECT not including a pg_search column from the ord... — committed to chef/supermarket by robbkidd 8 years ago

Most upvoted comments

@mateuszpalyz, just use the with_pg_search_rank scope, like this: Post.search_by_title('some title').with_pg_search_rank.uniq. See scope source here (v.1.0.5) if interested, it just adds proper selects as suggested above. Adding it automatically is definately a good idea!

+25

ikido on Dec 30, 2015

@ikido’s solution works perfectly, it should be documented or something.

+16

mzahidriaz-tr on Apr 12, 2017

Another workaround that works well with distinct, count and pg_search is to reorder(''). It looks like the ‘order by rank’ part is problematic, so if ordering by pg_search’s rank isn’t required, then this workaround would be a great fit.

Example, Post.search_by_title('some title').reorder('').distinct.count

+11

HusseinElMotayam on Oct 18, 2019

For me, the solution was to add the with_pg_search_rank scope. I wonder if it’s possible to detect this automatically when distinct is present.

+11

travisdahlke on Sep 21, 2015

+1 .uniq kills the query

+10

jchatel on Nov 22, 2016

thx @HusseinElMotayam Post.search_by_title('some title').reorder('').distinct works fine and doesn’t kill query like .uniq

IgrekYY on Jun 23, 2020

With 1.0.4 I had to add pg_search_series.rank to SELECTand switching to 1.0.5 needs pg_search_9cda38ea3a2bd78cbaecd4.rank, what is this stamp 9cda38ea3a2bd78cbaecd4, does it change between queries?

mateuszpalyz on Dec 6, 2015

@HusseinElMotayam solution fixed it for me. many thanks

GMolini on Jan 11, 2023