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: neutral (herse.apache.org: local policy)
From: "Jim Newsham" <jnewsham@referentia.com>
To: "'Derby Discussion'" <derby-user@db.apache.org>
Subject: RE: StackOverflowError
Date: Tue, 7 Nov 2006 10:01:25 -1000
Message-ID: <008b01c702a7$84bd9740$8700a8c0@referentia.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AccCIeme3fcmxz8oQeKMO3YaVUVxHAAhNJbQ
In-Reply-To: <45500596.2020306@gmail.com>


Thanks for the response, Rajesh.  I'll try the latest version of Derby later
and report on my results.  In the meantime, here's schema for the tables
involved.  

-- a sample time
create table time (
  id                  int not null generated always as identity primary key,
  time                timestamp not null unique
);

-- a node; nodes are hierarchically arranged structures which contain bands
create table node (
  id                  int not null generated always as identity (start with
1),
  name                varchar(32) not null,
  fk_parent_id        int not null,

  constraint node_pk primary key (id),
  constraint node_unique unique (name, fk_parent_id),
  constraint node_fk_parent foreign key (fk_parent_id) references node (id)
on delete cascade
);

-- insert root node with self as parent to satisfy "not null" constraint
insert into node (name, fk_parent_id) values ('', 1);

-- a band; an identifier for a series of data
create table band (
  id                  int not null generated always as identity primary key,
  fk_node_id          int not null,
  name                varchar(32) not null,
  type                int not null,
  
  constraint band_unique unique (fk_node_id, name, type),
  constraint band_fk_node foreign key (fk_node_id) references node (id) on
delete cascade
);

-- a sample in a double band
create table double_sample (
  fk_band_id          int not null,
  fk_time_id          int not null,
  value               double not null,

  constraint double_sample_unique unique (fk_band_id, fk_time_id),
  constraint double_sample_fk_band foreign key (fk_band_id) references band
(id) on delete cascade,
  constraint double_sample_fk_time foreign key (fk_time_id) references time
(id) on delete cascade
);

Regards,
Jim


> -----Original Message-----
> From: Rajesh Kartha [mailto:kartha02@gmail.com]
> Sent: Monday, November 06, 2006 6:04 PM
> To: Derby Discussion
> Subject: Re: StackOverflowError
> 
> 
> Hello Jim,
> 
> Is it possible to try this query out with the latest GA release 10.2.1.6 ?
> 
> http://db.apache.org/derby/derby_downloads.html#Latest+Official+Release
> 
> There have been some fixes in the related code generation areas for 10.2:
> DERBY-766, DERBY-1714 etc.
> 
> Also, can post the schema that can  reproduce this issue ?
> 
> Regards,
> Rajesh
> 
> 
> 
> 
> 
> Jim Newsham wrote:
> 
> >Hi everyone,
> >
> >I thought the problem would go away if I gave Derby a better written
> query,
> >so I fixed my query generator to be a bit smarter.  Unfortunately, I
> still
> >get a stack overflow error.  Here's the new query:
> >
> >ij(SAMPLEBASE)> select count(*) from time where time.id in (select
> time.id
> >from time, double_sample, band where band.id = double_sample.fk_band_id
> and
> >double_sample.fk_time_id = time.id and (band.id = 39 or band.id = 55));
> >1
> >-----------
> >ERROR 38000: The exception 'java.lang.StackOverflowError' was thrown
> while
> >evaluating an expression. SQLSTATE: XJ001: Java exception: ':
> >java.lang.StackOverflowError'.
> >
> >
> >This looks like an ugly bug for such a simple query.  I didn't find any
> bug
> >in jira which seemed to relate to this.  Is this a known bug?
> >
> >Any advice on how to work around the problem is appreciated.
> >
> >I'm using Derby 10.1.3.1.
> >
> >Thanks,
> >Jim
> >
> >P.S.  I just included the outer query ("select count(*) from..") to
> >reproduce the problem in ij.  My program actually uses jdbc, executes the
> >inner query, and calls ResultSet.last().  The result is the same, a
> >StackOverflowError.  Here's the stack trace I get in my app:
> >
> >Caused by: org.apache.derby.client.am.SqlException: The exception
> >'java.lang.StackOverflowError' was thrown while evaluating an expression.
> >SQLSTATE: XJ001: Java exception: ': java.lang.StackOverflowError'.
> >        at org.apache.derby.client.am.ResultSet.completeSqlca(Unknown
> >Source)
> >        at
> >org.apache.derby.client.net.NetResultSetReply.parseFetchError(Unknown
> > Source)
> >        at
> >org.apache.derby.client.net.NetResultSetReply.parseCNTQRYreply(Unknown
> >Source)
> >        at
> >org.apache.derby.client.net.NetResultSetReply.readPositioningFetch(Unknow
> n
> >Source)
> >        at
> >org.apache.derby.client.net.ResultSetReply.readPositioningFetch(Unknown
> >Source)
> >        at
> >org.apache.derby.client.net.NetResultSet.readPositioningFetch_(Unknown
> >Source)
> >        at org.apache.derby.client.am.ResultSet.getRowCount(Unknown
> Source)
> >        at org.apache.derby.client.am.ResultSet.lastX(Unknown Source)
> >        at org.apache.derby.client.am.ResultSet.last(Unknown Source)
> >        at
> >com.referentia.sdf.monitor.samplebase.derby.QueryDataSet.getSize(QueryDat
> aSe
> >t.java:139)
> >
> >
> >
> >
> >>-----Original Message-----
> >>From: Jim Newsham [mailto:jnewsham@referentia.com]
> >>Sent: Monday, November 06, 2006 11:21 AM
> >>To: 'Derby Discussion'
> >>Subject: StackOverflowError
> >>
> >>
> >>Any reason why I should get a stack overflow error with the following
> >>query?
> >>
> >>Yes, I know the query is a bit odd... it's not hand-written.  The query
> >>generator could be optimized.  Nevertheless... is the stack overflow
> here
> >>considered a bug or a limitation?  If limitation, what specifically is
> the
> >>limitation?
> >>
> >>
> >>ij(SAMPLEBASE)> select count(*) from time where time.id in (select
> time.id
> >>from time, double_sample, band where band.id = double_sample.fk_band_id
> >>and
> >>double_sample.fk_time_id = time.id and band.id = 57 union select id from
> >>time where 1 = 0);
> >>1
> >>-----------
> >>ERROR 38000: The exception 'java.lang.StackOverflowError' was thrown
> while
> >>evaluating an expression. SQLSTATE: XJ001: Java exception: ':
> >>java.lang.StackOverflowError'.
> >>
> >>Thanks,
> >>Jim
> >>
> >>
> >>
> >>
> >
> >
> >
> >
> >
> >
>