Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (idunn.apache.osuosl.org: domain gmail.com designates
 64.233.166.183 as permitted sender)
DomainKey-Status: good
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:references:in-reply-to:content-type;
        b=XB8ytkpY9Kef4Pq0TnFGBNOK4FjZyQ3fNJhV0+TgaO3GDXOp+ssdLjtiAKILHUgtTAKApNsvusIvI6V8BfmvNTWMJNvLIr5A+4jhdjeMpqxtiVWipUNEqLZPl8ryPhzff0XoBo67Uuc6rBUAn/86u8GWmwi5WgO8J1TLgPs7qO4=
Message-ID: <45079074.2070207@gmail.com>
Date: Tue, 12 Sep 2006 22:00:36 -0700
From: Rajesh Kartha <kartha02@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.7.12) Gecko/20050915
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: SQLException: The heap container with container id Container(-1,
 1157060695837) is closed.
References: 
 <2526D42D65F597428BFF422D6AB79E9E0108FAAA@NA1000EXM01.na.ds.monsanto.com>
In-Reply-To: 
 <2526D42D65F597428BFF422D6AB79E9E0108FAAA@NA1000EXM01.na.ds.monsanto.com>
Content-Type: multipart/mixed;
 boundary="------------010109090908030603000107"

This is a multi-part message in MIME format.
--------------010109090908030603000107
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

BALDWIN, ALAN J [AG-Contractor/1000] wrote:

>Michael,
>Thanks for the responses.  Here is what I know so far:
>
>** There is no other finally block that would close that result set.  The
>only operations that happen on that result set (other than the finally block
>you saw) are rs.getString(), rs.getInt(), etc...
>
>** I am quite certain that this is not your run-of-the mill jdbc error.  I
>would not be posting here if I thought it were.  
>
>** I can switch the order of the inner join clauses and reproduce this on
>datasets that previously had no problem.  For example:
>
>//This returns data:
>SELECT * FROM DeliveryNotification dn 
>INNER JOIN DealerTransaction dt ON dn.InventoryTransactionID =
>dt.TransactionId 
>INNER JOIN Product p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =
>dn.SeedYear
>//more joins here...
>
>//This throws the exception:
>SELECT * FROM DealerTransaction dt 
>INNER JOIN DeliveryNotification dn ON dn.InventoryTransactionID =
>dt.TransactionId
>INNER JOIN Product p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =
>dn.SeedYear
>//more joins here...
>
>We have one DealerTransaction row to many DeliveryNotification rows.  The
>joins following those three tables are mostly just lookup data (status
>tables and such... our database is very normalized), and they remain
>unchanged in both versions of this query.
>
>To me, this smells like something out of my control (jdbc driver, database
>engine?) is running out of resources.  This seems to be more frequent the
>larger the dataset gets.  This dataset for example, contains about 250
>DealerTransaction rows, ~1000 DeliveryNotification rows, and ~5000 products.
>Is this large by Derby standards?
>
>Regards,
>-Alan-
>  
>
Hi Alan,

