Bug: Cannot pass null in Parameter in Query for ISNULL

Hi,

Native PostgreSQL has no problem with queries like:

select id from author a where null is null or a.name = null

However the JDBC driver fails to process such a query with a
parameter:

ERROR: could not determine data type of parameter $1

The failure reproduces with a very simple and common JPQL query shown
at

http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples

SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) =
:lastName

While the final pass criterion is a JPA testcase, the error can also
be reproduced more directly with raw JDBC.

It appears to be unproductive to test for the "type" of null of a host
variable in "WHERE ? IS NULL" or "WHERE :lastName IS NULL"

Other drivers for databases e.g. MS SQL Server, Oracle, Sybase, mySQL
achieve the expected results.

To solve this defect would be quite rewarding because while the defect
appears to be perplexingly simple, the typical use cases are quite
prominient, useful and powerful.

Testcase JDBC (I can provide a zip file with both JPA and JBC cases if
required):

DROP TABLE REGION
CREATE TABLE REGION (ID INTEGER NOT NULL, PRIMARY KEY (ID))
DROP TABLE CUSTOMERORDER
CREATE TABLE CUSTOMERORDER (ID INTEGER NOT NULL, NAME VARCHAR(255),
region_id INTEGER, PRIMARY KEY (ID))
ALTER TABLE CUSTOMERORDER ADD CONSTRAINT FK_CUSTOMERORDER_region_id
FOREIGN KEY (region_id) REFERENCES REGION (ID)

package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class Jdbc {

private static final boolean NULL_WORKAROUND = false;

public static void main(String[] args){
try {
DriverManager.registerDriver(new org.postgresql.Driver());
String url = "jdbc:postgresql://localhost:5432/test";
String user ="postgres";
String password="passme";
Connection conn = DriverManager.getConnection(url, user,
password);
String sql = "SELECT ID, NAME, region_id FROM
CUSTOMERORDER WHERE ((? IS NULL) OR (region_id = ?))";
PreparedStatement pStmt = conn.prepareStatement(sql);
Integer regionId = null;
if(regionId == null && NULL_WORKAROUND){
pStmt.setNull(1, Types.INTEGER);
pStmt.setNull(2, Types.INTEGER);
}else{
pStmt.setObject(1, regionId);
pStmt.setObject(2, regionId);
}
ResultSet result = pStmt.executeQuery();
while(result.next()){
int id = result.getInt(1);
String name = result.getString(2);
Integer regionIdResult = (Integer)result.getObject(3);

}
} catch (SQLException ex) {
ex.printStackTrace();
}

}

}

On 30 November 2011 19:51, <bht(at)actrix(dot)gen(dot)nz> wrote:
> Hi,
>
> Native PostgreSQL has no problem with queries like:
>
> select id from author a where null is null or a.name = null
>
> However the JDBC driver fails to process such a query with a
> parameter:
>
> ERROR: could not determine data type of parameter $1

This is specific to calling PreparedStatement.setObject(index, null).
There is no type information provided when you call that, so it's not
entirely surprising you can get that error.
(Try a native PREPARE with a parameter type of "unknown" and you'll
see the same thing - it's not only JDBC)

To avoid this, use one of these instead:

* PreparedStatement.setObject(index, null, type)
* PreparedStatement.set<type>(index, null)
* PreparedStatement.setNull(index, type)

all of which provide type information that the driver needs.

Surely the JPA layer does know the type of the parameter it is
expecting, and so can easily call one of the variants that provides
type information?

Oliver

On 30 November 2011 20:01, Oliver Jowett <oliver(at)opencloud(dot)com> wrote:

> This is specific to calling PreparedStatement.setObject(index, null).

> To avoid this, use one of these instead:
>
>  * PreparedStatement.setObject(index, null, type)
>  * PreparedStatement.set<type>(index, null)
>  * PreparedStatement.setNull(index, type)
>
> all of which provide type information that the driver needs.

I'd also refer you to the JDBC javadoc for setObject(int,Object) which says:

==
Note: Not all databases allow for a non-typed Null to be sent to the
backend. For maximum portability, the setNull or the setObject(int
parameterIndex, Object x, int sqlType) method should be used instead
of setObject(int parameterIndex, Object x).
==

Oliver

Hi Oliver,

Your response is not entirely unexpected however perplexing in light
of the fact that other JDBC drivers don't have this bug. I have tested
them.

You are basically describing to me the nature of the bug from the
perspective of the driver's internals.

We can really only be interested in the resolution of it not in a
workaround. That is because I had already included the workaround in
my testcase posted to the mailing list - for illustration purposes.

The reason for not being able to use workarounds is that we are using
JPA which is a layer that is not accessible for modification.

It would be nice if you could take on board the obvious fact that it
is nonsense to test for the "type" of null that -
1) is only used in a parameter
2) the database does not have a problem processing natively
3) is correctly coded with ISNULL.

