MySQL Bugs: #8847: LIKE or '=' in select - statement is always case sensitive

Bug #8847	LIKE or '=' in select - statement is always case sensitive
Submitted:	28 Feb 2005 16:01	Modified:	1 Apr 2005 15:52
Reporter:	Harald Gölles	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.3-alpha (bk 27.02.05)	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
i filed a new bug as my comment seems not to show up and i don't think the answer is correct.
(original bug is #8832).

problem:

like should not be case sensitive:

>> http://dev.mysql.com/doc/mysql/en/string-comparison-functions.html
>> 
>>  The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:
>> 
>> mysql> SELECT 'abc' LIKE 'ABC';
>>         -> 1
>> mysql> SELECT 'abc' LIKE BINARY 'ABC';
>>         -> 0

when you try the above select, you don't get the expected result:

mysql> SELECT 'abc' LIKE 'ABC';
+------------------+
| 'abc' LIKE 'ABC' |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)
 
the same happens with 
mysql> SELECT 'abc' = 'ABC';
+---------------+
| 'abc' = 'ABC' |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

so, if it is no bug, how shall you do an case - insensitive comparison??

SELECT UCASE('abc') LIKE UCASE('ABC'); ?????

How to repeat:
SELECT 'abc' LIKE 'ABC';
or 
SELECT 'abc' = 'ABC';

Do you mean you were not able to add a comment to the bug#8832 ?

Anyway, the main question is the same: what does

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

return ?

i changed collation_server to _ci and it works now...

i still not unterstand why collation affects comparison:
shouldn't it only be used for sorting and hyphenation?

i also don't unterstand why i had to change collation_server when collation_connection and collation_database where already set to _ci?

btw, not a bug but something that remains unclear for me:
1: why is collation_connection needed? wouldn't collation_database be sufficient? what has precedence if I do a ORDER BY? in my opinion, collation_connection doesn't make sense when the database has a different collation (as text should always be sorted according to the locale of the data itself).

mysql> set collation_server=latin1_general_ci
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'A' like 'a';
+--------------+
| 'A' like 'a' |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

ps: my settings:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | latin1                            |
| character_set_connection | latin1                            |
| character_set_database   | latin1                            |
| character_set_results    | latin1                            |
| character_set_server     | latin1                            |
| character_set_system     | utf8                              |
| character_sets_dir       | /var/mysql5/share/mysql/charsets/ |
+--------------------------+-----------------------------------+
7 rows in set (0.02 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_general_cs |
+----------------------+-------------------+
3 rows in set (0.00 sec)

select col1 from t1 where col1 like 'A' order by col1

how can I 
- compare case - insensitive
- sort case sensitive

when collation affects both?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".