INT(8) Most Negative Value

mark_d_drake

I have 2 issues with Vertica's INT implementation and the Most Negative possible value.

For all other databases I have tested the most negative value for a BIGINT/INT(8) column appears to be -9223372036854775808, e.g. postgres

yadamu=# create table BIGINT_TEST (MAX_VALUE BIGINT, MIN_VALUE BIGINT);
CREATE TABLE
yadamu=# \d BIGINT_TEST
             Table "public.bigint_test"
  Column   |  Type  | Collation | Nullable | Default
-----------+--------+-----------+----------+---------
 max_value | bigint |           |          |
 min_value | bigint |           |          |


yadamu=# insert into BIGINT_TEST(MAX_VALUE) VALUES ('9223372036854775807');
INSERT 0 1
yadamu=# insert into BIGINT_TEST(MAX_VALUE) VALUES ('-9223372036854775808');
INSERT 0 1
yadamu=# select * from BIGINT_TEST;
      max_value       | min_value
----------------------+-----------
  9223372036854775807 |
 -9223372036854775808 |
(2 rows)

However the same test with Vertica yields

VMart=> create table BIGINT_TEST (MAX_VALUE INT, MIN_VALUE INT);
CREATE TABLE
VMart=> \d BIGINT_TEST
                                    List of Fields by Tables
 Schema |    Table    |  Column   | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------------+-----------+------+------+---------+----------+-------------+-------------
 public | BIGINT_TEST | MAX_VALUE | int  |    8 |         | f        | f           |
 public | BIGINT_TEST | MIN_VALUE | int  |    8 |         | f        | f           |
(2 rows)


VMart=> insert into BIGINT_TEST(MAX_VALUE) VALUES ('9223372036854775807');
 OUTPUT
--------
      1
(1 row)


VMart=> insert into BIGINT_TEST(MAX_VALUE) VALUES ('-9223372036854775808');
ERROR 5409:  Value "-9223372036854775808" is out of range for type int8

Any idea why this is?

Second when importing data from CSV containing the value -9223372036854775808, the value appears to treated as NULL, rather than generating an error.

VMart=> COPY BIGINT_TEST from STDIN PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "BIGINT_BAD_DATA"  NO COMMIT;
NOTICE 5795:  COPY is not going to commit.  Creating a TEMPORARY rejection table, which will be dropped at end-of-session
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
 @>> "9223372036854775807","-9223372036854775808" 
 @>> \.
VMart=> select * from BIGINT_TEST
VMart-> ;
      MAX_VALUE      | MIN_VALUE
---------------------+-----------
 9223372036854775807 |
 9223372036854775807 |
(2 rows)

VMart=>

Note I added the '@' characters when posting as the >> seemed to confuse the MARKDOWN

Bryan_H

This is documented at https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Numeric/INTEGER.htm
Specifically (from Notes):
The range of values is –2^63+1 to 2^63-1.
2^63 = 9,223,372,036,854,775,808 (19 digits).
The value –2^63 is reserved to represent NULL.

mark_d_drake

Yes, but why does vertica do this, when every other database that supports native BIGINT allows this value and supports NULL, and why does Vertica throw an error on an insert but store NULL on copy.

I guess treating the value as NULL is documented but the inconsistent behavoir between INSERT and COPY is questionable ?