Letterali e operazioni matematiche nelle espressioni

Se le stored procedure eseguono operazioni matematiche nelle espressioni, considerare attentamente gli intervalli supportati per i letterali e i possibili typecast impliciti che il sistema Netezza Performance Server potrebbe eseguire. Questi intervalli e comportamenti possono causare risultati inattesi o errori di overflow in espressioni matematiche complesse.

Il sistema Netezza Performance Server controlla la presenza di overflow durante l'esecuzione di aritmetiche intere. Ad esempio, tutti i letterali interi sono numeri a 32 bit (firmati) per impostazione predefinita, che supportano un intervallo di valori compreso tra -2147483648 e 2147483647. Se si eseguono operazioni matematiche su un letterale, il sistema controlla e restituisce un errore se rileva un overflow. Ad esempio, la seguente stored procedure definisce un valore numerico n, gli assegna il valore più grande supportato (2147483647) e poi aggiunge 1 al valore:

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;

Se si crea ed esegue questa stored procedure, la procedura fallisce e restituisce un errore di overflow di numeri interi:

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

Per evitare una condizione di overflow, è possibile utilizzare un'operazione di cast per lanciare 'n in un tipo di precisione superiore o assegnare valori a valori intermedi. Ad esempio, la seguente stored procedure crea il problema dell'overflow per il valore numerico n, ma utilizza i cast su numerici o bigint per aumentare l'intervallo di valori validi:

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;

Se si crea ed esegue questa stored procedure, l'output sarà simile al seguente:

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

Come mostra questo esempio, le fusioni esplicite durante le valutazioni aritmetiche e l'uso attento di letterali, costanti e tipi possono contribuire ad aumentare la precisione delle espressioni utilizzate nelle stored procedure.

Se si utilizzano numeri in virgola mobile nelle espressioni, Netezza Performance Server tenta di trasformarli in numeri, se possibile, con una precisione e una scala specifiche calcolate con regole di fusione definite internamente.

Poiché le operazioni aritmetiche di una stored procedure vengono valutate invocando l'executor del backend, esse vengono elaborate come istruzioni SELECT. Per vedere più chiaramente la forma calcolata del risultato dell'espressione, può essere utile utilizzarla per creare una tabella, che può essere descritta con il comando '\d. La stampa dei risultati potrebbe non fornire sufficienti informazioni sui tipi di dati risultanti.

Nell'espressione aritmetica che segue, Netezza Performance Server assegna la precisione e la scala in base alle regole interne di comportamento di Netezza Performance Server SQL:

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"

Nella tabella di esempio, le regole di casting interne di Netezza Performance Server hanno valutato l'espressione "1+00.08/365" e hanno determinato che il campo sarebbe stato un valore numerico con 8 cifre di precisione e 6 cifre di scala. Il comando seguente mostra il valore effettivo salvato nella riga:

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

Segue un altro esempio:

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"

Nell'esempio precedente, Netezza Performance Server sta valutando tre valori interi (4, 1 e 5). Il sistema Netezza Performance Server utilizza il tipo intero per la nuova colonna. Se si visualizza il valore della colonna, come segue, si può notare che la parte "decimale" del valore è stata troncata:

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

Segue un esempio simile, ma invece dell'espressione 1/2, questa espressione utilizza il valore numerico .5, come segue:

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 questo esempio, il valore " .5 viene interpretato come 0.5 e quindi lanciato come numeric(3,1).

Oltre ai cast che possono verificarsi durante l'analisi delle espressioni matematiche, le funzioni di Netezza Performance Server possono anche causare un typecast implicito. Ad esempio, la funzione " sqrt() accetta e restituisce un valore a doppia precisione. L'esempio seguente utilizza la funzione per definire una colonna della tabella:

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)

Nell'esempio test4, la funzione 'sqrt() fa sì che Netezza Performance Server esegua il cast del valore intero di input in un double e restituisca un double.

Ricordate questi comportamenti quando lavorate con le stored procedure che utilizzano espressioni aritmetiche per valutare i dati. I cast impliciti potrebbero non fornire il valore che ci si aspetterebbe se si valutasse la stessa espressione aritmetica con una calcolatrice. Segue un esempio:

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;

Segue un esempio di output:

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