Improve type cast handling of parameters in query string

[franciscojunior]

Npgsql sends parameter values with type casting depending on the type of the parameter. This is done to avoid possible ambiguities in parameters values. One example of this ambiguity is show in the test case:

[Test]
        public void AmbiguousFunctionParameterType()
        {
            ExecuteNonQuery(@"CREATE OR REPLACE FUNCTION ambiguousParameterType(int2, int4, int8, text, varchar(10), char(5)) returns int4 as '
                                select 4 as result;
                              ' language 'sql'");
            //NpgsqlConnection conn = new NpgsqlConnection(ConnectionString);
            NpgsqlCommand command = new NpgsqlCommand("ambiguousParameterType(:a, :b, :c, :d, :e, :f)", Conn);
            command.CommandType = CommandType.StoredProcedure;
            NpgsqlParameter p = new NpgsqlParameter("a", DbType.Int16);
            p.Value = 2;
            command.Parameters.Add(p);
            p = new NpgsqlParameter("b", DbType.Int32);
            p.Value = 2;
            command.Parameters.Add(p);
            p = new NpgsqlParameter("c", DbType.Int64);
            p.Value = 2;
            command.Parameters.Add(p);
            p = new NpgsqlParameter("d", DbType.String);
            p.Value = "a";
            command.Parameters.Add(p);
            p = new NpgsqlParameter("e", NpgsqlDbType.Char);
            p.Value = "a";
            command.Parameters.Add(p);
            p = new NpgsqlParameter("f", NpgsqlDbType.Varchar);
            p.Value = "a";
            command.Parameters.Add(p);

            command.ExecuteScalar();
        }

If the parameter cast isn't specified, Postgresql complains that it can't find the function because the types of the parameters are int2, int4 and int8.

On the other side, most of the time the user doesn't want this cast to be applied as it may cause problems and there is no easy way to make Npgsql not add those casts (unless an undocumented feature of setting the parameter dbtype to DbType.Object.

In a recent discussion: #124 (comment) the user MrJul tried to send a query and it returned an error because Npgsql added an explicit cast to text to one of the parameters.

Although the error message is correct because the first parameter of the convert function is of type bytea and not text, if Npgsql hadn't sent the type cast, Postgresql would infer the type and would execute the query without errors.

We would need to check when would be really needed to send the type cast in order to avoid this type of problem.

[franciscojunior]

We started to have other side effects of the current situation of Npgsql type handling. Check this comment for more info: #146 (comment)

[roji]

Guys, we should probably get the type issues referenced above fixed soon, for 2.2.x if possible or 3.0 if not. Is anyone looking at these?

[franciscojunior]

I started to play with it after resuming discussion in this thread: #146 (comment)

@Emill gave me some ideas I think are worth looking in order to fix this type handling inside NpgsqlParameter. The general idea would be that if no parameter type information is provided, we would send the parameter as text with single quotes.

I need to check what is the behavior of sqlclient regarding parameters handling and try to mimic it in Npgsql. I know sqlserver has the Variant sqldbtype to indicate any parameter type. I think we could use a similar Unknown npgsqldbtype to mimic that.

Sorry for not being able to check this more thoroughly. I'll check it and show you a possible solution.

I think it will be possible to add it to 2.2.x because it wouldn't change current behavior for existing types.
I'll check this impact as well.

[roji]

No longer relevant in 3.0, we don't do any sort of casting.