Bug #35896 4.1.22 Illegal mix of collations

Description:
Illegal mix of collations on 4.1.22, works fine on 5.x.
I am not sure my workaround is safe (using _utf8 before literals).
please advice.

How to repeat:
-- SETUP
CREATE TABLE `t` (`str` varchar(100) collate latin1_general_ci NOT NULL) 
ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `t` (`str`) VALUES('foo');

-- FAILS:
SELECT u FROM t right join (SELECT 'foobar' AS u) u2 ON u REGEXP str GROUP BY u HAVING count(str) > 0;

-- RESPONSE:
-- ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation 'regexp'

-- WORKS:
SELECT u FROM t right join (SELECT _utf8'foobar' AS u) u2 ON u REGEXP str GROUP BY u HAVING count(str) > 0;

Suggested fix:
my workaround is to add _utt8 before text literals.
not sure if this is safe.

Thank you for the report.

Yes, this bug has been fixed in version 5.0.

Seems to be duplicate of bug #21505.

Workarounds:

1. SET NAMES latin1 COLLATE latin1_general_ci;
2.  SELECT u, str FROM t right join (SELECT 'foobar' collate latin1_general_ci AS u) u2 on u regexp str;
3. Start server as mysqld --character-set-server=latin1 --collation-server=latin1_general_ci --skip-character-set-client-handshake

The proposed workarounds (as well as my own _utf8) workaround does not work.
they yield a similar error on my unit test against 4.1.22:

Database error: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation 'regexp'

MySQL Version: 4.1.22-standard

SQL Query:
SELECT useragent FROM firestats_useragent_classes right join (SELECT 'Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-TW; rv:1.8.1.6) Gecko/20070725 Firefox/2.0.0.6' collate latin1_general_ci AS useragent) urls ON useragent REGEXP wildcard GROUP BY useragent HAVING count(wildcard) > 0

my _utf8 workaround causes:
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'regexp' 

if you need help reproducing this, please let me know.

Thank you for the feedback.

Please provide output of SHOW CREATE TABLE firestats_useragent_classes, SHOW VARIABLES LIKE 'char%' and SHOW VARIABLES LIKE 'coll%'

Hi Sveta,
here is the requested output:

SHOW CREATE TABLE firestats_useragent_classes

+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                       | Create Table                                                                                                                                                                                                                                                        |
+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| firestats_useragent_classes | CREATE TABLE `firestats_useragent_classes` (
  `id` int(11) NOT NULL auto_increment COMMENT 'Primary key',
  `wildcard` varchar(100) NOT NULL default '' COMMENT 'Bots wildcard',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Bots table' |
+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SHOW VARIABLES LIKE 'char%';
+--------------------------+-------------------------------------------------------------------+
| Variable_name            | Value                                                             |
+--------------------------+-------------------------------------------------------------------+
| character_set_client     | latin1                                                            |
| character_set_connection | latin1                                                            |
| character_set_database   | latin1                                                            |
| character_set_results    | latin1                                                            |
| character_set_server     | latin1                                                            |
| character_set_system     | utf8                                                              |
| character_sets_dir       | /home/omry/dev/tools/mysql/bin/mysql-4.1.22/share/mysql/charsets/ |
+--------------------------+-------------------------------------------------------------------+

SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

Verified as described by using MySQL 4.1.25 (bk tree).

This only fails in version 4.1. It works fine in version 5.0.

is there a workaround for this problem?

I recommend you to upgrade to 5.0 version following the Manual's upgrade instructions.

I am developing an application that many users are running, and it supports mysql 4.1.x.

While MySQL 5.x deployment is getting better, 41% of my users are running older versions (31% of them runs 4.1).