At 01:49 PM 16/03/2004 +0100, you wrote:

>Hi
>
>I wonder if the occasional problems with subselects are resolved in FB1.5 ??
>
>Here is the excerpt from our procedure:
>
> SELECT VALID_FROM, PRICE FROM MATERIALCENA
> WHERE MATERIALid = :IMATERIALID AND VALID_FROM =
> (select max(MC.VALID_FROM from materialcena mc where mc.materialid =
>:imaterialid and mc.VALID_FROM <= 'today')
> INTO :VALID_FROM, :PRICE;
>
>Suddenly subselect didn't return max(valid_from)

It is not a very safe query. This should be more robust:

SELECT
m.VALID_FROM,
m.PRICE
FROM MATERIALCENA m
WHERE
m.MATERIALid = :IMATERIALID
AND m.VALID_FROM =
(select max(MC.VALID_FROM from materialcena mc
where mc.materialid = :imaterialid
and mc.VALID_FROM <= 'today')
INTO :VALID_FROM, :PRICE;

>After we restarted the server, the results are ok again

Probably a coincidence.

>PS.: this problem should have been solved years ago

It was solved years ago. It's really up to you to write good SQL to avoid
unpredictable results like you have observed. Fb 1.5 is much tighter on
these multi-table queries than its predecessors. To make use of the
improvement, write tight, clean SQL. Laxness with aliasing in self-referent
queries isn't clean.

/heLen