Bug #37065 Error in SELECT with row constructor used ( in ODBC 5.1, not in ODBC 3.51)
Submitted: 29 May 2008 11:02 Modified: 29 May 2008 15:25
Reporter: Eugen Schülter Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.1 OS:Windows
Assigned to: CPU Architecture:Any
Tags: ODBC 5.1 collation

[29 May 2008 11:02] Eugen Schülter
Description:
Select statment returns:
[MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
The same statement using the ODBC 3.51 connector runs fine.

The select uses the row construction feature.
"SELECT * FROM aa WHERE (a1,a2)=('ball','test')"

The WORKAROUND is to rewrite it to:
"SELECT * FROM aa WHERE a1='ball' AND a2'test'"

How to repeat:
mysql> create table aa (a1 varchar(6), a2 varchar(6));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into aa values ('hugo', 'otto'), ('ball', 'test');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from aa;
+------+------+
| a1   | a2   |
+------+------+
| hugo | otto |
| ball | test |
+------+------+
2 rows in set (0.00 sec)

###################################################
Now in the client program (VB6.0 with ADO):

gCnMain is a ADODB.connection object using
"ODBC;DATABASE=arv3main;DRIVER={MySQL ODBC 5.1 Driver};PORT=6666;SERVER=127.0.0.1;OPTION=3"
as connection string.

...
Dim s
Dim rs As ADODB.Recordset
s = "SELECT * FROM aa WHERE (a1,a2)=('ball','test')"
Set rs = gCnMain.Execute(s)
rs.Close

[MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
[29 May 2008 11:11] Tonci Grgin
Hi Eugen and thanks for your report. However it has nothing to do with c/ODBC:

c:\mysql507\bin>mysql -uroot -p test
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.60-pb1096-log MySQL Pushbuild Edition, build 1096

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table aa (a1 varchar(6), a2 varchar(6));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into aa values ('hugo', 'otto'), ('ball', 'test');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM aa WHERE (a1,a2)=('ball','test');
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (la
tin1_swedish_ci,COERCIBLE) for operation '='
mysql>

Leaving this for my colleagues.
[29 May 2008 11:20] Tonci Grgin
A word on MyODBC. MyODBC 3.51 and 5.1 handle c-sets very differently and 5.1 will *never* do things like 3.51 branch does them. Thus reporting that something works on 3.51 and does not work on 5.1 really means nothing (in cases like this). As my mysql cl client test shows, there's a problem in server. We'll have to see if it's a bug or something else.
[29 May 2008 12:28] MySQL Verification Team
Thank you for the bug report. I can't repeat with current source server:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.64-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table aa (a1 varchar(6), a2 varchar(6));
Query OK, 0 rows affected (0.37 sec)

mysql> insert into aa values ('hugo', 'otto'), ('ball', 'test');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM aa WHERE (a1,a2)=('ball','test');
+------+------+
| a1   | a2   |
+------+------+
| ball | test | 
+------+------+
1 row in set (0.36 sec)

mysql>
[29 May 2008 15:25] Eugen Schülter
I also tested with the command line client without error. That's why I thougt in first place that it must be related to the new ODBC driver 5.1. Moving from 3.51 to 5.1 was the only change I made and it breaked my code. In any case, I have no problems with the workaround.

Is there any advantage using ODBC 5.1?
[30 May 2008 7:36] Tonci Grgin
Eugen, I'm still monitoring this report. Please clarify for me:
> I also tested with the command line client without error. That's why I thougt in first place that it must be related to the new ODBC driver 5.1. 
> Moving from 3.51 to 5.1 was the only change I made and it breaked my code. In any case, I have no problems with the workaround.

 o) You say you do not see the error in mysql cl client??? Can you please retest.
 o) How did you made the upgrade? Did you leave any commands like "SET NAMES" in init string (which is no-no with 5.1 as described in manual)? There might also be some other things in your code making the error appear.

Apparently I will need much more info from you as I can not repeat the problem on my box:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.60-pb1096-log MySQL Pushbuild Edition, build 1096

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM aa WHERE (a1,a2)=('ball','test');
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (la
tin1_swedish_ci,COERCIBLE) for operation '='
mysql>

+

test in Microsoft generic ODBC client (odbcte32w.exe):

	Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'test51'.
SELECT * FROM aa WHERE (a1,a2)=('ball','test');
SQLExecDirect:
				In:				hstmt = 0x00851FD0, szSqlStr = "", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"a1", "a2"
"ball", "test"
1 row fetched from 2 columns.

As you can see, although my server exhibits the problem, I am unable to repeat it from ODBC client.