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

Join condition fails for UTF-8 databases. [CORE2826] #3213

Closed
firebird-automations opened this issue Jan 26, 2010 · 23 comments
Closed

Join condition fails for UTF-8 databases. [CORE2826] #3213

firebird-automations opened this issue Jan 26, 2010 · 23 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Håkan Johansson (hakjo)

Is related to CORE3056
Is related to QA274

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.

Commits: 12bb869 cdd366e 632e72c bac1cfa

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It could be related to CORE1997 and/or CORE2232.

@firebird-automations
Copy link
Collaborator Author

Commented by: Håkan Johansson (hakjo)

Raised priority to critical since this bug makes it impossible to use Firebird with UTF-8 data.

@firebird-automations
Copy link
Collaborator Author

Modified by: Håkan Johansson (hakjo)

priority: Major [ 3 ] => Critical [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I see no direct relation of this issue with CORE1997 (about cascade) and CORE2232 (commented saying lookup of empty keys are broken).

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Håkan Johansson (hakjo)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

> 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 ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Won't Fix [ 2 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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 !

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Won't Fix [ 2 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

And what about single-segment index keys ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Thanks !

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 RC3 [ 10381 ]

Fix Version: 2.1.4 [ 10361 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is related to CORE3056 [ CORE3056 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA274 [ QA274 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Q/A test made

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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

@firebird-automations
Copy link
Collaborator Author

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