Bug #20732 Partial index and long sjis search with '>' fails sometimes
Submitted: 27 Jun 2006 20:48 Modified: 13 Nov 2006 18:55
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:Any (all)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[27 Jun 2006 20:48] Peter Gulutzan
Description:
I create a table with a long SJIS column.
The table has a partial index with maximum length = 500.
There is no error during creation.
I insert a long string containing Katakana characters.
I try to search against this string using '>'.
The search fails.

How to repeat:
mysql> create table t88 (s1 varchar(600) character set sjis, key(s1(500)));
Query OK, 0 rows affected (0.33 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t88 values ('a'),('b'),('c'),(repeat('グ',600));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select count(*) from t88 where s1 = repeat('グ',600);
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from t88 where s1 > repeat('グ',500);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
[28 Jun 2006 16:35] MySQL Verification Team
Thank you for the bug report.
[28 Sep 2006 6:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12679

ChangeSet@1.2539, 2006-09-28 11:43:40+05:00, ramil@mysql.com +3 -0
  Fix for bug #20732: Partial index and long sjis search with '>' fails sometimes
  
  We miss some records sometimes using RANGE method if we have 
  partial key segments.
  Example:
    create table t1(a char(2), key(a(1)));                                          
    insert into t1 values ('a'), ('xx');                                            
    select a from t1 where a > 'x';                                                 
  We call index_read() passing 'x' key and HA_READ_AFTER_KEY flag
  in the handler::read_range_first() wich is wrong because we have 
  a partial key segment for the field and might miss records like 'xx'.
  
  Fix: don't use open segments in such a case.
[19 Oct 2006 7:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13940

ChangeSet@1.2578, 2006-10-19 12:52:37+05:00, ramil@mysql.com +6 -0
  Fix for bug #20732: Partial index and long sjis search with '>' fails sometimes
  
  We miss some records sometimes using RANGE method if we have
  partial key segments.
  Example:
    Create table t1(a char(2), key(a(1)));
    insert into t1 values ('a'), ('xx');
    select a from t1 where a > 'x';
  We call index_read() passing 'x' key and HA_READ_AFTER_KEY flag
  in the handler::read_range_first() wich is wrong because we have
  a partial key segment for the field and might miss records like 'xx'.
  
  Fix: don't use open segments in such a case.
[13 Nov 2006 18:55] Paul DuBois
Noted in 4.1.23, 5.0.30 (5.0.29), 5.1.13 changelogs.

Range searches on columns with an index prefix could miss records.