| Bug #3438 | index affects select result | ||
|---|---|---|---|
| Submitted: | 11 Apr 2004 18:31 | Modified: | 4 May 2004 12:01 |
| Reporter: | Masaaki HIROSE | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.1-alpha-Max-log | OS: | Linux (Gentoo Linux) |
| Assigned to: | Michael Widenius | CPU Architecture: | Any |
[11 Apr 2004 19:21]
Masaaki HIROSE
0common
Attachment: 0common.sh (application/x-sh, text), 17 bytes.
[4 May 2004 11:58]
Michael Widenius
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
Additional info:
I did a test of this with the newest MySQL 4.1 tree and it worked, so I assume this bug has already been fixed.
Here is the test case I used (and I added it to our test suite):
drop table if exists t1;
create table t1 (c1 varchar(8)) default character set 'ujis';
insert into t1 values (0xA4A2),(0xA2A2),(0xA4A2);
select c1 as 'no index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis);
create index idx_c1 on t1(c1);
select c1 as 'using index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis);
select c1 as 'no index' from t1 where c1 like cast(concat('%',0xA4A2, '%') as char character set ujis);
drop table t1;
(I did check with EXPLAIN that the 'using index' query did use indexes)
Regards,
Monty
[4 May 2004 12:01]
Michael Widenius
Forgot to mention that the fix will be in the 4.1.2 release
Description: I got different select result with index or without index. This occured under - mysqld --default-character-set=utf8 - create table ... default character set 'ujis' - select using index for japanese character column. no problem in other character set combinations. - mysqld(utf8) - create table(ujis) ... NG - mysqld(utf8) - create table(utf8) ... OK - mysqld(ujis) - create table(ujis) ... OK - mysqld(ujis) - create table(utf8) ... OK no problem on query not using index. - where like 'CHAR%' ... NG - where like '%CHAR%' ... OK How to repeat: * legend - JA : japanese hiragana "A" in euc-jp (0xA4A2) - JI : japanese hiragana "I" in euc-jp (0xA2A2) run mysqld with --default-character-set=utf8 create table x_ujis ( c1 varchar(8) ) default character set 'ujis'; insert into x_ujis values ('JA') ,('JI') ,('JA') ; select c1 as 'no index' from x_ujis where c1 like 'JA%'; +------+ | no index | +------+ | JA | | JA | +------+ # OK. no problem. create index idx_c1 on x_ujis(c1); select c1 as 'using index' from x_ujis where c1 like 'JA%'; +------+ | using index | +------+ | JA | +------+ # NG. only 1 row. select c1 as 'not using index' from x_ujis where c1 like '%JA%'; +------+ | not using index | +------+ | JA | | JA | +------+ # OK. no problem.