The amount of data that you have mentioned is really minimal.  We have 
run tests on Derby using joins of multiple tables (~64) and views with
huge amount of data - 500K ( see 
http://issues.apache.org/jira/browse/DERBY-805  in JIRA, for example). I 
am sure there are many users out there
who are using  Derby is much complex scenarios. It is really strange you 
are hitting this error.

I assume your app is a simple JDBC one without  any object-relational 
mapper (Hibernate etc.) in between.
Following your email thread and based on the details you have provided I 
tried re-creating the issue but was not successful. Attached is my
simple app. I noticed the data types you are using are pretty straight 
forward so I have stuck to those. However  I keep coming
across the 'data-dependent' issue in your thread, which seems unclear to 
me as to what data to use in the  repro. Moroever it is not clear
what type of isolation level, type of ResultSet (scrollable etc.) is 
being used.

Is it possible that you can create a simple reproduction (could modify 
the attached script to suit your needs) and create a JIRA issue.
This would be really really benefical in getting to the core of the problem.

-Rajesh


--------------010109090908030603000107
Content-Type: text/java;
 name="InnerJoinTest.java"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="InnerJoinTest.java"

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class InnerJoinTest {
	public static String jdbcurl="jdbc:derby:transdb;create=true";
	public static String driverClass="org.apache.derby.jdbc.EmbeddedDriver";
	static Connection conn=null;
	/**
	 * @param args
	 */
	public static void main(String[] args) {

		try{
				Class.forName(driverClass);
				conn=DriverManager.getConnection(jdbcurl);
				//createTables();
				//insertData();
				doSelect();
			}catch(ClassNotFoundException cne){
				cne.printStackTrace();
			}catch(SQLException sqe){
				sqe.printStackTrace();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	public static void doSelect() throws SQLException{
		Statement stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery("SELECT * FROM DeliveryNotification dn INNER JOIN DealerTransaction dt ON dn.transactionId=dt.transactionId INNER JOIN Products p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =dn.SeedYear");
		ResultSetMetaData rsmd=rs.getMetaData();
		int rows=0;
		while(rs.next()){
			for(int i=1;i<=rsmd.getColumnCount();i++){
				System.out.print(rs.getString(i)+"\t");
			}
			System.out.println("\n");
			rows++;
		}
		System.out.println("Total rows obtained "+rows);
		
		//Query # 2 
		rs.close();
		rs=stmt.executeQuery("SELECT * FROM DealerTransaction dt INNER JOIN DeliveryNotification dn ON dn.transactionId =dt.transactionId INNER JOIN ProductS p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =dn.SeedYear");
		rsmd=rs.getMetaData();
		rows=0;
		while(rs.next()){
			for(int i=1;i<=rsmd.getColumnCount();i++){
				System.out.print(rs.getString(i)+"\t");
			}
			System.out.println("\n");
			rows++;
		}
		System.out.println("Total rows obtained "+rows);
		rs.close();
		stmt.close();
	}
	public static void createTables() throws SQLException{
		Statement stmt=conn.createStatement();
		stmt.executeUpdate("Create table PRODUCTS(productUPC int, productCode char(15), productName varchar(25), seedYear int)");
		System.out.println("Products table created");
		stmt.executeUpdate("Create table DeliveryNotification (deliveryId int, productUPC int , seedYear int, transactionId bigint, dealerName varchar(25))");
		System.out.println("DeliveryNotification table created");
		stmt.executeUpdate("Create table DealerTransaction  (transactionId bigint, dealerName varchar(25), TransactionDate Date, seedyear int, ShipToName varchar(255), ShipFromName varchar(255), status char(15))");
		System.out.println("DealerTransaction table created");
		stmt.close();
	}
	public static void insertData() throws SQLException{
		PreparedStatement ps=conn.prepareStatement("Insert into PRODUCTS values(?,?,?,?)");
		conn.setAutoCommit(false);

		for(int i=0;i<5000;i++){
			ps.setInt(1,i);
			ps.setString(2,"P#"+i);
			ps.setString(3,"PRODUCT NAME - "+i);
			ps.setInt(4,i);
			ps.addBatch();
		}
		System.out.println("Insert into PRODUCTS table successful - "+ps.executeBatch().length);
		conn.commit();
		ps=conn.prepareStatement("Insert into DeliveryNotification values(?,?,?,?,?)");

		int dlrId=1;
		for(int i=0;i<1250;i++){
			ps.setInt(1,i);
			ps.setInt(2,1000+i); 
			ps.setInt(3,1000+i); //matching info as PRODUCTS
			ps.setLong(4,dlrId);
			ps.setString(5,"DEALER - "+dlrId);
			if(i%5==0){
				dlrId+=1; //5 entries for each Dealer
			}
			ps.addBatch();
		}
		System.out.println("Insert into DeliveryNotification table successful - "+ps.executeBatch().length);
		conn.commit();
		
		ps=conn.prepareStatement("Insert into DealerTransaction values(?,?,?,?, ? , ?, ?)");
		for(int i=1;i<=250;i++){
			ps.setLong(1,i);
			ps.setString(2,"DEALER - "+i);
			ps.setDate(3,new Date(System.currentTimeMillis()));
			ps.setInt(4,i);
			ps.setString(5,"SHIPPED TO ADDRESS "+i);
			ps.setString(6,"SHIPPED FROM ADDRESS "+i);
			ps.setString(7,"SHIPPED");
			ps.addBatch();
		}
		System.out.println("Insert into DealerTransaction table successful - "+ps.executeBatch().length);
		conn.commit();
		conn.setAutoCommit(true);
		ps.close();
	}

}


--------------010109090908030603000107--