Bug #14414 SQLColumn() does not return timestamp nullable attribute correctly
Submitted: 28 Oct 2005 1:30 Modified: 7 Aug 2007 4:47
Reporter: Derek Lai Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.12 OS:Microsoft Windows (Windows 2000)
Assigned to: Jim Winstead CPU Architecture:Any

[28 Oct 2005 1:30] Derek Lai
Description:
Here is a original script used for populating the test table on MySQL:
create table Ac_Null_Datatypes (
   …
   timestamp_null    timestamp,
...
   );

When our C++ application uses SQLColumns() to retrieve column information, the nullable attribute is not returned correctly.

Precisely the calls are as follows:

SQLColumns( hstmt, qualifierName, qualifierLen,	ownerName, ownerLen,
	tableName, Acstrlen( ( const AcWChar * ) tableName ),
	NULL, 0 );
....
....
SQLBindCol(hstmt, 11, SQL_C_SHORT, &dbNullable, 0, &cbNullable);
SQLFetch(hstmt);

The dbNullable value retrieved for "timestamp_null" column is 0.   The correct value should be 1.

 

How to repeat:
Using the Microsoft ODBC Test application (odbcte32.exe) should reproduce the problem.   I cannot get it to work because upon connection the Microsoft ODBC Test application crashes.   I can connect through our own C++ application, trace the debugger and observe the returned value being incorrect.
[30 Oct 2005 9:17] 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.
[1 Nov 2005 18:28] Derek Lai
In my test, all other attributes seems to be correct.  It looks like the nullable attribute is the one that has problems, although I don't know if this is only a symptom and that there is some underlying problem affecting other attributes as well.
[9 Jan 2006 21:57] Derek Lai
Raised to S1.  Functionality is missing and there is no workaround available.
[3 Apr 2006 14:08] Tonci Grgin
Hi. Thanks for your problem report. Can you please provide me with the output of
mysql> show create table Ac_Null_Datatypes\G
command?
[3 Apr 2006 15:22] Tonci Grgin
From the manual (http://dev.mysql.com/doc/refman/4.1/en/silent-column-changes.html):
Before MySQL 4.1.6, you cannot store a literal NULL in a TIMESTAMP  column; setting it to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL  attributes do not apply in the normal way and are ignored if you specify them. DESCRIBE tbl_name always reports that a TIMESTAMP column can be assigned NULL values.
[6 Apr 2006 8:09] Bogdan Degtyariov
It is definitely a bug of MyODBC because SQLColumns returns 18 result fields. 
field #18 - IS_NULLABLE, indicates that column values can or canno't include NULLs (0 for timestamp column because it cannot include NULL), 
field #11 NULLABLE - indicates whether the column accepts NULL values (1 for timestamp column because it accepts NULL values without displaying errors. Of course it saves current date and time instead of NULL, therefore #18 is 0)

Bugfix is being prepeared for this bug.
[22 Aug 2006 21:58] Derek Lai
Any updates on this bug?  Is there any fix available yet?
[30 Jul 2007 21:34] Jim Winstead
Fix reporting of NULLABLE and IS_NULLABLE for TIMESTAMP fields

Attachment: bug14414.patch (text/plain), 2.94 KiB.

[30 Jul 2007 21:36] Jim Winstead
Bogdan's comment is incorrect. IS_NULLABLE and NULLABLE both have nothing to say about whether a NULL will ever be returned from the column, only whether it is allowed to store a NULL to a column (regardless of what may actually be stored due to triggers or TIMESTAMP behavior).
[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:47] MC Brown
A note has been added to the 3.51.18 changelog: 

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