Hello Sascha,

a> i have the following two statements that i expect to deliver the same
a> results and they did so on Firebird SS-1.5.3.4870.nptl.i686. Yesterday
a> we updated to Firebird 2.0.0.12748-0.nptl.i686 and since then the first
a> statement returns nothing and the second statement returns the expected
a> row, but afaik has a worse execution time:

a> 1:
a> SELECT TYRE_STOCK_LOCATION,DESCRIPTION FROM TYRE_STOCK_LOCATIONS WHERE
a> TYRE_STOCK = 1 AND TYRE_STOCK_LOCATION NOT IN (SELECT
a> TYRE_STOCK_LOCATION FROM TYRE_STORAGES)
Try to alias:
SELECT A.TYRE_STOCK_LOCATION, A.DESCRIPTION
FROM TYRE_STOCK_LOCATIONS A
WHERE
A.TYRE_STOCK = 1 AND A.TYRE_STOCK_LOCATION NOT IN
(SELECT B.TYRE_STOCK_LOCATION FROM TYRE_STORAGES B)

a> 2:
a> SELECT TYRE_STOCK_LOCATION,DESCRIPTION FROM TYRE_STOCK_LOCATIONS A WHERE
a> TYRE_STOCK = 1 AND NOT EXISTS (SELECT TYRE_STOCK_LOCATION FROM
a> TYRE_STORAGES B WHERE B.TYRE_STOCK_LOCATION = A.TYRE_STOCK_LOCATION)
If you use aliases, prefix fields with those aliases everywhere:
SELECT A.TYRE_STOCK_LOCATION, A.DESCRIPTION
FROM TYRE_STOCK_LOCATIONS A
WHERE
A.TYRE_STOCK = 1 AND
NOT EXISTS(SELECT B.TYRE_STOCK_LOCATION
FROM TYRE_STORAGES B
WHERE B.TYRE_STOCK_LOCATION = A.TYRE_STOCK_LOCATION)

Have you converted your DB to a new ODS of FB2 (v.11)?


--
Best regards,
Pavel Menshchikov