Literals and math operations in expressions

If your stored procedures perform math operations in expressions, carefully consider the supported ranges for literals and the possible implicit typecasts that the Netezza Performance Server system might perform. These ranges and behaviors can cause unexpected results or overflow errors in complex math expressions.

The Netezza Performance Server system checks for overflows when performing integer arithmetic. For example, all integer literals are 32-bit (signed) numbers by default, which supports a range of values from -2147483648 to 2147483647. If you perform math operations on a literal, the system checks for and returns an error if it detects an overflow. For example, the following stored procedure defines a numeric value n, assigns it the largest supported value (2147483647) and then adds 1 to the value:

CREATE OR REPLACE PROCEDURE num() RETURNS BOOL LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
n NUMERIC;
BEGIN
n := 2147483647;
RAISE NOTICE 'n is %', n;
n := 2147483647 + 1;
RAISE NOTICE 'n is %', n;
END;
END_PROC;

If you create and run this stored procedure, the procedure fails and returns an integer overflow error:

NOTICE:  n is 2147483647
NOTICE:  Error occurred while executing PL/pgSQL function NUM
NOTICE:  line 6 at assignment
ERROR:  overflow in 32 bit arithmetic

To avoid an overflow condition, you can use a cast operation to cast n to a higher precision type, or assign values to intermediate values. For example, the following stored procedure creates the overflow issue for the numeric value n but uses casts to numerics or bigints to increase the range of valid values:

CREATE OR REPLACE PROCEDURE num() RETURNS BOOL LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
n NUMERIC;
BEGIN
    n := 2147483647;
    RAISE NOTICE 'n is %', n;
    n := 2147483647::numeric + 1;
    RAISE NOTICE 'n is %', n;
    n := 2147483647::bigint + 1;
    RAISE NOTICE 'n is %', n;
END;
END_PROC;

If you create and run this stored procedure, the output would be similar to the following:

NOTICE:  n is 2147483647
NOTICE:  n is 2147483648
NOTICE:  n is 2147483648

As this example shows, explicit casts during arithmetic evaluations and careful use of literals, constants, and types can help to increase the accuracy of the expressions used in your stored procedures.

If you use floating point numbers in expressions, Netezza Performance Server attempts to cast it into a numeric if possible, with a specific precision and scale that it calculates with internally defined casting rules.

Because arithmetic operations in a stored procedure are evaluated by invoking the backend executor, they are processed as SELECT statements. To more clearly see the calculated shape of the expression result, it can be helpful to use it to create a table, which can then be described by using the \d command. Printing the results might not provide enough insight to the resulting data types.

In the arithmetic expression that follows, Netezza Performance Server casts the precision and scale based on internal Netezza Performance Server SQL behavior rules:

DEV.SCH1(ADMIN)=> create table test as select (1 + 0.08/365) interest;
INSERT 0 1
DEV.SCH1(ADMIN)=> \d test
                Table "TEST"
Attribute |     Type     | Modifier | Default Value 
----------+--------------+----------+---------------
INTEREST  | NUMERIC(8,6) |          | 
Distributed on hash: "INTEREST"

In the sample table, the Netezza Performance Server internal casting rules evaluated the expression “1+0.08/365” and determined that the field would be a numeric value with 8 digits of precision and 6 digits of scale. The following command shows the actual value saved in the row:

DEV.SCH1(ADMIN)=> select * from TEST;
 INTEREST 
----------
 1.000219
(1 row)

Another example follows:

DEV.SCH1(ADMIN)=> create table test2 as select (4 + 1/5) loanrt;
INSERT 0 1
DEV.SCH1(ADMIN)=> \d test2
                Table "TEST2"
Attribute |  Type    | Modifier | Default Value 
----------+----------+----------+---------------
 LOANRT   | INTEGER  |          | 
Distributed on hash: "LOANRT"

