Home > Articoli > Not a Number Value in Float Field

Not a Number Value in Float Field

In questo articolo parleremo della situazione che si verifica quando nel DB vengono memorizzati dei valori numerici, in colonne di tipo float, che però non possono più essere letti correttamente.

Tutto è iniziato dalla segnalazione di un utente che lamentava un errore in fase di estrazione dei dati di fatturato. La versione di SQL Server in produzione è la 2000 (SP4 – versione 8.00.2039) e l’errore restituito durante l’esecuzione della query è: "An error occurred while executing batch. Error message is: Overflow aritmetico."

Dopo aver fatto alcune prove ho isolato la colonna che produce l’errore, è una colonna di tipo float in cui SQL Server ha memorizzato un valore non valido, si tratta del valore indefinito NaN (Not a Number) che è stato possibile memorizzare ma che ora non è più possibile leggere in modo tradizionale con una di questo tipo:

select
  fieldfloat
from
  dbo.tab_with_NaN

L’unico modo che ho trovato per leggere i dati è attraverso una conversione dal tipo float al tipo varchar:

select
  left(fieldfloat, 100) as fieldfloat
from
  dbo.tab_with_NaN

Con SQL Server 2000 (SP4) è possibile riprodurre la situazione come descritto di seguito.

Creazione tabella di test dbo.tab_with_NaN:

use [TestDB];
go

create table dbo.tab_with_NaN
(
  id int identity(1, 1) not null,
  fieldfloat float
  primary key(id)
);
go

Per inserire una riga valorizzando la colonna fieldfloat con il valore indefinito NaN si può utilizzare il seguente codice C# (lo stesso ragionamento è valido per i valori –Infinito (NegativeInfinity) e +Infinito (PositiveInfinity):

private void button1_Click(object sender, EventArgs e)
{

double dNot_a_Number = double.NaN;

// For NegativeInfinity
//dNot_a_Number = double.NegativeInfinity;

// For PositiveInfinity
//dNot_a_Number = double.PositiveInfinity;

// Connection
SqlConnection conn = new SqlConnection(@"Data Source=<server_name>;Initial Catalog=<database_name>;Persist Security Info=True;User ID=<user_name>");

SqlCommand cmd = new SqlCommand("Insert tab_with_NaN(fieldfloat) Values(@val)", conn);

cmd.Parameters.AddWithValue("@val", dNot_a_Number);

conn.Open();

// Insert Not a Number
int rows = cmd.ExecuteNonQuery();

conn.Close();

}

Se tentiamo la lettura dei dati con la query:

select
  *
from
  dbo.tab_with_NaN

Otteniamo l’errore: "An error occurred while executing batch. Error message is: Overflow aritmetico." Se tentiamo invece la lettura applicando alla colonna fieldfloat la conversione implicita a varchar otteniamo:

select
  left(fieldfloat, 100) as fieldfloat
from
  dbo.tab_with_NaN

Per sistemare i dati (recuperando lo storico) è sufficiente realizzare un’opportuna query di UPDATE (nel mio caso, fortunatamente , è stato possibile identificate in maniera semplice la logica di ricostruzione del valore numerico).

Dopo aver sistemato i dati si è voluto rafforzare l’integrità di dominio del DB definendo una regola per impedire la memorizzazione di valori indefiniti (o comunque fuori dal range del tipo di dato float) nella colonna floatfield, anche perchè questi valori non potranno essere letti correttamente. Per fare questo si utilizza un CONSTRAINT di tipo CHECK verificato attraverso la funzione (user-defined function) dbo.udf_SS2000_float_isnan:

— dbo.udf_SS2000_float_isnan
if object_id(‘udf_SS2000_float_isnan’) is not null
  drop function dbo.udf_SS2000_float_isnan;
go

create function dbo.udf_SS2000_float_isnan(@value float) returns smallint
as
begin
  /*
    Descrizione: User-defined function di controllo dei valori float
  */
 
  declare
    @result smallint
   
  set
    @result = -1  
 
  select
    @result = 0
  where
    ((@value between -1.79E+308 and -2.23E-308) or
     (@value = 0) or
     (@value between 2.23E-308 and 1.79E+308))
 
  return @result 
end
go

alter table
  dbo.tab_with_NaN
with nocheck add constraint
  chk_fieldfloat_NaN check (dbo.udf_SS2000_float_isnan(fieldfloat)=0)

Con SQL Server 2005 non sono riuscito a riprodurre il problema (nel mio caso è meglio così ), il DB, anche senza CHECK CONSTRAINT, impedisce il salvataggio di valori indefiniti nella colonna floatfield.

 

Conclusioni

Con SQL Server 2005 Microsoft non supporta i valori NaN, +Infinito e –Infinito come valori numerici memorizzabili nelle colonne di tipo float.

Qualcuno però ha già richiesto una modifica … date un’occhiata a questa segnalazione su connect: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674 

Limitare l’utilizzo del tipo di dato float solo nei casi strettamente necessari, la situazione anomala che ho riscontrato su SQL Server 2000 non si sarebbe verificata se la colonna floatfield fosse stata di tipo decimal().

 

Pulizia DB

use [TestDB];

go

if object_id(N’tab_with_NaN’, N’U’) is not null
  drop table dbo.tab_with_NaN;
go

— dbo.udf_SS2000_float_isnan
if object_id(‘udf_SS2000_float_isnan’) is not null
  drop function dbo.udf_SS2000_float_isnan;

 

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)

Introduzione Nell’articolo Automazione delle attività di manutenzione in Azure SQL Database abbiamo descritto le attività …