Squeel breaks the use of subqueries with associations #272

Closed
dnagir opened this Issue Oct 1, 2013 · 13 comments

Projects

None yet

8 participants

@dnagir

The issue has originally been open in the Rails repo here rails/rails#12409
But it is definitely a squeel issue because it works as expected without it.

The minute we introduce squeel into the example - it fails.

Full repro:

gem 'activerecord', '4.0.0'
gem "squeel", "~> 1.1.1"

require 'active_record'
require 'squeel'
require 'minitest/autorun'
require 'logger'

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(
  :adapter  => 'postgresql',
  :database => 'test'
)

ActiveRecord::Schema.define do
  create_table :blogs, :force => true do |t|
  end

  create_table :posts, :force => true do |t|
    t.integer :blog_id
  end
end

class Blog < ActiveRecord::Base
  has_many :posts, :dependent => :destroy
end

class Post < ActiveRecord::Base
  belongs_to :blog
  default_scope -> { order("posts.id") }
end

class TestSubquery < MiniTest::Unit::TestCase
  def test_subquery
    blog = Blog.create!
    post = blog.posts.create!
    posts = Post.where(id: blog.posts.where('posts.id > 0')).to_a

    assert_kind_of Array, posts
    assert_equal 1, posts.size
    assert_equal post, posts.first
  end
end

When executed produces following output:

