MySQL Bugs: #49655: SQLColAttribute SQL_DESC_NULLABLE incorrect info for auto

Bug #49655	SQLColAttribute SQL_DESC_NULLABLE incorrect info for auto_increment column
Submitted:	14 Dec 2009 3:18	Modified:	10 Jul 2012 9:28
Reporter:	Farid Zidan (Candidate Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any
Tags:	auto_increment, null, primary key, SQL_DESC_NULLABLE, SQLColAttribute SQL_DESC_NULLABLE

Description:
MySQL 5.1.37-community via TCP/IP Windows XP 64-bit
MySQL ODBC 5.1 Driver 5.01.06.00 Windows XP 64-bit and 32-bit

SQLColAttribute returns incorrect info SQL_NULLABLE (1) for primary key column that is auto_increment whereas column does not allow null

        SQLLEN nDescNullable = SQL_NULLABLE_UNKNOWN;
        rc = SQLColAttribute( hstmt,
                              nCol,
                              SQL_DESC_NULLABLE,
                              NULL,
                              0,
                              NULL,
                              &nDescNullable );

May be related to
http://bugs.mysql.com/bug.php?id=44966

How to repeat:
create table test_identity_null (
   ds_oid               integer not null auto_increment,
   dbms_name            varchar(255) not null,
   server_name          varchar(255),
   primary key (ds_oid)
);

execute SQLColAttribute SQL_DESC_NULLABLE on select * from the table, driver returns incorrect null info for column ds_oid (1st column in the resultset)

Suggested fix:
I am guessing data type has something to do with it since the driver returns correction null info for the other two columns in the table

incorrect null info for auoincrement primry key column

Attachment: autoincrement_null.JPG (image/jpeg, text), 40.83 KiB.

Farid,

this was obviously done deliberately:

	if ((field->flags & NOT_NULL_FLAG) &&
	  !(field->flags & TIMESTAMP_FLAG) &&
	  !(field->flags & AUTO_INCREMENT_FLAG))
	    irrec->nullable= SQL_NO_NULLS;
	else
	    irrec->nullable= SQL_NULLABLE;

The behavior of c/ODBC regarding this is well described in documentation:

	To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:
	You can find the row that contains the most recent AUTO_INCREMENT value by issuing a statement of the following form immediately after generating the value:
	SELECT * FROM tbl_name WHERE auto_col IS NULL
	This behavior can be disabled by setting SQL_AUTO_IS_NULL=0. See Section 5.1.4, “Session System Variables”.
	The MySQL server has a workaround for old versions of Microsoft Access
	(and possibly other products) that is no longer necessary, but is
	unfortunately enabled by default. We have to turn it off, or it causes
	other problems.

I will mark report as verified on the basis that we actually use old code (for example, Access now uses different queries to get real AI value) and that this old code does not check for sql_auto_is_null at all.

Assigning to JimW in order to get his opinion on this. Jim, please reassign later.

Just a side-note for things described in Bug#44966:
mysql> describe test_identity_null\G
*************************** 1. row ***************************
  Field: ds_oid
   Type: int(11)
   Null: NO
    Key: PRI
Default: NULL    <<<<<<
  Extra: auto_increment

Found also this Bug#26108
And although bug is about reporting auto_increment as nullable field, patch seems to add condition that auto_increment and timestamp fields are always nullable. If i understand correctly - to make data returned by different function consistent.

Check with Bug#44966.

--------------------------------------------------------------------------
REFINED PROBLEM DESCRIPTION
===========================

For auto-increment and timestamp field with auto-update/auto-set, we can
insert
NULL. So this make them SQL_NULLABLE field.
where,
     SQL_NULLABLE: The column allows NULL values.

(http://msdn.microsoft.com/en-us/library/windows/desktop/ms716289(v=vs.85).asp
x)

And same is used in code implementation. For auto-increment and
timestamp_flag (auto-update
and auto-initialize Timestamp ) columns SQL_NULLABLE is present.

   if ((field->flags & NOT_NULL_FLAG) &&
        !(field->flags & TIMESTAMP_FLAG) &&
        !(field->flags & AUTO_INCREMENT_FLAG))
      irrec->nullable= SQL_NO_NULLS;
    else
      irrec->nullable= SQL_NULLABLE;

If a field has TIMESTAMP_FLAG set, it's an auto-updating timestamp
field, and NULL can be stored to it (although it gets turned into
something else).
--------------------------------------------------------------------------

mysql> insert into test_identity_null( ds_oid,  dbms_name, server_name) values (
NULL, "dbms_name", "server_name");
Query OK, 1 row affected (0.00 sec)

mysql> select * from  test_identity_null;
+--------+-----------+-------------+
| ds_oid | dbms_name | server_name |
+--------+-----------+-------------+
|      1 | dbms_name | server_name |
|      2 | dbms_name | server_name |
+--------+-----------+-------------+
2 rows in set (0.00 sec)