Not a Number Value in Float Field

ottobre 09, 2009

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=;Initial Catalog=;Persist Security Info=True;User ID=”);

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];

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;