>Description:
When creating an index with multiple text fields, `where' clause
isn't handled correctly. If no index is present, "a = 'foo' and
b = 'bar'" does work well. However, if an index is present,
"a = 'foo'" and "a = 'foo' and b != 'bar'" work correctly, but
"a = 'foo' and b = 'bar'" doesn't. Surprisingly enough, "a = 'foo'
and (b = 'bar') = 1" still works!
>How-To-Repeat:
create table test (a text, b text);
insert test values ('foo', 'bar');
insert test values ('foo', 'foo');
/* This works. */
select * from test where a = 'foo' and b = 'bar';
create index test_index on test (a(3), b(3));
/* This doesn't work. */
select * from test where a = 'foo' and b = 'bar';
/* But this works. */
select * from test where a = 'foo' and (b = 'bar') = 1;
>Fix:
Use STRCMP.
>Submitter-Id: <submitter ID>
>Originator:
>Organization:
>MySQL support: none
>Synopsis: indexed text fields are mishandled.
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.32 (Source distribution)
>Server: mysqladmin Ver 8.14 Distrib 3.23.32, for sun-solaris2.8 on sparc
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.32
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 2 hours 59 min 50 sec
Threads: 2 Questions: 190 Slow queries: 7 Opens: 47 Flush tables: 1 Open tables:
8 Queries per second avg: 0.018
>Environment:
System: SunOS space 5.8 Generic_108528-04 sun4u sparc SUNW,Ultra-80
Architecture: sun4
Some paths: /bin/perl /usr/local/bin/make /opt/sfw/bin/gmake /opt/sfw/bin/gcc
GCC: Reading specs from /opt/sfw/lib/gcc-lib/sparc-sun-solaris2.8/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='-O6 -I/usr/local/include' CXX='gcc'
CXXFLAGS='-O6 -I/usr/local/include -felide-constructors -fno-exceptions -fno-rtti'
LDFLAGS=''
LIBC:
-rw-r--r-- 1 root bin 1731920 1月 6日 2000年 /lib/libc.a
lrwxrwxrwx 1 root root 11 1月 23日 10:02 /lib/libc.so -> ./libc.so.1
-rwxr-xr-x 1 root bin 1113260 1月 6日 2000年 /lib/libc.so.1
-rw-r--r-- 1 root bin 1731920 1月 6日 2000年 /usr/lib/libc.a
lrwxrwxrwx 1 root root 11 1月 23日 10:02 /usr/lib/libc.so ->
./libc.so.1
-rwxr-xr-x 1 root bin 1113260 1月 6日 2000年 /usr/lib/libc.so.1
Configure command: ./configure --prefix=/usr/local/mysql --with-low-memory
--enable-assembler
Perl: This is perl, version 5.005_03 built for sun4-solaris
---------------------------------------------------------------------
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
