Re: Case insensitive search in utf8 db
Posted by: Peter Brawley
Date: November 30, 2019 05:32PM

You say I "set these 3 words as equal." I did not, you're misunderstanding the test code.

select @u=@l; does not set @u equal to @l. It tests their equality. Look it up. Or try it ...

set @u='ΓΙΑΝΝΗΣ', @l='Γιαννης', @d='Γιάννης';
select @u=@l, @u=@d, @l=@d;
select @u, @l, @d;

Likewise, select u=l from cs; does not set u equal to l, it tests their equality. Look it up.

Those lines are meant to find if I can replicate your problem with charset=utf8 and (i) user vars (ii) column values. As I said, I can't (unless I add collation=utf_general_cs to the db or table def---which would create the case sensitivity problem you report---but I've assumed you wouldn't deliberately set the table to case sensitivity then report case sensitivity as a problem).

Yes the next test code line is inadequate, but it doesn't change the above. To prove it, comment out all lines after the insert, and just add the line ...

select * from cs where u=l and l=d and u=d;

... then let's see the output from the whole script ...

create schema if not exists cs character set='utf8';
use cs;
show variables like 'character%';
show variables like 'collat%';
drop table if exists cs;
create table cs( u varchar(32), l varchar(32), d varchar(32) ) character set='utf8';
show create table cs\G
insert into cs values('ΓΙΑΝΝΗΣ', 'Γιαννης', 'Γιάννης');
select * from cs where u=l and l=d and u=d;

If the last line doesn't produce ...

| u              | l              | d              |
| ΓΙΑΝΝΗΣ        | Γιαννης        | Γιάννης        |

... you've proved there's a charset problem with your system. If it does produce the above, you've proved the problem is local to the db and/or table where you observed what you reported, so let's see the char* and coll* settings for that db, also Show Create Database and Show Create Table results for that db and table.

Edited 1 time(s). Last edit at 12/01/2019 01:39PM by Peter Brawley.

Options: ReplyQuote

Written By
November 29, 2019 01:14PM
Re: Case insensitive search in utf8 db
November 30, 2019 05:32PM

Sorry, you can't reply to this topic. It has been closed.

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.