Same pb with version 3.23.47 >Description: Following is the test reproduced in several databases (tables of around 7 millions records). Mysql compiled with GBK charset. A select used with a like 'alpha%' condition fails to report exact matches. Even if the select is completed by an "OR" field = 'alpha', it does not work. This is shown afterwards on latin characters, but the problem is exactly the same on Chinese characters.
Script started on Wed Jan 9 11:53:26 2002 [root@www bin]# mysql cyber Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4193 to server version: 3.23.37-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql> describe addrs; +---------+------------+-----+----+--------+--------------- | Field | Type | Null | Key | Default | Extra | +---------+------------+-----+----+--------+--------------- | addrid | int(11) | | PRI | NULL | auto_increment | | sex | char(1) | | | | | | cname | varchar(20) | | MUL | | | | pcd | varchar(6) | | MUL | | | | province | varchar(6) | | MUL | | | | city | varchar(20) | | MUL | | | | addr1 | varchar(20) | | MUL | | | | addr2 | varchar(30) | | | | | | rtimes | int(11) | | | 0 | | +---------+------------+-----+----+--------+--------------- 9 rows in set (0.00 sec) mysql> show index from addrs; +------+-----------+---------+-------------+------------+----------+- ---- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi +------+-----------+---------+-------------+------------+----------+- ---- | addrs | 0 | PRIMARY | 1 | addrid | A | | addrs | 1 | cname | 1 | cname | A | | addrs | 1 | pcd | 1 | pcd | A | | addrs | 1 | province | 1 | province | A | | addrs | 1 | city | 1 | city | A | | addrs | 1 | addr1 | 1 | addr1 | A | +------+-----------+---------+-------------+------------+----------+- ---- 6 rows in set (0.00 sec) mysql> select count(*) from addrs; +--------- | count(*) | +--------- | 7418283 | +--------- 1 row in set (0.00 sec) mysql> select * from addrs where cname = 'Mark' ; +--------+----+------+-------+---------+-----+-------------------+-- ---- | addrid | sex | cname | pcd | province | city | addr1 | addr2 +--------+----+------+-------+---------+-----+-------------------+-- ---- | 6561541 | | Mark | | Õã½Ê¡ | ÅÍ°² | ÏØ | | 7416179 | M | Mark | 518007 | ¹ã¶«Ê¡ | ÉîÛÚ | Éϲ½Â·Î÷²©µçÄÔ¹«Ë¾ | +--------+----+------+-------+---------+-----+-------------------+-- ---- 2 rows in set (0.00 sec) mysql> select * from addrs where cname like 'Mark'; +--------+----+------+-------+---------+-----+-------------------+-- ---- | addrid | sex | cname | pcd | province | city | addr1 | addr2 +--------+----+------+-------+---------+-----+-------------------+-- ---- | 6561541 | | Mark | | Õã½Ê¡ | ÅÍ°² | ÏØ | | 7416179 | M | Mark | 518007 | ¹ã¶«Ê¡ | ÉîÛÚ | Éϲ½Â·Î÷²©µçÄÔ¹«Ë¾ | +--------+----+------+-------+---------+-----+-------------------+-- ---- 2 rows in set (0.00 sec) mysql> select * from addrs where cname like 'Mark%'; Empty set (0.00 sec) mysql> select * from addrs where cname = 'Mark' or cname like 'Mark'; +--------+----+------+-------+---------+-----+-------------------+-- ---- | addrid | sex | cname | pcd | province | city | addr1 | addr2 +--------+----+------+-------+---------+-----+-------------------+-- ---- | 6561541 | | Mark | | Õã½Ê¡ | ÅÍ°² | ÏØ | | 7416179 | M | Mark | 518007 | ¹ã¶«Ê¡ | ÉîÛÚ | Éϲ½Â·Î÷²©µçÄÔ¹«Ë¾ | +--------+----+------+-------+---------+-----+-------------------+-- ---- 2 rows in set (0.00 sec) mysql> select * from addrs where cname = 'Mark' or cname like 'Mark%'; Empty set (0.00 sec) mysql> select * from addrs where cname like 'Mark' or cname like 'Mark%'; Empty set (0.00 sec) mysql> Bye [root@www bin]# mysqladmin variables; +--------------------------------+-------------------------- | Variable_name | Value | +--------------------------------+-------------------------- | ansi_mode | OFF | | back_log | 50 | | basedir | /usr/local/ | | binlog_cache_size | 32768 | | character_set | gbk | | character_sets | gbk | | concurrent_insert | ON | ...| | key_buffer_size | 16773120 | | language | /database//mysql/english/ | | large_files_support | ON | ...| version | 3.23.37-log | | wait_timeout | 28800 | +--------------------------------+-------------------------- [root@www bin]# >How-To-Repeat: To compile a large database with GBK charset. To try to select on a field with index with an exact match and %. select * from table where fieldname like 'alpha%' will fail to report the records where fieldname is exactly 'alpha' >Fix: not known >Submitter-Id: [EMAIL PROTECTED] >Originator: root >Organization: Shenzhen cyber data processing ltd. >MySQL support: none >Synopsis: like condition bug with charset=GBK >Severity: critical >Priority: high >Category: mysql >Class: sw-bug >Release: mysql-3.23.37 (Source distribution) >Server: /usr/local/bin/mysqladmin Ver 8.19 Distrib 3.23.37, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.37-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 10 days 18 hours 13 min 22 sec Threads: 5 Questions: 6208149 Slow queries: 1229 Opens: 83 Flush tables: 1 Open tables: 64 Queries per second avg: 6.678 >Environment: System: Linux www 2.4.2-2smp #1 SMP Sun Apr 8 20:21:34 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='' CXX='c' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 29 2001 /lib/libc.so.6 -> libc-2.2.2.so -rwxr-xr-x 1 root root 1236396 Apr 7 2001 /lib/libc-2.2.2.so -rw-r--r-- 1 root root 26350254 Apr 7 2001 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 7 2001 /usr/lib/libc.so Configure command: ./configure --datadir=/database/ --with-pthread --enable-thread-safe-client --with-charset=gbk --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
