Bug #53738 Concat is not working with ucs2 character set
Submitted: 18 May 2010 9:39 Modified: 21 Jun 2010 8:56
Reporter: Sameer Sharma Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.1.41 OS:Linux (redhat)
Assigned to: CPU Architecture:Any
Tags: character set, collation

[18 May 2010 9:39] Sameer Sharma
Description:
Concat function is not working with ucs2 character set when we are trying to concat different data types like varchar,date,int.
if we give all the values as varchar its working.

How to repeat:
DROP PROCEDURE IF EXISTS EMPLOYEE;
CREATE PROCEDURE EMPLOYEE(IN FROM_DATE DATE,
                         IN TO_DATE DATE,
                         IN DEPT VARCHAR (500),
                         IN LOGIN_ID VARCHAR (50),
                         IN START_INDEX INT,
                         IN END_LIMIT INT,
                         IN ORDER_BY_CLAUSE VARCHAR (50)
)
	BEGIN
			set @sql	=	
						concat(' SELECT * FROM EMPLOYEE WHERE ',
									 ' DATE_FORMAT(DATE_OF_JOIN,''%Y-%m-%d'') BETWEEN DATE_FORMAT(''',FROM_DATE,''',''%Y-%m-%d'') ',
									 ' AND DATE_FORMAT(''',TO_DATE,''',''%Y-%m-%d'') AND EMP_DEPT IN (',DEPT,')',
									 ' ORDER BY ',
									 ORDER_BY_CLAUSE,
									 ' LIMIT ',
									 START_INDEX,
									 ',',
									 END_LIMIT
									 );
			PREPARE STMT FROM @sql;
			EXECUTE STMT;
	END;						 
									 

Suggested fix:
EXPECTING THAT...
[19 May 2010 5:01] Susanne Ebrecht
Many thanks for writing a bug report.

Unfortunately, I am not able to see the bug here.

Where do you have ucs2 characters here?
[19 May 2010 6:48] Sameer Sharma
ucs2 is in DB setting.

Go to information_schema -> System Objects -> SCHEMATA  and set DEFAULT_COLLATION_NAME & DEFAULT_CHARACTER_SET_NAME as ucs2 & ucs2_general_ci respectively. 

After setting this try exceuting the query .

The DEFAULT_COLLATION_NAME & DEFAULT_CHARACTER_SET_NAME as latin1 & latin1_swedish_ci Concat will work fine. Issue is only with ucs2 & ucs2_general_ci
[21 May 2010 8:56] Susanne Ebrecht
Please show output of:
SHOW VARIABLES LIKE '%char%';
[21 Jun 2010 23:00] Bugs System
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".