| 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 | ||
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.028 sec. using MySQL 8.0.36-u4-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
© 2024, Oracle Corporation and/or its affiliates
Description: 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. 13.2.8.5. 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.