Illegal collation?

Hi,

I'm using MySQL 5 on Fedora Core 5 Linux and I'm getting an error I've
never seen before. Here's the query

SELECT C.NAME, P.MODEL_NUM, P.NAME, CASE ISNULL(PC.RETAIL_PRICE) WHEN
1 THEN P.RETAIL_PRICE ELSE PC.RETAIL_PRICE END AS RETAIL_PRICE, CASE
ISNULL(PC.PRICE) WHEN 1 THEN P.PRICE ELSE PC.PRICE END AS PRICE, CASE
ISNULL(PC.DESCRIPTION) WHEN 1 THEN P.DESCRIPTION ELSE PC.DESCRIPTION
END AS DESCRIPTION FROM PROD_SUB_CATEGORIES S, PROD_PRODUCT_INK_TYPES
I, PROD_CATEGORIES C, PROD_PRODUCTS P LEFT JOIN
PROD_PRODUCT_CUSTOM_PRICES PC ON P.PRODUCT_ID = PC.PRODUCT_ID WHERE
P.SUB_CATEGORY_ID = S.SUB_CATEGORY_ID AND S.CATEGORY_ID =
C.CATEGORY_ID AND S.INK_TYPE_ID = I.INK_TYPE_ID AND I.INK_TYPE_ID = 1

and here's the error.

Illegal mix of collations (latin1_general_ci,IMPLICIT) and
(latin1_swedish_ci,IMPLICIT) for operation '='

What is wrong with my query?

Thanks, - Dave

laredotornado@zipmail.com wrote:
> Hi,
>
> I'm using MySQL 5 on Fedora Core 5 Linux and I'm getting an error I've
> never seen before. Here's the query
>
> SELECT C.NAME, P.MODEL_NUM, P.NAME, CASE ISNULL(PC.RETAIL_PRICE) WHEN
> 1 THEN P.RETAIL_PRICE ELSE PC.RETAIL_PRICE END AS RETAIL_PRICE, CASE
> ISNULL(PC.PRICE) WHEN 1 THEN P.PRICE ELSE PC.PRICE END AS PRICE, CASE
> ISNULL(PC.DESCRIPTION) WHEN 1 THEN P.DESCRIPTION ELSE PC.DESCRIPTION
> END AS DESCRIPTION FROM PROD_SUB_CATEGORIES S, PROD_PRODUCT_INK_TYPES
> I, PROD_CATEGORIES C, PROD_PRODUCTS P LEFT JOIN
> PROD_PRODUCT_CUSTOM_PRICES PC ON P.PRODUCT_ID = PC.PRODUCT_ID WHERE
> P.SUB_CATEGORY_ID = S.SUB_CATEGORY_ID AND S.CATEGORY_ID =
> C.CATEGORY_ID AND S.INK_TYPE_ID = I.INK_TYPE_ID AND I.INK_TYPE_ID = 1
>
> and here's the error.
>
> Illegal mix of collations (latin1_general_ci,IMPLICIT) and
> (latin1_swedish_ci,IMPLICIT) for operation '='
>
> What is wrong with my query?
>
> Thanks, - Dave
>


