cockroachdb / cockroachdb/cockroach

Type cannot be inferred for JDBC null bind variable in VALUES() clause

Open
#98,122 1 comment 0 reactions 0 assignees View on GitHub
C-bug O-community T-sql-foundations X-blathers-triaged
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.