sql: Different handling of prepared statement arguments compared to Postgresql

[plcplc]

Describe the problem

CockroachDB handles arguments to prepared statements differently from Postgres.
CockroachDB requires all specified arguments to a prepared statement are used in the statement being prepared, while Postgresql doesn't.

To Reproduce

On CockroachDB:

defaultdb=> prepare args_test(int) as select 42;
ERROR:  too many types provided

Expected behavior

On Postgresql:

postgres=# prepare args_test(int) as select 42;
PREPARE

Environment:

• CockroachDB version v22.1.5
• Server OS: Linux/NixOS
• Client app: psql

Additional context

While I don't think this policy is unreasonable in and of itself, it does introduce an incompatibility with Postgresql.

This incompatibility surfaced for me in the context of adding support for CockroachDB to the Hasura GraphQL Engine.

Parallel hasura issue: hasura/graphql-engine#8792

Jira issue: CRDB-18726
Epic CRDB-11916

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-experience (found keywords: Hasura)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

Thanks for this issue.

For when we work on this, here are some tests we should add that verify the Postgres behavior that we should match.

# Regression test for #86375 - make sure there can be more provided types than
# placeholders during PREPARE (and vice-versa). During EXECUTE, the counts
# of placeholders and arguments must match.

statement ok
PREPARE args_test_many(int, int) as select $1

query I
EXECUTE args_test_many(1, 2)
----
1

query error wrong number of parameters for prepared statement "args_test_many": expected 2, got 1
EXECUTE args_test_many(1)

statement ok
PREPARE args_test_few(int) as select $1, $2::int

query II
EXECUTE args_test_few(1, 2)
----
1  2

query error wrong number of parameters for prepared statement "args_test_few": expected 2, got 1
EXECUTE args_test_few(1)