In the previous example, Netezza Performance Server is evaluating three integer values (4, 1, and 5). The Netezza Performance Server system uses integer as the type for the new column. If you display the column value, as follows, you can see that the “decimal” portion of the value was truncated:

DEV.SCH1(ADMIN)=> select * from TEST2;
 LOANRT 
--------
      4
(1 row)

A similar example follows, but instead of the expression 1/2, this expression uses the numeric value .5 instead, as follows:

DEV.SCH1(ADMIN)=> create table test3 as select (4 + .5) loanrt; 
INSERT 0 1
DEV.SCH1(ADMIN)=> \d test3
                Table "TEST3"
Attribute |     Type      | Modifier | Default Value 
----------+---------------+----------+---------------
 LOANRT   | NUMERIC(3,1)  |          | 
Distributed on hash: "LOANRT"
DEV.SCH1(ADMIN)=> select * from TEST3;
 LOANRT 
--------
    4.5
(1 row)

In this example, the .5 value is interpreted as 0.5, and thus cast to numeric(3,1).

In addition to the casts that can occur when math expressions are parsed, Netezza Performance Server functions can also cause an implicit typecast. For example, the function sqrt() takes and returns a double precision value. The following example uses the function to define a table column:

DEV.SCH1(ADMIN)=> create table test4 as select (sqrt(42));
INSERT 0 1
DEV.SCH1(ADMIN)=> \d test4
                Table "TEST4"
Attribute |       Type       | Modifier | Default Value 
----------+------------------+----------+---------------
SQRT      | DOUBLE PRECISION |          | 
Distributed on hash: "SQRT"
DEV.SCH1(ADMIN)=> select * from TEST4;
      SQRT 
-----------------
 6.4807406984079
(1 row)

In the test4 example, the sqrt() function causes Netezza Performance Server to cast the input integer value to a double and to return a double.

Remember these behaviors when you work with stored procedures that use arithmetic expressions to evaluate data. The implicit casts might not provide the value that you would expect if you evaluated the same arithmetic expression with a calculator. An example follows:

CREATE OR REPLACE PROCEDURE sp_expressions_numeric02() RETURNS NUMERIC 
LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE
    thisnum9_2 NUMERIC(9,2);
    million NUMERIC(9,2) := 1000000.00;
    thisnum18 NUMERIC(18);
    litespeed NUMERIC := 186282;
    thisnum38 NUMERIC(38);
  BEGIN
    /* The following expression causes implicit casts in the math
       evaluation, reducing the precision and scale of the result. */
    thisnum9_2 := million * (1 + 0.08/365)^(365 * 20);
    RAISE NOTICE 'thisnum9_2 == %', thisnum9_2;

    /* The following expression uses an explicit cast to increase 
       the precision and scale of the intermediate evaluation. */
    thisnum9_2 := million * (1 + 0.08::numeric(20,15)/365)^(365 * 20);
    RAISE NOTICE 'thisnum9_2 == %', thisnum9_2;

    /* The following expression uses the numeric literal litespeed to 
       convert the speed of light from miles per sec to miles per 
       year. */
    thisnum18 :=  litespeed * 60 * 60 * 24 * 365.25;
    RAISE NOTICE 'thisnum18 == %', thisnum18;
    /* The following expression uses the integer 186282 to convert 
       lightspeed from miles per sec to miles per year. In the 
       right-side evaluation, however, the values overflowed the 
       upper limit of an int several times during evaluation, 
       which causes an  overflow error. */
    thisnum38 :=  186282 * 60 * 60 * 24 * 365.25;
    RAISE NOTICE 'thisnum38 == %', thisnum38;
  END;
END_PROC;

Sample output follows:

NOTICE:  thisnum9_2 == 4945731.93
NOTICE:  thisnum9_2 == 4952164.15
NOTICE:  thisnum18 == 5878612843200
NOTICE:  Error occurred while executing PL/pgSQL function SP_EXPRESSIONS_NUMERIC02
NOTICE:  line 16 at assignment
ERROR:  overflow in 32 bit arithmetic