Hello Set,

>
> This puzzles me, although I'm uncertain whether I'm puzzled by upper(2) not working with UNION or puzzled by upper(2) working without UNION.
>

Having a closer look, it appears that the resultset of the non-union query is not properly sorted.

> Here, I first wrote that it works when using a CTE, but then I discovered that it only works in the sense that it produces a result set, the ordering seems random. At least, the following simple SQL produces incorrect ordering:
>
> Select rdb$collation_name
> From rdb$collations
> Order by upper(1)
>
> This on Firebird 2.5.1.
>
> Hence, I think what you want to order by must be selected, i.e.
>
> With tmp as
> (select "TESTID", "NAME", upper(name) as MyOrdering from test
> union
> select "TESTID", "NAME", upper(name) from test order by 3)
>
> select "TESTID", "NAME"
> from tmp
>
> (I haven't tested this last query, just expects it to be returned correctly ordered).
>

I think your solution should work. I found another solution that does work without the need of an extra column in the select clause:

"select "TESTID", "NAME" from test
union select "TESTID", "NAME" from test order by 2 COLLATE UNICODE_CI"

Thank you for your effort,

Roy