title: 'Maximum number of expressions in a list is 1000' error with Grails and Oracle
body: <p>I'm using Grails with an Oracle database. Most of the data in my application is part of a hierarchy that goes something like this (each item containing the following one):</p>
  
  <ul>
  <li>Direction</li>
  <li>Group</li>
  <li>Building site</li>
  <li>Contract</li>
  <li>Inspection</li>
  <li>Non-conformity</li>
  </ul>
  
  <p>Data visible to a user is filtered according to his accesses which can be at the Direction, Group or Building Site level depending on user role.</p>
  
  <p>We easily accomplished this by creating a listWithSecurity method for the BuildingSite domain class which we use instead of list across most of the system. We created another listWithSecurity method for Contract. It basically does a Contract.findAllByContractIn(BuildingSite.listWithSecurity). And so on with the other classes. This has the advantage of keeping all the actual access logic in BuildingSite.listWithsecurity.</p>
  
  <p>The problem came when we started getting real data in the system. We quickly hit the "ora-01795 maximum number of expressions in a list is 1000" error. Fair enough, passing a list of over 1000 literals is not the most efficient thing to do so I tried other ways even though it meant I would have to deport the security logic to each controller.</p>
  
  <p>The obvious way seemed to use a criteria such as this (I only put the Direction level access here for simplicity):</p>
  
  <pre><code>def c = NonConformity.createCriteria()
  def listToReturn = c.list(max:params.max, offset: params.offset?.toInteger() ?: 0)
  {
      inspection {
          contract {
              buildingSite {
                  group {
                      'in'("direction",listOfOneOrTwoDirections)
                  }
              }
          }
      }
  }
  </code></pre>
  
  <p>I was expecting Grails to generate a single query with joins that would avoid the ora-01795 error but it seems to be calling a separate query for each level and passing the result back to Oracle as literal in an 'in' to query the other level. In other words, it does exactly what I was doing so I get the same error.</p>
  
  <p>Actually, it might be optimising a bit. It seems to be solving the problem but only for one level. In the previous example, I wouldn't get an error for 1001 inspections but I would get it for 1001 contracts or building sites.</p>
  
  <p>I also tried to do basically the same thing with findAll and a single HQL where statement to which I passed a single direction to get the nonConformities in one query. Same thing. It solves the first levels but I get the same error for other levels.</p>
  
  <p>I did manage to patch it by splitting my 'in' criteria into many 'in' inside an 'or' so no single list of literals is more than 1000 long but that's profoundly ugly code. A single findAllBy[…]In becomes over 10 lines of code. And in the long run, it will probably cause performance problems since we're stuck doing queries with a very large amount of parameters.</p>
  
  <p>Has anyone encountered and solved this problem in a more elegant and efficient way?</p>