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

Incorrect TEXT BLOB charset transliteration on VIEW with trigger [CORE3601] #3955

Closed
firebird-automations opened this issue Sep 16, 2011 · 12 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Radek Palmowski (palma)

On UTF8 connection:
isc_get_segment from V_T_TEST.MEMO_UTF8 V_T_TEST.MEMO_WIN1250 returns buffer as utf-8 string
but
isc_put_segment on V_T_TEST.MEMO_UTF8 with buffer as utf-8 is OK
isc_put_segment on V_T_TEST.MEMO_WIN1250 with buffer as utf-8 not transliterate character set and stores in table incorrect as utf-8
isc_put_segment on V_T_TEST.MEMO_WIN1250 with buffer as win1250 report error malformed string

on table T_TEST isc_get_segment, isc_put_segment with buffer utf-8 works OK

CREATE TABLE T_TEST (
ID UID NOT NULL /* UID = BIGINT */,
MEMO_UTF8 MEMO_UTF8 /* MEMO_UTF8 = BLOB SUB_TYPE 1 SEGMENT SIZE 100 */,
MEMO_WIN1250 MEMO_WIN1250 /* MEMO_WIN1250 = BLOB SUB_TYPE 1 SEGMENT SIZE 100 */,
MEMO_OCTETS MEMO_OCTETS /* MEMO_OCTETS = BLOB SUB_TYPE 1 SEGMENT SIZE 100 */
);

ALTER TABLE T_TEST ADD CONSTRAINT PK_T_TEST PRIMARY KEY (ID);

CREATE OR ALTER VIEW V_T_TEST(
ID,
MEMO_UTF8,
MEMO_WIN1250,
MEMO_OCTETS)
AS
SELECT
http://T.ID,
T.MEMO_UTF8,
T.MEMO_WIN1250,
T.MEMO_OCTETS
FROM T_TEST T
;

SET TERM ^ ;

/* Trigger: V_T_TEST_BD */
CREATE OR ALTER TRIGGER V_T_TEST_BD FOR V_T_TEST
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
DELETE FROM T_TEST
WHERE ID = http://OLD.ID;
END
^

/* Trigger: V_T_TEST_BI */
CREATE OR ALTER TRIGGER V_T_TEST_BI FOR V_T_TEST
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
INSERT INTO T_TEST(
ID,
MEMO_UTF8,
MEMO_WIN1250,
MEMO_OCTETS)
VALUES(
http://NEW.ID,
NEW.MEMO_UTF8,
NEW.MEMO_WIN1250,
NEW.MEMO_OCTETS);
END
^

/* Trigger: V_T_TEST_BU */
CREATE OR ALTER TRIGGER V_T_TEST_BU FOR V_T_TEST
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
UPDATE T_TEST SET
ID = http://NEW.ID,
MEMO_UTF8 = NEW.MEMO_UTF8,
MEMO_WIN1250 = NEW.MEMO_WIN1250,
MEMO_OCTETS = NEW.MEMO_OCTETS
WHERE ID = http://OLD.ID;
END
^

SET TERM ; ^

Commits: 3b9bdcd 2f416a1

====== Test Details ======

Section for 2.5 verifies that all OK when connection charset = win1250; section for 3.0 - when connection charset = UTF8.
Confirmed on WI-V2.5.1.26351: insert into TABLE works fine, but insert into view produces:

Statement failed, SQLSTATE = 22018
arithmetic exception, numeric overflow, or string truncation
-Cannot transliterate character between character sets

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Radek Palmowski (palma)

Component: API / Client Library [ 10040 ]

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Please show us the MEMO_* create domain commands.

@firebird-automations
Copy link
Collaborator Author

Commented by: Radek Palmowski (palma)

CREATE DOMAIN MEMO_UTF8 AS
BLOB SUB_TYPE 1 SEGMENT SIZE 100 CHARACTER SET UTF8;

CREATE DOMAIN MEMO_WIN1250 AS
BLOB SUB_TYPE 1 SEGMENT SIZE 100 CHARACTER SET WIN1250;

CREATE DOMAIN MEMO_OCTETS AS
BLOB SUB_TYPE 1 SEGMENT SIZE 100 CHARACTER SET OCTETS;

@firebird-automations
Copy link
Collaborator Author

Commented by: Radek Palmowski (palma)

On Win1250 connection:
isc_get_segment from V_T_TEST.MEMO_WIN1250 returns buffer as win1250 string
isc_get_segment from V_T_TEST.MEMO_UTF8 returns buffer as win1250 string
but
isc_put_segment on V_T_TEST.MEMO_WIN1250 with buffer as win1250 is OK
isc_put_segment on V_T_TEST.MEMO_UTF8 with buffer as win1250 report error transliterate between character sets
isc_put_segment on V_T_TEST.MEMO_UTF8 with buffer as utf-8 works

It looks as if the transliterating was only for reading from VIEW and the writing is no longer.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 3.0 Initial [ 10301 ]

summary: Incorrect TEXT BLOB charset transliteration on VIEW update in UTF8 connection => Incorrect TEXT BLOB charset transliteration on VIEW with trigger

Component: API / Client Library [ 10040 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Radek Palmowski (palma)

It is possible to backport to version 2.5?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Fix backported to 2.5.2.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Fix Version: 2.5.2 [ 10450 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

Test Details: Section for 2.5 verifies that all OK when connection charset = win1250; section for 3.0 - when connection charset = UTF8.
Confirmed on WI-V2.5.1.26351: insert into TABLE works fine, but insert into view produces:

Statement failed, SQLSTATE = 22018
arithmetic exception, numeric overflow, or string truncation
-Cannot transliterate character between character sets

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment