select * from (select "TESTID", "NAME" from test union select "TESTID", "NAME" from test) t order by upper(2)
Best Regards ========= || ISMAEL || ========= ----- Original Message ----- From: roydamman To: [email protected] Sent: Wednesday, June 27, 2012 12:22 PM Subject: [firebird-support] Unexpected behaviour when using union Configuration: - Windows 7 64 Bit professional SP1 NL - Firebird 2.5.1 64 Bit Table: CREATE TABLE "TEST" ( "TESTID" INTEGER NOT NULL, "NAME" VARCHAR(40), CONSTRAINT "TEST_PK" PRIMARY KEY ("TESTID") ); 2 records: insert into "TEST" ("TESTID", "NAME") values(1, 'EEN') insert into "TEST" ("TESTID", "NAME") values(2, 'twee') First query: "select "TESTID", "NAME" from test order by upper(2)" Answer (As expected): 1, EEN 2, twee Second query: "select "TESTID", "NAME" from test union select "TESTID", "NAME" from test order by 2" Answer (Unexpected): 1, EEN 2, twee The answer has only two rows. I would expect each row double, in total 4 rows. Is this normal behaviour? Third query: "select "TESTID", "NAME" from test union select "TESTID", "NAME" from test order by upper(2)" Answer (Unexpected): An error message: ISC ERROR CODE:335544569 ISC ERROR MESSAGE: Dynamic SQL Error SQL error code = -104 Invalid command invalid ORDER BY clause Especially the third query is annoying me. Is this according to the standard or is it a bug? Why is does "order by upper(2)" not work with an union and work without an union? [Non-text portions of this message have been removed]
