ORA-01795: maximum number of expressions in a list is 1000

[kalsan]

The error was reported six years ago in rails/rails#585 as well as in #120 back in the old days. @rsim stated in 2011 in #120 that the error would no longer occur:

Fix for this issue is included in Rails 3.1. If you change version of activerecord to 3.1.1 in your example then it will work without exception (ActiveRecord will generate several SQL statements without exceeding limit of 1000).

However we can reproduce the error in a modern Rails environment: We are using Rails 5.0.1 (with corresponding ActiveRecord and ActiveSupport versions) on JRuby 9.1.6.0 (2.3.1) 2016-11-09 0150a76 Java HotSpot(TM) 64-Bit Server VM 25.121-b13 on 1.8.0_121-b13 +jit [linux-x86_64]. The version of oracle-enhanced is activerecord-oracle_enhanced-adapter (1.7.8)

To reproduce, assume a system that has a model called User. The following will work fine:
User.where(id: Array(1..1_000)).pluck(:id)

However adding just one more user will cause the whole thing to fail:
User.where(id: Array(1..1_001)).pluck(:id)

Feel free to try at home, we got it to fail on any setup we tried.

Workarounds would be described in http://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is but of course a clean fix would be much preferable, if possible. Could you help us out?

Thanks a lot in advance!

[yahonda]

Thanks for the reporting. Would you create a reproduce script using https://github.com/rsim/oracle-enhanced/blob/master/guides/bug_report_templates/active_record_gem.rb

Thanks.

[kalsan]

Yes, it is enough to copy-paste the second code line from above below the assert_equal line. For completeness, here's the resulting script:

begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"
  gem "rails", github: "rails/rails", branch: "master"
  gem "arel", github: "rails/arel", branch: "master"
  gem "activerecord-oracle_enhanced-adapter",  github: "rsim/oracle-enhanced", branch: "master"
  gem "ruby-oci8"
  gem "minitest"
end

require "active_record"
require "minitest/autorun"
require "logger"
require "active_record/connection_adapters/oracle_enhanced_adapter"

# Ensure backward compatibility with Minitest 4
Minitest::Test = MiniTest::Unit::TestCase unless defined?(Minitest::Test)

# Set Oracle enhanced adapter specific connection parameters
DATABASE_NAME = ENV["DATABASE_NAME"] || "orcl"
DATABASE_HOST = ENV["DATABASE_HOST"]
DATABASE_PORT = ENV["DATABASE_PORT"]
DATABASE_USER = ENV["DATABASE_USER"] || "oracle_enhanced"
DATABASE_PASSWORD = ENV["DATABASE_PASSWORD"] || "oracle_enhanced"
DATABASE_SYS_PASSWORD = ENV["DATABASE_SYS_PASSWORD"] || "admin"

CONNECTION_PARAMS = {
  adapter: "oracle_enhanced",
  database: DATABASE_NAME,
  host: DATABASE_HOST,
  port: DATABASE_PORT,
  username: DATABASE_USER,
  password: DATABASE_PASSWORD
}

ActiveRecord::Base.establish_connection(CONNECTION_PARAMS)

ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
  end

  create_table :comments, force: true do |t|
    t.integer :post_id
  end
end

class Post < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

class BugTest < Minitest::Test
  def test_association_stuff
    post = Post.create!
    post.comments << Comment.create!

    assert_equal 1, post.comments.count
    assert_equal 1, Comment.count
    assert_equal post.id, Comment.first.post.id

    Post.where(id: Array(1..1_001)).pluck(:id)
  end
end

The only thing I added is the third last line. It reproduces the problem as described.

Hope this helps.

[yahonda]

I think this in_clause_length only controls the number of IN list for preloading only.

rails/rails#585 (comment)
#120 (comment)

If application specifies an array with more than 1000 elements, user need to split this array into several parts.

[kalsan]

After thorough discussion, we have come to the conclusion that it should be part of the requirements for the adapter to automatically split up such statements, with the following argumentation:

• The need for limiting the where clauses exists in Oracle-related systems only
• Rails has the goal to be as tolerant for different systems as possible
• It achieves this goal through its modularity gained by heavy use of adapter patterns
• Hence, oracle-enhanced is such adapter. It therefore should be the actor to handle oracle-specific problems that do not occur in other environments.

To put it differently: When an application gets migrated from one database to the other, it is desirable to achieve this migration with a minimum of changes in the code. In our case (a very large application), the change of all potentially failing queries would take several human-work-days and cost a large amount of money. In contrary to this, oracle-enhanced adapter both has the opportunity (i.e. it is technically feasible to auto-split at this level) and the duty (i.e. it conceptually makes sense to perform the task at this level) to abstract away this singularity by Oracle at a central and sensible level.

This is why we strongly believe that the right place to junk up the IN-statements is the oracle-enhance adapter itself and not actual production code.

Thank you for your time,
Kalsan

[yahonda]

I understand you would like to have this feature.

Although I still think this need to be addressed by application code, not database adapter side,
pull requests are welcomed. I'll review and consider to merge pull requests supporting this feature.

Thanks.

[kalsan]

At this point we do not have the competences to deal with the problem ourselves (we currently have no expert for this kind of problem). If we ever do, we will submit the pull request.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.