I'm having a problem with the following query:
SELECT title,
TimeFormat(time) AS x_time,
author,
module,
tests.id AS link,
xml,
duration
FROM tests, releases
WHERE tests.xmlid=releases.id
AND tests.id NOT IN (SELECT DISTINCT basedon
FROM xmltemp WHERE basedon IS NOT NULL)
This consistently gives me error 38000 (stack overflow) unless I get rid
of the final condition (the one with the nested select). The "releases"
table has about 100 entries, "tests" has 30, and "xmltemp" has 16. I'm
trying to find all tests which do not have a corresponding entry in
xmltemp, where the "basedon" field is either null or an id from the
"tests" table.
Can aanyone tell me why such an innocuous-looking query has such a
catastrophic result, or suggest a better way to achieve the desired
result?
Here's the exception message and stack dump:
ERROR 38000: The exception 'java.lang.StackOverflowError' was thrown while
evaluating an expression.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.iapi.error.StandardException.unexpectedUserException(Unknown
Source)
at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown Source)
at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(Unknown
Source)
at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
Source)
at org.apache.derby.impl.sql.execute.SortResultSet.getRowFromResultSet(Unknown
Source)
at org.apache.derby.impl.sql.execute.SortResultSet.getNextRowFromRS(Unknown
Source)
at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(Unknown Source)
at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown Source)
at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.openCore(Unknown
Source)
at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown
Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown
Source)
at actions.Action.showTable(Action.java:577)
... and so on
TIA,
----------------------------------------------------------------------
John English | mailto:[EMAIL PROTECTED]
Senior Lecturer | http://www.it.bton.ac.uk/staff/je
School of Computing & MIS | "Those who don't know their history
University of Brighton | are condemned to relive it" (Santayana)
----------------------------------------------------------------------
