Обсуждение: 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@actrix.gen.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@opencloud.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@actrix.gen.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@actrix.gen.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@actrix.gen.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@actrix.gen.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.tiihonen@nitorcreations.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.tiihonen@nitorcreations.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@truviso.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