MySQL Bugs: #14407: SQLColumns gives wrong information of not nulls

Bug #14407	SQLColumns gives wrong information of not nulls
Submitted:	27 Oct 2005 20:18	Modified:	7 Aug 2007 4:46
Reporter:	Henning Andersen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (Windows XP)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
HI,
I'm using MySQL version 5.0, odbc driver version 3.51.12.

I found that under certain circumstances the SQLColumns gives wrong information of a columns "not null" property. When looking at the column #11 (NULLABLE) and #18 (IS_NULLABLE) then it gives wrong result if the column is primary key and is auto_increment'ed.
But it seems only to occur if the column has the auto_increment attribute set.

How to repeat:
Create tables:
create table XX (
	XXID		INTEGER NOT NULL AUTO_INCREMENT ,
	primary key (XXID));

create table YY (
	YYID		INTEGER NOT NULL,
	primary key (YYID));

Upon reading the YYID column I get the correct answer that it is not nullable. But with XXID I get that it can be nullable which is wrong.

When running a describe in the MySQL command line client I can see that they are both created correctly:
mysql> describe xx;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| XXID  | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.05 sec)

mysql> describe yy;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| YYID  | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

Hello,
It also puzzles me a bit when looking at the output from the describe of table XX that it has a default value of "NULL" but is not allowed to have NULLs.
Maybe that is related to my bug report.
Best regards,
Henning

Hi,
FYI: I just realized that a previous bug report #10130 is similar yet slightly different. 

Best regards,
Henning

Could youo please say me SQLColumns returns other attributes correctly ? I tried to reproduce the bug in my test case but there are garbage instead of correct values.

Hello,
yes it also give wrong information of ordinal numbers, which I reported previously as bug # 10141. But other than that it gives me the information that I need and it seems to be correct.
Best regards,
Henning

Verified. My test case is attached.

Test case

Attachment: test.c (text/plain), 6.56 KiB.

Fix reporting of NULLABLE and IS_NULLABLE for AUTO_INCREMENT fields

Attachment: bug14407.patch (text/plain), 4.31 KiB.

The attached patch also includes the fix for Bug #14414.

The fix for this bug has been committed to the source repository, and will be in the 3.51.18 release.

Thanks for the bug report.

A note has been added to the 3.51.18 changelog: 

The SQLColumns() function could return incorrect information about AUTO_INCREMENT columns, indicating that the field was not nullable