Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
-Original Message- From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com] Sent: Friday, October 13, 2017 6:42 PM To: firebird-devel@lists.sourceforge.net Subject: Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows >> > SQL> SELECT FIELD_UTF_SV_CI_NUM > CON> FROM TABLE_T > CON> WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b' > CON> ORDER BY 1; > > FIELD_UTF_SV_CI_NUM > > 1 > 2 > 10 > 20 > a > A > aa > Aa > AA > b > B > > Adriano, is that with or without index? > > Ah, sorry, this is the problem and I now see it again. I can check it in some days. Please add it to the tracker. Thank you, I will do that. /Magnus -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
On 13/10/2017 13:28, Magnus Johansson wrote: > -Original Message- > From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com] > Sent: Friday, October 13, 2017 6:11 PM > To: firebird-devel@lists.sourceforge.net > Subject: Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows > > On 12/10/2017 13:49, Vlad Khorsun via Firebird-devel wrote: >> 12.09.2017 15:33, Adriano dos Santos Fernandes wrote: >>> On 12/09/2017 08:46, Magnus Johansson wrote: >> ... >>>> SELECT FIELD_UTF_SV_CI_NUM >>>> FROM TABLE_T >>>> WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b' >>>> ORDER BY 1; >>>> FIELD_UTF_SV_CI_NUM >>>> === >>>> 1 >>>> 2 >>>> 10 >>>> 20 >>>> a >>>> A >>>> aa >>>> Aa >>>> AA >>>> b >>>> === >>>> Not the expected result. >>>> Where did the 'B' go? >>> This should be verified. >> Adriano, did you investigate it ? May i help somehow ? >> > SQL> SELECT FIELD_UTF_SV_CI_NUM > CON> FROM TABLE_T > CON> WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b' > CON> ORDER BY 1; > > FIELD_UTF_SV_CI_NUM > > 1 > 2 > 10 > 20 > a > A > aa > Aa > AA > b > B > > So what is the problem? > > Adriano, is that with or without index? > > Ah, sorry, this is the problem and I now see it again. I can check it in some days. Please add it to the tracker. Adriano -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
-Original Message- From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com] Sent: Friday, October 13, 2017 6:11 PM To: firebird-devel@lists.sourceforge.net Subject: Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows On 12/10/2017 13:49, Vlad Khorsun via Firebird-devel wrote: > 12.09.2017 15:33, Adriano dos Santos Fernandes wrote: >> On 12/09/2017 08:46, Magnus Johansson wrote: > ... >>> SELECT FIELD_UTF_SV_CI_NUM >>> FROM TABLE_T >>> WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b' >>> ORDER BY 1; >>> FIELD_UTF_SV_CI_NUM >>> === >>> 1 >>> 2 >>> 10 >>> 20 >>> a >>> A >>> aa >>> Aa >>> AA >>> b >>> === >>> Not the expected result. >>> Where did the 'B' go? >> >> This should be verified. > > Adriano, did you investigate it ? May i help somehow ? > SQL> SELECT FIELD_UTF_SV_CI_NUM CON> FROM TABLE_T CON> WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b' CON> ORDER BY 1; FIELD_UTF_SV_CI_NUM 1 2 10 20 a A aa Aa AA b B So what is the problem? Adriano, is that with or without index? Regards, Magnus -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
On 12/10/2017 13:49, Vlad Khorsun via Firebird-devel wrote: > 12.09.2017 15:33, Adriano dos Santos Fernandes wrote: >> On 12/09/2017 08:46, Magnus Johansson wrote: > ... >>> SELECT FIELD_UTF_SV_CI_NUM >>> FROM TABLE_T >>> WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b' >>> ORDER BY 1; >>> FIELD_UTF_SV_CI_NUM >>> === >>> 1 >>> 2 >>> 10 >>> 20 >>> a >>> A >>> aa >>> Aa >>> AA >>> b >>> === >>> Not the expected result. >>> Where did the 'B' go? >> >> This should be verified. > > Adriano, did you investigate it ? May i help somehow ? > SQL> SELECT FIELD_UTF_SV_CI_NUM CON> FROM TABLE_T CON> WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b' CON> ORDER BY 1; FIELD_UTF_SV_CI_NUM 1 2 10 20 a A aa Aa AA b B So what is the problem? Adriano -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
12.09.2017 15:33, Adriano dos Santos Fernandes wrote: On 12/09/2017 08:46, Magnus Johansson wrote: ... SELECT FIELD_UTF_SV_CI_NUM FROM TABLE_T WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b' ORDER BY 1; FIELD_UTF_SV_CI_NUM === 1 2 10 20 a A aa Aa AA b === Not the expected result. Where did the 'B' go? This should be verified. Adriano, did you investigate it ? May i help somehow ? Regards, Vlad -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
> -Original Message-
> From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com]
> Sent: Tuesday, September 12, 2017 2:34 PM
> To: firebird-devel@lists.sourceforge.net
> Subject: Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
>
> > CREATE COLLATION ISO_SV_CI
> >FOR ISO8859_1
> >FROM SV_SV
> >CASE INSENSITIVE;
>
> This is old collation inherited from Interbase or
> earlier developed in Firebird before ICU adoption. It probably is not
> suitable for
> CASE INSENSITIVE or others options.
Good info, great to know.
Then I guess I can omit that as an option and concentrate on charset UTF8.
> If you want to use ICU with ISO8859_1, you should use ISO8859_1_UNICODE in
> FROM.
Not that it probably matters for me anymore based on what you wrote above, but
for the record I tried that with both the original ICU and ICU for 52.1, both
gives:
"COLLATION ISO8859_1_UNICODE for CHARACTER SET ISO8859_1 is not defined"
Although I can see that it exists in fbintl.conf:
charset = ISO8859_1 {
intl_module = fbintl
collation = ISO8859_1
collation = ISO8859_1_UNICODE
...
}
> > SELECT FIELD_UTF_SV_CI_NUM
> > FROM TABLE_T
> > WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b'
> > ORDER BY 1;
> >
> > FIELD_UTF_SV_CI_NUM
> > ===
> > 1
> > 2
> > 10
> > 20
> > a
> > A
> > aa
> > Aa
> > AA
> > b
> > ===
> > Not the expected result.
> > Where did the 'B' go?
>
> This should be verified.
Is there anything I can do here?
After looking a little more on this it seems that it is the last record that is
missing.
> You can also test:
>
> CREATE COLLATION UTF_SV_CI_NUM
> FOR UTF8
> FROM UNICODE
> CASE INSENSITIVE
> 'LOCALE=sv_SE;NUMERIC-SORT=1;MULTI-LEVEL=0';
I read about MULTI-LEVEL in README.intl.txt and must confess that I'm not sure
of what that means.
However, I can't see any difference with my tests using it in either way.
Adriano, thanks for your time on this.
And judging by your comments I take it that the steps I had taken and described
in how to replace the ICU libraries was correct and sufficient?
Regards,
Magnus Johansson
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
On 12/09/2017 08:46, Magnus Johansson wrote: > > After I replaced the ICU libraries as described before, I created a database, > some collations and a table with some data: ... > > > CREATE COLLATION ISO_SV_CI >FOR ISO8859_1 >FROM SV_SV >CASE INSENSITIVE; This has nothing to do with ICU. This is old collation inherited from Interbase or earlier developed in Firebird before ICU adoption. It probably is not suitable for CASE INSENSITIVE or others options. If you want to use ICU with ISO8859_1, you should use ISO8859_1_UNICODE in FROM. > > SELECT FIELD_UTF_SV_CI_NUM > FROM TABLE_T > WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b' > ORDER BY 1; > > FIELD_UTF_SV_CI_NUM > === > 1 > 2 > 10 > 20 > a > A > aa > Aa > AA > b > === > Not the expected result. > Where did the 'B' go? This should be verified. You can also test: CREATE COLLATION UTF_SV_CI_NUM FOR UTF8 FROM UNICODE CASE INSENSITIVE 'LOCALE=sv_SE;NUMERIC-SORT=1;MULTI-LEVEL=0'; Adriano -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
> -Original Message-
> From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com]
> Sent: Tuesday, September 12, 2017 1:21 PM
> To: firebird-devel@lists.sourceforge.net
> Subject: Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
>
> On 12/09/2017 08:10, Magnus Johansson wrote:
> >
> > But when testing, the result is not as expected.
> >
> >
> It will be difficult to help you with this problem description.
>
>
> Adriano
>
Hi Adriano,
I was hoping you would be so kind to answer. :-)
I perfectly understand that, but since I didn't want to do another lengthy post
I thought that I should break it up.
Anyhow, here is an excerpt from my original post.
I might as well add that it involves collation with Numeric-Sort and index.
After I replaced the ICU libraries as described before, I created a database,
some collations and a table with some data:
SET NAMES UTF8;
SET SQL DIALECT 3;
CREATE DATABASE 'C:\Data\Test\Collation_CI.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8;
SHOW VERSION;
ISQL Version: WI-V3.0.3.32802 Firebird 3.0
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V3.0.3.32802
Firebird 3.0"
on disk structure version 12.0
CREATE COLLATION UTF_SV_CI
FOR UTF8
FROM UNICODE
CASE INSENSITIVE
'LOCALE=sv_SE';
CREATE COLLATION UTF_SV_CI_NUM
FOR UTF8
FROM UNICODE
CASE INSENSITIVE
'LOCALE=sv_SE;NUMERIC-SORT=1';
CREATE COLLATION ISO_SV_CI
FOR ISO8859_1
FROM SV_SV
CASE INSENSITIVE;
COMMIT;
CREATE TABLE TABLE_T (
FIELD_UTF_SV_CI VARCHAR(5) CHARACTER SET UTF8 COLLATE UTF_SV_CI,
FIELD_UTF_SV_CI_NUM VARCHAR(5) CHARACTER SET UTF8 COLLATE
UTF_SV_CI_NUM,
FIELD_ISO_SV_CI VARCHAR(5) CHARACTER SET ISO8859_1 COLLATE ISO_SV_CI
);
COMMIT;
INSERT INTO TABLE_T VALUES ('1', '1', '1' );
INSERT INTO TABLE_T VALUES ('2', '2', '2' );
INSERT INTO TABLE_T VALUES ('10', '10', '10');
INSERT INTO TABLE_T VALUES ('20', '20', '20');
INSERT INTO TABLE_T VALUES ('a', 'a', 'a' );
INSERT INTO TABLE_T VALUES ('AA', 'AA', 'AA');
INSERT INTO TABLE_T VALUES ('aa', 'aa', 'aa');
INSERT INTO TABLE_T VALUES ('Aa', 'Aa', 'Aa');
INSERT INTO TABLE_T VALUES ('A', 'A', 'A' );
INSERT INTO TABLE_T VALUES ('b', 'b', 'b' );
INSERT INTO TABLE_T VALUES ('B', 'B', 'B' );
INSERT INTO TABLE_T VALUES ('o', 'o', 'o' );
INSERT INTO TABLE_T VALUES ('O', 'O', 'O' );
INSERT INTO TABLE_T VALUES ('x', 'x', 'x' );
INSERT INTO TABLE_T VALUES ('X', 'X', 'X' );
INSERT INTO TABLE_T VALUES ('å', 'å', 'å' );
INSERT INTO TABLE_T VALUES ('Å', 'Å', 'Å' );
INSERT INTO TABLE_T VALUES ('ä', 'ä', 'ä' );
INSERT INTO TABLE_T VALUES ('Ä', 'Ä', 'Ä' );
INSERT INTO TABLE_T VALUES ('ö', 'ö', 'ö' );
INSERT INTO TABLE_T VALUES ('Ö', 'Ö', 'Ö' );
COMMIT;
Now some tests.
SELECT FIELD_UTF_SV_CI
FROM TABLE_T
WHERE FIELD_UTF_SV_CI BETWEEN '' AND 'b'
ORDER BY 1;
FIELD_UTF_SV_CI
===
1
10
2
20
a
A
aa
Aa
AA
b
B
===
Result as expected
SELECT FIELD_UTF_SV_CI_NUM
FROM TABLE_T
WHERE FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b'
ORDER BY 1;
FIELD_UTF_SV_CI_NUM
===
1
2
10
20
a
A
aa
Aa
AA
b
B
===
Result as expected
SELECT FIELD_ISO_SV_CI
FROM TABLE_T
WHERE FIELD_ISO_SV_CI BETWEEN '' AND 'b'
ORDER BY 1;
FIELD_ISO_SV_CI
===
1
10
2
20
A
a
Ä
Å
Ö
ä
å
ö
AA
Aa
aa
B
b
===
Not the expected result.
Both wrong order and records that should not
Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
On 12/09/2017 08:10, Magnus Johansson wrote: > > But when testing, the result is not as expected. > > It will be difficult to help you with this problem description. Adriano -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] FB 3.0 Replacing ICU libraries on Windows
Hi, I have some problems with collation for Swedish. I did a rather lengthy post about the original problem in Firebird-Support but I guess that it was too much to read or perhaps not well written because it went without a reply. So I thought that just to exclude the basic I should start with if I did something wrong when replacing the existing ICU libraries. This is what I have done. First I have downloaded the snapshot for FB 3.0.3 for Windows (Firebird-3.0.3.32802-0_x64). Then I went to http://site.icu-project.org/download and downloaded icu4c-52_1-Win64-msvc10.zip from where I got the icu*52.dll's and extracted them to the FB3 folder, replacing the existing ones. No changes done in intl\fbintl.conf or anything else. After that I can create a collation like: CREATE COLLATION UTF_SV_CI_NUM FOR UTF8 FROM UNICODE CASE INSENSITIVE 'LOCALE=sv_SE;NUMERIC-SORT=1'; But when testing, the result is not as expected. The documentation I have found is rather sparse and not always clear if it applies to FB 3.0 or earlier versions, so I just want to rule out that I haven't missed something obvious, or done something wrong when I replaced the existing ICU libraries. Hopefully someone here can shed some light on this and enlightening me. Regards, Magnus Johansson -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
