Bad SQL creation from a filter #4078

jasonm23 · 2015-08-17T13:54:11Z

When doing a filter on email (contains) I'm getting the following error (see below).

(please note: already updated the gem to git head, problem still occurs.)

Any help would be much appreciated.

excerpt from stacktrace:

SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
') LIMIT 30 OFFSET 0) subquery_for_count
PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...RE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
                                                                ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
') LIMIT 30 OFFSET 0) subquery_for_count
  Rendered /Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/app/views/active_admin/resource/index.html.arb (123.4ms)
Completed 500 Internal Server Error in 172ms

users.email from schema.db...

t.string   "email",                   limit: 255, default: "",    null: false

Full stack trace

Started GET "/admin/users?utf8=%E2%9C%93&q%5Bemail_contains%5D=jas&commit=Filter&order=id_desc" for 127.0.0.1 at 2015-08-17 21:27:59 +0800
  ActiveRecord::SchemaMigration Load (0.3ms)  SELECT "schema_migrations".* FROM "schema_migrations"
Processing by Admin::UsersController#index as HTML
  Parameters: {"utf8"=>"✓", "q"=>{"email_contains"=>"jas"}, "commit"=>"Filter", "order"=>"id_desc"}
  User Load (0.5ms)  SELECT  "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND "users"."id" = 333  ORDER BY "users"."id" ASC LIMIT 1  [["id", 333]]
  Role Load (0.2ms)  SELECT  "roles".* FROM "roles" WHERE "roles"."id" = 1 LIMIT 1  [["id", 1]]
   (1.5ms)  SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
') LIMIT 30 OFFSET 0) subquery_for_count
PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...RE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
                                                                ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
') LIMIT 30 OFFSET 0) subquery_for_count
  Rendered /Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/app/views/active_admin/resource/index.html.arb (123.4ms)
Completed 500 Internal Server Error in 172ms
** [Airbrake] Notice was not sent due to configuration:
  Environment Monitored? false
  API key set? false
