[DERBY-756] OutOfMemory Error on continous execution of select statement using COUNT() and DISTINCT on same connection

• 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
• Component/s: Demos/Scripts, JDBC, Network Client, Network Server, SQL
• 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
• Assignee: Manish Khettry
• Reporter: Bill Chen
• Created: 14/Dec/05 01:27
• Updated: 19/Oct/07 16:12
• Resolved: 15/Feb/06 14:55

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(); }

}

}

Sub-Tasks

• OutOfMemory error on continuous execution of SQL statement: Closed
• OutOfMemory error still happens in 10.2.1.6: Closed