citext parameters silently fail

[bbstilson]

I have found these related issues/pull requests

https://github.com/launchbadge/sqlx/issues/3212

Description

When querying a citext column, the query will fail to find anything without explicitly casting the parameter to citext. If the types are incompatible, then I would expect the query to not compile. However, text is compatible with citext, so it is surprising that the query silently fails.

Reproduction steps

Given a table:

create table foos (name citext);

insert into foos(name) values ('foo'), ('bar');

this query fails, and by fails i mean does not return a row when it should:

sqlx::query!("select count(*) from foos where name = $1", "Foo")

this query succeeds:

sqlx::query!("select count(*) from foos where name = $1::citext", "Foo")

If I copy and paste the query into other libraries (like psycopg for python) or into psql itself, it works as expected.

SQLx version

0.8.3

Enabled SQLx features

"runtime-tokio-rustls",     "macros",     "postgres",     "json",     "chrono",     "uuid"

Database server and version

Postgres 15

Operating system

Ubuntu 22

Rust version

1.90.0

[abonander]

I believe this is because psql never sends parameter types when it prepares a query, so $1 in that case gets inferred to be citext.

However, we send the parameter types of the arguments to prevent any ambiguities, so the type of $1 becomes text, and since there's no operator definition for citext = text, it coerces the citext to text for a case-sensitive comparison.

I suppose in most cases we could just stop sending parameter types. We don't send them from the query!() macros since we don't know types yet at that point, so they're already required to be unambiguous.

But there may be some cases outside of the query macros where users are relying on the sending of parameter types to resolve ambiguities. I imagine most of the time the user wouldn't even be aware of this. So changing that has the potential to be quite disruptive.

Ultimately, this is just a really weird footgun with the design of citext. If they defined a citext = text operator then it would work as expected. But that would also be a subtle change to behavior that existing users may be relying on. It's just kind of a dumb situation all around.

[BeauGieskens]

I definitely agree that in a perfect world, citext = text should be properly defined by Postgres. In the absence of that though, suppose we wanted to set up Unicase so that it's always encoded as citext, and we would then give that as a parameter instead of &str. Would it be desirable/worth adding a new feature flag to SQLx to provide that? If so, I would be interested in creating a PR.

[bbstilson]

To make sure I'm understanding, it sounds like we're between a rock and a hard place. On the one hand, Postgres goofed up (though I'm sure there are good reasons for their design decisions) and made citext subtly incompatible with text. On the other, sqlx can't just change the behavior of an existing feature without a major version change (or a bunch of fan-fare).

Is there a path forward where sqlx::query! can error when a citext column is being queried without an explicit cast on the parameter? Strictly speaking, not backwards compatible, but the forward fix for users is trivial, and it at least tells the user about this footgun.