Silent conversion of NULL to 0 in PgSqlDataReader.GetInt32
Posted: Thu 04 Sep 2008 21:08
I'm currently evaluating PostgreSQLDirect.Net, and ran across what I believe to be a serious flaw...
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.
Code: Select all
PgSqlCommand command = new PgSqlCommand("SELECT NULL", connection);
PgSqlDataReader reader = command.ExecuteReader();
reader.Read();
bool isNull = reader.IsDBNull(0); // returns true
int value = reader.GetInt32(0); // returns 0Most 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.