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.


From: Lily Wei <lilywei@yahoo.com>
To: PBortnovskiy@Jefferies.com
Cc: lilywei@yahoo.com
Date: 01/20/2011 05:25 PM
Subject: Re: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE)





Hi Pavel:
     How are you? This is Lily Wei. I am one of the Derby committers. I am also doing projects for clients.

     I am curious in turn of how do you draw to conclusion that this is one of Derby's problem.
What error message you get from Derby? What message is print to derby.log.

     I did a simple experiment and the query seem to work. However,  it is totally possible that your query is different than my query.

     For example:
     =========
create table T1 (col1 int, col2 char(20));
insert into T1 values (1, 'row 1';);
insert into T1 values (2, 'row 2');
create table T2 (col1 int, col2 char(20));
insert into T2 values (1, 'row 1 for T2');
insert into T2 values (2, 'row 2 for T2');
ij> select T.col1, T.col2 from T1 T, (select col1, col2 from T2) as M where M.co
l1 = T.col1;
COL1       |COL2
--------------------------------
1          |row 1
2          |row 2

      Would you mind forward me your table definition, your data and the query you were running that Derby can not handle. It will also be good to have derby.log information as well?

       Sorry for so many questions. I sincerely hope I can help you.


Thanks,
Lily



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


the more I am working through this issue, the more I get convinced that it's a Derby issue.

When the select statement is modified slightly (not using joining of tables on themselves, but rather using IN), then everything works without a hiccup:


SELECT
   
P1.ID ,
   R1.description

   P1.BOOK

   P1.NOMINAL

   P1.NOMINAL * R1.Factor

FROM

   P_TABLE P1,

   R_TABLE R1

WHERE

   P1.IN_ID = R1.IN_ID  AND

   R1.IN_ID in (

     select
             R2.IN_ID

     from
             P_TABLE P2,

             R_TABLE R2

     where
             P2.IN_ID = R2.IN_ID AND

             P2.NOMINAL <> 0  AND

             R2.IType='X'

     GROUP BY R2.IN_ID
     HAVING COUNT(*) >1

   )



So, this leads me to believe that SELECT statements such as this causes a problem within Derby:


select

   *

from

   TABLE T1,

    (

        select <field> from TABLE T2

    ) as M

where

   M.<field> = T1.<field>


----- Forwarded by Pavel Bortnovskiy/JEFCO on 01/20/2011 03:55 PM -----
From: Pavel Bortnovskiy/JEFCO
To: Derby Discussion <derby-user@db.apache.org>
Date: 01/20/2011 01:28 PM
Subject: Can it be that Derby (in-memory) is deadlocking on this query?





Hello:


while running my application, I noticed that when the following query (which uses a subquery with tables joining on themselves) is executed, the application processes 185 records and then sits indefinitely in ResultSet.next() method:


SELECT
   P1.ID ,

   R1.description

   P1.BOOK

   P1.NOMINAL

   P1.NOMINAL * R1.Factor

FROM

   P_TABLE P1,

   R_TABLE R1,

   (

     select
             R2.IN_ID

     from
             P_TABLE P2,

             R_TABLE R2

     where
             P2.IN_ID = R2.IN_ID AND

             P2.NOMINAL <> 0  AND

             R2.IType='X'

     GROUP BY R2.IN_ID
     HAVING COUNT(*) >1

   ) as MULTI

WHERE

   P1.IN_ID = R1.IN_ID  AND

   MULTI.IN_ID = R1.IN_ID



Then I tried running AquaDataStudio with this query and it's been over 16 minutes without any results back:




However, when I run the subquery itself, it executes practically instanteneously:




And if I replace the subquery with where
R1.IN in ('P32764', 'P32765', ... [all results from subquery]), it executes in a few ms:



I have a suspicion that Derby (which is running in in-memory only mode) is deadlocking.

What can I do on my end (without exposing our data) to help you diagnose this.


Please respond as soon as you can, since this is quite important and urgent.


Thank you,

Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.






Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.