-- create_table(:blogs, {:force=>true})
D, [2013-10-01T10:26:58.829908 #19953] DEBUG -- :    (1.4ms)  DROP TABLE "blogs"
D, [2013-10-01T10:26:58.852932 #19953] DEBUG -- :    (22.8ms)  CREATE TABLE "blogs" ("id" serial primary key) 
   -> 0.0374s
-- create_table(:posts, {:force=>true})
D, [2013-10-01T10:26:58.855148 #19953] DEBUG -- :    (1.0ms)  DROP TABLE "posts"
D, [2013-10-01T10:26:58.856683 #19953] DEBUG -- :    (1.4ms)  CREATE TABLE "posts" ("id" serial primary key, "blog_id" integer) 
   -> 0.0036s
MiniTest::Unit::TestCase is now Minitest::Test. From tmp/test.rb:34:in `<main>'
Run options: --seed 36386

# Running:

D, [2013-10-01T10:26:58.887637 #19953] DEBUG -- :    (0.1ms)  BEGIN
D, [2013-10-01T10:26:58.893765 #19953] DEBUG -- :   SQL (0.5ms)  INSERT INTO "blogs" DEFAULT VALUES RETURNING "id"
D, [2013-10-01T10:26:58.894207 #19953] DEBUG -- :    (0.1ms)  COMMIT
D, [2013-10-01T10:26:58.899774 #19953] DEBUG -- :    (0.3ms)  BEGIN
D, [2013-10-01T10:26:58.909623 #19953] DEBUG -- :   SQL (2.6ms)  INSERT INTO "posts" ("blog_id") VALUES ($1) RETURNING "id"  [["blog_id", 1]]
D, [2013-10-01T10:26:58.910217 #19953] DEBUG -- :    (0.2ms)  COMMIT
D, [2013-10-01T10:26:58.915782 #19953] DEBUG -- :   Post Load (0.6ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (SELECT "posts"."id" FROM "posts" WHERE "posts"."blog_id" = $1 AND (posts.id > 0) ORDER BY posts.id) ORDER BY posts.id
E, [2013-10-01T10:26:58.915893 #19953] ERROR -- : PG::UndefinedParameter: ERROR:  there is no parameter $1
LINE 1: ...sts"."id" FROM "posts"  WHERE "posts"."blog_id" = $1 AND (po...
                                                             ^
: SELECT "posts".* FROM "posts"  WHERE "posts"."id" IN (SELECT "posts"."id" FROM "posts"  WHERE "posts"."blog_id" = $1 AND (posts.id > 0)  ORDER BY posts.id)  ORDER BY posts.id
E

Finished in 0.033144s, 30.1714 runs/s, 0.0000 assertions/s.

  1) Error:
TestSubquery#test_subquery:
ActiveRecord::StatementInvalid: PG::UndefinedParameter: ERROR:  there is no parameter $1
LINE 1: ...sts"."id" FROM "posts"  WHERE "posts"."blog_id" = $1 AND (po...
                                                             ^
: SELECT "posts".* FROM "posts"  WHERE "posts"."id" IN (SELECT "posts"."id" FROM "posts"  WHERE "posts"."blog_id" = $1 AND (posts.id > 0)  ORDER BY posts.id)  ORDER BY posts.id
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_no_cache'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:138:in `block in exec_query'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:425:in `block in log'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activesupport-4.0.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:420:in `log'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:885:in `select'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/querying.rb:36:in `find_by_sql'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/relation.rb:585:in `exec_queries'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/relation.rb:471:in `load'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/relation.rb:220:in `to_a'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/relation.rb:598:in `exec_queries'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/relation.rb:471:in `load'
    /Users/dnagir/.rvm/gems/ruby-2.0.0-p195/gems/activerecord-4.0.0/lib/active_record/relation.rb:220:in `to_a'
    tmp/test.rb:38:in `test_subquery'

1 runs, 0 assertions, 0 failures, 1 errors, 0 skips

When we uncomment require 'squeel', the output is correct and there are no errors:

-- create_table(:blogs, {:force=>true})
D, [2013-10-01T10:28:22.323371 #19993] DEBUG -- :    (1.5ms)  DROP TABLE "blogs"
D, [2013-10-01T10:28:22.325735 #19993] DEBUG -- :    (2.2ms)  CREATE TABLE "blogs" ("id" serial primary key) 
   -> 0.0166s
-- create_table(:posts, {:force=>true})
D, [2013-10-01T10:28:22.327342 #19993] DEBUG -- :    (0.7ms)  DROP TABLE "posts"
D, [2013-10-01T10:28:22.328670 #19993] DEBUG -- :    (1.2ms)  CREATE TABLE "posts" ("id" serial primary key, "blog_id" integer) 
   -> 0.0029s
MiniTest::Unit::TestCase is now Minitest::Test. From tmp/test.rb:34:in `<main>'
Run options: --seed 30078

# Running:

D, [2013-10-01T10:28:22.424789 #19993] DEBUG -- :    (0.1ms)  BEGIN
D, [2013-10-01T10:28:22.429695 #19993] DEBUG -- :   SQL (0.4ms)  INSERT INTO "blogs" DEFAULT VALUES RETURNING "id"
D, [2013-10-01T10:28:22.430178 #19993] DEBUG -- :    (0.2ms)  COMMIT
D, [2013-10-01T10:28:22.437837 #19993] DEBUG -- :    (0.1ms)  BEGIN
D, [2013-10-01T10:28:22.447785 #19993] DEBUG -- :   SQL (2.6ms)  INSERT INTO "posts" ("blog_id") VALUES ($1) RETURNING "id"  [["blog_id", 1]]
D, [2013-10-01T10:28:22.448278 #19993] DEBUG -- :    (0.2ms)  COMMIT
D, [2013-10-01T10:28:22.450622 #19993] DEBUG -- :   Post Load (0.7ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (SELECT "posts"."id" FROM "posts" WHERE "posts"."blog_id" = $1 AND (posts.id > 0) ORDER BY posts.id) ORDER BY posts.id  [["blog_id", 1]]
.

Finished in 0.031126s, 32.1275 runs/s, 96.3824 assertions/s.

1 runs, 3 assertions, 0 failures, 0 errors, 0 skips
@ernie
ActiveRecord Hackery member

Confirmed. I have a failing test case written and working on getting binds passed through when subquerying against an association proxy.

@ernie ernie added a commit that referenced this issue Oct 2, 2013
@ernie ernie Find AR::Relations in build_where, append binds
Possible fix for #272.
eaa0033
@ernie
ActiveRecord Hackery member

@dnagir Could you give master a shot, and let me know if this resolves your issue?

@dnagir

@ernie unfortunately I had to remove squeel from the project due to this issue (and in anticipation of others upgrading to Rails 4). But if my repro above works then pretty sure you fixed it.
Thanks a lot for that 👍

@ronalchn

The master branch fixes the issue, please release a new version.

@ronalchn

Actually, it appears to cause a different issue. It appears that when one parameter $1 is used multiple times in the statement, squeel is supplying 2 copies of it.

The bad query:

SELECT "test_cases".* FROM "test_cases"  WHERE "test_cases"."problem_id" = $1 AND "test_cases"."id" IN (SELECT "test_case_relations"."test_case_id" FROM "test_sets" INNER JOIN "test_case_relations" ON "test_case_relations"."test_set_id" = "test_sets"."id" WHERE "test_sets"."problem_id" = $1 AND "test_sets"."prerequisite" = 't'  ORDER BY "test_sets".problem_order ASC)  ORDER BY "test_cases".problem_order ASC

is built using:

class Problem < ActiveRecord::Base
  has_many :prerequisite_sets, -> { where(:prerequisite => true).rank(:problem_order) }, :class_name => TestSet
end
class TestSet < ActiveRecord::Base
  belongs_to :problem
  has_many :test_case_relations, :dependent => :destroy
end

problem.test_cases.where(:id => problem.prerequisite_sets.joins(:test_case_relations).select(:test_case_relations => :test_case_id))

Stacktrace of original error:

PG::ProtocolViolation: ERROR:  bind message supplies 2 parameters, but prepared statement "a5" requires 1
: SELECT "test_cases".* FROM "test_cases"  WHERE "test_cases"."problem_id" = $1 AND "test_cases"."id" IN (SELECT "test_case_relations"."test_case_id" FROM "test_sets" INNER JOIN "test_case_relations" ON "test_case_relations"."test_set_id" = "test_sets"."id" WHERE "test_sets"."problem_id" = $1 AND "test_sets"."prerequisite" = 't'  ORDER BY "test_sets".problem_order ASC)  ORDER BY "test_cases".problem_order ASC

./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/connection_adapters/postgresql_adapter.rb:786:in `get_last_result'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/connection_adapters/postgresql_adapter.rb:786:in `exec_cache'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:139:in `block in exec_query'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:435:in `block in log'
./vendor/bundle/ruby/2.0.0/gems/activesupport-4.0.2/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:430:in `log'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/connection_adapters/postgresql_adapter.rb:891:in `select'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/querying.rb:36:in `find_by_sql'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/relation.rb:585:in `exec_queries'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/association_relation.rb:15:in `exec_queries'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/relation.rb:471:in `load'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/relation.rb:220:in `to_a'
./vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.2/lib/active_record/relation/delegation.rb:12:in `each'
./app/workers/judge_submission_worker.rb:67:in `block in judge'
./app/workers/judge_submission_worker.rb:104:in `block (2 levels) in setup_judging'
./app/services/isolate.rb:18:in `box'
./app/workers/judge_submission_worker.rb:102:in `block in setup_judging'
/opt/ruby/ruby-2.0.0-p353/lib/ruby/2.0.0/tmpdir.rb:88:in `mktmpdir'
./app/workers/judge_submission_worker.rb:100:in `setup_judging'
./app/workers/judge_submission_worker.rb:49:in `judge'
./app/workers/judge_submission_worker.rb:18:in `perform'
./app/workers/judge_submission_worker.rb:12:in `perform'
./vendor/bundle/ruby/2.0.0/bundler/gems/qless-930b3f30f93d/lib/qless/job.rb:43:in `around_perform'
./app/workers/gc_worker_middleware.rb:3:in `around_perform'
./vendor/bundle/ruby/2.0.0/bundler/gems/qless-930b3f30f93d/lib/qless/job.rb:58:in `perform'
./vendor/bundle/ruby/2.0.0/bundler/gems/qless-930b3f30f93d/lib/qless/worker/base.rb:108:in `around_perform'
@kurevin

Had a similar problem, had to workaround by converting relation to a string:

Model.where{id == ANY(array(`#{ActiveRecord::Base.connection.unprepared_statement { filtered_ids.to_sql }}`))}
@cameron-martin

Any updates on this?

@radar
ActiveRecord Hackery member

@cameron-martin If there were updates for this you can be assured that they would be posted here.

@bigxiang

It only happens on Postgresql, because it's using a different binding way to sqlite & mysql. I'm digging it, would response later.

@bigxiang bigxiang added this to the 1.2.1 milestone Jul 17, 2014
@bigxiang bigxiang added a commit that referenced this issue Jul 18, 2014
@bigxiang bigxiang Properly append binds from subqueries and associations with PostgreSQL.
The issue happens if there are more than 1 bind in the SQL. Fixes #272

- Also add mysql, mysql2, postgresql adapter to specs. It should make sure
  that Squeel would run against all these databases consistently.
- Extend travis scripts to support multiple adapters.
5df3bab
@bigxiang bigxiang added a commit that referenced this issue Jul 18, 2014
@bigxiang bigxiang Properly append binds from subqueries and associations with PostgreSQL.
The issue happens if there are more than 1 bind in the SQL. Fixes #272

- Also add mysql, mysql2, postgresql adapter to specs. It should make sure
  that Squeel would run against all these databases consistently.
- Extend travis scripts to support multiple adapters.
43e9804
@bigxiang bigxiang added a commit that referenced this issue Jul 18, 2014
@bigxiang bigxiang Properly append binds from subqueries and associations with PostgreSQL.
The issue happens if there are more than 1 bind in the SQL. Fixes #272

- Also add mysql, mysql2, postgresql adapter to specs. It should make sure
  that Squeel would run against all these databases consistently.
- Extend travis scripts to support multiple adapters.
b6d30e1
@bigxiang bigxiang added a commit that referenced this issue Jul 18, 2014
@bigxiang bigxiang Properly append binds from subqueries and associations with PostgreSQL.
The issue happens if there are more than 1 bind in the SQL. Fixes #272

- Also add mysql, mysql2, postgresql adapter to specs. It should make sure
  that Squeel would run against all these databases consistently.
- Extend travis scripts to support multiple adapters.
5ed15f5
@bigxiang bigxiang added a commit that closed this issue Jul 18, 2014
@bigxiang bigxiang Properly append binds from subqueries and associations with PostgreSQL.
The issue happens if there are more than 1 bind in the SQL. Fixes #272.

Generate table names correctly when joining throught an association.
Fixes #302.

add mysql, mysql2, postgresql adapter to specs. It should make sure
that Squeel would run against all these databases consistently.

Extend travis scripts to support multiple adapters.
321aed1
@bigxiang bigxiang closed this in 321aed1 Jul 18, 2014
@bigxiang

@ronalchn @cameron-martin Please try master branch. It should be fixed now 😄 Thank you very much.

@kaleemullah

@bigxiang same issue on master branch
PG::UndefinedParameter: ERROR: there is no parameter $1

@bigxiang

hi @kaleemullah ,would you please give me a gist to represent the error ? It should be very helpful for me.

@bigxiang

It looks like rails/rails#16238

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment