-
Notifications
You must be signed in to change notification settings - Fork 310
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
rails4: ActiveRecord::StatementInvalid: OCIError: ORA-00911 at test_find_on_has_many_association_collection_with_include_and_conditions #185
Comments
I'm still having this error and tring to find any fixes. Here are SQL statements for each database adapter.
SELECT * FROM (SELECT DISTINCT "POSTS".id, FIRST_VALUE(#<Arel::Nodes::Ascending:0x000000027777a0>) OVER (PARTITION BY "POSTS".id ORDER BY #<Arel::Nodes::Ascending:0x000000027777a0>) AS alias_0__ FROM "POSTS" LEFT OUTER JOIN "COMMENTS" ON "COMMENTS"."POST_ID" = "POSTS"."ID" INNER JOIN "READERS" ON "POSTS"."ID" = "READERS"."POST_ID" WHERE "READERS"."PERSON_ID" = :a1 AND (comments.id is null) ORDER BY alias_0__) WHERE ROWNUM <= 1
SELECT * FROM (SELECT DISTINCT "POSTS".id FROM "POSTS" LEFT OUTER JOIN "COMMENTS" ON "COMMENTS"."POST_ID" = "POSTS"."ID" INNER JOIN "READERS" ON "POSTS"."ID" = "READERS"."POST_ID" WHERE "READERS"."PERSON_ID" = 1 AND (comments.id is null)) WHERE ROWNUM <= 1
SELECT DISTINCT "posts".id FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" INNER JOIN "readers" ON "posts"."id" = "readers"."post_id" WHERE "readers"."person_id" = ? AND (comments.id is null) ORDER BY "posts"."id" ASC LIMIT 1 [["person_id", 1]]
SELECT DISTINCT "posts".id, "posts"."id" AS alias_0 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" INNER JOIN "readers" ON "posts"."id" = "readers"."post_id" WHERE "readers"."person_id" = $1 AND (comments.id is null) ORDER BY "posts"."id" ASC LIMIT 1 [["person_id", 1]]
SELECT DISTINCT `posts`.id FROM `posts` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = `posts`.`id` INNER JOIN `readers` ON `posts`.`id` = `readers`.`post_id` WHERE `readers`.`person_id` = ? AND (comments.id is null) ORDER BY `posts`.`id` ASC LIMIT 1 [["person_id", 1]]
SELECT DISTINCT `posts`.id FROM `posts` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = `posts`.`id` INNER JOIN `readers` ON `posts`.`id` = `readers`.`post_id` WHERE `readers`.`person_id` = 1 AND (comments.id is null) ORDER BY `posts`.`id` ASC LIMIT 1 |
I made a change to show the prediction value as follows. Then I found after 3 times $ ARCONN=oracle ruby -Itest test/cases/associations/has_many_through_associations_test.rb -n test_find_on_has_many_association_collection_with_include_and_conditions
Using oracle
Run options: -n test_find_on_has_many_association_collection_with_include_and_conditions --seed 55576
# Running tests:
[DEBUG] PROJECTIONS [#<struct Arel::Attributes::Attribute relation=#<Arel::Table:0x000000024d8af8 @name="people", @engine=Person(id: integer, first_name: string, primary_contact_id: integer, gender: string, number1_fan_id: integer, lock_version: integer, comments: string, best_friend_id: integer, best_friend_of_id: integer, created_at: datetime, updated_at: datetime), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>, name="*">]
[DEBUG] PROJECTIONS [#<struct Arel::Attributes::Attribute relation=#<Arel::Table:0x00000002a7cd60 @name="posts", @engine=Post(id: integer, author_id: integer, title: string, body: string, type: string, comments_count: integer, taggings_count: integer, taggings_with_delete_all_count: integer, taggings_with_destroy_count: integer, tags_count: integer, tags_with_destroy_count: integer, tags_with_nullify_count: integer), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>, name="*">]
[DEBUG] PROJECTIONS ["DISTINCT \"POSTS\".id, FIRST_VALUE(#<Arel::Nodes::Ascending:0x000000015c7050>) OVER (PARTITION BY \"POSTS\".id ORDER BY #<Arel::Nodes::Ascending:0x000000015c7050>) AS alias_0__"]
E
Finished tests in 1.724049s, 0.5800 tests/s, 0.0000 assertions/s.
1) Error:
test_find_on_has_many_association_collection_with_include_and_conditions(HasManyThroughAssociationsTest):
ActiveRecord::StatementInvalid: OCIError: ORA-00911: invalid character: SELECT * FROM (SELECT DISTINCT "POSTS".id, FIRST_VALUE(#<Arel::Nodes::Ascending:0x000000015c7050>) OVER (PARTITION BY "POSTS".id ORDER BY #<Arel::Nodes::Ascending:0x000000015c7050>) AS alias_0__ FROM "POSTS" LEFT OUTER JOIN "COMMENTS" ON "COMMENTS"."POST_ID" = "POSTS"."ID" INNER JOIN "READERS" ON "POSTS"."ID" = "READERS"."POST_ID" WHERE "READERS"."PERSON_ID" = :a1 AND (comments.id is null) ORDER BY alias_0__) WHERE ROWNUM <= 1
stmt.c:253:in oci8lib_191.so
/home/yahonda/.rvm/gems/ruby-1.9.3-p194@railsmaster/gems/ruby-oci8-2.1.2/lib/oci8/oci8.rb:474:in `exec'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_oci_connection.rb:143:in `exec'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:639:in `block in exec_query'
/home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:288:in `block in log'
/home/yahonda/git/rails/activesupport/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:283:in `log'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1322:in `log'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:619:in `exec_query'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1276:in `select'
/home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:19:in `select_all'
/home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
/home/yahonda/git/rails/activerecord/lib/active_record/querying.rb:40:in `block in find_by_sql'
/home/yahonda/git/rails/activerecord/lib/active_record/explain.rb:38:in `logging_query_plan'
/home/yahonda/git/rails/activerecord/lib/active_record/querying.rb:39:in `find_by_sql'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:174:in `exec_queries'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:164:in `block in to_a'
/home/yahonda/git/rails/activerecord/lib/active_record/explain.rb:38:in `logging_query_plan'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:163:in `to_a'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/delegation.rb:6:in `collect'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:246:in `construct_limited_ids_condition'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:231:in `apply_join_dependency'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:220:in `construct_relation_for_association_find'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:199:in `find_with_associations'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:174:in `exec_queries'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:164:in `block in to_a'
/home/yahonda/git/rails/activerecord/lib/active_record/explain.rb:38:in `logging_query_plan'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:163:in `to_a'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:328:in `find_first'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:93:in `first'
/home/yahonda/.rvm/gems/ruby-1.9.3-p194@railsmaster/bundler/gems/active_record_deprecated_finders-c5418c5119b3/lib/active_record_deprecated_finders/relation.rb:130:in `first'
/home/yahonda/git/rails/activerecord/lib/active_record/associations/collection_association.rb:570:in `first_or_last'
/home/yahonda/git/rails/activerecord/lib/active_record/associations/collection_association.rb:91:in `first'
/home/yahonda/git/rails/activerecord/lib/active_record/associations/collection_proxy.rb:871:in `first'
test/cases/associations/has_many_through_associations_test.rb:595:in `test_find_on_has_many_association_collection_with_include_and_conditions'
1 tests, 0 assertions, 0 failures, 1 errors, 0 skips
$ |
I've been thinking it might have been Arel but I was wrong. This storage SQL statement is generated at $ git diff
diff --git a/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb b/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb
index 140c6c6..077be88 100644
--- a/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb
+++ b/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb
@@ -1202,6 +1202,8 @@ module ActiveRecord
#
# distinct("posts.id", "posts.created_at desc")
def distinct(columns, order_by) #:nodoc:
+ puts caller.first
+ puts "ORDER_BY #{order_by}"
return "DISTINCT #{columns}" if order_by.blank?
# construct a valid DISTINCT clause, ie. one that includes the ORDER BY columns, using
$ $ ARCONN=oracle ruby -Itest test/cases/associations/has_many_through_associations_test.rb -n test_find_on_has_many_association_collection_with_include_and_conditions
Using oracle
Run options: -n test_find_on_has_many_association_collection_with_include_and_conditions --seed 17927
# Running tests:
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:242:in `construct_limited_ids_condition'
ORDER_BY [#<Arel::Nodes::Ascending:0x00000001c95648 @expr=#<struct Arel::Attributes::Attribute relation=#<Arel::Table:0x00000001992ae0 @name="posts", @engine=Post(id: integer, author_id: integer, title: string, body: string, type: string, comments_count: integer, taggings_count: integer, taggings_with_delete_all_count: integer, taggings_with_destroy_count: integer, tags_count: integer, tags_with_destroy_count: integer, tags_with_nullify_count: integer), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>, name="id">>]
E
Finished tests in 2.067693s, 0.4836 tests/s, 0.0000 assertions/s.
1) Error:
test_find_on_has_many_association_collection_with_include_and_conditions(HasManyThroughAssociationsTest):
ActiveRecord::StatementInvalid: OCIError: ORA-00911: invalid character: SELECT * FROM (SELECT DISTINCT "POSTS".id, FIRST_VALUE(#<Arel::Nodes::Ascending:0x00000001c95648>) OVER (PARTITION BY "POSTS".id ORDER BY #<Arel::Nodes::Ascending:0x00000001c95648>) AS alias_0__ FROM "POSTS" LEFT OUTER JOIN "COMMENTS" ON "COMMENTS"."POST_ID" = "POSTS"."ID" INNER JOIN "READERS" ON "POSTS"."ID" = "READERS"."POST_ID" WHERE "READERS"."PERSON_ID" = :a1 AND (comments.id is null) ORDER BY alias_0__) WHERE ROWNUM <= 1
stmt.c:253:in oci8lib_191.so
/home/yahonda/.rvm/gems/ruby-1.9.3-p194@railsmaster/gems/ruby-oci8-2.1.2/lib/oci8/oci8.rb:474:in `exec'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_oci_connection.rb:143:in `exec'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:639:in `block in exec_query'
/home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:288:in `block in log'
/home/yahonda/git/rails/activesupport/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:283:in `log'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1324:in `log'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:619:in `exec_query'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1278:in `select'
/home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:19:in `select_all'
/home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
/home/yahonda/git/rails/activerecord/lib/active_record/querying.rb:40:in `block in find_by_sql'
/home/yahonda/git/rails/activerecord/lib/active_record/explain.rb:38:in `logging_query_plan'
/home/yahonda/git/rails/activerecord/lib/active_record/querying.rb:39:in `find_by_sql'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:174:in `exec_queries'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:164:in `block in to_a'
/home/yahonda/git/rails/activerecord/lib/active_record/explain.rb:38:in `logging_query_plan'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:163:in `to_a'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/delegation.rb:6:in `collect'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:246:in `construct_limited_ids_condition'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:231:in `apply_join_dependency'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:220:in `construct_relation_for_association_find'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:199:in `find_with_associations'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:174:in `exec_queries'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:164:in `block in to_a'
/home/yahonda/git/rails/activerecord/lib/active_record/explain.rb:38:in `logging_query_plan'
/home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:163:in `to_a'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:328:in `find_first'
/home/yahonda/git/rails/activerecord/lib/active_record/relation/finder_methods.rb:93:in `first'
/home/yahonda/.rvm/gems/ruby-1.9.3-p194@railsmaster/bundler/gems/active_record_deprecated_finders-267cd0efbbb9/lib/active_record_deprecated_finders/relation.rb:130:in `first'
/home/yahonda/git/rails/activerecord/lib/active_record/associations/collection_association.rb:570:in `first_or_last'
/home/yahonda/git/rails/activerecord/lib/active_record/associations/collection_association.rb:91:in `first'
/home/yahonda/git/rails/activerecord/lib/active_record/associations/collection_proxy.rb:871:in `first'
test/cases/associations/has_many_through_associations_test.rb:595:in `test_find_on_has_many_association_collection_with_include_and_conditions'
1 tests, 0 assertions, 0 failures, 1 errors, 0 skips
$ It looks the def construct_limited_ids_condition(relation)
orders = relation.order_values.map { |val| val.presence }.compact
values = @klass.connection.distinct("#{@klass.connection.quote_table_name table_name}.#{primary_key}", orders)
relation = relation.dup
ids_array = relation.select(values).collect {|row| row[primary_key]}
ids_array.empty? ? raise(ThrowResult) : table[primary_key].in(ids_array)
end |
Opened a pull request #198. |
Tested with Rails master branch, Got an ActiveRecord::StatementInvalid: OCIError: ORA-00911 at test_find_on_has_many_association_collection_with_include_and_conditions.
When this test successfully finished with Oracle enhanced adapter with Rails 3-2 stable branch,
order_by
is an Arrayorder_by.blank?
returns trueOn the other hand, when it get an error with rails master branch,
order_by
is an Array same as 3.2order_by.blank?
returns falsep order_by
shows following values.I'm still trying to address this error. If anyone has any advice, it would be really appreciated.
The text was updated successfully, but these errors were encountered: