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

CREATE COLLATION does not verify base collation charset [CORE4180] #4506

Closed
firebird-issue-importer opened this issue Aug 13, 2013 · 9 comments

Comments

@firebird-issue-importer

Submitted by: @pavel-zotov

C:\>chcp 1251
<message in national lang. here>: 1251

C:\>C:\1Install\fb30\isql localhost/3330:empty30 -user sysdba -pas masterke -n -ch win1251
Database: localhost/3330:empty30, User: sysdba
SQL> create collation ci_coll for win1251 from unicode case insensitive;
SQL> commit;
SQL> recreate table tci(id int, name varchar(30) character set win1251 collate ci_coll);
SQL> commit;
SQL> insert into tci(id, name) values(1, 'one row');
SQL> insert into tci(id, name) values(2, 'oNE rOW');
SQL> insert into tci(id, name) values(3, 'ONE ROW');
SQL> commit;
SQL> select distinct name from tci;

NAME

one row
oNE rOW
ONE ROW

SQL> show table tci;
ID INTEGER Nullable
NAME VARCHAR(30) CHARACTER SET WIN1251 Nullable
COLLATE CI_COLL
SQL>

SQL> show version;
ISQL Version: WI-T3.0.0.30583 Firebird 3.0 Alpha 1
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.30583 Firebird 3
.0 Alpha 1"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.30583 Firebird 3
.0 Alpha 1/tcp (CSMIRROR)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.30583 Firebir
d 3.0 Alpha 1/tcp (CSMIRROR)/P13"
on disk structure version 12.0

---------
PS. The statement like this: create collation ci_coll for win1251 from unicode case insensitive; - was fault in FB 2.5.x with message: COLLATION UNICODE for CHARACTER SET WIN1251 is not defined

Commits: f6d7855 FirebirdSQL/fbt-repository@4e55a19

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 14, 2013

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 15, 2013

Commented by: @asfernandes

FB 2.5 is right, this is the bug here.

What you want in the correct form is:

create collation win1252_unicode for win1252; -- this is special collation named (charset_unicode)
create collation ci_coll for win1252 from win1252_unicode case insensitive;

commit;
recreate table tci(id int, name varchar(30) character set win1252 collate ci_coll);
commit;
insert into tci(id, name) values(1, 'one row');
insert into tci(id, name) values(2, 'oNE rOW');
insert into tci(id, name) values(3, 'ONE ROW');
commit;
select distinct name from tci;

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 15, 2013

Modified by: @asfernandes

summary: Wrong results after creating collation or win1251 from unicode case insensitive: strings are considered as distinct while they don't => CREATE COLLATION does not verify base collation charset

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 15, 2013

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 15, 2013

Commented by: @pavel-zotov

Adriano,

I never work with charset win1252 in practice, it't not actual for me.
As of win1251 charset I can get case insens. collation in it using 'create ... for utf8 from unicode' - as in 2.5

This ticket appeared due to occasional error: I've mistaked with choise of 'target' and typed 'for WIN1251' instead of 'for UTF8' - and compiler did not decline this in 3.0.

------------
OFFTOP.
Can you please translate this document:

http://asfernandes.blogspot.ru/2009/12/integracao-da-jvm-ao-firebird.html

- in English (or just write something in doc/readme.* about integrating java and Firebird) ?
I can not apply google "machine translator" because of very bad quality of such tools :-)

TIA, Pavel.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 15, 2013

Commented by: @asfernandes

Where I said win1252, it could be win1251 as well.

About the FB/Java document, this was a work for university, it's verbose and difficult to translate.

There is more concise and in English documentation here: http://sourceforge.net/p/firebird/code/58496/tree/java-plugin/trunk/src/etc/plugin/fbjava-doc.pdf?format=raw

But note, the plugin is not working with latest core code. I'll work on this.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 15, 2013

Commented by: @pavel-zotov

Adriano, thank you very much.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 27, 2014

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 30, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

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

No branches or pull requests

2 participants