Hi Pavel:
     Will you be able to find the exact query executing from AquaDataStudio to Derby? Will the query be in derby.log? If you have the query, do you mind executing the query in ij? Also, when the query hang, would you mind getting the thread dump? You can edit out the proprietary parts

     I am just thinking out loud here. I did another query for the same table as T1. It still work with my simple table.
i.e.
==
ij> select T.col1, T.col2 from T1 T, (select col1, col2 from T1 as T2) as M wher
e M.col1 = T.col1;
COL1       |COL2
--------------------------------
1          |row 1
2          |row 2


Hope this help,
Lily


From: Pavel Bortnovskiy <PBortnovskiy@Jefferies.com>
To: Lily Wei <lilywei@yahoo.com>
Cc: Derby Discussion <derby-user@db.apache.org>
Sent: Thu, January 20, 2011 2:43:11 PM
Subject: Re: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE)

Hello, Lily:

thank you for your response.

while I am still trying to create a test for Derby users, your experiment is not the same. Notice that in our query we are joining on the same table. So, T2 is not a different table. It's still the same T1 table, but aliased as T2...

try something like this:

select
        *
from
        TABLE T1,
        (
                select
                        T2.col
                from
                        TABLE T2
        ) as M
where
        M.col = T1.col

Once I succeed reproducing the error, I will show my test.

What makes me suspect that there is a problem with Derby is two-fold:
- our code seems to hang in ResultSet.next(), which is a call to Derby JDBC API implementation
- accessing those in-memory tables with AquaDataStudio and running the same query never returns/succeeds (I stopped after 20 minutes of having it running, where as the subqueries themselves run in a few milliseconds). AquaDataStudio is a commercial product and I doubt that it breaks precisely at the same point and query as our code...

P.