[Firebird-devel] [ firebird-Bugs-841696 ] coalesce fails with data type varchar and a non ascii value

[SourceForge.net <no...@so...>]

Bugs item #841696, was opened at 2003-11-13 22:08
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=841696&group_id=9028

Category: Core Engine
Group: Initial Bug
Status: Open
Resolution: None
Priority: 5
Submitted By: Opher Shachar (opher)
Assigned to: Nobody/Anonymous (nobody)
Summary: coalesce fails with data type varchar and a non ascii value

Initial Comment:
I use coalesce in a 'before insert' trigger with 
parameters of type varchar and a non ascii value (ie. 'à')
In the attached example the last insert to table CLASS2 
produces this error:
Statement failed, SQLCODE = -802

arithmetic exception, numeric overflow, or string 
truncation
-Cannot transliterate character between character sets

Changing the offending expression to:

   new.CLASS_NAME = case when :name is not null 
then :name else new.CLASS_NAME end;

Does not help. But please notice that this:

   new.CLASS_NAME = case when :name is null then 
new.CLASS_NAME else :name end;

Works correctly!!
Also, I work in hebrew (as I libe in Israel) and changing 
the character sets:
  SET NAMES WIN1255;
  CREATE DATABASE ... WIN1255;
or
  SET NAMES WIN1255; 
  CREATE DATABASE ... UNICODE_FSS;
produces the same behaviour.

Opher Shachar.

------ Environment
Firebird 1.5 RC7 (also RC6)
Windows XP, Windows 2000 SP2 Server

------ SQL script to produce bug
SET NAMES WIN1252;

SET SQL DIALECT 3;

CREATE DATABASE ':c:\temp\testdb.fdb'
 USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096  DEFAULT CHARACTER SET WIN1252;

RECREATE TABLE TRANS_TABLE
(
                           TCODE             SMALLINT         
NOT NULL,
                            CODE             SMALLINT         
NOT NULL,
                            NAME              VARCHAR(    10),
 CONSTRAINT TRANS_TABLE_PRIMARYKEY PRIMARY KEY 
(TCODE, CODE)
);

RECREATE TABLE CLASS1
(
                      CLASS_NAME              VARCHAR(    10) 
NOT NULL,
                       CLASS_NUM             SMALLINT         
NOT NULL,
                      TEACHER_ID              INTEGER,
 CONSTRAINT PK_CLASS1 PRIMARY KEY (CLASS_NAME, 
CLASS_NUM)
);

RECREATE TABLE CLASS2
(
                      CLASS_NAME              VARCHAR(    10) 
NOT NULL,
                       CLASS_NUM             SMALLINT         
NOT NULL,
                      TEACHER_ID              INTEGER,
 CONSTRAINT PK_CLASS2 PRIMARY KEY (CLASS_NAME, 
CLASS_NUM)
);

SET TERM ^^ ;
CREATE TRIGGER CLASS1 FOR CLASS1 ACTIVE BEFORE 
INSERT POSITION 0 AS
/*
  Trigger:

  Author   : Opher Shachar
  Date     : 2003-10-29
  Purpose  : Convert from class code to name
*/
  declare variable name varchar(10);
begin
  select name from TRANS_TABLE c where c.tcode=2 
and c.code=new.CLASS_NAME
  into :name;
  new.CLASS_NAME = case when :name is null then 
new.CLASS_NAME else :name end;
/*  new.CLASS_NAME = coalesce(:name, 
new.CLASS_NAME);*/
end
^^
CREATE TRIGGER CLASS2 FOR CLASS2 ACTIVE BEFORE 
INSERT POSITION 0 AS
/*
  Trigger:

  Author   : Opher Shachar
  Date     : 2003-10-29
  Purpose  : Convert from class code to name
*/
  declare variable name varchar(10);
begin
  select name from TRANS_TABLE c where c.tcode=2 
and c.code=new.CLASS_NAME
  into :name;
/*  new.CLASS_NAME = case when :name is null then 
new.CLASS_NAME else :name end;*/
  new.CLASS_NAME = coalesce(:name, 
new.CLASS_NAME);
end
^^
SET TERM ; ^^
COMMIT;

INSERT INTO TRANS_TABLE(TCODE, CODE, NAME) 
VALUES (2, 1, 'à');
COMMIT;

/* This executes OK */
INSERT INTO CLASS1(CLASS_NAME, CLASS_NUM, 
TEACHER_ID) VALUES (1, 1, NULL);
COMMIT;

/* !!! This FAILS !!! */
INSERT INTO CLASS2(CLASS_NAME, CLASS_NUM, 
TEACHER_ID) VALUES (1, 1, NULL);
COMMIT;


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=841696&group_id=9028