Don't you think that it would be worth the trouble spending some extra
driver coding, to detect and allow this scenario and pass the
perfectly valid and correct query to the database?

Kind Regards,

Bernard

On Wed, 30 Nov 2011 20:04:43 +1300, you wrote:

>> Hi,
>>
>> Native PostgreSQL has no problem with queries like:
>>
>> select id from author a where null is null or a.name = null
>>
>> However the JDBC driver fails to process such a query with a
>> parameter:
>>
>> ERROR: could not determine data type of parameter $1

>This is specific to calling PreparedStatement.setObject(index, null).
>There is no type information provided when you call that, so it's not
>entirely surprising you can get that error.
>(Try a native PREPARE with a parameter type of "unknown" and you'll
>see the same thing - it's not only JDBC)

> This is specific to calling PreparedStatement.setObject(index, null).

> To avoid this, use one of these instead:
>
>  * PreparedStatement.setObject(index, null, type)
>  * PreparedStatement.set<type>(index, null)
>  * PreparedStatement.setNull(index, type)
>
> all of which provide type information that the driver needs.

>I'd also refer you to the JDBC javadoc for setObject(int,Object) which says:

>==
>Note: Not all databases allow for a non-typed Null to be sent to the
>backend. For maximum portability, the setNull or the setObject(int
>parameterIndex, Object x, int sqlType) method should be used instead
>of setObject(int parameterIndex, Object x).
>==

>Oliver

On 30 November 2011 20:36, <bht(at)actrix(dot)gen(dot)nz> wrote:
> Hi Oliver,
>
> Your response is not entirely unexpected however perplexing in light
> of the fact that other JDBC drivers don't have this bug. I have tested
> them.

It's not a bug; this is not a case where the driver is behaving
incorrectly. It might be convenient for your application if the driver
supported this case differently, but in general JDBC drivers aren't
required to handle it, and it is technically difficult to handle it in
the postgresql driver. (And presumably in other drivers that you
haven't tested - the warning in the JDBC javadoc didn't come from
nowhere)

> The reason for not being able to use workarounds is that we are using
> JPA which is a layer that is not accessible for modification.

Have you asked your JPA vendor for a fix? Arguably, it's a
compatibility bug in your JPA layer - the JPA layer is doing something
that the JDBC javadoc explicitly says to avoid doing.

> Don't you think that it would be worth the trouble spending some extra
> driver coding, to detect and allow this scenario and pass the
> perfectly valid and correct query to the database?

No, I don't think it's worth the trouble, TBH. It's not going to be a
simple driver modification, because the error isn't even being
generated in the driver. The limitation is embedded deep in the
details of how statements are prepared and executed at the protocol
level. The short version: when a statement is prepared, the driver
gives the general, parameterized, form of the statement to the server,
along with the desired type of each parameter. Parameters where the
driver has no type information are passed as unknowns. During the
server's parsing of the statement, it attempts to infer types for any
parameters of unknown type. If the server can't infer a type for a
parameter from the context of the query, then the server generates the
error you're seeing. I can't see any way around that in the driver
without either inventing type information (would you like magical
unicorns with that?), or transforming the query in the driver, which
means you'll probably need a full-blown SQL parser in the driver too.
That way lies madness.

A more sensible approach might be something like a server-side change
to allow the case where the server can't infer a type for a parameter,
but the parameter was only used in contexts where the type didn't
matter (e.g. it is in an IS NULL expression). This probably has
implications for the parameter Bind step too (you'd have to skip
parsing non-null values since you don't have a type to use for
parsing). If you do go down that route, then the -hackers list is
probably a better venue for discussing the details.

Did you try the PREPARE case I suggested? That is more like what the
driver is doing than the literal text substitution of parameters that
you tried.

A workaround that might work (I haven't tried) would be to attach an
explicit cast to the parameter. That's basically doing the same as
your JPA layer *should* be doing, but doing it in the query text
rather than at the API level.

(I find it mildly amusing that this is the reverse of the usual
complaint, which is where applications call setString() and are then
upset when the driver actually tries to treat the parameter as a
String and runs headlong into a type mismatch.. we even have a driver
option that makes setString() *not* set type information as a
workaround!)

Oliver

(PS: posting to a public list from an address that bounces direct mail
is a bit obnoxious)

Hi Oliver,

Thanks for your reply. Onfortunately I cannot see a solution in your
elaboration. However I am still confident that there will be one at
some point in the future.

From an application perspective it is of course nonsense to code
variations of the same SQL statement depending on parameters having
specific values as required by the workaround. In case of JPA this
option does not even exist.

A Java application programmer has to go through extreme hassle in such
a simple case. That hassle is not worth it.

I would like to repeat that JDBC drivers with all other major database
engines do not have this problem. To achieve their level of quality
was apparently worthwhile for them.

It appears that PostreSQL/JDBC do things in a way that hurts. The kind
of picture that you are projecting looks horrible to me and I can
understand your frustration. I hope you are not suggesting that I
discuss this with PostgreSQL DB engine engineers. I really can't do
that so I will keep requesting a fix here.

Would it be possible that you open a request/ticket/issue where this
can be addressed formally? This could be on the driver side or on the
DB engine side - as long as the outcome is a resolution.

Kind Regards

Bernard

On Wed, 30 Nov 2011 21:22:33 +1300, you wrote:

>On 30 November 2011 20:36, <bht(at)actrix(dot)gen(dot)nz> wrote:
>> Hi Oliver,
>>
>> Your response is not entirely unexpected however perplexing in light
>> of the fact that other JDBC drivers don't have this bug. I have tested
>> them.
>
>It's not a bug; this is not a case where the driver is behaving
>incorrectly. It might be convenient for your application if the driver
>supported this case differently, but in general JDBC drivers aren't
>required to handle it, and it is technically difficult to handle it in
>the postgresql driver. (And presumably in other drivers that you
>haven't tested - the warning in the JDBC javadoc didn't come from
>nowhere)
>
>> The reason for not being able to use workarounds is that we are using
>> JPA which is a layer that is not accessible for modification.
>
>Have you asked your JPA vendor for a fix? Arguably, it's a
>compatibility bug in your JPA layer - the JPA layer is doing something
>that the JDBC javadoc explicitly says to avoid doing.
>
>> Don't you think that it would be worth the trouble spending some extra
>> driver coding, to detect and allow this scenario and pass the
>> perfectly valid and correct query to the database?
>
>No, I don't think it's worth the trouble, TBH. It's not going to be a
>simple driver modification, because the error isn't even being
>generated in the driver. The limitation is embedded deep in the
>details of how statements are prepared and executed at the protocol
>level. The short version: when a statement is prepared, the driver
>gives the general, parameterized, form of the statement to the server,
>along with the desired type of each parameter. Parameters where the
>driver has no type information are passed as unknowns. During the
>server's parsing of the statement, it attempts to infer types for any
>parameters of unknown type. If the server can't infer a type for a
>parameter from the context of the query, then the server generates the
>error you're seeing. I can't see any way around that in the driver
>without either inventing type information (would you like magical
>unicorns with that?), or transforming the query in the driver, which
>means you'll probably need a full-blown SQL parser in the driver too.
>That way lies madness.
>
>A more sensible approach might be something like a server-side change
>to allow the case where the server can't infer a type for a parameter,
>but the parameter was only used in contexts where the type didn't
>matter (e.g. it is in an IS NULL expression). This probably has
>implications for the parameter Bind step too (you'd have to skip
>parsing non-null values since you don't have a type to use for
>parsing). If you do go down that route, then the -hackers list is
>probably a better venue for discussing the details.
>
>Did you try the PREPARE case I suggested? That is more like what the
>driver is doing than the literal text substitution of parameters that
>you tried.
>
>A workaround that might work (I haven't tried) would be to attach an
>explicit cast to the parameter. That's basically doing the same as
>your JPA layer *should* be doing, but doing it in the query text
>rather than at the API level.
>
>(I find it mildly amusing that this is the reverse of the usual
>complaint, which is where applications call setString() and are then
>upset when the driver actually tries to treat the parameter as a
>String and runs headlong into a type mismatch.. we even have a driver
>option that makes setString() *not* set type information as a
>workaround!)
>
>Oliver
>
>(PS: posting to a public list from an address that bounces direct mail
>is a bit obnoxious)

On 12/01/2011 09:58 AM, bht(at)actrix(dot)gen(dot)nz wrote:
> Hi Oliver,
>
> Thanks for your reply. Onfortunately I cannot see a solution in your
> elaboration. However I am still confident that there will be one at
> some point in the future.
>
> From an application perspective it is of course nonsense to code
> variations of the same SQL statement depending on parameters having
> specific values as required by the workaround. In case of JPA this
> option does not even exist.
>
> A Java application programmer has to go through extreme hassle in such
> a simple case. That hassle is not worth it.

So far it has been established that the JPA implementation you are
using uses an unportable hack that happens to work for some DB vendors
but not with postgresql.

Can you comment on why our proposal to remove the hack from the JPA
implementation and replace it with the JDBC recommended way of sending
NULL is not the correct way forward?

This would enable the JPA driver to work with any DB vendor providing
a standard compliant JDBC driver such as postgres, mysql, oracle etc.

As the promise of a JPA layer is to abstract the DB specifics away,
when you report the issue to your JPA vendor it should be of high
priority to them to fix their code. In this particular case the
JPA implementation has the correct type information readily at hand
but the forgets to pass it to the JDBC driver as recommeneded in
JDBC specification.

PS. Have you tried if a newer version of the JPA implementation you are
using has already fixed the problem?

-Mikko

On 1 December 2011 20:58, <bht(at)actrix(dot)gen(dot)nz> wrote:

> I hope you are not suggesting that I
> discuss this with PostgreSQL DB engine engineers. I really can't do
> that so I will keep requesting a fix here.

Actually, that was exactly what I was suggesting. As I described in my
previous mail, it's really not something that can be handled in the
driver. You want a change to how parameters of unknown type are
handled in IS NULL expressions; that's entirely server-side, the
driver has no idea what an IS NULL expression looks like. You'll
probably want a stronger argument than "our JPA implementation doesn't
pass type information" first though.

> Would it be possible that you open a request/ticket/issue where this
> can be addressed formally? This could be on the driver side or on the
> DB engine side - as long as the outcome is a resolution.

The -jdbc list is the right place to do this for the JDBC driver, so
you're already there.
For changes to the backend, the -hackers list is the place to discuss them.
There is a -bugs list & a bug reporting form that feeds that, but
usually any in-depth discussion moves on to the appropriate list.

That said, I don't have plans to look at this further, so you'll
either need to convince someone else that it's worth working on, or
provide a patch yourself. That's the way that most things beyond
obvious bugfixes get implemented - someone scratches their own itch
and contributes the changes.

If you want more support than that, there are 3rd parties that can
provide commercial support for you - see
http://www.postgresql.org/support/professional_support/

Oliver

On 1 December 2011 22:21, Mikko Tiihonen
<mikko(dot)tiihonen(at)nitorcreations(dot)com> wrote:

> So far it has been established that the JPA implementation you are
> using uses an unportable hack that happens to work for some DB vendors
> but not with postgresql.

While we're at it - what is the JPA implementation in question?

Oliver

On 12/01/2011 05:38 PM, Oliver Jowett wrote:
> On 1 December 2011 22:21, Mikko Tiihonen
> <mikko(dot)tiihonen(at)nitorcreations(dot)com> wrote:
>
>> So far it has been established that the JPA implementation you are
>> using uses an unportable hack that happens to work for some DB vendors
>> but not with postgresql.
>
> While we're at it - what is the JPA implementation in question?

I'd be interested too, as I've had no issues related to null handling
using EclipseLink or Hibernate (3 and 4) with PostgreSQL.

(on a side-note):

I *have* had annoying issues working with the `xml' datatype (and other
text-like types) where I'd like to work with them as String in Java.
This is really a JPA deficiency as much as a PgJDBC one, in that JPA
doesn't really offer a way to say "this is the Java type, but please use
this other SQLTYPE when passing it to the JDBC driver". The JPA
implementation would use setString(...) instead of setObject(...) with
the SQLXML type constant, and Pg would refuse to accept a `text' value
for an `xml' field.

I kind of wish there was an implicit cast from `text' to `xml' since
there's well-formedness checking done anyway, but in this case the real
problem is that JPA doesn't let you specify an explicit JPA type or
override how the value is passed to the JDBC driver. Some specific JPA
implementations offer extensions for this, though.

--
Craig Ringer

>> I hope you are not suggesting that I
>> discuss this with PostgreSQL DB engine engineers. I really can't do
>> that so I will keep requesting a fix here.
>
> Actually, that was exactly what I was suggesting. As I described in my
> previous mail, it's really not something that can be handled in the
> driver. You want a change to how parameters of unknown type are
> handled in IS NULL expressions; that's entirely server-side, the
> driver has no idea what an IS NULL expression looks like. You'll
> probably want a stronger argument than "our JPA implementation doesn't
> pass type information" first though.

Actually, since this is the root of the problem, I'd be curious as to
how all the other major drivers/DBs handle this if they work as
claimed. The other drivers must either inline the values into the SQL
(i.e., the driver takes care of parameterization) which side-steps
this problem (trading it for others), have server-side support for
this (I'd be surprised if this is widely supported since it's not a
trivial feature and not that broadly useful), or work some unexpected
magic in the driver (though I'm rather skeptical that something is
feasible there). If you're serious about this, a good way to continue
this discussion would be to illustrate how this works in other
implementations of the spec.

I guess one possible way to do this would be to only inline null
values when binding parameters, although that seems on the dangerous
side of clever (and you'd have a hell of a time getting it to work
with named prepared statements).
---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

Maciek Sakrejda <msakrejda(at)truviso(dot)com> writes:
> I guess one possible way to do this would be to only inline null
> values when binding parameters, although that seems on the dangerous
> side of clever (and you'd have a hell of a time getting it to work
> with named prepared statements).

That seems unlikely to help much. Presumably, the OP isn't interested
in a constant-true result, so what he's really doing is hoping that
"$1 IS NULL" will report whether or not the passed parameter is null,
when sometimes it will be and sometimes it won't.

The difficulty from the server's point of view is really what data type
should be reported for the parameter symbol, if the client does a
Describe on the prepared statement. There is no context that would let
us guess what the application is thinking will happen. If we simply
removed the error checks about this, what would happen is that the
server would report "unknown" (yes, there is actually a type named
"unknown" in the Postgres system catalogs), and maybe the client-side
code would deal gracefully with that or maybe it wouldn't. If we try to
resolve the unknown to any specific ordinary type, we're likely to break
things even worse, if the application is expecting something different.

A lesser problem is that if we let the type stand as "unknown", the
existing server I/O functions take that as equivalent to "text".
Which means that if you send some binary data (perhaps because the
application thinks the parameter is integer), the server will most
likely spit up, because the data isn't a well-formed string in the
proper encoding. We could probably redefine things to avoid this
issue, but I'd be a bit worried about breakage.

In the end the nastiest issues are probably on the driver or interface
library. If it doesn't know what data type the parameter is supposed to
be, how is it going to go about forwarding a value to the server? Dumb
libraries like libpq may not have much of an issue here, but anything
with any intelligence is likely to be unhappy.

regards, tom lane

I've been thinking some more about this:

> Presumably, the OP isn't interested
> in a constant-true result, so what he's really doing is hoping that
> "$1 IS NULL" will report whether or not the passed parameter is null,
> when sometimes it will be and sometimes it won't.

Well, yes, but I think we only need to treat NULL parameters
specially. Really, what we're interested in is a situation like the
following in JDBC code:

stmt.setObject(1, param);

The idea is to get a simple do-what-I-mean Java-type-to-oid mapping.
Now, if param is an instance of something, the driver can just look up
the Java class of that type, see what oid that maps to in the backend,
and send this info in Parse. So any time the driver needs to send an
actual value, we don't need to send the unknown oid, so the encoding
is not a problem. In fact, I think this is going to be the case for
client code in any strongly-typed language (that is, any driver will
support most parameter types through a driver-managed do-what-I-mean
mapping from the client language's native types to PostgreSQL types).

If param is null, however, the driver leaves this unspecified because
it doesn't have any type information on the Java side. The backend
complains because it can't infer the type from the null.

However, if the value is actually null, the type information should be
unnecessary in most (all?) cases.

So...

> The difficulty from the server's point of view is really what data type
> should be reported for the parameter symbol, if the client does a
> Describe on the prepared statement. There is no context that would let
> us guess what the application is thinking will happen. If we simply
> removed the error checks about this, what would happen is that the
> server would report "unknown"

I got curious, changed the driver to send the unknown oid in the case
where a null parameter is provided without additional type information
(previously it was unspecified), took out the check where the back-end
complains if some parameter types remain unknown (I leave the check
for unspecified) after the call to parse_analyze_varparams in
postgres.c (the check remains for InvalidOid) and tried again. Make
check passes and the OP's test case passes. However, the JDBC test
suite has a couple of failures:

[junit] Testsuite: org.postgresql.test.jdbc2.Jdbc2TestSuite
[junit] Tests run: 296, Failures: 0, Errors: 2, Time elapsed: 83.009 sec
[junit]
[junit] Testcase:
testSetNull(org.postgresql.test.jdbc2.ArrayTest): Caused an ERROR
[junit] Can't change resolved type for param: 1 from 705 to 1007
...
[junit]
[junit] Testcase:
testSetNull(org.postgresql.test.jdbc2.PreparedStatementTest): Caused
an ERROR
[junit] Can't change resolved type for param: 1 from 705 to 25

I believe what's happening is that these used to be unspecified
because of a setObject(index, null), leaving the server to figure out
the type. Now they explicitly tell the server it's unknown, and later
say "oh wait, it's an array", and the server complains. In theory, it
should be possible for the server to handle this sort of "upgrade"
situation, but I'm not volunteering to add that.

I think more graceful server behavior on unspecified types for null
parameters would be nice, but as Oliver said, it looks like there is
some back-end work here. Any thoughts on the general reasoning,
though?

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com