MySQL Bugs: #27419: MyODBC 3.51.23 Functionnality Regression

Bug #27419	MyODBC 3.51.23 Functionnality Regression
Submitted:	24 Mar 2007 20:15	Modified:	25 Jan 2008 18:57
Reporter:	Mathieu Tremblay	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.23, 5.0.45	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
When I install MyODBC 3.51.14 With MySQL Server 5.0.37, About half of my queries are now crashing(100 queries). 

I'm using ADODB with delphi so I decided to come back with MyODBC 3.51.12.

Can you tell me what are supposed to be improve in the 3.51.14 version.

How to repeat:
Queries with DATE DATETIME COUNT and MORE...

Thank you for the bug report. Could you please provide test case to
repeat the issues you are experiencing: table definition with data
if needed, code of your client application, server version and any
information will be useful for analyze and reproduce the bugs.
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".

Hi Mathieu and thanks for your report. Sorry for the delay in processing.

Now, for your Button2 test please see Bug#28420. I explained changes in behavior there. As for your Button1 test please do try 3.51.15 and inform me of result.

I found another bug in MyODBC 3.51.14 and  3.51.15

Problem to do implicit conversion of String like this format: "29 86 66 16". The string is interpret as variant see the file TEST3.zip

This problem is not present in MyODBC 3.51.12

Contain script and Delphi code to test the third problem

Attachment: TEST3.zip (application/x-zip-compressed, text), 359.82 KiB.

Mathieu, this is the same problem as I already described, leading back to Bug#10491. The problem you're seeing is in, otherwise, perfectly correct fix added in MyODBC 3.51.14. What happens when:
 - you add explicit cast to DATE?
 - when you use 3.51.15?

If its the same problem, that not been corrected because I try with MyODBC 3.51.15 and the problem still occur.

Did you told me that there no issue to correct the problem ? So the problem would still going to occur ?

Mathieu, I would say yes... Tell me, why are both Delphi test cases exactly the same?
I'll try C test case now.

Mathieu, here's what I've found:
 - Part of the problem is server adding BINARY flag where it should not (Bug#10491)
 - Other part of problem is VARCHAR(20) returned as adVarChar(200) type instead of adChar(129) which is a bug in my opinion.

C:\mysql507\bin>mysql -uroot -T error
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 77
Server version: 5.0.38-log Source distribution

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

1) CONCAT behaves as described in manual returning STRING data type. You should try some cast in Delphi to make this work:
mysql> SELECT (SELECT CONCAT(CURRENT_DATE()," ",DateStart) FROM Day WHERE Numero
=0) AS DateStart, (SELECT CONCAT(CURRENT_DATE()+ INTERVAL 1 DAY," ",DateEnd) FRO
M Day WHERE Numero=0) AS DateEnd FROM DUAL;
Field   1:  `DateStart`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     19
Max_length: 19
Decimals:   31
Flags:

Field   2:  `DateEnd`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     38
Max_length: 19
Decimals:   31
Flags:

+---------------------+---------------------+
| DateStart           | DateEnd             |
+---------------------+---------------------+
| 2007-05-28 05:00:00 | 2007-05-29 04:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

2) CASTing fields directly to DATE adds BINARY flag (though I don't know why but I'm almost sure that this is reported as a server bug) causing behavior described in Bug#10491:
mysql> SELECT DATE((SELECT CONCAT(CURRENT_DATE()," ",DateStart) FROM Day WHERE N
umero=0)) AS DateStart, DATE((SELECT CONCAT(CURRENT_DATE()+ INTERVAL 1 DAY," ",D
ateEnd) FROM Day WHERE Numero=0)) AS DateEnd FROM DUAL;
Field   1:  `DateStart`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   31
Flags:      BINARY

Field   2:  `DateEnd`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   31
Flags:      BINARY

+------------+------------+
| DateStart  | DateEnd    |
+------------+------------+
| 2007-05-28 | 2007-05-29 |
+------------+------------+
1 row in set (0.00 sec)

