[Firebird-net-provider] FbCommandBuilder incorrect behaivor in where clause generation on nullable fields!

[Dmitry A. <fd...@ua...>]

Hello firebird-net-provider,

   [vb.net]
   _command.CommandText = "SELECT ID FROM EMPLOYES WHERE (BIRTHDAY=@B1 OR (BIRTHDAY IS NULL AND @B2 IS NULL))"
   _command.Parameters.Add("@ID", 1)
   _command.Parameters.Add("@B1", DBNull.Value)
   _command.Parameters.Add("@B2", DBNull.Value)
   Dim __id As Integer = _command.ExecuteScalar()

   and in execution gets exception: Data type unknown
   all right, because fbserver unknown type of @B2 parameter (it's he
   strange feautre of fbserver).

   but if rewrite on this:
        _command.CommandText = "SELECT ID FROM EMPLOYES WHERE " & _
        "BIRTHDAY=@B1 OR " & _
        "(BIRTHDAY IS NULL AND COALESCE(@B2, CAST(NULL AS DATE)) IS NULL)"
   all work :)
   
   When field is not null we may generate whereClausule2 template (as
   pk field).
   But when field nullable -- you generate whereClausule1, but this
   incorrect (when parameter not null -- in result set returned
   records and equal parameter value and field IS NULL. it's
   incorrect. and as result we got spare record).
         whereClausule1     = "(({0} IS NULL) OR ({0} = ?))";
         whereClausule2     = "({0} = ?)";

   In SqlClient use template ({0}=? OR ({0} IS NULL AND ? IS NULL)),
   but it's not work on fbserver. But if we work on firebird 1.5 we
   can release correct work thru, for example COALESCE keyword. When
   all be works fine! (May be CASE works too, but need 3 params, but
   not 2).

   I think that in server this feature will else long, but we already
   can get correct work, if using coalesce keyword, on FB1.5 at least
   once! 

-- 
Best regards,
 Dmitry                          mailto:fd...@ua...