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 10:19	Modified:	14 Sep 2007 14:43
Reporter:	Eric MaLossi
Status:	Closed
Category:	Connector/ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Microsoft Windows (win xp)
Assigned to:	Jim Winstead	Target Version:

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()