Error on SELECT with UNION

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Tuanonheb
Posts: 4
Joined: Tue 26 Jul 2005 14:11

Error on SELECT with UNION

Post by 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 :)

swierzbicki
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Post by swierzbicki » Thu 09 Mar 2006 14:12

Have you tried your query within mysql ?

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

Post by Tuanonheb » Thu 09 Mar 2006 14:18

I have tried the query with MySQL Query Browser -> no error

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by 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.

Post Reply