|
Raised priority to critical since this bug makes it impossible to use Firebird with UTF-8 data.
Start with no database.
$ isql -z ISQL Version: LI-V2.1.1.17910 Firebird 2.1 $ isql CREATE DATABASE 'localhost:/tmp/test_db.fdb' USER 'sysdba' PASSWORD 'password' PAGE_SIZE=16384 DEFAULT CHARACTER SET UTF8; CREATE COLLATION UNICODE_NOPAD FOR UTF8 FROM UNICODE NO PAD; UPDATE RDB$CHARACTER_SETS SET RDB$DEFAULT_COLLATE_NAME = 'UNICODE_NOPAD' WHERE RDB$CHARACTER_SET_NAME = 'UTF8'; COMMIT; exit; $isql CONNECT 'localhost:/tmp/test_db.fdb' USER 'sysdba' PASSWORD 'password'; CREATE TABLE tst1 ( k1 VARCHAR(3), k2 INT, k3 CHAR(1) , PRIMARY KEY (k1, k2, k3) ); CREATE TABLE tst2 ( k1 VARCHAR(3), k2 INT, k3 CHAR(1) , PRIMARY KEY (k1, k2, k3) ); COMMIT; INSERT INTO tst1 VALUES ('AP', 123, ' '); INSERT INTO tst2 VALUES ('AP', 123, ' '); INSERT INTO tst1 VALUES ('HEL', 666, 'V'); INSERT INTO tst2 VALUES ('HEL', 666, 'V'); COMMIT; SELECT t1.* FROM tst1 t1, tst2 t2 WHERE 1 = 1 AND t1.k1 = t2.k1 AND t1.k2 = t2.k2 AND t1.k3 = t2.k3; ----------------------------------------------------------------------------------------------------------------------------------------- I get no result from the SELECT statement. If I don't exit isql between creating the schema and running the commands I get the expected result. > If I don't exit isql between creating the schema and running the commands I get the expected result.
What if you issue SET NAMES UTF8 *before* CONNECT ? Well, SET NAMES doesn't matters there.
When tables created without re-connect to the database, its columns have collate UTF8. When tables created after re-connect to the database, its columns have collate UNICODE_NOPAD. So, UPDATE RDB$CHARACTER_SETS have no effect on database until metadata cache is reloaded. Update of system tables has never been a supported feature, and this specific one is know to cause problem after backup/restore. Anyway, it appears to work in HEAD (and probably in 2.5 too).
Also, 2.5 has ALTER CHARACTER SET to set its default collation. So feel free to test it and report if it has any bug. I don't think ticket should be closed.
Here more simple example: CREATE COLLATION UNICODE_NOPAD FOR UTF8 FROM UNICODE NO PAD; COMMIT; -- (1) RECREATE TABLE tst1_nopad ( k1 VARCHAR(3) COLLATE UNICODE_NOPAD, k2 INT, k3 CHAR(1) COLLATE UNICODE_NOPAD, PRIMARY KEY (k1, k2, k3) ); COMMIT; INSERT INTO tst1_nopad VALUES ('AP', 123, ' '); INSERT INTO tst1_nopad VALUES ('HEL', 666, 'V'); COMMIT; SELECT t1.* FROM tst1_nopad t1 WHERE t1.k1 = 'AP' AND t1.k2 = 123 AND t1.k3 = ' '; no rows SELECT t1.* FROM tst1_nopad t1 WHERE t1.k1 = 'AP' AND t1.k2 = 123 AND t1.k3 = ' ' PLAN (T1 NATURAL) one row as result. The reason is that BTR_key (used to insert index key) and BTR_make_key (used to search a row value) creates different keys for the same (?) values. Also, note, without COMMIT at point (1), fields of tst1_nopad will have collate UTF8 ! Reason of collation not being used when doing things in the same transaction is because collation id is generated in DFW but used before it, so it's used as 0 (no collation, just the charset). I'm not going to fix this in 2.5 and older versions.
Second problem of incorrect lookup of the string is not because BTR_key/BTR_make_key usage. It's because a text descriptor is used without shrinking it with INTL_adjust_text_descriptor. As the collation is NO-PAD, that matters. > Reason of collation not being used when doing things in the same transaction is because collation id is generated in DFW but used before it, so it's used as 0 (no collation, just the charset). I'm not going to fix this in 2.5 and older versions.
It should be properly documented, IMHO > Second problem of incorrect lookup of the string is not because BTR_key/BTR_make_key usage. It's because a text descriptor is used without shrinking it with INTL_adjust_text_descriptor. As the collation is NO-PAD, that matters. Is it possible to backport INTL_adjust_text_descriptor into 2.5 and 2.1 ? Try to create index on CHAR(1) COLLATE UNICODE_NOPAD field and you'll see why i ask for backporting ;) > It should be properly documented, IMHO
I'll create another ticket for it. IMO, 2.5.0 could live without it. First HEAD, then 2.5.X later. > Is it possible to backport INTL_adjust_text_descriptor into 2.5 and 2.1 ? Try to create index on CHAR(1) COLLATE UNICODE_NOPAD field and you'll see why i ask for backporting ;) The function is there, probably with another name and static in evl.cpp. I didn't verified yet. I'll do later. BTW, for documenting purposes, it's not the lookup that's incorrect, but the stored keys. And what about single-segment index keys ?
> And what about single-segment index keys ?
Oh, I forgot again to do the usual text find to look for duplicate blocks of code! Will do late too, when I have more time and commits are allowed. Thanks for review. Thanks !
PS In both 2.0 and 2.1 there is EVL\adjust_text_descriptor() ;) |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CORE-1997and/orCORE-2232.