Hi Paul!
> 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.
I would have written your query slightly differently with Fb 2, probably
similar to (uncertain whether I would use DISTINCT in the CTE or use EXISTS
rather than JOIN to TMP):
WITH TMP AS
(SELECT DISTINCT ps.UserId
FROM ProductSubscription ps
JOIN IssueProduct ip ON ps.ProductId = ip.ProductId
LEFT JOIN IssueBlacklist bl on ps.UserId = bl.UserId
and ip.IssueId = bl.IssueId
WHERE ip.IssueId = 105
AND bl.UserId IS NULL)
SELECT u.Id, u.Name, u.Email
FROM Punter u
JOIN TMP t ON u.Id = t.UserId
WHERE u.ALIVE = 1
However, I cannot see why the query you use should produce different results on
Fb 1.5 and newer versions, particularly not when you say that it works if you
remove JOIN IssueProduct. So, although I doubt I could shed any more light to
your problem, it would be interesting to see your SQL script.
Set
