Smallint and short range problems

[Sivannnnnn]

In most programming languages, short ranges from -32768 to 32767, but the heavyDB Minimum value: -32,767; Maximum value: 32,767; maximum value: 32,767，Should I change the type from SmallInt to INTEGER to store -32768？

java.sql.SQLException: executeBatch failed: TException - service has thrown: TDBException(error_msg=Exception: Integer -32768 exceeds minimum value for nullable SMALLINT(16)) at com.omnisci.jdbc.OmniSciPreparedStatement.executeBatch(OmniSciPreparedStatement.java:955) at com.yisa.datatrans.LT2Omni.main(LT2Omni.java:174)

[cdessanti]

Hi,

we reserve a single value of any data type to store NULL values when a field is nullable. In the case of smallint is the -32768 value.

If you need to use that value to retain the smallint type you must define the field as not nullable.

heavysql> create table test_smallint (f1 smallint not null);
heavysql> insert into test_smallint values(-32768);
heavysql> select * from test_smallint;
f1
-32768
heavysql> create table test_smallint (f1 smallint);
heavysql> insert into test_smallint values(-32768);
heavysql> select * from test_smallint;
f1
NULL

The reason behind this is by doing that you don't need anything else to track if a field contains a null more not; It would need at least 1 additional bit, but it would be necessary to use 1 byte.

Regards, Candido