3) Here we have some bug in Delphi ADO implementation. I've traced the problem to field "Raw" being recognized like "Memo" but "String" was expected. I can't see any reason other than Delphi bug for this... As you can see, MS generic ODBC client has no problems returning the data correctly:
mysql> select * from test1;
Field   1:  `Number`
Catalog:    `def`
Database:   `error`
Table:      `test1`
Org_table:  `test1`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `Raw`
Catalog:    `def`
Database:   `error`
Table:      `test1`
Org_table:  `test1`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     20
Max_length: 11
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

+--------+-------------+
| Number | Raw         |
+--------+-------------+
|      1 | 29 86 66 16 |
+--------+-------------+
1 row in set (0.00 sec)

Full Connect(Use Driver)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
Successfully connected to DSN 'myodbc1cln'.
SQLExecDirect: In: hstmt = 0x008B1978, szSqlStr = "use error", cbSqlStr = -3
Return:	SQL_SUCCESS=0
SQLExecDirect: In: hstmt = 0x008B1978, szSqlStr = "select * from test1", cbSqlStr = -3
Return:	SQL_SUCCESS=0

Get Data All:
"Number", "Raw"
1, "29 86 66 16"
1 row fetched from 2 columns.

SQLColumns:
	In:		StatementHandle = 0x008B1978, CatalogName = "error", 
NameLength1 = 5, SchemaName = "error", NameLength2 = 5, TableName = "test1", 
NameLength3 = 5, ColumnName = "Raw", NameLength4 = 3
Return:	SQL_SUCCESS=0

Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
"error", "", "test1", "Raw", 12, "varchar", 20, 20, <Null>, <Null>, 0, "", <Null>, 12, <Null>, 20, <Null>, "NO"
1 row fetched from 18 columns.

Trying from VBS I get adVarChar (200) for field Raw which might be wrong as it's only for Parameters:
adChar 	 	129 	A string value.
adVarChar 	200 	A string value (Parameter object only).

VBS test case

Attachment: Bug27419.vbs (application/octet-stream, text), 4.39 KiB.

The bugs have been corrected with the version MyODBC connector 3.51.16.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: Mathieu, yes, 3.51.16 works correctly.

The bugs have been reintroduce in the version MyODBC connector 3.51.23

SELECT CONCAT(CURRENT_DATE()," ","04:00:00") AS DateStart FROM Dual
is not consider a DateTime field with MyODBC 3.51.23 and was ok with previous version.

Any version of Connector/ODBC that treated the result of CONCAT() as a date was incorrect. CONCAT() always returns a string. And because CURRENT_DATE() returns a binary string, the result of CONCAT(CURRENT_DATE()," ","04:00:00") will also be a binary string. Some previous versions of Connector/ODBC got this incorrect.

If you want a non-binary string, you need to cast the result of CONCAT() to the type you want, such as: CAST(CONCAT(CURRENT_DATE()," ","04:00:00") AS CHAR)

You can see what types the server is returning by running the mysql command-line client with the "--column-type-info" flag. For example:

mysql> SELECT CURRENT_DATE(), CONCAT(CURRENT_DATE()," ","04:00:00"), CAST(CONCAT(CURRENT_DATE()," ","04:00:00") AS CHAR);
Field   1:  `CURRENT_DATE()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   0
Flags:      NOT_NULL BINARY 

Field   2:  `CONCAT(CURRENT_DATE()," ","04:00:00")`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   31
Flags:      NOT_NULL BINARY 

Field   3:  `CAST(CONCAT(CURRENT_DATE()," ","04:00:00") AS CHAR)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     19
Max_length: 19
Decimals:   31
Flags:      NOT_NULL 

+----------------+---------------------------------------+-----------------------------------------------------+
| CURRENT_DATE() | CONCAT(CURRENT_DATE()," ","04:00:00") | CAST(CONCAT(CURRENT_DATE()," ","04:00:00") AS CHAR) |
+----------------+---------------------------------------+-----------------------------------------------------+
| 2008-01-25     | 2008-01-25 04:00:00                   | 2008-01-25 04:00:00                                 | 
+----------------+---------------------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>