Once again – i've posted wrong procedure results L

 Server: Firebird 2.5.4.

 I have following stored procedure:

 ALTER PROCEDURE TEST_XXXX (

    I SMALLINT)

RETURNS (

    SUMA NUMERIC(15,2),

    A NUMERIC(15,2),

    B NUMERIC(15,2),

    C INTEGER,

    D VARCHAR(10),

    E VARCHAR(10))

AS

begin

for select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from xxxx t

    group by c, d, e

    order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING

    into :suma, :a, :b, :c, :d, :e

    do suspend;

end

 If i run it with i = 0 it gives wrong sort order.

 SUMA  A             B             C             D             E
-1,1        1,2          2,3          1             11           3
-1,8        3,4          5,2          2             11           3
0             3             3             2             12           2
12           14           2             1             11           1

Otherwise the sort order is ok.
 This propagates only when there is iif within iif clause.
 Am i doing something wrong or it is a bug ?

 Best regards

 Bogdan

 =====  complete script:

 SET SQL DIALECT 3;

 /******************************************************************************/

/****                          Stored procedures                           ****/

/******************************************************************************/

 SET TERM ^ ;

 CREATE PROCEDURE TEST_XXXX (

    I SMALLINT)

RETURNS (

    SUMA NUMERIC(15,2),

    A NUMERIC(15,2),

    B NUMERIC(15,2),

    C INTEGER,

    D VARCHAR(10),

    E VARCHAR(10))

AS

BEGIN

  SUSPEND;

END^

SET TERM ; ^

/******************************************************************************/

/****                                Tables                                ****/

/******************************************************************************/

CREATE TABLE XXXX (

    A  NUMERIC(15,2),

    B  NUMERIC(15,2),

    C  INTEGER,

    D  VARCHAR(10) COLLATE PXW_SLOV,

    E  VARCHAR(10) COLLATE PXW_SLOV

);

/******************************************************************************/

/****                          Stored procedures                           ****/

/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER PROCEDURE TEST_XXXX (

    I SMALLINT)

RETURNS (

    SUMA NUMERIC(15,2),

    A NUMERIC(15,2),

    B NUMERIC(15,2),

    C INTEGER,

    D VARCHAR(10),

    E VARCHAR(10))

AS

begin

for select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from xxxx t

    group by c, d, e

    order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING

    into :suma, :a, :b, :c, :d, :e

    do suspend;

end^

SET TERM ; ^

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (1.2, 2.3, 1, '11', '3');

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (3.4, 5.2, 2, '11', '3');

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (0, 1, 2, '12', '2');

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (3, 2, 2, '12', '2');

INSERT INTO XXXX (A, B, C, D, E)

          VALUES (14, 2, 1, '11', '1');

COMMIT WORK;