Bug #11438 UNION causes error

• Submitted: 19 Jun 2005 19:56
• Modified: 10 Sep 2005 22:28
• Reporter: Robin Bailes
• Status: Not a Bug
• Category: MySQL Server: User-defined functions ( UDF )
• Severity: S2 (Serious)
• Version: 5.0.7
• OS: Microsoft Windows (Windows XP)
• 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"