List:General Discussion« Previous MessageNext Message »
From:Jani Tolonen Date:May 10 1999 8:46am
Subject:Error comparing equality with numeric(18,0)
View as plain text  
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.

Thread
Error comparing equality with numeric(18,0)mjhans8 May
  • Error comparing equality with numeric(18,0)Jani Tolonen10 May
  • Re: Error comparing equality with numeric(18,0)Fred Read10 May
    • Re: Error comparing equality with numeric(18,0)Jani Tolonen11 May