>Description:
Very similar to a problem I reported more than 2 years ago, which
was then fixed in a subsequent release, it now appears that the index on
a column that is declared as decimal(14,2) is not being built correctly.
Dropping the index and re-creating it seems to fix the problem temporarily
but when new inserts elsewhere in the table are done, it begins to fail
again.
>How-To-Repeat:
My table definition looks like this:
+-----------+---------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+------------+----------------+
| claimid | int(11) | | PRI | NULL | auto_increment |
| client_id | varchar(10) | | MUL | | |
| pctlnum | varchar(20) | | | | |
| claimamt | decimal(14,2) | | | 0.00 | |
| dos | date | | | 0000-00-00 | |
| provid | varchar(14) | | | | |
| payerid | varchar(6) | | MUL | | |
| memid | varchar(20) | | | | |
| db_time | datetime | YES | | NULL | |
| db_oper | char(1) | | | | |
| db_user | varchar(12) | | | | |
| incatbox | char(1) | | | N | |
| loaded | date | | | 0000-00-00 | |
+-----------+---------------+------+-----+------------+----------------+
Note that a unique index exists on columns (client_id, pctlnum, claimamt,
dos, provid). Insert data. Make sure to insert 5 or 10 rows which have
identical keys in the multi-column index except that the dos column value
is different. Insert many other rows. Try to select rows using partial key,
e.g. Select
pctlnum, claimamt, dos, provid, payerid, memid
>From claim
Where client_id = 'ndabsolute' AND pctlnum = '000000021169'
and claimamt = 325.00;
You will find some but not all of the rows which match.
>Fix:
No work around or fix known.
>Submitter-Id: <submitter ID>
>Originator: Chris Johnson
>Organization:
>MySQL support: none
>Synopsis: Indexing of decimal columns fails to locate all rows.
>Severity: critical
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.47 (FreeBSD port: mysql-server-3.23.47)
>Environment:
System: FreeBSD cheetah.claimlynx.com 4.5-RELEASE FreeBSD 4.5-RELEASE #0: Sat Feb 16
16:21:47 CST 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/CHEETAH i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.3 20010315 (release) [FreeBSD]
Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='cc' CXXFLAGS='-O -pipe
-felide-constructors -fno-rtti -fno-exceptions' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1208074 Jan 28 07:11 /usr/lib/libc.a
lrwxrwxrwx 1 root wheel 9 Feb 16 09:58 /usr/lib/libc.so -> libc.so.4
-r--r--r-- 1 root wheel 573760 Jan 28 07:11 /usr/lib/libc.so.4
Configure command: ./configure --localstatedir=/var/db/mysql --without-perl
--without-debug --without-readline --without-bench --with-mit-threads=no
--with-libwrap --with-low-memory '--with-comment=FreeBSD port: mysql-server-3.23.47'
--enable-assembler --with-berkeley-db --with-innodb --prefix=/usr/local
i386--freebsd4.5
Perl: This is perl, version 5.005_03 built for i386-freebsd
---------------------------------------------------------------------
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
