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

[firebird-automations]

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]

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[firebird-automations]

Modified by: Radek Palmowski (palma)

Component: API / Client Library [ 10040 ]

Component: Engine [ 10000 ]

[firebird-automations]

Commented by: @asfernandes

Please show us the MEMO_* create domain commands.

[firebird-automations]

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]

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]

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]

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

[firebird-automations]

Commented by: Radek Palmowski (palma)

It is possible to backport to version 2.5?

[firebird-automations]

Commented by: @asfernandes

Fix backported to 2.5.2.

[firebird-automations]

Modified by: @asfernandes

Fix Version: 2.5.2 [ 10450 ]

[firebird-automations]

Modified by: @pcisar

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

[firebird-automations]

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