>Description:
With latin1_de character set and non binary strings I get
results differnet from each other for queries that should IMHO
produce the same result sets.
Example (see test set below):
In a table containing
1 h�rt
2 hoert
3 h�rt
I expect as result for
mysql> select * from test where wort_alph = 'h�rt';
either rows 1 and 3 or all three rows, but definitely not row 1+2.
Analogous I expect the same behaviour for
mysql> select * from test where wort_alph = 'hoert';
After running
mysql> select * from test where wort_alph like 'hoert';
(which produces an empty result set!) I get all three results from
the first query.
Best regards,
Fabian Schmidt.
>How-To-Repeat:
create table test(
wort_nr mediumint(8) unsigned auto_increment not null default 0 primary key,
wort_alph varchar(32) not null default '',index (wort_alph)
);
insert into test (wort_alph) values ('h�rt');
insert into test (wort_alph) values ('hoert');
insert into test (wort_alph) values ('h�rt');
select * from test where wort_alph = 'h�rt' /*
+-------+------+---------------+-----------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+-----------+---------+-------+------+------------+
| test | ref | wort_alph | wort_alph | 32 | const | 2 | where used |
+-------+------+---------------+-----------+---------+-------+------+------------+
+---------+-----------+
| wort_nr | wort_alph |
+---------+-----------+
| 1 | h�rt |
| 2 | hoert |
+---------+-----------+
2 rows in set (0.00 sec) */;
select * from test where wort_alph = 'hoert' /*
+-------+------+---------------+-----------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+-----------+---------+-------+------+------------+
| test | ref | wort_alph | wort_alph | 32 | const | 2 | where used |
+-------+------+---------------+-----------+---------+-------+------+------------+
+---------+-----------+
| wort_nr | wort_alph |
+---------+-----------+
| 1 | h�rt |
| 2 | hoert |
| 3 | h�rt |
+---------+-----------+
3 rows in set (0.00 sec) */ ;
select * from test where wort_alph like 'h�rt' /*
+-------+-------+---------------+-----------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+-----------+---------+------+------+------------+
| test | range | wort_alph | wort_alph | 32 | NULL | 2 | where used |
+-------+-------+---------------+-----------+---------+------+------+------------+
+---------+-----------+
| wort_nr | wort_alph |
+---------+-----------+
| 1 | h�rt |
+---------+-----------+
1 row in set (0.00 sec) */ ;
select * from test where wort_alph like 'hoert' /*
Empty set (0.00 sec) */ ;
select * from test where wort_alph = 'h�rt' /*
+---------+-----------+
| wort_nr | wort_alph |
+---------+-----------+
| 1 | h�rt |
| 2 | hoert |
| 3 | h�rt |
+---------+-----------+
3 rows in set (0.00 sec) */ ;
>Fix:
wrong type of conversion in my_like_range_latin1_de or in
table index?
>Submitter-Id: <submitter ID>
>Originator:
>Organization:
>MySQL support: none
>Synopsis: wrong and nondeterministic results with latin1_de
>Severity: serious
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.0-alpha (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 8.21 Distrib 3.23.42, 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 4.0.0-alpha
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 hour 31 min 40 sec
Threads: 1 Questions: 46 Slow queries: 0 Opens: 13 Flush tables: 1 Open tables: 2
Queries per second avg: 0.008
>Environment:
System: Linux tech 2.4.4-4GB #1 Wed May 16 00:37:55 GMT 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/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc' CFLAGS='-O3 -mpentiumpro' CXX='gcc' CXXFLAGS='-O3
-mpentiumpro -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 1343073 Mai 11 2001 /lib/libc.so.6
-rw-r--r-- 1 root root 24539184 Mai 11 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Mai 11 2001 /usr/lib/libc.so
Configure command: ./configure --enable-assembler --with-charset=latin1_de
--with-extra-charsets=all --prefix=/usr/local/mysql4 --with-raid --without-debug
--without-innodb
---------------------------------------------------------------------
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
