rails: to_sql in Rails 4.2 returns parameterized queries instead of full SQL statements

I don’t know if this belongs in Arel or in ActiveRecord so I’ll move it if necessary.

As of Rails 4.2, to_sql’s behavior has changed. Previously when I had an ActiveRelation and I called to_sql on it, I would get a SQL statement that I could copy and paste directly into Postgres’ console or use in a find_by_sql call.

Currently in Rails 4.2 I get back a SQL statement that is identical except that it parameterizes it by removing all of the variable bits.

Here’s a real life example (with the names stripped and aligned for your viewing pleasure):

# to_sql in Rails 4.1
SELECT "table_0".* FROM "table_0" WHERE "table_0"."id" = 'ed7b67eb-2b16-48a7-a23c-7656cdf7be5e' UNION SELECT "some_column" FROM "table_1" INNER JOIN "table_3" ON "table_3"."id" = "table_1"."table_3_id" INNER JOIN "table_4" ON "table_4"."table_3_id" = "table_3"."id" WHERE "table_3"."name" = 'Model' AND "table_1"."status" = 'active' AND "table_1"."id" = 'ed7b67eb-2b16-48a7-a23c-7656cdf7be5e' )

# to_sql in Rails 4.2
SELECT "table_0".* FROM "table_0" WHERE "table_0"."id" = $1                                     UNION SELECT "some_column" FROM "table_1" INNER JOIN "table_3" ON "table_3"."id" = "table_1"."table_3_id" INNER JOIN "table_4" ON "table_4"."table_3_id" = "table_3"."id" WHERE "table_3"."name" = $2      AND "table_1"."status" = $3       AND "table_1"."id" = $4 )

Effectively here’s the code that’s running:

def self.get_my_big_ole_query(some_id)
    TableZeroClass.
    where(id: some_id).
    union(
      my_other_active_relation.
      where(id: some_id),
    ).
    to_sql
end

def self.my_other_active_relation
    TableOneClass.
    models.
    active.
    select(
      :some_column,
    )
end

As a minor version release shouldn’t break backward compatibility I see this as a fairly large problem.

About this issue

Original URL
State: closed
Created 9 years ago
Comments: 18 (9 by maintainers)

Commits related to this issue

Ensure that bind values are passed https://github.com/rails/rails/issues/18379 — committed to g5search/kaminari by davidw 8 years ago

Most upvoted comments

First and most importantly, what do you all need from me to help you all track this bug down? 🐛

And in response to your comments:

@rafaelfranca I’d thought I’d heard that Rails had started using SemVer. Evidently it’s using MarketingVer where the first digit means nothing other than “this is how many times we’ve released Rails at RailsConf” 😄

@rafaelfranca @matthewd to_sql is, in fact, documented to return an unparameterized query:

If it was not the case then it would be:

User.where(name: 'Oscar').to_sql
# => SELECT "users".* FROM "users"  WHERE "users"."name" = $1

If you mean it’s not explicitly documented to return only an unparameterized query and therefore it’s ok to make it do so, I think that’s a poor argument. I’m sure there are a lot of things in the documentation which are not explicit but which are expected by most developers.

I think most developers would be surprised that a method called to_sql doesn’t return a full and executable SQL statement.

jfelchner on Jan 7, 2015

As a minor version release shouldn’t break backward compatibility I see this as a fairly large problem.

This is not what our maintenance policy say: http://guides.rubyonrails.org/maintenance_policy.html

Minor Y

New features, may contain API changes (Serve as major versions of Semver). Breaking changes are paired with deprecation notices in the previous minor or major release.

About to_sql it still returns what is documented. It was never made to return queries without bind parameters. In fact it returns bind parameters since 3.2 for what I can tell, but not in all queries. Not it is returning in all queries.

If you don’t want this behavior you can:

disable prepared statement globally
disable prepared statement before calling to_sql with unprepared_statement.

def self.get_my_big_ole_query(some_id)
  TableZeroClass.unprepared_statement do
    TableZeroClass.
    where(id: some_id).
    union(
      my_other_active_relation.
      where(id: some_id),
    ).
    to_sql
  end
end

rafaelfranca on Jan 7, 2015

@sgrif I know it’s not ready to be a public API, I was mentioning that I think it should be. And that I’d be willing to do the work.

So you’re saying that you don’t think that union would be a valuable addition to the Relation API?

If so, that’s fine, I just want to make sure we’re on the same page.

jfelchner on Jan 10, 2015