MySQL Bugs: #15249: Prepared select statement with "<primaryKey> IS NULL" delivers wrong result

Bug #15249	Prepared select statement with "<primaryKey> IS NULL" delivers wrong result
Submitted:	25 Nov 2005 17:04	Modified:	28 Nov 2005 19:15
Reporter:	Markus Lehmann	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	4.13, 4.15 JDBC Connector 3.1.11	OS:	MacOS (Mac OS X)
Assigned to:	Vasily Kishkin	CPU Architecture:	Any

Description:
I have a query like this:

SELECT SEQ_NUM,NAME FROM DEPARTMENT WHERE SEQ_NUM IS NULL

Since SEQ_NUM is the primary key the query should return 0 rows (it does when exceuted from the MySQL Query Browser).

When I use Java (JDBC Connector 3.1.11) and a prepared statement I get > 0 rows in the result set.

How to repeat:
String sql=''SELECT SEQ_NUM,NAME FROM DEPARTMENT WHERE SEQ_NUM IS NULL";

PreparedStatement preparedStatement=connection.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

ResultSet resultSet=preparedStatement.executeQuery();

... Wrong result

Could you please write here table definition ?

Here is the table definition (actually I do a select on all columns (explicitly named)):

CREATE TABLE `department` (
  `SEQ_NUM` bigint(20) NOT NULL auto_increment,
  `CREATION` datetime NOT NULL default '0000-00-00 00:00:00',
  `CR_USER_LINK` bigint(20) default NULL,
  `MODIFICATION` datetime NOT NULL default '0000-00-00 00:00:00',
  `MD_USER_LINK` bigint(20) default NULL,
  `DEPT_TYPE` char(1) NOT NULL default '',
  `NAME` varchar(64) NOT NULL default '',
  `ACCOUNT` varchar(10) default NULL,
  PRIMARY KEY  (`SEQ_NUM`),
  KEY `NAME_IDX` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Does the query work if you add "useServerPrepStmts=false" as a JDBC configuration parameter? (if so, the bug is most likely with the server's prepared statement feature).

Setting useServerPrepStmts=false in the URL does not change the behavior.

I tried to isolate the bug and I am not sure that the problem is in the JDBC driver because the scenario is this:

1. insert department (column SEQ_NUM is primary key and value is auto generated)

2. commit

3. select SEQ_NUM,... WHERE FROM DEPARTMENT WHERE SEQ_NUM IS NULL (in a prepared statement, same database connection)

Returns the newly created department (may be there is a problem with a cache that has not been modifiesd to contain the auto generated value for SEQ_NUM?).

If you're trying to make IS NULL return the record with the most recently auto-generated key, that only works for ODBC. Other clients do not set the flag on the server that causes this behavior because it is not standard.

You either need to use LAST_INSERT_ID() in the query, or use the JDBC method getGeneratedKeys() on your Statement to retrieve the value and set it as a parameter in your query.

Well, the sql described in my last comment is some kind of auto generated (from a database framework), so it is hard to see the reason behind.

Actually I do not expect to get records back from the select, the problem is that I actually get the last record back...

Sorry, had it backwards, this behavior is default with the server, see:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html 

(look for "IS NULL").

You can disable this behavior by adding "sessionVariables=SQL_AUTO_IS_NULL=0" as part of your JDBC connection configuration properties.

I use now a SET SESSION QL_AUTO_IS_NULL=0 and it works.

However I think that people using JDBC are not expecting the default behavior - so what about having the JDBC driver to send this command are creating a connection / session automatically

I really can't comment on whether a user expects the behavior or not just because it's JDBC. 

It's actually non-sensical to issue the query, given that with the feature turned off, you should almost always get no records returned, since most often users utilize AUTO_INCREMENT for primary keys, so the column in question will never be NULL.

There's an extra round-trip between client and server to set this by default (which most users do _not_ want), and it can be configured on the server as well via the "init_file" configuration parameter which will run the "SET ..." on the server.