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:[EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]
> >>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
> >>
> >>
> >>
> >>
> >
> >
> >
> >
> >
> >
>
