MySQL Bugs: #6875: ResultSetMetada.isNullable() return wrong value for CachedRowSet RI

Bug #6875	ResultSetMetada.isNullable() return wrong value for CachedRowSet RI
Submitted:	29 Nov 2004 12:54	Modified:	31 Mar 2014 7:54
Reporter:	Christophe JOLIF	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	3.0.16-ga	OS:	Windows (Windows XP)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
When a field of a table is in auto increment mode MySQL returns on the ResultSetMetadata that isNullable() is false while of course Null is a correct value (that will trigger the auto increment).
This would just be an annoying thing is Sun CachedRowSet Reference Implementation would not refuse to insert a row in the CachedRowSet for tables that contains at least one column with non nullable value (see that attached sample).

How to repeat:
package foo;

import java.sql.SQLException;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

/**
 * @author jolif
 *
 */
public class RowSetPb3
{
  final static String databaseURL = "jdbc:mysql:///ilog_demos";
  final static String user = "";
  final static String passwd =  "";
  final static String driverName = "com.mysql.jdbc.Driver";
  final static String TABLE_NAME = "activities";
     
  public RowSetPb3()
  {
  }

  // CJO 11/04
  public static void main(String[] arg)
    throws Exception
  {
    new RowSetPb3().test();
  }
  
  public void test() throws ClassNotFoundException, InstantiationException,
                            IllegalAccessException, SQLException, InterruptedException
  {
    Class.forName(driverName);
   
    CachedRowSet rowSet = new CachedRowSetImpl();
    rowSet.setUrl(databaseURL);
    rowSet.setUsername(user);
    rowSet.setPassword(passwd);
    rowSet.setCommand("select * from "+TABLE_NAME);
    rowSet.setReadOnly(false);
    rowSet.execute();     
/*    RowSetMetaData metadata = (RowSetMetaData)rowSet.getMetaData();    
    int columns = metadata.getColumnCount();
    for (int i = 0; i < columns; i++) {
      // workaround for mySQL vs how CachedRowSetImpl behaves
      if (metadata.isAutoIncrement(i+1)) {
        metadata.setNullable(i+1, RowSetMetaData.columnNullable);
      }
    }*/
    rowSet.moveToInsertRow();
    rowSet.insertRow();
    rowSet.moveToCurrentRow();
    rowSet.acceptChanges();    
  }
}

Suggested fix:
isNullable() should return true for column that have isAutoIncrement() returning true as indeed Null is an accepted value.

However, the issue is how 'true' we are to the JDBC spec in this case (and Sun's cached rowset as well).

The only way you _can_ have an autoincrement column is if it's defined _NOT_NULL_, which is what most clients of ResultSetMetaData will expect to see when they call isNullable().

If we implement this, it will have to be configurable, because other clients will expect the 'correct' behavior.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I close this report as "Not a Bug" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if something should be done here.