Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Collation errors with [type of] <domain>, type of column [CORE3401] #3766

Closed
firebird-issue-importer opened this issue Mar 22, 2011 · 7 comments

Comments

@firebird-issue-importer

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).

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 22, 2011

Commented by: @asfernandes

I'm resolving this for 3.0 only, cause it have two potential breaking changes.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 22, 2011

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 22, 2011

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

Link: This issue is related to QA561 [ QA561 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 28, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

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).

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 23, 2015

Commented by: @pcisar

Test created.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 23, 2015

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment