"could not determine data type of parameter" when setting null parameters with specific type

[stevenschlansker]

Describe the issue

Hi, I am trying to send in a timestamp to PG that may or may not be null and drive some conditional logic by it. To my surprise, the parameter type can not be determined even when I specifically set it via setNull(1, Types.TIMESTAMP) or setObject(1, null, Types.TIMESTAMP).

I see there is some previous discussion around this at https://www.postgresql.org/message-id/CAG2M1febgZbhBwm0DQK4Y5ekDrTbzTyAa1syKcdXw%2B6Y8caK4g%40mail.gmail.com but it looks like what I am doing should work...

I apologize in advance if I missed some trivial detail that makes this my fault :)

Driver Version?

42.7.2

Java Version?

21.0.2

OS Version?
Mac 14.2.1

PostgreSQL Version?

15.3

To Reproduce

This code fails:

final var c = ...;
final var stmt = c.prepareStatement("select (? is null or ? < '2020-01-01')");
stmt.setNull(1, Types.TIMESTAMP);
stmt.setNull(2, Types.TIMESTAMP);
stmt.executeQuery();

This doesn't change anything:

stmt.setObject(1, null, Types.TIMESTAMP);
stmt.setObject(2, null, Types.TIMESTAMP);

I also tried Types.TIMESTAMP_WITH_TIME_ZONE with no change in behavior.

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
...

Expected behavior
I expected that specifying Types.TIMESTAMP would allow the backend to know the parameter type

Logs

2024-03-04 14:56:14.044 PST [80132] ERROR:  could not determine data type of parameter $1

[stevenschlansker]

Just to be clear, adding an explicit cast like ?::timestamptz fixes the issue. But I feel that that shouldn't be necessary if I specify the java.sql.Types type for the parameter.

[davecramer]

This is the most relevant explanation:
https://www.postgresql.org/message-id/CAB%3DJe-EAdHMPLEmgP110U1Gpzuqx0nLvd9%2BPRRz6d0iB_%2BSP%2BQ%40mail.gmail.com

see

pgjdbc/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java

Line 267 in 818953a

case Types.TIME_WITH_TIMEZONE:

[stevenschlansker]

Thanks for the specific reference, sorry I missed that - it's a long and detailed thread.
That's too bad that this is just how it is - it "feels" broken. But I cannot suggest any specific improvement, except that maybe this is worth somehow special casing for ? is null, so I will close this issue unless there's some hope of finding less surprising behavior :)

[davecramer]

seems broken to me as well, does work fine in simple query mode, but that has it's own issues.

[vlsi]

If we can't fix this (I do not know if we can), we might try improving the error message for the specific case