MySQL Bugs: #1408: When a date column is 0000-00-00 the entire record cannot pass through MyODBC

Bug #1408	When a date column is 0000-00-00 the entire record cannot pass through MyODBC
Submitted:	26 Sep 2003 6:39	Modified:	2 Oct 2003 21:01
Reporter:	Emery Fabrice NZEYIMANA	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.06	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
I have a table with 15 columns and one of them is a date column. If for example I do a SELECT on that table in need for a record and the date column of that record contains 0000-00-00 as value, the entire record cannot pass through MyODBC.

How to repeat:
Create a table: For example

CREATE TABLE `tbl_mytable` (
  `id` varchar(11) NOT NULL default '',
  `names` varchar(50) NOT NULL default '',
  `birth_date` date NOT NULL default '0000-00-00',
  `sex` enum('M','F') NOT NULL default 'M',
  PRIMARY KEY  (`id`),
) TYPE=MyISAM;

# Dumping data for table
INSERT INTO `tbl_mytable` (`id`, `names`, `birth_date`, `sex`) VALUES("2000", "Emery Fabrice NZEYIMANA", "1976-09-05", "M");
INSERT INTO `tbl_mytable` (`id`, `names`, `birth_date`, `sex`) VALUES("1873", "Brigitte MUHORAKEYE", "0000-00-00", "F");

Doing SELECT * FROM tbl_mytable WHERE id='2000' will return values correctly (one record)

But Doing SELECT * FROM tbl_mytable WHERE id='1873' will return no values since the `birth_date` contains a '0000-00-00' value!!!

In a VB application the record_set will immediately have an EOF value set to true and a RecordSet.recordcount will generate an E_FAIL error.

Suggested fix:
Have MyODBC instructed not to validate DATEs coming from the server. Just give that date to the Application and handling should be done in the application.

I did a test using the ODBC tool: ODBCTE32.EXE and:

SQLExecDirect:
	In:  hstmt = 0x00991700, 
	    szSqlStr = "SELECT * FROM tbl_mytable WHERE id='1873'", cbSqlStr = -3
	Return:	SQL_SUCCESS=0

Get Data All:
"id", "names", "birth_date", "sex"
"1873", "Brigitte MUHORAKEYE", 0000-00-00, "F"
1 row fetched from 4 columns.

What do you think Venu ?

This bug and bug #7918 are similar, if not duplicates.

I can recreate this bug using Visual Basic 6, ADO 2.8, and MyODBC 3.51.12-win32.

CREATE TABLE `foo` (
  `pk` int(10) unsigned NOT NULL auto_increment,
  `df` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`pk`)

INSERT INTO `foo` VALUES ( NULL, '0000-00-00' );

The, in VB, create a new "Standard EXE" project.  Add the "Microsoft ActiveX Data Objects 2.8 Library" reference.  Finally, run the following code:

    Dim objODBCConnection As New ADODB.Connection
    Dim rstTest As New ADODB.Recordset
    Dim strSQL As String
        
    objODBCConnection.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=theServer;"DATABASE=theDatabase;UID=username;                      PWD=password;OPTION=3"

    objODBCConnection.open

    rstTest.CursorLocation = adUseClient

    strSQL = "SELECT * FROM foo"

    rstTest.Open strSQL, objODBCConnection

Place a breakpoint on the "rstTest.Open" line and a watch on "rstTest".  The status property after the line is executed becomes "Either BOF or EOF is True, or the current reocrd has been delete. Requested operations requires a current record."  Also notice that PageCount and RecordCount failed.

Check Bug#3621.