Wow, to me it seems like you found an error in Firebird! Adding +0 to your query in the correct place and thus change the plan for the first subselect from PLAN JOIN to PLAN MERGE(SORT, gets the correct result:

SELECT t1.id FROM table1 t1
WHERE
t1.id IN (SELECT A.ID FROM table1 A
JOIN table1 B ON B.ID+0 = A.ID
WHERE B.ID IN(1, 2, 3))
AND
NOT t1.id IN (SELECT A1.ID FROM table1 A1
JOIN table1 B1 ON B1.ID = A1.ID
WHERE B1.ID IN(2, 3))

(well, I tested with an INTEGER ID, hence I didn't use the quotes. With a character ID, you have to use ||'' rather than +0)

I'm happy that I always use EXISTS rather than IN(subselect), since this doesn't have the same problem (although NOT IN is not exactly the same as NOT EXISTS when t1.id is NULL).

I've always thought of using +0 as a means of optimization, never a means to get the correct result...

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of klitscheronald
Sent: 16. november 2009 10:15
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Problem with joined subqueries

> Try it this way instead and report back:
>
> SELECT t1.id FROM table1 t1
> WHERE
> t1.id IN (SELECT A.ID FROM table1 A
> JOIN table1 B ON B.ID = A.ID
> WHERE B.ID IN('1', '2', '3'))
> AND
> NOT t1.id IN (SELECT A1.ID FROM table1 A1
> JOIN table1 B1 ON B1.ID = A1.ID
> WHERE B1.ID IN('2', '3'))
>
> ./helen

Hi Helen,

No, not working.

Ronald




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links