Bug #11438 UNION causes error
Submitted: 19 Jun 2005 19:56 Modified: 10 Sep 2005 22:28
Reporter: Robin Bailes Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S2 (Serious)
Version:5.0.7 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[19 Jun 2005 19:56] Robin Bailes
Description:
UNION operator causes an error.  This worked under release 5.0.6, but stopped working under 5.0.7

How to repeat:
-- Create a test table
CREATE TABLE `testtable` (
  `Col1` varchar(20) NOT NULL,
  PRIMARY KEY  (`Col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Create some testdata
INSERT INTO testtable VALUES ('A'), ('B'), ('C'), ('D'); 

-- Create an inquiry procedure with a UNION
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`prcTest`$$
CREATE PROCEDURE `test`.`prcTest`()
begin
SELECT 'test' AS testCol
UNION
SELECT Col1
FROM testtable;
-- ORDER BY 1;

end$$

DELIMITER ;

-- Execute the procedure
CALL prcTest()

-- This error is displayed
Illegal mix of colations (utf8_general_ci,COERCIBLE) and (latin_swedish_ci,IMPLICI) for operation 'UNION'  ErrorNr. 1267
[10 Sep 2005 22:28] Hartmut Holzgraefe
language setting problem,

works ok with "SET NAMES latin1", 

fails with "SET NAMES utf8"