Bug #29326 comparisons using row constructors throw collation erro
Submitted: 24 Jun 2007 23:46 Modified: 19 Dec 2008 8:09
Reporter: Peter Brawley (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.0.40, 5.1.19 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: qc
Triage: Triaged: D3 (Medium) / R2 (Low) / E3 (Medium)

[24 Jun 2007 23:46] Peter Brawley
This succeeds:

select * from mysql.user where host='localhost' and user='test';

but this ...

select * from mysql.user where (host,user) = ('localhost','test');

fails with ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='.

What user can be expected, reasonably, to anticipate that her mysql database, as it was delivered with her copy of MySQL, defaults to collation-incompatibility with the mysql client software delivered with her database!? 

Sec. of the manual says flatly:

"SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

The queries here are both TRUE if table t2 has a row where column1 = 1 and column2 = 2."

Nothing in the manual suggests that a comparison of (col1, col2) with (val1, val2) will fail where scalar comparisons of the same columns and values succeeds.

So is it a documentation error or a server error? Looks like the latter.

How to repeat:
As above

Suggested fix:
Avoid row constructors in comparison clauses, a serious drawback for writing general purpose queries. A drawback which ought not to be there.
[19 Dec 2008 8:09] Alexander Barkov
This is a duplicate for Bug#37601.

Although #29326 was reported earlier, I'm closing bug#29326
as a duplicate for bug#37601 (not vica versa).

Bug#37601 is tagged with higher importance.