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
Comments
Commented by: Håkan Johansson (hakjo) Raised priority to critical since this bug makes it impossible to use Firebird with UTF-8 data. |
Modified by: Håkan Johansson (hakjo)priority: Major [ 3 ] => Critical [ 2 ] |
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. |
Commented by: Håkan Johansson (hakjo) Start with no database. $ isql -z $ isql $isql CREATE TABLE tst1 ( CREATE TABLE tst2 ( INSERT INTO tst1 VALUES ('AP', 123, ' '); SELECT t1.* ----------------------------------------------------------------------------------------------------------------------------------------- If I don't exit isql between creating the schema and running the commands I get the expected result. |
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 ? |
Commented by: @hvlad Well, SET NAMES doesn't matters there. So, UPDATE RDB$CHARACTER_SETS have no effect on database until metadata cache is reloaded. |
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. |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Won't Fix [ 2 ] assignee: Adriano dos Santos Fernandes [ asfernandes ] |
Commented by: @hvlad I don't think ticket should be closed. CREATE COLLATION UNICODE_NOPAD FOR UTF8 FROM UNICODE NO PAD; RECREATE TABLE tst1_nopad ( INSERT INTO tst1_nopad VALUES ('AP', 123, ' '); SELECT t1.* no rows SELECT t1.* 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 ! |
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. |
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 ? |
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. |
Commented by: @hvlad And what about single-segment index keys ? |
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. |
Commented by: @hvlad Thanks ! PS In both 2.0 and 2.1 there is EVL\adjust_text_descriptor() ;) |
Modified by: @asfernandesstatus: 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 ] |
Modified by: @asfernandes |
Commented by: @pmakowski Q/A test made |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
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
The text was updated successfully, but these errors were encountered: