Hi mysqllers,
1. following installation
localhost.addresses2> show global variables like "version%";
+-------------------------+------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------+
| version | 4.1.14-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
+-------------------------+------------------------------------------+
4 rows in set (0.00 sec)
localhost.addresses2>
2. following two tables are involved:
localhost.addresses2> describe contacts2;
+------------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------------+------------------+------+-----+-------------------+----------------+
| name | text | YES | | NULL
| |
| firm | text | YES | | NULL
| |
| title | text | YES | | NULL
| |
| phone | text | YES | | NULL
| |
| mail | text | YES | | NULL
| |
| comment | text | YES | | NULL
| |
| status | text | YES | | NULL
| |
| url | text | YES | | NULL
| |
| businesscategory | text | YES | | NULL
| |
| address | text | YES | | NULL
| |
| kanton | text | YES | | NULL
| |
| addon | text | YES | | NULL
| |
| givenname | text | YES | | NULL
| |
| history | text | YES | | NULL
| |
| favorit | text | YES | | NULL
| |
| last_update | timestamp | YES | | CURRENT_TIMESTAMP
| |
| counter | int(10) unsigned | | PRI | NULL |
auto_increment |
+------------------+------------------+------+-----+-------------------+----------------+
17 rows in set (0.00 sec)
localhost.addresses2>
localhost.addresses2> describe tasks2;
+------------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------------+------------------+------+-----+-------------------+----------------+
| task_name | text | YES | | NULL
| |
| actiony | text | YES | | NULL
| |
| date_start | date | YES | | NULL
| |
| date_end | date | YES | | NULL
| |
| date_last_action | date | YES | | NULL
| |
| date_next_action | date | YES | | NULL
| |
| start_time | time | YES | | NULL
| |
| end_time | time | YES | | NULL
| |
| task_address | text | YES | | NULL
| |
| task_comment | text | YES | | NULL
| |
| task_responsible | text | YES | | NULL
| |
| project | text | YES | | NULL
| |
| task_history | text | YES | | NULL
| |
| task_last_update | timestamp | YES | | CURRENT_TIMESTAMP
| |
| contact_link | int(11) | YES | | NULL
| |
| task_counter | int(10) unsigned | | PRI | NULL |
auto_increment |
+------------------+------------------+------+-----+-------------------+----------------+
16 rows in set (0.00 sec)
localhost.addresses2>
3. following character-sets and collations:
localhost.addresses2> show session variables like "character%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)
localhost.addresses2> show session variables like "collat%";
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
localhost.addresses2>
4. following problem:
when i do a union statement between the two tables, where obviousely at
least in one select of the union some fields are left open by the
placeholder "" or '' , these left-off fields are acted on with the
collation_database (latin1_swedish_ci), wherease all non-left-off fields
(i.e. those with a real column value in it are acted on with the
collation_connection (utf8_general_ci), which then turns out in the
error 1267.
if i give those left off fields a _latin1 converter, everything works
fine. to find out, however, if the _latin1 converter is necessary, is
not easy.
is there a better solution to solve this problem? straight away: is this
a bug?
5. following examples:
is accepted:
(SELECT name,task_name,actiony,date_last_action,counter,task_counter
FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link =
contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
UNION (SELECT name,firm,status,'', counter,'' FROM contacts2 WHERE name
LIKE '%meier%' ORDER BY counter LIMIT 100) ORDER BY counter LIMIT 100;
is accepted:
(SELECT
name,firm,status,businesscategory,addon,date_last_action,counter,task_counter
FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link =
contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
UNION (SELECT name,firm,status,businesscategory,addon, '', counter,''
FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
ORDER BY counter LIMIT 100;
is not accepted:
(SELECT
name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter
FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link =
contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
UNION (SELECT name,firm,status,businesscategory,addon, '','', counter,''
FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
ORDER BY counter LIMIT 100;
ERROR 1267 (HY000): Illegal mix of collations
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for
operation 'UNION'
is accepted:
(SELECT
name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter
FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link =
contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
UNION (SELECT name,firm,status,businesscategory,addon, _latin1'','',
counter,'' FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter
LIMIT 100) ORDER BY counter LIMIT 100;
suomi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
