MySQL Bugs: #32568: MyODBC 3.51.22 using ADO 2.8

Bug #32568	MyODBC 3.51.22 using ADO 2.8
Submitted:	21 Nov 2007 12:24	Modified:	29 Nov 2007 17:47
Reporter:	Renato Gontijo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.22	OS:	Any (Visual Studio 6.0.)
Assigned to:		CPU Architecture:	Any
Tags:	ADO, MyODBC

Description:
Details environment test:
=========================
S.O...................: Windows Vista Business (32 bits)
MySQL Server version..: 5.1.22
MyODBC................: 3.51.22
ADO Version...........: 2.8

When I execute the SQL Command:

SELECT nm, IF(dt1 is null, dtnasc, dt1) as dt FROM cliente WHERE dtnasc BETWEEN '1950-01-01' AND '2007-01-01';

The recordset returns data ???????? when i'm using MyODBC 3.51.22, if i use MyODBC 3.51.21 the query returns correct values.

How to repeat:
Creating test database:
=======================
USE test;

CREATE TABLE `test`.`cliente` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `nm` VARCHAR(50) NOT NULL,
  `dtnasc` DATETIME NOT NULL,
  `dt1` DATETIME,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

INSERT INTO `test`.`cliente` VALUES(NULL, 'JOSE DA SILVA', '1980-02-08 00:00:00', NULL);
INSERT INTO `test`.`cliente` VALUES(NULL, 'MARIA DA SILVA', '1983-02-02 00:00:00', NULL);
INSERT INTO `test`.`cliente` VALUES(NULL, 'MARCELA JUNQUEIRA', '1970-02-27 00:00:00', '1970-02-27 01:00:00');
INSERT INTO `test`.`cliente` VALUES(NULL, 'SILVANETE MARISTELA', '1960-05-18 00:00:00', NULL);

Program used to test:
=====================

Private Sub Command1_Click()
    Dim conn   As ADODB.Connection
    Dim rs     As ADODB.Recordset
    Dim strSQL As String
    
    ' cria conexao
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=root;PWD=slxgatadm;"
    conn.Open
    
    
    ' cria recordset
    strSQL = "SELECT nm, IF(dt1 is null, dtnasc, dt1) as dt FROM cliente WHERE dtnasc BETWEEN '1950-01-01' AND '2007-01-01';"
    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly
    
    Do While Not rs.EOF
        Debug.Print rs!dt
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    
    conn.Close
    Set conn = Nothing
End Sub

Result (MyODBC 3.51.21):
========================
08/02/1980 
02/02/1983 
27/02/1970 
18/05/1960 

Results (MyODBC 3.51.22):
=========================
?????????
?????????
?????????
?????????

Hi Renato and thanks for your problem report. This is not a bug, although it seems to you like it is... Let me explain:

3.51.22 (13-Nov-2007)

  Functionality added or changed:
  * Removed workaround for bug#10491 in the server, which has now been
    fixed in MySQL Server 5.0.48 and 5.1.21.

Please use MySQL server 5.0.48 and up (or 5.1.21 and up) and the error will go away. The workaround has been removed as it was fixed in proper place, server.

I saw the errors but I guess this is an erros. I'm using Database Server 5.1.22.

Some notes (using MyODBC 3.51.22):
==================================
if i execute the following sql:
SELECT nm, dtnasc as dt FROM cliente WHERE dtnasc BETWEEN '1950-01-01' AND '2007-01-01';

the data response ok and i read it as well, 

but when i execute the sql with IF() function the data retrives with bad format.
SELECT nm, IF(dt1 is null, dtnasc, dt1) as dt FROM cliente WHERE dtnasc BETWEEN '1950-01-01' AND '2007-01-01';

the data retrived is incorrect, i can`t understand why data retrives diferent formats with IF() function and using directly field.

if i use function, in VB6, to convert data, i'm able to read it but this is impossible to implement.
For example:
rs!dt = ?????????
strconv(rs!dt, vbUnicode) = 1980-02-08 00:00:00

i guess that this version of myodbc has a bad behavior.

I saw the errors but I guess this is an erros. I'm using Database Server 5.1.22.

Some notes (using MyODBC 3.51.22):
==================================
if i execute the following sql:
SELECT nm, dtnasc as dt FROM cliente WHERE dtnasc BETWEEN '1950-01-01' AND '2007-01-01';

the data response ok and i read it as well, 

but when i execute the sql with IF() function the data retrives with bad format.
SELECT nm, IF(dt1 is null, dtnasc, dt1) as dt FROM cliente WHERE dtnasc BETWEEN
'1950-01-01' AND '2007-01-01';

the data retrived is incorrect, i can`t understand why data retrives diferent formats with
IF() function and using directly field.

if i use function, in VB6, to convert data, i'm able to read it but this is impossible to
implement.
For example:
rs!dt = ?????????
strconv(rs!dt, vbUnicode) = 1980-02-08 00:00:00

i guess that this version of myodbc has a wrong behavior.

This is not a bug in the ODBC driver. The type returned by IF() when the second and third arguments are datetime types is a binary string, not a datetime. You can explicitly cast the type of the IF() to a datetime using CAST(IF(...) AS DATETIME).

(It could be argued that IF() should return a datetime if the second and third arguments are datetime types. That would be a bug in the server.)

You can see this by using the command-line client to display the type information (using -T in 5.0, or --column-type-info in 5.1):

mysql> create table t1 (a datetime not null, b datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values ('2007-01-13 16:30:00', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select a, b, IF(b is null, a, b) c from t1;
Field   1:  `a`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY NO_DEFAULT_VALUE 

Field   2:  `b`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 0
Decimals:   0
Flags:      BINARY 

Field   3:  `c`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   6
Flags:      BINARY 

+---------------------+------+---------------------+
| a                   | b    | c                   |
+---------------------+------+---------------------+
| 2007-01-13 16:30:00 | NULL | 2007-01-13 16:30:00 | 
+---------------------+------+---------------------+
1 row in set (0.00 sec)

Thanks Jim.
Renato, please search bugsdb since there were a lot of problems like this one reported in last few months.

I disagree...
I use IF() function and in true part and false part data type is the same, a field with datetime, the return value would be a datetime data type, why binary?!
It's more logical do a CAST() if i need to return results diferent of true and false parts, when both are equal.
If true and false parts use different data type a binary would be returned, no problem in this case.
This new feature break a compatible with SQL Server 2005, Access and Oracle, i hate this.

I disagree and i continue using myodbc 3.51.21 because it's much better.

Please, if possible, include a link in myodbc downloads to changelog connectors, it's more easy to analyze the new features in connectors.
Thanks.

I don't disagree that the return type of IF() is bad in the case of datetime handling. But this is a problem in the server, not the ODBC driver. That it behaved as you desired in previous releases was itself a bug in the driver, which caused problems for things other than this instance of IF() and datetimes.

ok, i agree that this is a database resource, but i updated only myodbc, i was using mysql server 5.1.22 and myodbc 3.51.21, i only update myodbc to version 3.51.22 and the problem happens, i guess that has a problem in myodbc 3.51.22 because if i uninstall myodbc 3.51.22 and install 3.51.21 it has a behavior (using the same mysql server 5.1.22), only in myodbc 3.51.22 this happens.