Silent conversion of NULL to 0 in PgSqlDataReader.GetInt32

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Posts: 1
Joined: Thu 04 Sep 2008 20:18

Silent conversion of NULL to 0 in PgSqlDataReader.GetInt32

Post by Cagey » Thu 04 Sep 2008 21:08

I'm currently evaluating PostgreSQLDirect.Net, and ran across what I believe to be a serious flaw...

Code: Select all

PgSqlCommand command = new PgSqlCommand("SELECT NULL", connection);
PgSqlDataReader reader = command.ExecuteReader();
bool isNull = reader.IsDBNull(0); // returns true
int value = reader.GetInt32(0); // returns 0
In the above sample, the reader is apparently performing a silent conversion to 0. Other implementations of DbDataReader throw an InvalidCastException (perferably) or ArgumentNullException as it attempts to set an integer variable to C# null. Is there a setting I can change to get the behavior I expect?

Most of my data is never expected to contain NULL values, so it is actually an exceptional case if I see one--I could write a wrapper around your code to verify IsDBNull=false and throw if it isn't on every column I expect to always have a value, but in addition to making my program silently accept bad data, the conversion you're performing is also a violation of the SQL standard.

The standard states that NULL represents an unknown value, which is why NULL = NULL is NULL instead of TRUE and the IS NULL check exists. The choice of conversion to 0 isn't any more valid than conversion to -1 or 12. In some cases, 0 is not an acceptable value for a column, and your conversion can lead to bugs far down the road from the initial insertion of the value.

Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 05 Sep 2008 13:36

It is expected behavior of the GetInt32 function. Exception is not thrown because it can affect the work of other components. It is just a feature of the PgSqlDataReader class. You are right, usage of the IsDBNull function is a workaround for this situation.

Post Reply