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: firebird-support@yahoogroups.com
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]