D, [2015-08-17T21:27:59.890947 #82349] DEBUG -- : [Rollbar] Reporting exception: PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...RE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
                                                                ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
') LIMIT 30 OFFSET 0) subquery_for_count
D, [2015-08-17T21:27:59.891017 #82349] DEBUG -- : [Rollbar] Exception not reported because Rollbar is disabled

ActiveRecord::StatementInvalid - PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...RE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
                                                                ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
') LIMIT 30 OFFSET 0) subquery_for_count:
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract_adapter.rb:455:in `translate_exception_class'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract_adapter.rb:468:in `rescue in log'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract_adapter.rb:466:in `log'
  activerecord (4.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `exec_no_cache'
  activerecord (4.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:584:in `execute_and_clear'
  activerecord (4.2.0) lib/active_record/connection_adapters/postgresql/database_statements.rb:160:in `exec_query'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:336:in `select'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/query_cache.rb:68:in `block in select_all'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/query_cache.rb:83:in `cache_sql'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/query_cache.rb:68:in `select_all'
  activerecord (4.2.0) lib/active_record/relation/calculations.rb:264:in `execute_simple_calculation'
  activerecord (4.2.0) lib/active_record/relation/calculations.rb:221:in `perform_calculation'
  activerecord (4.2.0) lib/active_record/relation/calculations.rb:127:in `calculate'
  activerecord (4.2.0) lib/active_record/relation/calculations.rb:42:in `count'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/helpers/collection.rb:9:in `collection_size'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/helpers/collection.rb:13:in `collection_is_empty?'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/index.rb:45:in `items_in_collection?'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/index.rb:49:in `build_collection'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/index.rb:28:in `block in main_content'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:14:in `batch_action_form'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/index.rb:36:in `wrap_with_batch_action_form'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/index.rb:26:in `main_content'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/base.rb:87:in `block (2 levels) in build_main_content_wrapper'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:14:in `div'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/base.rb:86:in `block in build_main_content_wrapper'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:14:in `div'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/base.rb:85:in `build_main_content_wrapper'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/base.rb:71:in `block in build_page_content'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:14:in `div'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/base.rb:70:in `build_page_content'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/base.rb:48:in `block (2 levels) in build_page'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:14:in `div'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/base.rb:44:in `block in build_page'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/base.rb:43:in `build_page'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/views/pages/base.rb:10:in `build'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:30:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/app/views/active_admin/resource/index.html.arb:2:in `block in ___sers_neo__rvm_gems_ruby_______airdrilling_bundler_gems_active_admin__aef______d__app_views_active_admin_resource_index_html_arb___1845682948105804477_70354850399060'
  arbre (1.0.2) lib/arbre/context.rb:45:in `initialize'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/app/views/active_admin/resource/index.html.arb:1:in `___sers_neo__rvm_gems_ruby_______airdrilling_bundler_gems_active_admin__aef______d__app_views_active_admin_resource_index_html_arb___1845682948105804477_70354850399060'
  actionview (4.2.0) lib/action_view/template.rb:145:in `block in render'
  activesupport (4.2.0) lib/active_support/notifications.rb:164:in `block in instrument'
  activesupport (4.2.0) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  activesupport (4.2.0) lib/active_support/notifications.rb:164:in `instrument'
  actionview (4.2.0) lib/action_view/template.rb:333:in `instrument'
  actionview (4.2.0) lib/action_view/template.rb:143:in `render'
  haml_coffee_assets (1.16.0) lib/haml_coffee_assets/rails/engine.rb:73:in `render'
  actionview (4.2.0) lib/action_view/renderer/template_renderer.rb:54:in `block (2 levels) in render_template'
  actionview (4.2.0) lib/action_view/renderer/abstract_renderer.rb:39:in `block in instrument'
  activesupport (4.2.0) lib/active_support/notifications.rb:164:in `block in instrument'
  activesupport (4.2.0) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  activesupport (4.2.0) lib/active_support/notifications.rb:164:in `instrument'
  actionview (4.2.0) lib/action_view/renderer/abstract_renderer.rb:39:in `instrument'
  actionview (4.2.0) lib/action_view/renderer/template_renderer.rb:53:in `block in render_template'
  actionview (4.2.0) lib/action_view/renderer/template_renderer.rb:61:in `render_with_layout'
  actionview (4.2.0) lib/action_view/renderer/template_renderer.rb:52:in `render_template'
  actionview (4.2.0) lib/action_view/renderer/template_renderer.rb:14:in `render'
  actionview (4.2.0) lib/action_view/renderer/renderer.rb:42:in `render_template'
  actionview (4.2.0) lib/action_view/renderer/renderer.rb:23:in `render'
  actionview (4.2.0) lib/action_view/rendering.rb:100:in `_render_template'
  actionpack (4.2.0) lib/action_controller/metal/streaming.rb:217:in `_render_template'
  actionview (4.2.0) lib/action_view/rendering.rb:83:in `render_to_body'
  actionpack (4.2.0) lib/action_controller/metal/rendering.rb:32:in `render_to_body'
  actionpack (4.2.0) lib/action_controller/metal/renderers.rb:37:in `render_to_body'
  actionpack (4.2.0) lib/abstract_controller/rendering.rb:25:in `render'
  actionpack (4.2.0) lib/action_controller/metal/rendering.rb:16:in `render'
  actionpack (4.2.0) lib/action_controller/metal/instrumentation.rb:41:in `block (2 levels) in render'
  activesupport (4.2.0) lib/active_support/core_ext/benchmark.rb:12:in `block in ms'
   () Users/neo/.rvm/rubies/ruby-2.2.2/lib/ruby/2.2.0/benchmark.rb:303:in `realtime'
  activesupport (4.2.0) lib/active_support/core_ext/benchmark.rb:12:in `ms'
  actionpack (4.2.0) lib/action_controller/metal/instrumentation.rb:41:in `block in render'
  actionpack (4.2.0) lib/action_controller/metal/instrumentation.rb:84:in `cleanup_view_runtime'
  activerecord (4.2.0) lib/active_record/railties/controller_runtime.rb:25:in `cleanup_view_runtime'
  actionpack (4.2.0) lib/action_controller/metal/instrumentation.rb:40:in `render'
  remotipart (1.2.1) lib/remotipart/render_overrides.rb:14:in `render_with_remotipart'
  actionpack (4.2.0) lib/action_controller/metal/implicit_render.rb:10:in `default_render'
  responders (2.0.2) lib/action_controller/responder.rb:236:in `default_render'
  responders (2.0.2) lib/action_controller/responder.rb:170:in `to_html'
  responders (2.0.2) lib/responders/flash_responder.rb:107:in `to_html'
  responders (2.0.2) lib/action_controller/responder.rb:163:in `respond'
  responders (2.0.2) lib/action_controller/responder.rb:156:in `call'
  responders (2.0.2) lib/action_controller/respond_with.rb:203:in `respond_with'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/inherited_resources-54eb09943793/lib/inherited_resources/actions.rb:7:in `index'
   () Users/neo/.rvm/gems/ruby-2.2.2@airdrilling/bundler/gems/active_admin-7aef260921d4/lib/active_admin/resource_controller/streaming.rb:12:in `index'
  actionpack (4.2.0) lib/action_controller/metal/implicit_render.rb:4:in `send_action'
  actionpack (4.2.0) lib/abstract_controller/base.rb:198:in `process_action'
  actionpack (4.2.0) lib/action_controller/metal/rendering.rb:10:in `process_action'
  actionpack (4.2.0) lib/abstract_controller/callbacks.rb:20:in `block in process_action'
  activesupport (4.2.0) lib/active_support/callbacks.rb:117:in `call'
  activesupport (4.2.0) lib/active_support/callbacks.rb:151:in `block in halting_and_conditional'
  activesupport (4.2.0) lib/active_support/callbacks.rb:169:in `block in halting'
  activesupport (4.2.0) lib/active_support/callbacks.rb:169:in `block in halting'
  activesupport (4.2.0) lib/active_support/callbacks.rb:169:in `block in halting'
  activesupport (4.2.0) lib/active_support/callbacks.rb:169:in `block in halting'
  activesupport (4.2.0) lib/active_support/callbacks.rb:234:in `block in halting'
  activesupport (4.2.0) lib/active_support/callbacks.rb:169:in `block in halting'
  activesupport (4.2.0) lib/active_support/callbacks.rb:92:in `_run_callbacks'
  activesupport (4.2.0) lib/active_support/callbacks.rb:734:in `_run_process_action_callbacks'
  activesupport (4.2.0) lib/active_support/callbacks.rb:81:in `run_callbacks'
  actionpack (4.2.0) lib/abstract_controller/callbacks.rb:19:in `process_action'
  actionpack (4.2.0) lib/action_controller/metal/rescue.rb:29:in `process_action'
  actionpack (4.2.0) lib/action_controller/metal/instrumentation.rb:31:in `block in process_action'
  activesupport (4.2.0) lib/active_support/notifications.rb:164:in `block in instrument'
  activesupport (4.2.0) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  activesupport (4.2.0) lib/active_support/notifications.rb:164:in `instrument'
  actionpack (4.2.0) lib/action_controller/metal/instrumentation.rb:30:in `process_action'
  actionpack (4.2.0) lib/action_controller/metal/params_wrapper.rb:250:in `process_action'
  activerecord (4.2.0) lib/active_record/railties/controller_runtime.rb:18:in `process_action'
  actionpack (4.2.0) lib/abstract_controller/base.rb:137:in `process'
  actionview (4.2.0) lib/action_view/rendering.rb:30:in `process'
  actionpack (4.2.0) lib/action_controller/metal.rb:195:in `dispatch'
  actionpack (4.2.0) lib/action_controller/metal/rack_delegation.rb:13:in `dispatch'
  actionpack (4.2.0) lib/action_controller/metal.rb:236:in `block in action'
  actionpack (4.2.0) lib/action_dispatch/routing/route_set.rb:73:in `dispatch'
  actionpack (4.2.0) lib/action_dispatch/routing/route_set.rb:42:in `serve'
  actionpack (4.2.0) lib/action_dispatch/journey/router.rb:43:in `block in serve'
  actionpack (4.2.0) lib/action_dispatch/journey/router.rb:30:in `serve'
  actionpack (4.2.0) lib/action_dispatch/routing/route_set.rb:802:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  omniauth (1.2.2) lib/omniauth/strategy.rb:186:in `call!'
  omniauth (1.2.2) lib/omniauth/strategy.rb:164:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/rack/agent_hooks.rb:30:in `traced_call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:55:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/rack/browser_monitoring.rb:23:in `traced_call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:55:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/rack/developer_mode.rb:56:in `traced_call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:55:in `call'
  warden (1.2.3) lib/warden/manager.rb:35:in `block in call'
  warden (1.2.3) lib/warden/manager.rb:34:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rack (1.6.4) lib/rack/etag.rb:24:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rack (1.6.4) lib/rack/conditionalget.rb:25:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rack (1.6.4) lib/rack/head.rb:13:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  remotipart (1.2.1) lib/remotipart/middleware.rb:27:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/params_parser.rb:27:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/flash.rb:260:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rack (1.6.4) lib/rack/session/abstract/id.rb:225:in `context'
  rack (1.6.4) lib/rack/session/abstract/id.rb:220:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/cookies.rb:560:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  activerecord (4.2.0) lib/active_record/query_cache.rb:36:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:647:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  activerecord (4.2.0) lib/active_record/migration.rb:378:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
  activesupport (4.2.0) lib/active_support/callbacks.rb:88:in `_run_callbacks'
  activesupport (4.2.0) lib/active_support/callbacks.rb:734:in `_run_call_callbacks'
  activesupport (4.2.0) lib/active_support/callbacks.rb:81:in `run_callbacks'
  actionpack (4.2.0) lib/action_dispatch/middleware/callbacks.rb:27:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/reloader.rb:73:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/remote_ip.rb:78:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  airbrake (4.1.0) lib/airbrake/rails/middleware.rb:13:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rollbar (1.3.1) lib/rollbar/middleware/rails/rollbar.rb:24:in `block in call'
  rollbar (1.3.1) lib/rollbar.rb:747:in `scoped'
  rollbar (1.3.1) lib/rollbar/middleware/rails/rollbar.rb:22:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  better_errors (2.1.1) lib/better_errors/middleware.rb:84:in `protected_app_call'
  better_errors (2.1.1) lib/better_errors/middleware.rb:79:in `better_errors_call'
  better_errors (2.1.1) lib/better_errors/middleware.rb:57:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
  rollbar (1.3.1) lib/rollbar/middleware/rails/show_exceptions.rb:22:in `call_with_rollbar'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  railties (4.2.0) lib/rails/rack/logger.rb:38:in `call_app'
  railties (4.2.0) lib/rails/rack/logger.rb:20:in `block in call'
  activesupport (4.2.0) lib/active_support/tagged_logging.rb:68:in `block in tagged'
  activesupport (4.2.0) lib/active_support/tagged_logging.rb:26:in `tagged'
  activesupport (4.2.0) lib/active_support/tagged_logging.rb:68:in `tagged'
  railties (4.2.0) lib/rails/rack/logger.rb:20:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/request_id.rb:21:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rack (1.6.4) lib/rack/methodoverride.rb:22:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rack (1.6.4) lib/rack/runtime.rb:18:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rack (1.6.4) lib/rack/lock.rb:17:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  actionpack (4.2.0) lib/action_dispatch/middleware/static.rb:113:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rack (1.6.4) lib/rack/sendfile.rb:113:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  airbrake (4.1.0) lib/airbrake/user_informer.rb:16:in `_call'
  airbrake (4.1.0) lib/airbrake/user_informer.rb:12:in `call'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  railties (4.2.0) lib/rails/engine.rb:518:in `call'
  railties (4.2.0) lib/rails/application.rb:164:in `call'
  railties (4.2.0) lib/rails/railtie.rb:194:in `method_missing'
  newrelic_rpm (3.9.9.275) lib/new_relic/agent/instrumentation/middleware_tracing.rb:57:in `call'
  rack (1.6.4) lib/rack/deflater.rb:35:in `call'
  rack (1.6.4) lib/rack/content_length.rb:15:in `call'
  thin (1.6.3) lib/thin/connection.rb:86:in `block in pre_process'
  thin (1.6.3) lib/thin/connection.rb:84:in `pre_process'
  thin (1.6.3) lib/thin/connection.rb:53:in `process'
  thin (1.6.3) lib/thin/connection.rb:39:in `receive_data'
  eventmachine (1.0.4) lib/eventmachine.rb:187:in `run'
  thin (1.6.3) lib/thin/backends/base.rb:73:in `start'
  thin (1.6.3) lib/thin/server.rb:162:in `start'
  rack (1.6.4) lib/rack/handler/thin.rb:19:in `run'
  rack (1.6.4) lib/rack/server.rb:286:in `start'
  railties (4.2.0) lib/rails/commands/server.rb:80:in `start'
  railties (4.2.0) lib/rails/commands/commands_tasks.rb:80:in `block in server'
  railties (4.2.0) lib/rails/commands/commands_tasks.rb:75:in `server'
  railties (4.2.0) lib/rails/commands/commands_tasks.rb:39:in `run_command!'
  railties (4.2.0) lib/rails/commands.rb:17:in `<top (required)>'
   () Users/neo/workspace/OpsManager/bin/rails:25:in `<top (required)>'
  spring (1.2.0) lib/spring/client/rails.rb:27:in `call'
  spring (1.2.0) lib/spring/client/command.rb:7:in `call'
  spring (1.2.0) lib/spring/client.rb:26:in `run'
  spring (1.2.0) bin/spring:48:in `<top (required)>'
  spring (1.2.0) lib/spring/binstub.rb:11:in `<top (required)>'
   () Users/neo/workspace/OpsManager/bin/spring:16:in `<top (required)>'
   () rails:3:in `<main>'



Started POST "/__better_errors/f4b027739c287fdc/variables" for 127.0.0.1 at 2015-08-17 21:27:59 +0800
  User Load (1.0ms)  SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
')  ORDER BY "users"."id" desc
PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...RE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
                                                                ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
')  ORDER BY "users"."id" desc

The text was updated successfully, but these errors were encountered:

timoschilling · 2015-08-17T14:02:57Z

Please check this on the Rails console:

User.ransack(email_cont: "jas").result

Which database query will be performed and is the result right?

jasonm23 · 2015-08-17T14:14:08Z

[1] pry(main)> User.ransack(email_cont: "jas").result

User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" ILIKE '%jas%')

=> [#<User:0x007f8f0aed14f8
id: 333,

timoschilling · 2015-08-17T14:23:32Z

and this:

User.ransack(email_contains: "jas").result

jasonm23 · 2015-08-17T14:40:23Z

Ok

[1] pry(main)> User.ransack(email_contains: "jas").result

  User Load (2.4ms)  SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
')

PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...RE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
                                                                ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- jas
')

=> #<User::ActiveRecord_Relation:0x3ffdd142a554>

jasonm23 · 2015-08-17T14:41:59Z

Does this mean it's actually a Ransack issue?

timoschilling · 2015-08-17T17:27:11Z

Whats happen if you request this URL on your system?

/admin/users?utf8=%E2%9C%93&q%5Bemail_cont%5D=jas&commit=Filter&order=id_desc

timoschilling · 2015-08-17T17:27:37Z

Do you use any custom ransacker?

jasonm23 · 2015-08-18T01:34:21Z

Whats happen if you request this URL on your system?

Same, this is what first surfaced the error.

Do you use any custom ransacker?

I have the following in our User model.

ransacker :user_groups, ({formatter: lambda do |has|
    search_criterion = has == "true" ? :user_groups_id_present : :user_groups_id_blank
    User.search(search_criterion => true).result.pluck(:id)
  end}) do |parent|
    parent.table[:id]
  end

Nothing relating to users.email though.

timoschilling · 2015-08-18T01:36:56Z

Did you have seen the change that I have made in the url?

your url: /admin/users?utf8=%E2%9C%93&q%5Bemail_contains%5D=jas&commit=Filter&order=id_desc
my url:   /admin/users?utf8=%E2%9C%93&q%5Bemail_cont%5D=jas&commit=Filter&order=id_desc

jasonm23 · 2015-08-18T01:45:36Z

Sorry!

/admin/users?utf8=%E2%9C%93&q%5Bemail_cont%5D=jas&commit=Filter&order=id_desc

Works. Of course the URL we are using is generated by ActiveAdmin.

timoschilling · 2015-08-18T01:59:43Z

You should can fix your problem with:

filter :email, filters: [:cont, :eq, :start, :end]

But you need to add some traslations

jasonm23 · 2015-08-19T01:59:49Z

@timoschilling shouldn't https://github.com/activeadmin/activeadmin/blob/master/lib/ransack_ext.rb be mapping contains to cont anyway?

Any clues why this isn't happening?

timoschilling · 2015-08-19T07:32:47Z

Yes, but it seams to not work in your case.

It's happen, otherwise you will get a non method error, but it don't work.

jasonm23 · 2015-09-21T03:05:13Z

This was caused by a bad ransack config that wasn't found before.

jasonm23 mentioned this issue Aug 17, 2015

Bad SQL creation from a Ransack.search object. activerecord-hackery/ransack#573

Closed

timoschilling mentioned this issue Aug 17, 2015

Remove metasearch backports #3511

Closed

timoschilling mentioned this issue Aug 24, 2015

FIlter using string and 'contains' method generates invalid Postgres SL #4094

Closed

jasonm23 closed this as completed Sep 21, 2015

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Bad SQL creation from a filter #4078

Bad SQL creation from a filter #4078

jasonm23 commented Aug 17, 2015

timoschilling commented Aug 17, 2015

jasonm23 commented Aug 17, 2015

timoschilling commented Aug 17, 2015

jasonm23 commented Aug 17, 2015

jasonm23 commented Aug 17, 2015

timoschilling commented Aug 17, 2015

timoschilling commented Aug 17, 2015

jasonm23 commented Aug 18, 2015

timoschilling commented Aug 18, 2015

jasonm23 commented Aug 18, 2015

timoschilling commented Aug 18, 2015

jasonm23 commented Aug 19, 2015

timoschilling commented Aug 19, 2015

jasonm23 commented Sep 21, 2015

Bad SQL creation from a filter #4078

Bad SQL creation from a filter #4078

Comments

jasonm23 commented Aug 17, 2015

Full stack trace

timoschilling commented Aug 17, 2015

jasonm23 commented Aug 17, 2015

timoschilling commented Aug 17, 2015

jasonm23 commented Aug 17, 2015

jasonm23 commented Aug 17, 2015

timoschilling commented Aug 17, 2015

timoschilling commented Aug 17, 2015

jasonm23 commented Aug 18, 2015

timoschilling commented Aug 18, 2015

jasonm23 commented Aug 18, 2015

timoschilling commented Aug 18, 2015

jasonm23 commented Aug 19, 2015

timoschilling commented Aug 19, 2015

jasonm23 commented Sep 21, 2015