db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andreas Kyrmegalos <andreaskyrmega...@hotmail.com>
Subject RE: Inaccurate query results
Date Wed, 28 Jan 2015 22:38:11 GMT
I would venture to say that you have hit the max number of arguments allowed in the IN clause.
A join operation would be preferable.
e.g. select dg.DG_ID, dg.col2, dg.col3 etc from DISTROGROUP as dg join RECIPIENTS on dg.DG_ID=RECIPIENTS.DG_ID
where LOAD_STATUS is null

This is not restricted to derby alone. PostgreSQL has a practical limit of ~32k arguments
for such clauses. 
From: Michael.McCollough@emc.com
To: derby-user@db.apache.org
Date: Wed, 28 Jan 2015 14:03:11 -0500
Subject: Inaccurate query results

I have a simple database, two tables DISTROGROUP and RECIPIENTS, each have the field DG_ID
and this field is indexed I have 86,795 rows in DISTROGROUPI have 230,823 rows in RECIPIENTS
I am using the table to migrate items. I have processed almost all of them except ones where
I have a DISTROGROUP.LOAD_STATUS is NULL, these should be rows where there is a distribution
group but no recipients.
The query I used to process is select DG_ID, col2, col3, etc from DISTROGROUP where LOAD_STATUS
is NULL and DG_ID in (select DG_ID from RECIPIENTS). This returns 0 resultsHowever, I can
take one of the rows from DISTROGROUP that has a LOAD_STATUS = NULL, and I CAN match up the
DG_ID to a DG_ID in RECIPIENTS so why don’t I get results in the original query? I would
use a NOT IN query to separate out actual DISTROGROUP rows with no match, but I waited over
15 minutes for the NOT IN query before giving up on it. Any help/guidance is appreciated.
View raw message