cockroachdb / cockroachdb/cockroach
Type cannot be inferred for JDBC null bind variable in VALUES() clause
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
The following JDBC code snippet binding a `NULL` value to the `VALUES()` table constructor works with pgjdbc on PostgreSQL 15, but not on CockroachDB.
**To Reproduce**
```java
try (PreparedStatement s = connection.prepareStatement("select * from (values (?), (?)) as t(a)")) {
s.setInt(1, 1);
s.setObject(2, null);
try (ResultSet rs = s.executeQuery()) {
while (rs.next())
System.out.println(rs.getInt(1));
}
}
```
This works on PostgreSQL 15, printing:
```
1
null
```
But it doesn't work on CockroachDB, where it produces this error:
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: could not determine data type of placeholder $2
Hinweis: consider adding explicit type casts to the placeholder arguments
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
at org.jooq.testscripts.JDBC.main(JDBC.java:48)
```
The problem doesn't appear with `select ? union all select ?`
The workaround is to provide the type explicitly:
```java
s.setObject(2, null, Types.INTEGER);
```
Or to cast in SQL:
```sql
select * from (values (?), (cast(? as integer))) as t(a)
```
**Expected behavior**
A clear and concise description of what you expected to happen.
**Environment:**
- CockroachDB version: CockroachDB CCL v22.2.6 (x86_64-pc-linux-gnu, built 2023/03/03 19:31:13, go1.19.4)
- Server OS: Linux in Docker
- Client app: JDBC: `org.postgresql:postgresql:42.5.4`
Jira issue: CRDB-25087
Epic CRDB-60813
Contributor guide
Assessment
This issue has not been assessed yet.