Bug #2553 Index should not be used when a field collation <> operation collation

• Submitted: 29 Jan 2004 5:58
• Modified: 2 Feb 2004 5:56
• Reporter: Alexander Barkov
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.2-bk-current
• OS: Any (all)
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[29 Jan 2004 5:58] Alexander Barkov

Description:
Index should not be used if an operation's effective
collation is not the same with the field collation.

How to repeat:
Imagine a table:

CREATE TABLE t1 (a char(10) COLLATE latin1_german1_ci, key(a));
INSERT INTO t1 VALUES .....

1. This query should not use the key, because
the index is built in latin1_german1_ci order,
but the effective collation is latin1_swedish_ci:

EXPLAIN SELECT * FROM t1 WHERE a = 'a' COLLATE latin1_swedish_ci

2. But it the same time, this query should be optimized:
because the effective collation, according to the
coercibility rules,  is latin1_german1_ci, which is
the same with the index's collation.

SET collation_connection=latin1_swedish_ci;
EXPLAIN SELECT * FROM t1 WHERE a = 'a';

Suggested fix:
Change in optimizer to check the operation effective collation.

[2 Feb 2004 5:56] Alexander Barkov

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html