title: Application of the result set's fetch size kills the program
body: <p>This is a very interesting question for me, and I hope that you will help me solve it. I am making a query to select all rows from an Oracle DB table. There is used the oracle jdbc driver. To avoid connection timeout, queries are performed using rownum in increments of 100 rows. Everything would be fine, but the program freezes on row 91 of the result set trying to do resultSet.next(). Wherein there are no any exceptions. I tried to looking for the reason of this behavior and realized that the matter is in the fetch size of the result set. The default value of fetch size is 10. This behavior looks like these 10 rows are pulled out from the result set and program is freezed when we get into the freed space. Then we set fetch size 0, all works perfect. Is this the expected behavior? And if so, why? In the example below, this problem is bypassed by exiting the loop by row number.</p>
  
  <pre><code>private static volatile int bottomRow = -99;
  private static volatile int topRow = 0;
  private static final String SQL = "SELECT * from (select m.*, rownum r from keyspace.table m) where r &gt;= ? and r &lt; ?";
  
  public static void select(Connection connection) {
      try (PreparedStatement preparedStatement=connection.prepareStatement(SQL)){
          while (true) {
              incrementCounters();
              preparedStatement.setInt(1, bottomRow);
              preparedStatement.setInt(2, topRow);
              ResultSet rs = preparedStatement.executeQuery();
            // rs.getFetchSize(); -&gt; default value is 10
              if (rs.next()) {
                  do {
                      rs.getString("id");
                      rs.getString("customer_name");
                      /* some logic */
                      if (rs.getRow() == 90) {
                              break;
                      }
                   } while (rs.next());
              } else break;
          }
      } catch (Exception e) {
      } 
  }
  
  private synchronized static void incrementCounters() {
      Thread.sleep(700);
      if (topRow != 0) {
          bottomRow += 90;
          topRow = bottomRow + 100;
      } else {
          bottomRow += 100;
          topRow += 100;
      }
  }
  </code></pre>
  
  <p>jdbc driver version</p>
  
  <pre><code>&lt;dependency&gt;
      &lt;groupId&gt;com.oracle&lt;/groupId&gt;
      &lt;artifactId&gt;jdbc&lt;/artifactId&gt;
      &lt;version&gt;11.2.0.3&lt;/version&gt;
      &lt;type&gt;pom&lt;/type&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
      &lt;groupId&gt;com.oracle&lt;/groupId&gt;
      &lt;artifactId&gt;ojdbc6&lt;/artifactId&gt;
      &lt;version&gt;11.2.0.3&lt;/version&gt;
  &lt;/dependency&gt;
  </code></pre>
  
  <p>Configurations of pool properties</p>
  
  <pre><code>private static DataSource ds=null;
  public static Connection getConnection() throws SQLException{
      if (ds==null){
          synchronized (Source.class.getName()) {
              if (ds==null)
                  try {
                      DriverManager.setLoginTimeout(1);
                      String driverClassName="oracle.jdbc.OracleDriver";
                      PoolProperties p = new PoolProperties();
                      p.setUrl(url);
                      p.setDriverClassName(driverClassName);
                      p.setUsername(username);
                      p.setPassword(password);
                      p.setJmxEnabled(false);
                      p.setTestWhileIdle(false);
                      p.setTestOnBorrow(true);
                      p.setValidationQuery("SELECT 1 from dual");
                      p.setTestOnReturn(false);
                      p.setTestOnConnect(false);
                      p.setValidationInterval(5*1000);
                      p.setTimeBetweenEvictionRunsMillis(120000);
                      p.setMaxActive(500);
                      p.setInitialSize(0);
                      p.setMinIdle(30);
                      p.setMaxIdle(100);
                      p.setRemoveAbandonedTimeout(60);
                      p.setMinEvictableIdleTimeMillis(120000);
                      p.setLogAbandoned(false);
                      p.setRemoveAbandoned(true);
                      p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState; org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer; org.apache.tomcat.jdbc.pool.interceptor.StatementCache");
                      ds = new DataSource();
                      ds.setPoolProperties(p);
                  } catch (Exception e) {
                      log.error("error {}",e.getMessage());
                      throw new RuntimeException(e);
                  }
          }
      }
      return ds.getConnection();
  }
  </code></pre>