Search code examples
sqldatabase-agnostic

Expected behavior or truncated/overflow cast

Is there a standard way for how the following statements evaluate, or is it implementation-defined?

SELECT CAST(100000 AS int16);        # integer overflow
SELECT CAST("hello" AS VARCHAR(2));  # string overflow (truncation?)

For example, should the number get the max 16-bit value? Raise an error (and if so, why?) Should the string just chop off the first two chars? etc.

Solution

  • select cast(100000 as smallint) ... is expected to raise a numeric value out range exception. (smallint is ANSI/ISO SQL data type, which I'd consider database agnostic.)

    select cast('hello' as varchar(2)) ... will give you 'he'.

    Note that character data truncation is treated differently depending on if you read/select, or write/insert.

    select 'hello' into :chr2 ..., where chr2 is a 2 character target, will give 'he', and a string data truncation warning.

    Bit if you try to INSERT the value 'hello' into a varchar(2) column, you will get truncation error, and no value is inserted.