MySQL Bugs: #26108: MyODBC ADO field attributes reporting adFldMayBeNull for not null columns

Bug #26108	MyODBC ADO field attributes reporting adFldMayBeNull for not null columns
Submitted:	6 Feb 2007 9:19	Modified:	14 Sep 2007 12:43
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Windows (win xp)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
There seems to be a problem when a table column is defined as AUTO_INCREMENT.  If the drivers see a column defined this way tell ADO that the field may be NULL, and is NULL'able.  This occurs even if you explicitly define the column as NOT NULL also.  The attributes in question are adFldMayBeNull, and adFldIsNullable.

A case can be made (and has in bug 3857) that if a field is AUTO_INCREMENT then you can set it to null, and the server will automatically set it to the next value.  However even the mysql client gets angry if you try to update a record and set the AUTO_INCREMENT column to NULL.  Therefore it isn't really Null'able.

Additionally, This problem doesn't seem to be present in 5.0.  There should be some consistency here.  

This test can also be found in the ADO compliance suite in file fldattributes.vbs

How to repeat:
mysql>CREATE TABLE ADO_TEST (C1 INT auto_increment primary key, C2 CHAR(1));
mysql>INSERT INTO ado_test VALUES (1, 'A');

## IN VB OR VBS
Dim strSQL As String
Dim strConn As String
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim fld As ADODB.Field

Public Function myTest()
    Set oConn = New ADODB.Connection
    Set oRs = New ADODB.Recordset
    strSQL = "select * from ado_test"
    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=3;"

    oConn.ConnectionString = strConn
    oConn.CursorLocation = CInt(adUseClient)
    oConn.Open
    oRs.Open strSQL, oConn, adOpenStatic
    oRs.MoveFirst

    For Each fld In oRs.Fields
       If fld.Name = "c1" Then
          MsgBox adFldMayBeNull And fld.Attributes
          MsgBox adFldIsNullable And fld.Attributes
       End If
    Next
End Function

*******************************
This returns both adFldMayBeNull, and adFldIsNullable.
So try again and make it explicit...

mysql>ALTER TABLE ado_test MODIFY COLUMN c1 INT auto_increment not null;

*********************************
Call function again and the same thing happens, so remove auto_increment

mysql>ALTER TABLE ado_test MODIFY COLUMN c1 INT not null;

Now if you run it, these two properties will return 0 as expected

My comment regarding v5.0 is evidently not correct as these two attributes aren't functioning there - see bug #26110.

To clarify this issue, there are two attributes in question, adFldIsNullable, and adFldMayBeNull.  The definitions of these are:

adFldMayBeNull: Indicates that you can read Null values from the field. 
adFldIsNullable Indicates that the field accepts Null values. 

A case could be made that adFldIsNullable could be correct in this instance, although it is still a bit odd if the column is explicitly NOT NULL.  However adFldMayBeNull can't be true, but is being reported as such.

Fix SQLDescribeCol to match nullability reported by SQLColumns

Attachment: bug26108.diff (text/plain), 3.32 KiB.

Fix committed, will be included in 3.51.21.

I should clarify that the behavior with this fix is to be consistent with what is returned for NULLABLE and IS_NULLABLE from SQLColumns(), not necessarily what is described by the ADO specification. That appears to be a mismatch between what ADO wants to report and what is available via ODBC.

A note has been added to the 3.51.21 changelog: 

When using ADO, a column marked as AUTO_INCREMENT could incorrectly report that the column allowed NULL values. This was dur to an issue with NULLABLE and IS_NULLABLE return values from the call to SQLColumns()