mjhans@stripped writes:
> >Description:
> There is a problem when comparing numeric(18,0) data with large
> values. Values are being returned as being equal when they are in fact
> not.
> >How-To-Repeat:
> Here is the SQL code:
> create table bad(a numeric(18,0), b numeric(18,0));
> create table good(a bigint, b bigint);
> insert into bad values (128352589380062897, 128352589380062896);
> insert into bad values (128352589380062896, 128352589380062895);
> insert into bad values (128352589380062896, 128352589380062896);
> insert into good values (128352589380062897, 128352589380062896);
> insert into good values (128352589380062896, 128352589380062895);
> insert into good values (128352589380062896, 128352589380062896);
> select a, b from bad where a=b;
> select a, b from good where a=b;
>
> Here is the output (note that "bad" returns 3 rows when it should return
> 1, which "good" does):
>
> mysql> select a, b from bad where a=b;
> +--------------------+--------------------+
> | a | b |
> +--------------------+--------------------+
> | 128352589380062897 | 128352589380062896 |
> | 128352589380062896 | 128352589380062895 |
> | 128352589380062896 | 128352589380062896 |
> +--------------------+--------------------+
> 3 rows in set (0.00 sec)
>
> mysql> select a, b from good where a=b;
> +--------------------+--------------------+
> | a | b |
> +--------------------+--------------------+
> | 128352589380062896 | 128352589380062896 |
> +--------------------+--------------------+
> 1 row in set (0.00 sec)
>
Hello!
The problem is that numeric is a floating point number,
which are never accurate. One should never compare floating
pint numbers with '='. But you can use 'like' instead, which
will work.
Yours,
- Jani
--
+---------------------------------------------------------------------+
| TcX ____ __ _____ _____ ___ == mysql@stripped |
| /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Jani Tolonen |
| /*/ /*/ /*/ \*\_ |*| |*||*| mailto: jani@stripped |
| /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Helsinki |
| /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|_____ Finland |
| ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ |
| /*/ \*\ Developers Team |
+---------------------------------------------------------------------+
> >Fix:
> Using bigint is a workaround.
>
> >Submitter-Id: <submitter ID>
> >Originator: Matthew Hanselman
> >Organization: Quiq Incorporated
> - Matt
>
> >
> >MySQL support: none
> >Synopsis: Error comparing equality with numeric(18,0)
> >Severity: serious
> >Priority: low
> >Category: mysql
> >Class: sw-bug
> >Release: mysql-3.22.16a-gamma (TCX binary)
> >Server: /usr/local/bin/mysqladmin Ver 7.8 Distrib 3.22.16a-gamma, for
pc-linux-gnu on i686
> TCX Datakonsult AB, by Monty
>
> Server version 3.22.16a-gamma
> Protocol version 10
> Connection Localhost via UNIX socket
> UNIX socket /tmp/mysql.sock
> Uptime: 1 day 2 hours 5 min 0 sec
>
> Threads: 15 Questions: 57389 Slow queries: 0 Opens: 266 Flush tables: 1 Open
tables: 54
> >Environment:
>
> System: Linux webserver.ehives.com 2.2.6 #1 Sat Apr 24 14:09:54 CDT 1999 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.7.2.3/specs
> gcc version 2.7.2.3
> Compilation info: CC='gcc' CFLAGS='-O6 -mpentium -fomit-frame-pointer' CXX='gcc'
CXXFLAGS='-O6 -mpentium -fomit-frame-pointer -felide-constructors' LDFLAGS='-static'
> Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=TCX
binary' --enable-assembler --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --disable-shared
> Perl: This is perl, version 5.004_05 built for i386-linux
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread3078@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
