Hi,

I'm porting an application from FB1.5 to 2.x and a query that worked on 1.5
doesn't work on 2.0 or 2.1 (I haven't tried 2.5)

The query is:

SELECT u.Id, u.Name, u.Email FROM Punter u
WHERE u.ALIVE = 1
/* and they haven't blacklisted this issue */
AND u.Id not IN (SELECT bl.UserId FROM IssueBlacklist bl WHERE bl.IssueId
= 105)

/* and subsribed via the product */
AND u.id in (SELECT ps.UserId FROM ProductSubscription ps
JOIN IssueProduct ip ON (ps.ProductId
= ip.ProductId)
WHERE ip.IssueId = 105 )


With my test data I'm expecting 1 result, however I always get an empty
result.

If I replace the first subquery with a list of id's say 21,22,23 the query
behaves as expected.
If I remove the join in the second subquery the query behaves as expected.

The query always behaves as I'd expect on FB 1.5

I'm comparing Firebird 1.5.6 and 2.0.6
I'm executing the query in FlameRobin, running on WinXP, and for ease of
the testing I'm running Firbird as an application.

I can add a sql script to create a test database if anyone is interested.

Thanks
Paul


[Non-text portions of this message have been removed]