MySQL Bugs: #13011: UNION can not keep the length of a char binary field

Bug #13011	UNION can not keep the length of a char binary field
Submitted:	6 Sep 2005 11:02	Modified:	7 Feb 2008 15:07
Reporter:	ANGEL MARTIN	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Linux (RED HAT 3 & WINDOWS)
Assigned to:		CPU Architecture:	Any

Description:
I've been using the ODBC connector with "PAD CHAR TO FULL LENGTH" activated.

When I make a select in which there are some char binary field, the result of the query gives all the char binary field result with spaces at the end til the length in the definition of the field.

When I make a UNION o UNION ALL, the result of the query gives all the char binary field without spaces at the end  of the field.

The problem is that I've got some of the field that have some spaces at the end, 
so when I use these fields in a union I've got the wrong value from the field, because I lost the spaces at the end of the field.

How to repeat:
I can repeat always I make a UNION with this kind of fields.

Suggested fix:
The answer from the query could return spaces at the end of the field, if both of the fields are char binary.

Could you please provide the table schema with some data inserts and
a code sample (if possible for to run in Access).

Thanks in advance.

Compressed file with code in cpp. Just the function. It's been compiled with Borlnadc++ 5.0

Attachment: CODE.zip (application/zip, text), 1.28 KiB.

Compressed file with Insert Sentences with the information needed for the test

Attachment: PFCCICL.zip (application/zip, text), 6.38 KiB.

Compressed file with Insert Sentences with the information needed for the test

Attachment: PFCFUNC.zip (application/zip, text), 168.82 KiB.

I was able to reproduce this problem using the supplied files and sql statements

Tested under WinXP

Yes, it was tested under windows XP.

Best Regards

Angel, I know it's been a while but could you turn your attention to this bug once more please? I believe this is reflection of Bug#28577. Can you do the cl client test as described there? Also, versions of SW used here are too old, are you able to repeat the problem against newer releases?

The reason for my request (cl test) is in that I want to simplify your test case as much as possible.

Either server or MyODBC fixed this issue so I'm not seeing it any more (Buffer is padded to full length of 16):

+		Buffer	0x0012ff14 "1ec7bb          "	char [45]

Shortened version of test:
    SDWORD cbName;
    char Buffer[45];
    rc = SQLExecDirect(hstmt, (SQLCHAR *)"USE test", SQL_NTS);
    rc = SQLExecDirect(hstmt, (SQLCHAR *)"SELECT GGUID FROM bug28577 UNION SELECT GGUID FROM bug28577", SQL_NTS);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
    SQLINTEGER rs;
    rc = SQLFetch(hstmt);
    memset(Buffer,0,45);
    SQLGetData(hstmt,1,SQL_C_CHAR,Buffer,45,&cbName);

Table structure:
CREATE TABLE `bug28577` (
  `GGUID` char(16) character set utf8 collate utf8_bin NOT NULL,
  `Keyword` varchar(40) default NULL,
  PRIMARY KEY  (`GGUID`),
  KEY `IDX_Keyword41` (`Keyword`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
and data: insert into bug28577 values ('1ec7bb', 'test');

Environment:
 MySQL server 5.0.54BK on WinXP Pro SP2 localhost
 MyODBC 3.51.23 and a system DSN with "Pad Char To Full Length" checked