Implicit data type conversion to char ends up in Unicode

Database
Supporter

Implicit data type conversion to char ends up in Unicode

Hi all,

I am on 13.10.

Why is an implicit decimal or integer to char conversion with coalesce resulting in character set unicode?

create volatile table vt_a (a decimal(3,0), b integer, c date) no primary index;

create volatile table vt_b as (select coalesce(a,'What ever') as char_a, coalesce(b,'What ever') as char_b, coalesce(cast(c as varchar(10)),'What ever') as char_c from vt_a) with no data no primary index;

show table vt_a;
show table vt_b;

results in 

CREATE MULTISET VOLATILE TABLE ULI.vt_b ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
LOG
(
char_a VARCHAR(9) CHARACTER SET UNICODE NOT CASESPECIFIC,
char_b VARCHAR(11) CHARACTER SET UNICODE NOT CASESPECIFIC,
char_c VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
NO PRIMARY INDEX
ON COMMIT DELETE ROWS;

So a direct cast is latin but implicit is unicode.

Can someone guide me to the right position in the documentation?

Is this related to ANSI standard?

Ulrich

1 REPLY
Junior Contributor

Re: Implicit data type conversion to char ends up in Unicode

Hi Ulrich,

it's not direct vs. implicit cast, it's the literals: any character literal is in Unicode and the CASE/COALESCE is using the most appropriate datatype.

Should be documented somewhere in the Datatypes and Literals SQL manual.

Standard SQL is probably not the cause, in lots of different areas it's "implementation defined".

Dieter