Citext comparison not working properly in version 3.2.5

[nescada]

After upgrading to the version npgsql 3.2.5 (from the 2.2.7 version) queries against citext columns started behaving as a normal text, this means that instead of ignoring the case as it was in the previous version it is checking if the value matches exactly the provided parameter value.

Apparently this behavior started in the version 3.0 remains as described in the 3.2.7 version.

Code Sample

        NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres;" +
                                "Password=postgres;Database=test;");
        conn.Open();
        
        NpgsqlCommand cmd = new NpgsqlCommand("Select * from test_table where citext_col=@val", conn);
      
        cmd.Parameters.AddWithValue("@val", "Value");

        NpgsqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
            Console.Write("{0}\n", dr[0]);

Further technical details

Npgsql version: 3.+
PostgreSQL version: 9.5
Operating system: Windows Seven

[MrFabio]

I have the same issue with 3.2.5. On version 2.2.7 the conditions with citext datatype work as expected.

[lansdon2]

Ditto, our citext data types/columns don't query correctly in 3.x. I get the following exception with our existing code:
The field 'data' has a type currently unknown to Npgsql (OID 84143). You can retrieve it as a string by marking it as unknown, please see the FAQ.
In 2.x these columns would read as strings without a problem, and GetFieldType() says that it's a string type. But GetString() throws the exception.

[YohDeadfall]

I'll take a look at it. @roji assign me please.

[roji]

Here are some thoughts, but if you think there's more feel free to follow up @YohDeadfall.

@nescada tl;dr you should probably set your parameter's NpgsqlDbType to Citext. Here's why.

Unlike Npgsql 2.x, 3.x is a strongly-typed client which sends data types with parameter data. In your code above, the default datatype for strings is text, so you're executing something similar to the following:

select * from test_table where citext_col='some_value'::text

(note the explicit cast to text). PostgreSQL's citext behavior seems to be to apply case-insensitive comparison only if both comparands are of type citext, which isn't the case here, so you get case-sensitive comparison. So you must tell Npgsql to send a citext parameter via the NpgsqlDbType enum. For those curious, Npgsql 2.x used to send untyped parameter data, allowing PostgreSQL to implicitly cast based on context (this is why this used to work), but this caused other issues.

@lansdon2, your issue may be caused by adding the citext extension (CREATE EXTENSION citext) in the same process trying to access the data, in which case you may need to add a call to NpgsqlConnection.ReloadTypes(). Otherwise please post a code sample which demonstrates the issue, it's hard to know without more information.

[YohDeadfall]

@roji I'll wait for answers and if @nescada and @lansdon confirm that it's not a bug, it would be worth to document that in the remarks section of NpgsqlParameter.

[roji]

OK. Yeah, the parameters page definitely needs work.

[nescada]

@roji shouldn't citext have a differnt behaviour since it doesn't have a direct correspondence with the native datatypes?

If we send the exact same command using a string format instead of using parameters the query work normally.

[lansdon2]

NpgsqlConnection.ReloadTypes() seems to be helping my problem of reading citext fields. Thanks for the help!

[roji]

@lansdon2 great to hear.

@nescada:

@roji shouldn't citext have a differnt behaviour since it doesn't have a direct correspondence with the native datatypes?

I'm not sure I understand your question... In a sense you're right: as I wrote above, if you just send a string parameter without specifying its NpgsqlDbType, Npgsql will map that to the PostgreSQL text type, because that's the PostgreSQL type most fitting by default. Indeed, the citext datatype doesn't have a corresponding CLR type - there's no case-insensitive string in .NET - so we use .NET string and require the user to explicitly specify citext via NpgsqlDbType.

If we send the exact same command using a string format instead of using parameters the query work normally.

Do you mean the following?

select * from test_table where citext_col='some_value'

If so, this should correctly perform a case-insensitive comparison as I explained above, because the type of some_value is unknown. However, if you replace some_value by a parameter, that parameter will be typed, defaulting to text. Let me know if this is still in any way unclear.

Am going to close this issue as everything seems to be resolved, but we can definitely continue the conversation.