> 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.

- Same data in both tests I guess?
- Did you use 2.0.6 on the 1.5.6 database or have you run a
backup/restore cycle, thus using a Firebird 2.0 ODS then?
- How does the execution plan for both tests look like?

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

I can have a quick look if you like.

t . steinmaurer @ upscene . com



--
With regards,
Thomas Steinmaurer

* Upscene Productions - Database Tools for Developers
http://www.upscene.com/

* My Blog
http://blog.upscene.com/thomas/index.php

* Firebird Foundation Committee Member
http://www.firebirdsql.org/en/firebird-foundation/