MySQL Bugs: #62219: ODBC missing columns

Bug #62219	ODBC missing columns
Submitted:	21 Aug 2011 9:31	Modified:	26 Sep 2011 14:11
Reporter:	John Fabinder	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1.8	OS:	Windows (XP Prof.)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
I use database 5.5.12 and ODBC 5.1.5

With 
Select rta_meldezeit,rta_betrag from rt_abrechnungen 
I get the result

|rta_meldezeit        |rta_betrag   |
|---------------------|-------------|
|20.08.2008 10:13:25  |       362,50|
|20.08.2008 08:24:57  |       715,23|
|---------------------|-------------|

but 
select Date_Format(rta_meldezeit,'%d.%m.%Y'),format(rta_rech_betrag,2)
from rt_abrechnungen

I receive

|      |
|------|
|      |
|      |
|------|
In combination with other columns these columns are missing.

When I use ODBC 3.51.12 I have correct results

|rta_meldezeit|rta_betrag   |
|-------------|-------------|
|20.08.2008   |362.50       |
|20.08.2008   |715.23       |
|-------------|-------------|

With ODBC 5.1.8 I checked the option "Always handle binary function results as character data"
whitout effect.
With ODBC 3.51.12 no options are checked.

The definion of the columns are
rta_meldezeit datetime
rta_bertag    double.

I think it is a big bug and not a feature!

How to repeat:
I can repeat on an other database (5.5.13) with the same table and field definition.

John, thank you for your report.
Before we continue, could you please clarify confirm the version you experience the problem with. Because in one place you say 5.1.5, in other 5.1.8. just to exclude misunderstanding on our side.

On my own PC I had version 5.1.5, on an other one 5.1.8
Now I updateed my PC to 5.1.8 with the same result.

test case

Attachment: bug62219.c (text/plain), 2.68 KiB.

John,

I have uploaded a C test case (bug62219.c), which I tried to use to verify this bug.
Here is the output I am getting:

DATETIME: [23.08.2011]   DOUBLE: [234.43]
DATETIME: [12.07.2010]   DOUBLE: [123.32]

Please try running it in your system and let me know what it displays for you.

I am sorry, but Ihave no C-compiler.
I use Delphi 2005 with BDE 5.01 ond Borland SQL Explorer 4.00
If I use the statment at MySql-workbench it is also correct.

John,

Please enable ODBC tracing (through ODBC Data Source Administrator / Tracing / Start Tracing now) and run your Delphi program. The driver manager should generate the ODBC trace file, which can help us a lot with identifying the issue.

Also, if you do not have a C compiler, perhaps you could send us a sample Delphi project, which repeats the problem?

I wrote a simple program in Delphi. This an the nessacary datatables are loaded up. Also the tracelog-file but in two parts.
I also can send you four hardcopies with the same results with ODBC 5.1.8 and 3.51.12
Tell me if you want.

Hi John,

Thanks for uploading the test case.
Please check the screenshot (bug62219.jpg), which shows that the result output starts from rta_eins_ort column instead of "MZ". 
Is that the problem you wanted to report? Please confirm.

Is it possible to somehow reduce the query and make it to work with just one column (something like "select Date_Format(rta_meldezeit,'%d.%m.%Y') MZ from rt_abrechnungen")? This would really simplify diagnosing the issue.

Unfortunately, I cannot modify the query in the query window because each time I click "Query1" button the query gets refreshed (I guess it is hardcoded in your program).

Thanks.

test case screenshot

Attachment: bug62219.jpg (image/jpeg, text), 27.91 KiB.

Hi Bogdan,
I modified the program: "query1" and "query2" only load the fixed queries into the memo. You can change now. New button "Qu-Exe" will execute the query from the memo.
Other question: can the problem come from the characterset? I use latin1.

John,

Thanks for uploading another test case.
I was able to allocate the problem.
The ODBC trace has the following entries that correspond to missing columns:

ODBCTest        770-7cc	ENTER SQLDescribeCol 
    HSTMT               027D1FF8
    UWORD                        1 
    UCHAR *             0x00FB7F5E 
    SWORD                       32 
    SWORD *             0x0012F436
    SWORD *             0x0012F3EE
    SQLULEN *           0x0012F3E8
    SWORD *             0x00FB7F84
    SWORD *             0x0012F434

ODBCTest        770-7cc	EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
    HSTMT        027D1FF8                   // stmt
    UWORD                 1                 // col number
    UCHAR *      0x00FB7F5E [       2] "MZ" // col name
    SWORD                       32          // buffer lenght
    SWORD *      0x0012F436 (2)             // returned col name length 2
    SWORD *      0x0012F3EE (-9)            // returned type (-9) SQL_C_WVARCHAR
    SQLULEN *    0x0012F3E8 (10)            // col size 10
    SWORD *      0x00FB7F84 (0)             // decimal digits
    SWORD *      0x0012F434 (1)             // nullability (1) SQL_NULLABLE

ODBCTest        770-7cc	ENTER SQLBindCol 
    HSTMT               027D1FF8
    UWORD                        1 
    SWORD                        1 <SQL_C_CHAR>
    PTR                0x00FB738C
    SQLLEN                    11
    SQLLEN *            0x00FB72FC

ODBCTest        770-7cc	EXIT  SQLBindCol  with return code 0 (SQL_SUCCESS)
    HSTMT               027D1FF8
    UWORD                        1 
    SWORD                        1 <SQL_C_CHAR>
    PTR                0x00FB738C
    SQLLEN                    11
    SQLLEN *            0x00FB72FC (0)

Even though the column was reported as SQL_C_WVARCHAR, the application is trying to bind SQL_C_CHAR buffer. This leads to misinterpreting the column data.

Here is the result of the query in mysql command line:

+------------+
| MZ         |
+------------+
| 24.08.2008 |
| 24.08.2008 |
| NULL       |
| 03.01.2002 |
| 30.11.2010 |
| 21.10.2008 |
| 24.08.2008 |
| 24.08.2008 |
+------------+

After setting CHARSET=utf8 for the appropriate DSN the result was displayed correctly in the Delphi application too (see screenshot2.jpg).

screenshot2

Attachment: screenshot2.jpg (image/jpeg, text), 52.50 KiB.

So far I have not seen any malfunction in the driver.
On the other hand, BDE is not interpreting the result correctly.
However, setting CHARSET=UTF8 fixes its incorrect behavior.

Conclusion: it is not a bug in MySQL Connector/ODBC.
Please let me know if there you have any clear evidences of the opposite.