MySQL Bugs: #3621: MyODBC returns no rows when one has a zero date

Bug #3621	MyODBC returns no rows when one has a zero date
Submitted:	1 May 2004 5:44	Modified:	29 May 2013 13:07
Reporter:	Joshua Potts	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.06	OS:	Windows (Windows 2000 SP4)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When querying a table with a not-null date column, and some of the rows have zero-value dates (0000-00-00), MyODBC will not return any rows when called by ADODB.Connection.Execute(). This method takes two parameters - a SQL query and a counter. The counter will be filled with the number of rows that should have been returned (the number of rows that matched), but the recordset returned contains no rows.

Using:
MySQL 4.0
MyODBC 3.51.06
ADO 2.0
Visual Basic 6

How to repeat:
1. Create a table in MySQL that has a not-null date column
2. Create some rows with non-zero dates, and some rows with 0000-00-00 dates.
3. Setup MyODBC to connect to the database using a DSN
4. Connect to the DSN using ADO, specifically the ADODB.Connection object
5. Use ADODB.Connection.Execute with a query on the table that matches some rows with zero dates, and pass a counter to Execute()
6. Check the counter - it should indicate the number of matching rows
7. Check the recordset - it should have that many rows, but it's empty
8. Change the SQL query to include something like "WHERE testdate <> '0000-00-00'", and repeat steps 5 and 6.
9. Check the recordset - it will have the correct rows

Suggested fix:
Temporary fix for clients:
==========================
Using the WHERE clause, filter out the rows with zero dates

For MyODBC:
===========
I have no idea

Appears to be an ADO/VB 6 issue because when your connection is created this way:

Dim conn As Connection
Set conn = New Connection
conn.Open "yourdsnname"
conn.Execute(sql, counter)

the rows with zero dates will be returned, but when you use a Connection object created in the Data Environment designer and pointed at the same DSN with the same parameters, the rows with zero dates will not be returned.

Visual Basic 6 project showing difference between manual and Data Environment connection

Attachment: Zerodatetest_in_VB6_w_ADO2.zip (application/x-zip-compressed, text), 3.58 KiB.

Second Client Workaround:

If using VB 6, do not use connection objects created in the Data Environment - create them in code.

I was able for to fetch the data using Access, ASP so I am assuming like
you noticed an issue ADO/VB 6.

Thank you for the bug report.

This needs to get re-checked now that we have options to handle 0000-00-00 differently.

Could you please test with latest released version 3.51.21. Thanks in
advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I would close this if all agree as problem seems to be in VB6 not MyODBC:

c:\mysql507\bin>mysql -uroot -p test
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.54-max-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bug3621 (Id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
, NDat DATE);
Query OK, 0 rows affected (0.20 sec)
--<cut>--
mysql> select * from bug3621;
+----+------------+
| Id | NDat       |
+----+------------+
|  1 | 0000-00-00 |
|  2 | 2008-01-04 |
|  3 | 2005-10-10 |
|  4 | NULL       |
|  5 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)

Following code works with/without FLAG_ZERO_DATE_TO_MIN on VS2003, ADODB 2.8:
        Const DSN = "Uid=root;Pwd=*****;Driver={MySQL ODBC 3.51 Driver};Server=QCore;Database=test;OPTION=3" '+16777216 << FLAG_ZERO_DATE_TO_MIN
        Const QUERY = "SELECT * FROM bug3621"

        Dim c As ADODB.Connection
        Dim Count As Long
        Dim Matches As Long
        Dim Message As String
        Dim Rows As ADODB.Recordset

        c = New ADODB.Connection
        c.Open(DSN)

        Rows = c.Execute(QUERY, Matches)
        Count = 0
        Do Until Rows.EOF
            Count = Count + 1
            Rows.MoveNext()
        Loop

        Message = "Rows returned = " & CStr(Count) & vbCrLf _
                & "Matches = " & CStr(Matches) & vbCrLf & vbCrLf _
                & IIf(Matches = Count, "Fetched rows with zero dates correctly.", _
                "FAILED TO FETCH CORRECT NUMBER OF ROWS.")

        MsgBox(Message, vbInformation)
    End Sub

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.