Bug #30235 Bad result expected in DATETIME Function with MyODBC
Submitted: 3 Aug 2007 21:36 Modified: 4 Oct 2007 0:02
Reporter: DOUGLAS HERRERIAS Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.17, 3.51.19 OS:Microsoft Windows
Assigned to: Jim Winstead CPU Architecture:Any
Tags: date, MyODBC

[3 Aug 2007 21:36] DOUGLAS HERRERIAS
Description:
For example:
SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field

This should return: 
'2007-08-03 17:32:00'

but returns:
?????????

It happens for other MySQL date functions like DATE_ADD.
I'm using MS Windows XP SP2, VB6 with ADO 2.8, MyODBC 3.51.17
Server: MySQL 5.0.45 for Linux 

In MyODBC version 3.51.12 this problem doesn't happen.

How to repeat:
Dim cnn As ADODB.Connection
Dim Vcon as string
Dim rs As ADODB.Recordset, vResult as string

Vcon  = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=192.168.0.1;" & _
        "DATABASE=mydb;" & _
        "USER=root;" & _
        "PASSWORD=1234;" & _
        "PORT=3306;" & _
        "OPTION=" & 1 + 2 + 8 + 32

Set cnn = New ADODB.Connection
cnn.Open Vcon

Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.ActiveConnection = cnn
.Source = "SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field"
.Open
End With
vResult = rs("field").Value
[19 Aug 2007 14:39] Tonci Grgin
Hello Douglas and thanks for your report.
Indeed there seems to be a problem with this, much like the one that made us release 3.51.19:

Server version: 5.0.48-pb1038-log MySQL Pushbuild Edition, build 1038

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

mysql> SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field;
Field   1:  `field`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   31
Flags:      BINARY

+---------------------+
| field               |
+---------------------+
| 2007-08-03 17:32:00 |
+---------------------+

odbcte32.exe test:
	Full Connect(Use Driver)
	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
	Successfully connected to DSN 'myodbc1cln'.
SQLExecDirect:
	In:	hstmt = 0x008B19C0, szSqlStr = "SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINU...", cbSqlStr = -3
	Return:	SQL_SUCCESS=0

Get Data All:
"field"
0x2007-08-03 17:32:00
1 row fetched from 1 column.

As you can see, the result is returned as "0x2007-08-03 17:32:00" so you should trim leading "0x" or explicitly cast value to something suitable until this is fixed.

Accompanying SQL log shows the problem:
odbcte32        111c-bb0	EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
		HSTMT               008B1C60
		UWORD                        1 
		UCHAR *             0x0009B9A0 
		SWORD                      100 
		SWORD *             0x00000000
		SWORD *             0x0009BA04 (-2) << SQL_BINARY
		SQLULEN *           0x0009BA08 (58)
		SWORD *             0x00000000
		SWORD *             0x00000000

odbcte32        111c-bb0	ENTER SQLFetch 
		HSTMT               008B1C60

odbcte32        111c-bb0	EXIT  SQLFetch  with return code 0 (SQL_SUCCESS)
		HSTMT               008B1C60

odbcte32        111c-bb0	ENTER SQLGetData 
		HSTMT               008B1C60
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                 0x0009BDA8 
		SQLLEN                   174
		SQLLEN *            0x0006F5FC

odbcte32        111c-bb0	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               008B1C60
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                 0x0009BDA8 [      19] "2007-08-03 17:32:00"
		SQLLEN                   174
		SQLLEN *            0x0006F5FC (19)

The SQL log is in accordance to metadata sent by server (field has Collation:  binary (63) and FLAG: BINARY set) and on trail of bug#10491...
[19 Aug 2007 14:40] Tonci Grgin
Adding 3.51.19 to version, raising severity to S2.
[4 Oct 2007 0:02] Jim Winstead
This is not a bug in the ODBC driver, but a server limitation. The type returned by DATE_SUB() is a binary string if the first argument to DATE_SUB() is a string. You can avoid this by wrapping the first argument in the DATE() function, which will cause DATE_SUB() to return a DATETIME, and things will work correctly.

I wasn't able to dig up the bug filed against the server for these issues, but opened a new bug (Bug #31384) for the specific problem of DATE_ADD() and DATE_SUB() returning an unhelpful data type.