Hi Tom, I can partly confirm your findings - that is, I do observe incorrect ordering in Firebird 2.1.3.18185 when trying to order by a field in one of the tables I INNER JOIN - I notice no problems if I include one field from the table I LEFT JOIN to in the ORDER BY clause.

Here's what I did to duplicate your problem:

CREATE TABLE TEST_MAIN
(
PK INTEGER NOT NULL,
STATUS CHAR( 1) COLLATE NO_NO,
CONSTRAINT PK_TEST_MAIN PRIMARY KEY (PK)
);

CREATE TABLE TEST_SUB1
(
PK INTEGER NOT NULL,
MAIN_PK INTEGER,
CONSTRAINT PK_TEST_SUB1 PRIMARY KEY (PK)
);

CREATE TABLE TEST_SUB2
(
PK INTEGER NOT NULL,
SUB1_PK INTEGER,
MYCHAR CHAR( 1) COLLATE NO_NO,
CONSTRAINT PK_TEST_SUB2 PRIMARY KEY (PK)
);

commit;

CREATE VIEW TEST_VIEW
(
STATUS,
S1_PK,
S2_PK,
MYCHAR
) AS

SELECT DISTINCT M.STATUS, S1.PK, S2.PK, S2.MYCHAR
FROM TEST_MAIN M
JOIN TEST_SUB1 S1 ON M.PK = S1.MAIN_PK
LEFT JOIN TEST_SUB2 S2 ON S1.PK = S2.SUB1_PK
WHERE M.STATUS != 'V'
;

commit;

INSERT INTO TEST_MAIN (PK, STATUS) VALUES (1, 'A');
INSERT INTO TEST_MAIN (PK, STATUS) VALUES (2, 'B');
INSERT INTO TEST_MAIN (PK, STATUS) VALUES (3, 'V');
INSERT INTO TEST_MAIN (PK, STATUS) VALUES (4, 'C');
INSERT INTO TEST_SUB1 (PK, MAIN_PK) VALUES (1, 1);
INSERT INTO TEST_SUB1 (PK, MAIN_PK) VALUES (2, 1);
INSERT INTO TEST_SUB1 (PK, MAIN_PK) VALUES (3, 2);
INSERT INTO TEST_SUB1 (PK, MAIN_PK) VALUES (4, 3);
INSERT INTO TEST_SUB1 (PK, MAIN_PK) VALUES (5, 4);
INSERT INTO TEST_SUB2 (PK, SUB1_PK, MYCHAR) VALUES (1, 3, 'A');
INSERT INTO TEST_SUB2 (PK, SUB1_PK, MYCHAR) VALUES (2, 1, 'C');
INSERT INTO TEST_SUB2 (PK, SUB1_PK, MYCHAR) VALUES (3, 4, 'B');

commit;

Then, the result is identical whether I order by STATUS or S1_PK in ASCending or DESCending order, whereas if I add ordering by S2_PK things works as expected, e.g

SELECT * FROM TEST_VIEW
ORDER BY status desc

return the result set in a presumably incorrect order,
whereas

SELECT * FROM TEST_VIEW
ORDER BY status desc, S2_PK

orders as I would expect. I assume this to be an error that ought to be reported in the tracker (if it isn't already there), but maybe someone on this list can shed some further light on the problem?

Set