-5

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.

David542
  • 104,438
  • 178
  • 489
  • 842
  • 1
    I'm curious, what problem are you trying to solve by asking this? – squillman Sep 20 '22 at 23:00
  • 1
    Of course it's implementation defined. On Microsoft SQL Server, for example, `SELECT CAST(100000 AS int16);` returns the error message, `Type int16 is not a defined system type.` – AlwaysLearning Sep 20 '22 at 23:01
  • 1
    Instead of asking for general answers, which in SQL are hard to come by, ask for the most standard approach for a given RDBMS. This helps focus the problem. – tadman Sep 20 '22 at 23:03
  • @DaleK I see, ok I'll change to that. – David542 Sep 20 '22 at 23:13
  • @squillman parsing a SQL grammar (for no particular database) – David542 Sep 20 '22 at 23:17
  • @AlwaysLearning for SQL server it would be: `[HY019] [FreeTDS][SQL Server]The conversion of the varchar value '100000' overflowed an INT2 column. Use a larger integer column` – David542 Sep 20 '22 at 23:21
  • @David542 I guess you've found a client driver variation on the error message. The error I quoted was produced via SQL Server Management Studio. – AlwaysLearning Sep 20 '22 at 23:37
  • @AlwaysLearning sure but yours is jus saying `int16` isn't a valid type -- the typename is `smallint` in sqlserver (which I assume is echoed back as `int2`). – David542 Sep 20 '22 at 23:50

1 Answers1

0

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63