Prashanth Menon wrote:
I did the sysinfo call and here is what it spit out:
[ snip ]
[C:\...org.apache.derby\derby.jar] 10.1.2.5 - (413784)
[C:\...org.apache.derby\derbytools.jar] 10.1.2.5 - (413784)
There was quite a bit of work happening in the optimizer at the time of this
particular release (10.1.2.5 - 413784), including work to resolve some issues
that were introduced as part of an earlier set of changes. All known problems
were subsequently resolved in later releases--so I wonder if perhaps the problem
you are seeing has been resolved in a more recent release?
I think the first official release for which the relevant bugs were fixed was
Derby 10.2.1.6. That release--and also a more recent 10.2 release--can be
downloaded from here:
http://db.apache.org/derby/derby_downloads.html
Note that all of the relevant changes were ported to the 10.1 branch, as well,
but according to the above page there haven't been any 10.1 releases since that
happened...(maybe someone on the list can correct me if that's not true?).
So as a first step I would create a copy of your database, upgrade the copied
database to the latest 10.2 release (10.2.2.0), and see if your query still
fails. It might be good to run with the "debug" distribution so that the
failure gives as much information as possible.
If the query fails with 10.2.2.0 then it would be great if you could file a new
Jira issue for the problem. Even if you cannot include the query/database, it
would be good to file a bug and include the stack trace from the "debug" version
of 10.2.2.0. That will ensure that we don't lose track of the bug:
http://db.apache.org/derby/DerbyBugGuidelines.html
I can't give the query (or the DB), but I can give you a gist of how it
goes:
Thank you for the high-level description of the query. Without a reproducible
query/database, my guess is that this is going to be a tough problem to track
down. After filing a Jira (as mentioned above), you could try to create a new
database from the DDL provided by the "dblook" utility, and then execute your
query against that new (clean) database:
> java org.apache.derby.tools.dblook -d jdbc:derby:mydb -o mydb.ddl -verbose
> java org.apache.derby.tools.ij
ij version 10.1
ij> connect 'mydbempty;create=true';
ij> run 'mydb.ddl';
ij> [ execute your query now... ]
It's a long shot, but if the query fails with no data in the tables then you
might be able to work with the DDL to create a reproducible test case that can
be shared with the community. This would also indicate that the problem does
not depend on any specific data, which could be useful information.
Any idea? What more info do you need?
Other than what I suggested above, I do not have any ideas at the moment. My
(perhaps overly optimistic) hope is that the problem you are seeing was fixed as
part of other Derby optimizer changes made in the last year. But if that's not
the case then the first step is to create a Jira and attach whatever information
you can (starting with the info you have included in this thread).
If you have any questions/problems with creating a Jira, please feel free to ask
on this list...
Army
