Bug #6321 strange error, string function FIELD(<uservariable content NULL>, ...

• Submitted: 29 Oct 2004 14:14
• Modified: 5 Nov 2004 10:10
• Reporter: Matthias Leich
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[29 Oct 2004 14:14] Matthias Leich

Description:
I get an error message, if the first parameter of the string function 
FIELD is a uservariable containing NULL . If I use a constant instead I get no error.
Example:

select FIELD( NULL,'1it','Hit','3it') as my_column ;
my_column
0
set @var= NULL ;
select FIELD( @var,'1it','Hit','3it') as my_column ;
ERROR HY000: Illegal mix of collations for operation 'field'

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL 4.1 compiled from source
        last ChangeSet@1.2061, 2004-10-28

How to repeat:
Please execute the statements above

[29 Oct 2004 15:13] Matthias Leich

One additional observation:
If I replace      select FIELD( @var,'1it','Hit','3it') as my_column ;
with a prepared statement like
   prepare stmt from "select FIELD( ?,'1it','Hit','3it') as my_column" ;
   execute stmt using @var ;
I get the expected result.

[5 Nov 2004 6:32] Alexander Barkov

A bit more descriptive example:

set @var= NULL;
select FIELD( @var,'1it','Hit') as my_column ;
ERROR 1270 (HY000): Illegal mix of collations (binary,NONE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'