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

[27 Oct 2005 20:18] Henning Andersen
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>
[27 Oct 2005 20:21] Henning Andersen
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
[27 Oct 2005 20:27] Henning Andersen
Hi,
FYI: I just realized that a previous bug report #10130 is similar yet slightly different. 

Best regards,
Henning
[30 Oct 2005 9:16] Vasily Kishkin
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.
[30 Oct 2005 16:35] Henning Andersen
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
[31 Oct 2005 8:34] Vasily Kishkin
Verified. My test case is attached.
[31 Oct 2005 8:35] Vasily Kishkin
Test case

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

[30 Jul 2007 21:43] Jim Winstead
Fix reporting of NULLABLE and IS_NULLABLE for AUTO_INCREMENT fields

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

[30 Jul 2007 21:44] Jim Winstead
The attached patch also includes the fix for Bug #14414.
[3 Aug 2007 15:29] Jim Winstead
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.
[7 Aug 2007 4:46] MC Brown
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