sqlx says that the database returned an error on a query, but the query runs fine in the database

[nschmeller]

Bug Description

sqlx cannot compile basic queries like SELECT * FROM information_schema.tables for the streaming database Materialize. However, it can compile Materialize-specific syntax like CREATE CLUSTER ....

Specifically, sqlx complains with error returned from database: Expected SELECT, VALUES, or a subquery in the query body, found identifier "verbose". When I run the same query in the database, it returns the expected results.

I understand that Materialize does not completely implement Postgres and that sqlx can't claim to support Materialize, but this message makes me think that the error that sqlx shows is not accurate (since the database is happy with the query), and that it should at least be more specific.

Minimal Reproduction

Running Materialize locally with DATABASE_URL=postgres://materialize@localhost:6875/materialize in the .env file.

sqlx::query!("SELECT * FROM information_schema.tables")
    .execute(&dbpool)
    .await
    .unwrap();

Info

• SQLx version: sqlx-cli 0.6.3
• SQLx features enabled: sqlx = { version = "0.6", features = [ "offline", "postgres", "runtime-tokio-native-tls" ] }
• Database server and version: Materialize (Postgres) v0.26.4
• Operating system: Linux ip-172-31-24-188 5.19.0-1022-aws #23~22.04.1-Ubuntu SMP Fri Mar 17 15:38:24 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux
• rustc --version: rustc 1.68.0 (2c8cc3432 2023-03-06) (built from a source tarball)

[nschmeller]

From the Materialize team (https://materializecommunity.slack.com/archives/C015KDVS7EV/p1684354996310519?thread_ts=1684352200.283219&cid=C015KDVS7EV):

The fundamental issue here is that sqlx is doing an EXPLAIN VERBOSE at build-time to typecheck the query, and of course Materialize's EXPLAIN syntax differs from Postgres' (most notably, as highlighted in the error above, we don't support the VERBOSE flag)
See https://docs.rs/sqlx/latest/sqlx/macro.query.html#force-nullable for details:

In Postgres, we patch up this inference by analyzing EXPLAIN VERBOSE output (which is not well documented, is highly dependent on the query plan that Postgres generates, and may differ between releases) to find columns that are the result of left/right/full outer joins. This analysis errs on the side of producing false positives (marking columns nullable that are not in practice) but there are likely edge cases that it does not cover yet.

[nschmeller]

Turns out this is fixed by #2282