rails: PG::ProtocolViolation: ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1

This issue is related to: #12753 #18469 #17983 #17770

Test case: https://gist.github.com/cxz/64efd78be1167a66f127

Activerecord: 4.2.1 Bug happens only with postgresql (works fine with sqlite3).

In summary:

from(arel_table.create_table_alias(where(id: 1).arel, table_name))

fails with:

PG::ProtocolViolation: ERROR:  bind message supplies 0 parameters, but prepared statement "" requires 1
: SELECT "posts".* FROM (SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1) "posts"

About this issue

  • Original URL
  • State: closed
  • Created 9 years ago
  • Comments: 23 (14 by maintainers)

Commits related to this issue

Most upvoted comments

I accidentally found a workaround while I was playing with a different query. I think my workaround will be related to the OP’s issue. I passed the Id inside an array instead of just passing a literal id.

try this:

from(arel_table.create_table_alias(where(id: [1]).arel, table_name))

I dunno why it works, but it does.

I’m using Rails 4.2.4 and checked this against Postgre 9.5.4.

Having this issue also with Rails 5.0 and Rails 5.1. Solved it with the brackets trick for now. Thank you @samuel012

Why was this issue closed? The bug seems alive and well for me on Rails 4.2.5.

@joshukraine I am affected by this issue in an application that uses Arel to generate EXISTS clauses in code similar to the below example:

def foobar
  scope = where(:column => 'value')
  scope = scope.where(arel_table[:id].eq(Bar.arel_table[:foo_id]))
  where(scope.exists)
end

The workaround for me was to invoke to_sql on the scope and pass the resulting SQL string into the WHERE clause:

def foobar
  scope = where(:column => 'value')
  scope = scope.where(arel_table[:id].eq(Bar.arel_table[:foo_id]))
  where("EXISTS #{scope.to_sql}")
end

@norman just want to thank you 4 years later: your Arel exists scenario was exactly what I was battling with today and your workaround is a good compromise that manages not to ruin the code’s readability 🙇

Is there other options besides the brackets trick?

I just upgraded from Rails 4.2.6 to 5.0.7 and basically half of my queries are now broken.

The brackets work but, what’s the ultimate solution for this?

I’m finding that this bug can occur even without explicit use of Arel, and just with nested AR relations. My current workaround in an old version of AR is to avoid using prepared statements.

Example, forked from @cxz 's original testcase: https://gist.github.com/Kache/524a475cffc2191c97a6580949892c03

(This bug appears fixed for me when I use 4.2.1, though, which doesn’t match the original report)