Error: bind message supplies 2 parameters, but prepared statement requires 1

[xtagon]

Hi,

Thanks for writing this gem, it is very helpful and has saved me some headaches. Great work!

There was an issue in one of my apps today where I got a PG::ProtocolViolation when trying to nest a recursive query in the where clause of another query. There is a workaround, but here is a description of the problem:

# Query for the user's organization with all of its parent organizations recursively
organizations = Organization.join_recursive do |query|
  query.start_with(id: current_user.organization.id).connect_by(parent_organization_id: :id)
end

# Query for all licenses that belong to these organization and belong to this app
licenses = License.where(organization_id: organizations, license_app_id: license_app.id)

This fails because the last where clause has a $1 in the prepared statement, but the nested select from the recursive join also has a $1. Full example:

PG::ProtocolViolation: ERROR: bind message supplies 2 parameters, but prepared statement "a5" requires 1 : SELECT "licenses".* FROM "licenses" WHERE "licenses"."organization_id" IN (SELECT "organizations"."id" FROM "organizations" INNER JOIN (WITH RECURSIVE "organizations__recursive" AS ( SELECT "organizations"."id", "organizations"."parent_organization_id" FROM "organizations" WHERE "organizations"."id" = $1 UNION ALL SELECT "organizations"."id", "organizations"."parent_organization_id" FROM "organizations" INNER JOIN "organizations__recursive" ON "organizations__recursive"."parent_organization_id" = "organizations"."id" ) SELECT "organizations__recursive".* FROM "organizations__recursive") AS "organizations__recursive" ON "organizations"."id" = "organizations__recursive"."id") AND "licenses"."license_app_id" = $1

Notice that $1 appears twice. The solution was to do a separate query for the list of IDs like so:

licenses = License.where(organization_id: organizations.pluck(:id)).where(license_app_id: license_app.id)

Do you know of a way this can be fixed?

Thanks, Justin

[take-five]

Hey Justin,

Thanks for the report, I'll take a look at it ASAP. Could you please provide me with your Rails version before I'll dig into it?

[xtagon]

Rails 4.2.3, activerecord-hierarchical_query 0.0.7 On Aug 22, 2015 3:26 AM, "Alexey Mihaylov" notifications@github.com wrote:

Hey Justin,

Thanks for the report, I'll take a look at it ASAP. Could you please provide me with your Rails version before I'll dig into it?

— Reply to this email directly or view it on GitHub https://github.com/take-five/activerecord-hierarchical_query/issues/3#issuecomment-133676044 .

[xtagon]

A similar error occurs if I add where() or exists?(conditions) clause to a relation that has join_recursive called on it. I may be misunderstanding the usage of your library though, am I supposed to do the where or exists? check within the join block?

[take-five]

No, it's definitely a bug. Sorry, I don't have time to dig into it, right now. I will try to investigate it next week.

[take-five]

Took almost a year to, but problem is finally solved :) Fix is released in version 0.1.2

[xtagon]

Thank you so much! :smile:

[take-five]

I hope you're still on rails 4.2, because 5.0 isn't supported yet :)

[xtagon]

I am for now, but that's good to know. I'll keep an eye on #13