Off hand I would suggest your tables have different collations. Some
are latin1_general_ci and others are latin_swedish _ci.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Feb 22, 7:34 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> laredotorn...@zipmail.com wrote:
> > Hi,
>
> > I'm using MySQL 5 on Fedora Core 5 Linux and I'm getting an error I've
> > never seen before. Here's the query
>
> > SELECT C.NAME, P.MODEL_NUM, P.NAME, CASE ISNULL(PC.RETAIL_PRICE) WHEN
> > 1 THEN P.RETAIL_PRICE ELSE PC.RETAIL_PRICE END AS RETAIL_PRICE, CASE
> > ISNULL(PC.PRICE) WHEN 1 THEN P.PRICE ELSE PC.PRICE END AS PRICE, CASE
> > ISNULL(PC.DESCRIPTION) WHEN 1 THEN P.DESCRIPTION ELSE PC.DESCRIPTION
> > END AS DESCRIPTION FROM PROD_SUB_CATEGORIES S, PROD_PRODUCT_INK_TYPES
> > I, PROD_CATEGORIES C, PROD_PRODUCTS P LEFT JOIN
> > PROD_PRODUCT_CUSTOM_PRICES PC ON P.PRODUCT_ID = PC.PRODUCT_ID WHERE
> > P.SUB_CATEGORY_ID = S.SUB_CATEGORY_ID AND S.CATEGORY_ID =
> > C.CATEGORY_ID AND S.INK_TYPE_ID = I.INK_TYPE_ID AND I.INK_TYPE_ID = 1
>
> > and here's the error.
>
> > Illegal mix of collations (latin1_general_ci,IMPLICIT) and
> > (latin1_swedish_ci,IMPLICIT) for operation '='
>
> > What is wrong with my query?
>
> > Thanks, - Dave
>
> Off hand I would suggest your tables have different collations. Some
> are latin1_general_ci and others are latin_swedish _ci.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

How do I find out what collation a table is? When I created my
tables, I didn't say anything about collations, only that the tables
were type = INNODB.

Thanks, - Dave

> How do I find out what collation a table is? When I created my
> tables, I didn't say anything about collations, only that the tables
> were type = INNODB.

It is the fields that have collations. The tables have collations also,
but they are the default for the fields. Just issue

SHOW FULL FIELDS FROM <table name>

and it will show the column collations.

Best regards
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

> It is the fields that have collations. The tables have collations also,
> but they are the default for the fields.

If you want to change the existing situation, look at the statement

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

(see http://dev.mysql.com/doc/refman/4.1/en/alter-table.html)
This can save you a lot of work.
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

"laredotornado@zipmail.com" <laredotornado@zipmail.com> wrote:
>
> I'm using MySQL 5 on Fedora Core 5 Linux and I'm getting an error I've
> never seen before. Here's the query
>
> SELECT C.NAME, P.MODEL_NUM, P.NAME, CASE ISNULL(PC.RETAIL_PRICE) WHEN
> 1 THEN P.RETAIL_PRICE ELSE PC.RETAIL_PRICE END AS RETAIL_PRICE, CASE
> ISNULL(PC.PRICE) WHEN 1 THEN P.PRICE ELSE PC.PRICE END AS PRICE, CASE
> ISNULL(PC.DESCRIPTION) WHEN 1 THEN P.DESCRIPTION ELSE PC.DESCRIPTION
> END AS DESCRIPTION FROM PROD_SUB_CATEGORIES S, PROD_PRODUCT_INK_TYPES
> I, PROD_CATEGORIES C, PROD_PRODUCTS P LEFT JOIN
> PROD_PRODUCT_CUSTOM_PRICES PC ON P.PRODUCT_ID = PC.PRODUCT_ID WHERE
> P.SUB_CATEGORY_ID = S.SUB_CATEGORY_ID AND S.CATEGORY_ID =
> C.CATEGORY_ID AND S.INK_TYPE_ID = I.INK_TYPE_ID AND I.INK_TYPE_ID = 1
>
> and here's the error.
>
> Illegal mix of collations (latin1_general_ci,IMPLICIT) and
> (latin1_swedish_ci,IMPLICIT) for operation '='
>
> What is wrong with my query?

You apparently try to compare two string-type columns having different
collations. I suggest you familiarize yourself with collations first:

http://dev.mysql.com/doc/refman/5.0/en/charset.html


Unfortunately you have multiple '=' in your query. So you have to check
each pair of columns used left and right of a '=' if their collations
match. You have been told about SHOW FULL FIELDS already.

BTW1: a column name ending in _ID is suspected to be of an integer
type, usually. Maybe you want to check your column types.

BTW2: you're mixing "," and JOIN. I suggest to switch to the JOIN
keyword. Many people are cought by the fact that the JOIN keyword
binds stronger than "," so in fact your JOIN is

S, I, C, (P LEFT JOIN PC) and not
(S, I, C, P) LEFT JOIN PC as you might have expected


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/