Re: Bug: Cannot pass null in Parameter in Query for ISNULL

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