Issue Details (XML | Word | Printable)

Key: CORE-2826
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Adriano dos Santos Fernandes
Reporter: Håkan Johansson
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Join condition fails for UTF-8 databases.

Created: 26/Jan/10 02:23 PM   Updated: 03/May/11 11:45 AM
Component/s: None
Affects Version/s: 2.1.1
Fix Version/s: 2.5 RC3, 2.1.4, 3.0 Alpha 1

Time Tracking:
Not Specified

Environment:
Linux, 64bit, Red Hat Enterprise Linux Client release 5.4 (Tikanga)
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
Join conditions fail in some specific conditions when run on a database created for UTF-8 charset.

Steps to reproduce (I am not sure all is needed, but that is what our framework does):
CREATE DATABASE test_db.fdb USER 'sysdba' PASSWORD 'password' PAGE_SIZE=16384 DEFAULT CHARSET 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';

CREATE TABLE tst1 (
  k1 VARCHAR(3),
  k2 INT,
  k3 CHAR(1),
  PRIMARY KEY (k1, k2, k3)
)
INSERT INTO tst1 VALUES ('AP', 123, ' ');
INSERT INTO tst1 VALUES ('HEL', 666, 'V');

CREATE TABLE tst2 (
  k1 VARCHAR(3),
  k2 INT,
  k3 CHAR(1),
  PRIMARY KEY (k1, k2, k3)
)
INSERT INTO tst2 VALUES ('AP', 123, ' ');
INSERT INTO tst2 VALUES ('HEL', 666, 'V');

-- NOTE: tst1 and tst2 are identical, both in structure and data.
-- NOTE: If the 'PRIMARY KEY' is removed, then everything works ok.
-- NOTE: If 'PRIMARY KEY' is replaced with 'UNIQUE', then the error still occurs.
-- NOTE: If the type of 'k3' is changed to 'VARCHAR(1)', then everything works ok.


-- Problem statement:
SELECT t1.*
FROM tst1 t1, tst2 t2
WHERE t1.k1 = t2.k1
  AND t1.k2 = t2.k2
  AND t1.k3 = t2.k3


-- The result is that no matches are found.
-- NOTE: If the 'k1' test is removed, then the expected result is returned.
-- NOTE: If the 'k2' test is removed, then the expected result is returned.
-- NOTE: If the 'k3' test is removed, then the expected result is returned.
-- NOTE: This means that all combinations of checking two key colums return the expected result, but checking all three at the same time will return nothing.


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 26/Jan/10 02:35 PM
It could be related to CORE-1997 and/or CORE-2232.

Håkan Johansson added a comment - 07/May/10 11:56 AM
Raised priority to critical since this bug makes it impossible to use Firebird with UTF-8 data.

Adriano dos Santos Fernandes added a comment - 07/May/10 12:10 PM
I see no direct relation of this issue with CORE-1997 (about cascade) and CORE-2232 (commented saying lookup of empty keys are broken).

BTW, I can't reproduce this one in HEAD and in 2.1.1.

Håkan Johansson added a comment - 10/May/10 08:10 AM
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.

Vlad Khorsun added a comment - 10/May/10 08:38 AM
> 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 ?

Vlad Khorsun added a comment - 10/May/10 08:55 AM
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.

Adriano dos Santos Fernandes added a comment - 10/May/10 10:45 AM
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.

Vlad Khorsun added a comment - 10/May/10 11:17 AM
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 !

Adriano dos Santos Fernandes added a comment - 11/May/10 03:51 PM
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.

Vlad Khorsun added a comment - 11/May/10 05:55 PM
> 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 ;)

Adriano dos Santos Fernandes added a comment - 11/May/10 06:03 PM
> 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.

Vlad Khorsun added a comment - 11/May/10 06:14 PM
And what about single-segment index keys ?

Adriano dos Santos Fernandes added a comment - 11/May/10 06:21 PM
> 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.

Vlad Khorsun added a comment - 11/May/10 06:27 PM
Thanks !

PS In both 2.0 and 2.1 there is EVL\adjust_text_descriptor() ;)

Philippe Makowski added a comment - 03/May/11 11:45 AM
Q/A test made