50236 - An "out of range" error occurs when you insert a BIGINT value into a Netezza table

Problem Note 50236: An "out of range" error occurs when you insert a BIGINT value into a Netezza table

this is the Log showing the code that was used to illustrate the behavior reported

148  proc sql;
149  connect to netezza (user=dbitest pass=XXXXXXX server=enzee database=test);

150  execute (drop table test_bigint1, test_bigint2, test_bigint3, test_bigint4) by netezza;
151  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.12 seconds
      cpu time            0.00 seconds


152
153  proc sql;
154  connect to netezza (user=dbitest pass=XXXXXXX server=enzee database=test);

155  execute (create table test_bigint1 (col1 bigint)) by netezza;
156  execute (insert into test_bigint1 values (9223372036854775806)) by netezza;
157  execute (insert into test_bigint1 values (9223372036854775888)) by netezza;
ERROR: CLI execute error: ERROR:  int8 value out of range: "9223372036854775888"
158  execute (create table test_bigint2 (col1 bigint)) by netezza;
159  execute (insert into test_bigint2 values (92233720368547)) by netezza;
160  execute (insert into test_bigint2 values (92233720368555)) by netezza;
161  execute (create table test_bigint3 (col1 bigint)) by netezza;
162  execute (insert into test_bigint3 values (922337203685477)) by netezza;
163  execute (insert into test_bigint3 values (922337203690000)) by netezza;
164  execute (create table test_bigint4 (col1 bigint)) by netezza;
165  execute (insert into test_bigint4 values (9223372036854775)) by netezza;
166  execute (insert into test_bigint4 values (9223372036859999)) by netezza;
167  execute (create table test_bigint5 (col1 bigint)) by netezza;
ERROR: CLI execute error: ERROR:  Relation 'TEST_BIGINT5' already exists
168  execute (insert into test_bigint5 values (9223372036854775806)) by netezza;
169  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.95 seconds
      cpu time            0.01 seconds

170


171  proc sql;
172  select * from nzunique.test_bigint1;
ERROR: Invalid Operation.
ERROR: Termination due to Floating Point Exception
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.10 seconds
      cpu time            0.01 seconds

173  quit;
174
175


176  proc sql;
177  select * from nzunique.test_bigint2;
178  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.11 seconds
      cpu time            0.01 seconds


179
180  proc sql;
181  select * from nzunique.test_bigint3;
182  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.10 seconds
      cpu time            0.01 seconds


183
184  proc sql;
185  select * from nzunique.test_bigint4;
ERROR: CLI cursor extended fetch error: Numeric value out of range : Numeric value out of range
186  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.01 seconds

187


188  proc sql;
189  select * from nzunique.test_bigint5;
ERROR: Invalid Operation.
ERROR: Termination due to Floating Point Exception
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.00 seconds

190  quit;

===========the following error occurs when trying to open in Windows Explorer===============================

ERROR: Invalid Operation.
ERROR: Termination due to Floating Point Exception
ERROR: Invalid Operation.
ERROR: Termination due to Floating Point Exception

Product Family	Product	System	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	SAS/ACCESS Interface to Netezza	64-bit Enabled AIX	9.22_M2	9.4	9.2 TS2M3	9.4 TS1M0

Type:	Problem Note
Priority:	high

Date Modified:	2013-07-05 12:35:21
Date Created:	2013-06-20 10:35:59

Support

Problem Note 50236: An "out of range" error occurs when you insert a BIGINT value into a Netezza table

Operating System and Release Information