Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-756

OutOfMemory Error on continous execution of select statement using COUNT() and DISTINCT on same connection

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1, 10.1.2.1
    • Fix Version/s: 10.1.3.2, 10.2.1.6
    • Labels:
      None
    • Environment:
      Windows XP, Java 1.5.0_05, Derby network server 10.0.2.1, 10.1.2.1, Derby heapsize 128m, IBM Universal JDBC driver

      Description

      The OutOfMemory is thrown when I continously execute a sql statement on an openned JDBC connection. A PreparedStatement and ResultSet is created on the connection everytime and close after each execution. I suspect that the bug is related to function COUNT() with keyword DISTINCT. For example, "select count(distinct ID) from TEST where FLAG <> 2". It will be fine if DISTINCT is not used, or select count is used.

      The exception like "Exception in thread "DRDAConnThread_2" java.lang.OutOfMemoryError: Java heap space" could be thrown on Derby side, or sometimes on client side.

      Please find the test code and schema I used for testing:
      create table:
      CREATE TABLE Test
      (
      ID BIGINT NOT NULL,
      NAME VARCHAR(512) NOT NULL,
      FLAG int,
      CONSTRAINT PK_ID PRIMARY KEY (ID)
      );

      insert data:
      insert into TEST values (0, 'name0', 0);
      insert into TEST values (1, 'name1', 1);
      insert into TEST values (2, 'name2', 2);

      Java client:

      import java.sql.Connection;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.DriverManager;
      import java.sql.SQLException;

      public class DerbyTest
      {
      static public void main(String args[]) throws Exception
      {
      Class.forName("com.ibm.db2.jcc.DB2Driver");
      Connection conn = null;
      try
      {
      conn = DriverManager.getConnection("jdbc:derby:net://localhost:1527/testDB", "admin", "admin");
      for (int i = 0; i < 10000000; i++)
      {
      System.out.println("Query "+i);

      String sql = "select count(distinct ID) from TEST where FLAG <> 2";
      PreparedStatement pStmt = null;
      ResultSet rs = null;

      try
      {

      pStmt = conn.prepareStatement(sql);
      rs = pStmt.executeQuery();
      if (rs.next())

      { rs.getInt(1); }

      }
      catch (SQLException e)

      { e.printStackTrace(); }

      finally

      { if (rs != null) rs.close(); if (pStmt != null) pStmt.close(); }

      }
      }
      finally

      { if (conn != null) conn.close(); }

      }

      }

        Attachments

        1. mem_leak.diff
          2 kB
          Manish Khettry

          Activity

            People

            • Assignee:
              mkhettry Manish Khettry
              Reporter:
              billchen Bill Chen
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: