JDK-4312435 : using setNull() in a WHERE clause doesn't work (4029237 NOTt fixed)
  • Type: Bug
  • Component: core-libs
  • Sub-Component: java.sql
  • Affected Version: 1.3.0
  • Priority: P3
  • Status: Closed
  • Resolution: Not an Issue
  • OS: windows_nt
  • CPU: x86
  • Submitted: 2000-02-14
  • Updated: 2000-03-06
  • Resolved: 2000-02-14
Description

Name: krT82822			Date: 02/14/2000


java version "1.3.0rc1"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0rc1-T)
Java HotSpot(TM) Client VM (build 1.3.0rc1-S, mixed mode)

This problem was submitted previously, and evaluated as a
duplicate of bug #4029237, which is supposed to be fixed in kestrel.
After testing again with the jdk 1.3 rc1, I found out that the problem is NOT
fixed.  [Perhaps it is fixed in the final kestrel, but that cannot be determined from RC1.]

Again, the problem is limited to bind variables in the WHERE clause that can be
either (sql)null or not (sql)null. Using setNull() to set null variables in
other parts of a sql statement works fine.

By the way: I doubt that it is a bridge issue. I have the same problem using the
Oracle Thin Driver against an oracle db. seems to be a generic parsing problem.

The test case is still in your bug db.

-----------------------------

from previous report (#94830):

The problem can be summarized in the following example:
   UPDATE TAB1 SET B=? WHERE A=?
   A and B can be null or not null

Using setNull() to set the value of B works fine. Using setNull() to set A
doesn't produce the expected result. I have the same problem with
   SELECT * FROM TAB1 where A=?
if A = null, the statement returns 0 rows (should return 1 row in my test case).
INSERT statements work fine. The problem seems to be restricted to the WHERE clause.
I am using JDK1.2.2 (same pb with 1.2.0 and 1.2.1). I tested the following piece
of code with an Oracle db (Oracle thin driver and JdbcDdbc drv) and MS Access
(JdbcOdbc drv). Seems to be a generic issue.

Note: a workaround like SELECT ... WHERE A IS NULL is not applicable. I need generic routines to process null and not null values.

Test case:
   drop table TAB1;
   create table TAB1 (A number, B number);
   insert into TAB1(A,B) values(1, 10);
   insert into TAB1(A,B) values(null, 20);
   commit;

Output:
   select: count=1 A=1                            ...OK
   select: count=0 A=null                         ...wrong. should return count=1
   update: count=1 A=1 B=111                      ...OK
   update: count=1 A=1 B=null                     ...OK
   update: count=0 A=null B=222                   ...wrong. should return count=1

Test code:
import java.sql.*;
public class JdbcTest
{
   Connection           conn = null;
   PreparedStatement    stmt = null;

   // genericSelect()
   void genericSelect(Object a)
   {
      String sql = "select count(*) from TAB1 where A=?";
      try
      {
         stmt = conn.prepareStatement(sql);
         if (a == null)    stmt.setNull(1, Types.INTEGER);
         else              stmt.setObject(1, a);
         ResultSet rs = stmt.executeQuery();
         rs.next();
         System.out.println("select: count=" + rs.getObject(1) + " A=" + a);
      }
      catch (SQLException e)
      {
         System.out.println("ooops: " + e.getMessage());
      }
   }

   // genericUpdate()
   void genericUpdate(Object a, Object b)
   {
      final String sql = "update TAB1 set B=? where A=?";
      try
      {
         stmt = conn.prepareStatement(sql);
         if (b == null)    stmt.setNull(1, Types.INTEGER);
         else              stmt.setObject(1, b);
         if (a == null)    stmt.setNull(2, Types.INTEGER);
         else              stmt.setObject(2, a);

         int count = stmt.executeUpdate();
         System.out.println("update: count=" + count + " A=" + a + " B=" + b);
      }
      catch (SQLException e)
      {
         System.out.println("ooops: " + e.getMessage());
      }
   }

   // main()
   public static void main(String[] args)
   {
      JdbcTest appl = new JdbcTest();
      try
      {
         // register the driver
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         appl.conn = DriverManager.getConnection ("jdbc:odbc:Db1");
         appl.conn.setAutoCommit(false);
      }
      catch (Exception e)
      {
         System.out.println("Exception: " + e.getMessage());
      }

      appl.genericSelect(new Integer(1));                   // works...
      appl.genericSelect(null);                             // doesn't work...

      appl.genericUpdate(new Integer(1), new Integer(111)); // works...
      appl.genericUpdate(new Integer(1), null);             // works...
      appl.genericUpdate(null, new Integer(222));           // doesn't work...
   }
}

Thank you...
(Review ID: 101208) 
======================================================================

Comments
WORK AROUND Name: krT82822 Date: 02/14/2000 generate sql statements dynamically depending on the variable values (a nightmare with complex sql statements). ======================================================================
11-06-2004

PUBLIC COMMENTS .
10-06-2004

EVALUATION This is not a JDBC bug. The SQL equality predicate cannot be used to compare values to null. This is a 'feature' of SQL and is the reason that the 'is null' and 'is not null' constructs exist. The setNull() method is therefore only really useful for setting IN parameters such as the values list of an insert statement or the set list of an update statement. jon.ellis@Eng 2000-02-14
14-02-2000