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;
CREATE MULTISET VOLATILE TABLE ULI.vt_b ,NO FALLBACK ,
CHECKSUM = DEFAULT,
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?
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".