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:
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.06 OS:Windows (Windows 2000 SP4)
Assigned to: Assigned Account CPU Architecture:Any

[1 May 2004 5:44] Joshua Potts
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.

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

I have no idea
[1 May 2004 6:24] Joshua Potts
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.
[1 May 2004 6:35] Joshua Potts
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.

[1 May 2004 6:38] Joshua Potts
Second Client Workaround:

If using VB 6, do not use connection objects created in the Data Environment - create them in code.
[22 Jul 2004 16:25] MySQL Verification Team
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.
[13 Sep 2007 18:02] Jim Winstead
This needs to get re-checked now that we have options to handle 0000-00-00 differently.
[10 Oct 2007 14:45] MySQL Verification Team
Could you please test with latest released version 3.51.21. Thanks in
[11 Nov 2007 0:00] Bugs System
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".
[4 Jan 2008 14:44] Tonci Grgin
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.

, NDat DATE);
Query OK, 0 rows affected (0.20 sec)
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

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

        Message = "Rows returned = " & CStr(Count) & vbCrLf _
                & "Matches = " & CStr(Matches) & vbCrLf & vbCrLf _
                & IIf(Matches = Count, "Fetched rows with zero dates correctly.", _

        MsgBox(Message, vbInformation)
    End Sub
[29 May 2013 13:07] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.