Error on SELECT with UNION

Error on SELECT with UNION

Postby Tuanonheb » Thu 09 Mar 2006 11:50

This Query:

SELECT 0 AS ID, SPACE(75) AS Name
UNION
SELECT ID, Name FROM kunde
ORDER BY Name

results an error:
Illegal mix of collations (latin1_swedish_ci,COERCILBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'

TMyConnetion.Options.Charset = latin1
Table format = latin1
Database = MySQL 5.0.18nt
MyDac = 4.30.0.10 for Delphi 7 (Test with Delphi 2006 have same result)

MySQL Query Browser makes no Error with the same query and returns a correct resultset

if i change the query to:

SELECT 0 AS ID, COERCIBILITY(SPACE(75) COLLATE latin1_swedish_ci) AS Name
UNION
SELECT ID, Name FROM kunde
ORDER BY Name

no error results.

But why must i change the query? There is no UTF8 format defined.
Bug of MyDac?

JB :)
Tuanonheb
 
Posts: 4
Joined: Tue 26 Jul 2005 14:11

Postby swierzbicki » Thu 09 Mar 2006 14:12

Have you tried your query within mysql ?
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby Tuanonheb » Thu 09 Mar 2006 14:18

I have tried the query with MySQL Query Browser -> no error
Tuanonheb
 
Posts: 4
Joined: Tue 26 Jul 2005 14:11

Postby Antaeus » Fri 10 Mar 2006 09:59

Most possibly this error happens because you use different charsets on client side (latin1) and on server side (utf8). Try to set property UseUnicode to True to use unicode on client side too.
Antaeus
 
Posts: 2098
Joined: Tue 14 Feb 2006 10:14


Return to MySQL Data Access Components