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: [email protected] To: [email protected] 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.
