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 <lily...@yahoo.com>
To:
pbortnovs...@jefferies.com
Cc:
lily...@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 <pbortnovs...@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.<<image/gif>>
<<image/gif>>
<<image/gif>>
