Submitted by: @paulvink

Is related to QA561

Sometimes, the collation isn't included when casting to [type of] <domain> or type of column, or when declaring PSQL vars/params this way.

(Preparation)
-------------

-- create a case-insensitive domain:
create domain dom_ci as varchar(40) character set utf8 collate unicode_ci;
commit;

-- create two case-insensitive columns: one via the domain, one directly:
create table t(
col_ci_dom dom_ci,
col_ci_dir varchar(40) character set utf8 collate unicode_ci
);
commit;

(Case 1)
--------

-- test 'a' = 'A' when casting to type of column col_ci_dir:

with q(a_lower, a_upper) as (
select cast('a' as type of column t.col_ci_dir),
cast('A' as type of column t.col_ci_dir)
from rdb$database
)
select a_lower, a_upper, case when a_lower = a_upper then 1 else 0 end equal from q;

-- columns a_lower, a_upper are described as having charset 4 (UTF8), collation 3 (UNICODE_CI), but:
-- result: 'a', 'A', 0 !!!

(When casting to "dom_ci", "type of dom_ci", or "type of column t.col_ci_dom", the result is always 1, as it should be.)

(Case 2)
--------

-- test 'a' = 'A' in domain dom_ci, using local vars in an executable block:

execute block returns (equal smallint)
as
declare a dom_ci = 'a';
declare b dom_ci = 'A';
begin
equal = case when a = b then 1 else 0 end;
suspend;
end

--returns 1 (OK)

(With "type of dom_ci" and "type of column t.col_ci_dom", the result is also 1. But with "type of column t.col_ci_dir", the result is 0.)

-- same test, now using input params:

execute block (a dom_ci = ?, b dom_ci = ?) returns (equal smallint)
as
begin
equal = case when a = b then 1 else 0 end;
suspend;
end

-- input params are described as having charset 4 (UTF8), collation 3 (UNICODE_CI)
-- fill in parameters 'a' and 'A', execute, and result is 0

(Same descriptions and result with "type of dom_ci", "type of column t.col_ci_dom" and "type of column t.col_ci_dir".)

(Case 3)
--------

-- test 'a' = 'A' in domain dom_ci, using a stored procedure with input params:

create procedure selproc_dom(a dom_ci, b dom_ci) returns (equal smallint)
as
begin
equal = case when a = b then 1 else 0 end;
suspend;
end

select * from selproc_dom('a', 'A')

-- returns 1

select * from selproc_dom(?, ?)

-- input params are described as having charset 4 (UTF8), collation 3 (UNICODE_CI)
-- fill in 'a' and 'A', execute, and result is 1

(Same results if arguments are "type of dom_ci" or "type of column t.col_ci_dom". However, with "type of column t.col_ci_dir":)

create procedure selproc_typeof_col_ci_dir(a type of column t.col_ci_dir, b type of column t.col_ci_dir) returns (equal smallint)
as
begin
equal = case when a = b then 1 else 0 end;
suspend;
end

select * from selproc_typeof_col_ci_dir('a', 'A')

-- returns 0

select * from selproc_typeof_col_ci_dir(?, ?)

-- input columns are now described as charset 4, collation 0!! fill in 'a' an 'A', execute, and result is 0.
-- well, at least here the result is consistent with the column description.

Commits: aeefde3

====== Test Details ======

Test compares many NON-ascii characters with diacritical marks - it was done intentionally.
All the cases successfully PASSED on WI-T3.0.0.31767 (Win XP) and LI-T3.0.0.31719 (Ubuntu 14.10).