Commented by: Sean Leyne (seanleyne)

Where these values reported by ISQL or another database tool?

Commented by: @dyemanov

This is a known issue, caused by the lack of common sub-expression elimination in Firebird. The sub-query will be executed twice (in the ORDER BY clause and in the select list), hence two plans reported.

Commented by: Maxim Filatov (mnf)

In some case this lead to unpredictably results. Example:

CREATE TABLE CALC (
ID INTEGER NOT NULL,
RESULT DOUBLE PRECISION DEFAULT 0
);

COMMIT WORK;

INSERT INTO calc (id, result) VALUES (1, -1);
INSERT INTO calc (id, result) VALUES (2, 2);
INSERT INTO calc (id, result) VALUES (3, -3);
INSERT INTO calc (id, result) VALUES (0, 0);

COMMIT WORK;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE REVERSE_CALC (
ID INTEGER)
RETURNS (
RESULT DOUBLE PRECISION)
AS
BEGIN
SELECT result FROM calc WHERE id = :id INTO :result;
UPDATE calc SET result = - :result WHERE id = :id;
result = COALESCE(result, 0);
SUSPEND;
END
^

SET TERM ; ^

COMMIT WORK;

SELECT (SELECT result FROM reverse_calc(http://c.id)) AS get_result,
c.*
FROM calc c
ORDER BY 1